Defining Tables for PostgreSQL

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

A complete table definition showing columns and indexes. View the public.actor table on GitHub .

{
  "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 (with force option)
Table Access Method and Persistence Type (Logged/Unlogged)
Should Apply Expressions
All column data types
Column expressions and stored/virtual generated columns (PG 17+)
Column storage method (TOAST) and compression (pglz/lz4)
Indexes with NullsNotDistinct and deferrable support
Foreign keys with MatchType and deferrable support
Check constraints (deferrable)
Exclude constraints
Statistics (ndistinct, dependencies, mcv)
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.

Property Reference

Complete reference for all table definition components. Each section documents the JSON properties available for that component type.

Table Properties

Property	Type	Description
`Schema`	string	Schema name. Defaults to `public`.
`Name`	string	Table name Required
`AccessMethod`	string	Table access method. Defaults to `heap`. Other options depend on installed extensions (e.g., `columnar`).
`PersistenceType`	string	Table persistence: `Logged` (default, WAL-logged) or `Unlogged` (faster writes, not crash-safe, not replicated).
`RowLevelSecurity`	boolean	Enable row-level security policies on this table.
`ForceRowLevelSecurity`	boolean	Force RLS policies to apply to the table owner as well. Without this, table owners bypass RLS.
`UpdateFillFactor`	boolean	Allow SchemaQuench to update fill factor on indexes for this table.
`FillFactor`	integer	Table-level fill factor (0-100). `0` uses the server default.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this table. See Should Apply Expression.
`OldName`	string	Previous table name for renaming. See Renaming Tables.
`ContentFile`	string	Path to the data file for data delivery. Supports CSV and JSON formats.
`MergeType`	string	How data is merged: `MergeInsertOnly`, `MergeInsertUpdate`, `MergeInsertUpdateDelete`, or `Truncate`. See Data Delivery.
`MatchColumns`	string	Comma-separated columns used to match source rows to target rows during merge.
`MergeFilter`	string	WHERE clause to limit which target rows are affected by the merge.
`MergeDisableTriggers`	boolean	Disable triggers during data delivery. Defaults to `false`.
`MergeDisableRules`	boolean	Disable rules during data delivery. Defaults to `false`.
`MergeUpdateDescendents`	boolean	Update descendent tables after merge. Defaults to `false`.

Column Properties

Property	Type	Description
`Name`	string	Column name Required
`DataType`	string	PostgreSQL data type (e.g., `int4`, `varchar(50)`, `timestamp`) Required
`Nullable`	boolean	Allow NULL values. Defaults to `false`.
`Default`	string	Default value expression (e.g., `now()`, `nextval('seq'::regclass)`)
`Collation`	string	Column-level collation override
`Generated`	string	Generation type: `NEVER` (regular column) or `ALWAYS` (generated column)
`GenerationExpression`	string	Expression for generated columns (e.g., `first_name \|\| ' ' \|\| last_name`)
`Virtual`	boolean	`false` = stored/materialized (default), `true` = virtual, computed on read. Requires PostgreSQL 17+.
`Storage`	string	TOAST storage strategy: `PLAIN` (no TOAST), `MAIN` (compress in-line), `EXTERNAL` (out-of-line, no compression), `EXTENDED` (out-of-line with compression, default for variable-length types)
`Compression`	string	Column compression method: `pglz`, `lz4`, or `DEFAULT`. Requires PostgreSQL 14+ for lz4.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this column. See Should Apply Expression.
`OldName`	string	Previous column name for renaming. See Renaming Columns.

Index Properties

Property	Type	Description
`Name`	string	Index name Required
`PrimaryKey`	boolean	Whether this index backs the primary key constraint.
`Unique`	boolean	Enforce uniqueness on the indexed columns.
`UniqueConstraint`	boolean	Whether this is a unique constraint (as opposed to a unique index).
`Clustered`	boolean	Cluster the table on this index (physically reorders rows).
`IndexColumns`	string	Comma-separated indexed columns. Supports `ASC`/`DESC` and `NULLS FIRST`/`NULLS LAST` per column. Required
`IncludeColumns`	string	Comma-separated non-key columns to include in the index (covering index).
`AccessMethod`	string	Index access method: `btree` (default), `hash`, `gist`, `gin`, `spgist`, `brin`
`FilterExpression`	string	WHERE clause for a partial index (e.g., `is_active = true`).
`FillFactor`	integer	Percentage of each leaf-level page to fill (0-100). `0` uses the server default.
`NullsNotDistinct`	boolean	Treat NULL values as equal for uniqueness checks. By default, PostgreSQL treats NULLs as distinct. Requires PostgreSQL 15+.
`Deferrable`	boolean	Allow the unique constraint check to be deferred until transaction commit.
`InitiallyDeferred`	boolean	When `Deferrable` is `true`, defer the constraint check by default (not just when explicitly requested).
`UpdateFillFactor`	boolean	Allow SchemaQuench to update this index's fill factor.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this index.

Foreign Key Properties

Property	Type	Description
`Name`	string	Foreign key constraint name Required
`Columns`	string	Comma-separated columns in this table Required
`RelatedTableSchema`	string	Schema of the referenced table. Defaults to `public`.
`RelatedTable`	string	Referenced table name Required
`RelatedColumns`	string	Comma-separated columns in the referenced table Required
`DeleteAction`	string	Referential action on delete: `NO ACTION`, `RESTRICT`, `CASCADE`, `SET NULL`, `SET DEFAULT`
`UpdateAction`	string	Referential action on update: `NO ACTION`, `RESTRICT`, `CASCADE`, `SET NULL`, `SET DEFAULT`
`MatchType`	string	Foreign key match type: `FULL` (default), `PARTIAL`, or `SIMPLE`. Controls how NULLs in composite foreign keys are handled.
`Deferrable`	boolean	Allow the FK check to be deferred until transaction commit. Useful for circular references or bulk loads.
`InitiallyDeferred`	boolean	When `Deferrable` is `true`, defer the FK check by default.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this foreign key.

Check Constraint Properties

Property	Type	Description
`Name`	string	Constraint name Required
`Expression`	string	CHECK expression (e.g., `end_date > start_date`) Required
`Deferrable`	boolean	Allow the check to be deferred until transaction commit.
`InitiallyDeferred`	boolean	When `Deferrable` is `true`, defer the check by default.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this constraint.

Exclude Constraint Properties

Exclusion constraints ensure that two rows cannot have overlapping values for specified columns using a given operator. Common with range types and GiST indexes.

Property	Type	Description
`Name`	string	Exclude constraint name Required
`AccessMethod`	string	Index access method for the constraint (e.g., `gist`).
`ExcludeColumns`	array	Array of objects, each with `Column` (column name or expression) and `Operator` (e.g., `=`, `&&`) Required
`FilterExpression`	string	WHERE clause to limit which rows the constraint applies to.
`Deferrable`	boolean	Allow the exclusion check to be deferred until transaction commit.
`InitiallyDeferred`	boolean	When `Deferrable` is `true`, defer the check by default.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this constraint.

Statistics Properties

Property	Type	Description
`Name`	string	Statistics object name Required
`Kind`	string	Statistics kind: `ndistinct`, `dependencies`, or `mcv` (most common values)
`StatisticsColumns`	string	Comma-separated columns for the statistics object Required
`ShouldApplyExpression`	string	Conditional expression to include/exclude these statistics.

Additional Resources

Forge Deeper with SchemaQuench. Deep dive Product Demos - Sample databases converted to the SchemaSmith metadata format. Examples Product Folder Structure - See the basic layout on disk for your product. Reference Custom Properties - Extend your domain seamlessly into your model. Reference Data Delivery - Making data deployment easier. Reference

Also available for SQL Server and MySQL

Defining Tables - PostgreSQL (Enterprise)