Multiple schema in a Netezza database

Reading Time: 2 minutes

Starting from NPS V7.0.3 we can now create multiple schemas in a database.

When we enable this option (system wide setting and by default this setting is disabled), we can choose to either throw error when invalid schema is referenced in any query or throw warning and use default schema. Only admin or users with create/alter schema can create or make changes to schema.

In previous releases we used to have only one schema in a database and that is the database owner schema i.e. user who created the database becomes the default schema.

How to enable multiple schema mode:
To enable multiple schema support we have to edit /nz/data/postgresql.conf and look for parameter enable_schema_dbo_check. We have to uncomment this option and set it to 0, 1 or 2.
0 – Do not enable multiple schema support. Default value.
1 – Enable multiple schema but with limited support. In this case if someone uses invalid schema then system will throw warning and will use Default schema instead.
2 – Enable multiple schema in full mode and system will throw error if invalid schema is referenced.

Once the changes are saved in postgresql.conf issue nzstop and nzstart for the change to take effect.

To disable multi schema mode just set the value to 0 or comment out enable_schema_dbo_check option in postgresql.conf file. When you disable schema users can still reference schemas in the queries but will not be able to make any changes to the schema.