Defining Tables for PostgreSQL - Enterprise

Overview

Tables are defined via JSON files in the tables folder of your product in the form <Schema name.Table name.json>. After a little use, the format will feel very comfortable. Arrays of objects are used to define collections like Columns, Indexes and ForeignKeys. Each object in the collection has the properties you would expect.

Columns have required properties like Name and DataType, and optional properties like Nullable or Default. Indexes and Foreign Keys are similar.

Example

An example of a fairly complex table definition can be seen by looking at the DVDRental public.actor table.

{
  "Schema": "public",
  "Name": "actor",
  "Columns": [
    {
      "Name": "actor_id",
      "DataType": "int4",
      "Nullable": false,
      "Default": "nextval('actor_actor_id_seq'::regclass)",
      "Generated": "NEVER",
      "Storage": "PLAIN",
      "Compression": "DEFAULT",
      "ShouldApplyExpression": "",
      "OldName": ""
    },
    {
      "Name": "first_name",
      "DataType": "varchar(45)",
      "Nullable": false,
      "Generated": "NEVER",
      "Storage": "EXTENDED",
      "Compression": "DEFAULT",
      "ShouldApplyExpression": "",
      "OldName": ""
    },
    {
      "Name": "last_name",
      "DataType": "varchar(45)",
      "Nullable": false,
      "Generated": "NEVER",
      "Storage": "EXTENDED",
      "Compression": "DEFAULT",
      "ShouldApplyExpression": "",
      "OldName": ""
    },
    {
      "Name": "last_update",
      "DataType": "timestamp",
      "Nullable": false,
      "Default": "now()",
      "Generated": "NEVER",
      "Storage": "PLAIN",
      "Compression": "DEFAULT",
      "ShouldApplyExpression": "",
      "OldName": ""
    }
  ],
  "Indexes": [
    {
      "Name": "actor_pkey",
      "PrimaryKey": true,
      "Unique": true,
      "UniqueConstraint": false,
      "Clustered": false,
      "IndexColumns": "actor_id",
      "AccessMethod": "btree",
      "ShouldApplyExpression": ""
    },
    {
      "Name": "idx_actor_last_name",
      "PrimaryKey": false,
      "Unique": false,
      "UniqueConstraint": false,
      "Clustered": false,
      "IndexColumns": "last_name",
      "AccessMethod": "btree",
      "ShouldApplyExpression": ""
    }
  ],
  "ForeignKeys": [],
  "CheckConstraints": [],
  "ShouldApplyExpression": "",
  "ContentFile": "Table Contents/public.actor.tabledata",
  "MergeType": "Insert/Update",
  "MergeDisableTriggers": false,
  "MergeDisableRules": false,
  "MergeUpdateDescendents": false,
  "OldName": "",
  "RowLevelSecurity": false,
  "ForceRowLevelSecurity": false,
  "AccessMethod": "heap",
  "PersistenceType": "Logged"

Supported Concepts

Schema
Row Level Security
Table Access Method
Table Persistence Type
Should Apply Expressions
All column data types
Column expressions
Stored generated columns
Column storage method
Column compression
Indexes
Foreign keys
Table level check constraints
Custom Table Properties fully supported at the table level and all components
Data Delivery

Tip

If you do not require a property, for example, column compression, do not include it in the definition. In most cases, as long it is not something required, like name or data type, you can reasonably expect it to default as you would think.

Renaming Tables

To rename a table, first, rename your table json file to match your new table name. Within the metadata file add a property named OldName with the current name. Finally, change the Name property to be your new name. SchemaQuench will use this to rename the table when the current name does not yet exist and the old name is still present. After the rename has occurred in all of your environments, you can safely remove the OldName property.

Renaming Columns

Similar to renaming a table, add an OldName property with the current column name. Change the Name property to your new column name. After SchemaQuench has run in all of your environments and the rename has been rolled out, you can safely remove the OldName property.

Tip

SchemaHammer does this for you behind the scenes when you to do either a table or column rename.

Should Apply Expression

You can define an expression that must be satisfied in order to deploy a given table or component. This can be useful for things like specific version requirements for an index or data type. You could define the same index or column in two ways specific to the server version or compatibility level and set the expression so that only the supported one is deployed on the version of the server being updated. You could use the expression to define environment-specific or even customer-specific indexing or tables. You could also use this feature to define items you want to track for documentation or custom attribute purposes but not actually apply to your database.

Defining Tables