Templates for PostgreSQL

Overview

Each Template.json file targets one logical group of databases on PostgreSQL. For the full field reference, quench slot pipeline, and template ordering rules that apply across all platforms, see Products & Templates.

This page covers PostgreSQL Enterprise-specific features: the pg_database identification pattern, default folder conventions with spaces in names, Views assigned to the AfterTablesObjects slot, and index-only deployments for third-party databases.

PostgreSQL Example

From the SchemaSmith PostgreSQL Enterprise Demos, a template targeting a single database with custom script folders:

{
  "Name": "Main",
  "DatabaseIdentificationScript": "SELECT datname FROM pg_database WHERE datistemplate = false AND datname = '{{MainDB}}'",
  "VersionStampScript": "DO $$\nBEGIN\nRAISE NOTICE '{{ReleaseVersion}}';\nEND;\n$$;",
  "ScriptTokens": {
    "TestTableData": "<*File*>Tables/public.TestTable - MoreChildren.data",
    "TemplateQueryToken": "<*Query*>SELECT 'true'"
  },
  "ScriptFolders": [
    { "FolderPath": "Before Scripts",  "QuenchSlot": "Before" },
    { "FolderPath": "Schemas",         "QuenchSlot": "Objects" },
    { "FolderPath": "Domain Types",    "QuenchSlot": "Objects" },
    { "FolderPath": "Functions",       "QuenchSlot": "Objects" },
    { "FolderPath": "Procedures",      "QuenchSlot": "Objects" },
    { "FolderPath": "Triggers",        "QuenchSlot": "AfterTablesObjects" },
    { "FolderPath": "Views",           "QuenchSlot": "AfterTablesObjects" },
    { "FolderPath": "After Scripts",   "QuenchSlot": "After" }
  ]
}

PostgreSQL Notes

PostgreSQL Identification

PostgreSQL templates use DatabaseIdentificationScript to query pg_database with datistemplate = false to exclude system template databases. The {{MainDB}} token is resolved from script tokens at runtime.

16 Default Folders

PostgreSQL Enterprise templates default to 16 script folders including PostgreSQL-specific types: Domain Types, Extensions, Materialized Views, and Sequences. See Configurable Script Folders.

Views in AfterTablesObjects

On PostgreSQL, Views are assigned to the AfterTablesObjects quench slot by default (alongside Triggers), rather than the Objects slot used on SQL Server. This ensures views that depend on table columns are created after table quenching completes.

Folder Names with Spaces

PostgreSQL default folder names use spaces (e.g., "Before Scripts", "After Scripts", "Domain Types") rather than the single-word or CamelCase conventions used on other platforms. Custom ScriptFolders can use any naming convention you prefer.

Index Only Table Quenches

When IndexOnlyTableQuenches is true, SchemaQuench skips all structural table changes (column additions, modifications, and removals) and only processes index definitions. Table definitions in the Tables/ folder are still loaded and parsed, but only index creation and modification DDL is generated.

Use Case

Third-Party Databases

When maintaining indexes on a third-party database where you do not control the table structure, use index-only mode to manage custom indexes without risking modifications to vendor-owned columns or constraints.

Configuration

Enable index-only mode in your Template.json:

{
  "Name": "ThirdPartyIndexes",
  "DatabaseIdentificationScript": "SELECT datname FROM pg_database WHERE datistemplate = false AND datname = '{{VendorDb}}'",
  "IndexOnlyTableQuenches": true
}

Table Definition

Tables in an index-only template need only the Schema, Name, and Indexes properties:

{
  "Schema": "public",
  "Name": "TestSecondaryTable",
  "Indexes": [
    {
      "Name": "PK_TestSecondaryTable",
      "PrimaryKey": true,
      "IndexColumns": "TestSecondaryID"
    },
    {
      "Name": "CIX_DateCreated",
      "Clustered": true,
      "IndexColumns": "DateCreated"
    }
  ]
}

When this table is quenched, only the indexes are maintained. The table structure itself is managed through the vendor's process or some other means.

Update Fill Factor

The UpdateFillFactor template setting (default: true) controls whether SchemaQuench updates index fill factor values to match the definitions in your table JSON files. When enabled, any fill factor difference between the definition and the live index triggers a rebuild.

`true` (Default)

Fill factor values from your table definitions are enforced. Indexes with a different fill factor on the server are rebuilt to match.

`false`

Fill factor differences are ignored. Use this when DBAs tune fill factor per-server based on workload characteristics and you do not want schema deployments to override those adjustments.

Templates - PostgreSQL (Enterprise)