PostgreSQL syntax

Set "engine": "postgres" to write a version source for PostgreSQL. Everything in shared syntax applies; this page covers what is specific to PostgreSQL.

{
  "engine": "postgres",
  "name": "example-db",
  "version": [
    {
      "_id": "1.0.0",
      "createtable": {
        "users": {
          "primary_key": "id",
          "id": { "type": "INTEGER", "generated": "ALWAYS" },
          "email": { "type": "VARCHAR", "length": 255, "unique": true },
          "settings": { "type": "JSONB", "null": true },
          "created_at": { "type": "TIMESTAMPTZ", "default": "NOW()" }
        }
      }
    }
  ]
}

Column types

Types are written in uppercase.

Length

length is used for types that take a size, such as VARCHAR. It’s ignored for types without a size, such as INTEGER, TEXT, BOOLEAN, JSONB, UUID and the date and time types. NUMERIC accepts a decimal length; other types need a whole number.

Identity columns

PostgreSQL doesn’t use auto_increment. To create an auto-incrementing column, set generated to one of these values:

• "ALWAYS": the database always generates the value. Inserting your own value fails.
• "BY DEFAULT": the database generates a value unless you provide one. Use this when default data inserts explicit IDs.

"id": { "type": "INTEGER", "generated": "BY DEFAULT" }

generated can’t be combined with "null": true, and can’t be used on text types such as VARCHAR and TEXT.

Boolean defaults

For BOOLEAN columns, default is written as true or false without quotes.

"active": { "type": "BOOLEAN", "default": true }

Postgres-only features

• Extensions: manage extensions with createextension, dropextension and alterextension.
• Partial indexes: add a condition to an index, in createtable or with altertable.
• Modifying columns: AlphaDB reads the current column type from earlier versions, so you can change attributes without repeating type.