A Definition is a statement that changes the schema of the database, like a createTable, dropTable, or alterTable command. Definitions may be composed using the >>> operator.

Left-to-right composition

createTable adds a table to the schema.

>>> :set -XOverloadedLabels
>>> :{
type Table = '[] :=>
  '[ "a" ::: 'NoDef :=> 'Null 'PGint4
   , "b" ::: 'NoDef :=> 'Null 'PGfloat4 ]
:}

>>> :{
let
  setup :: Definition '[] '["tab" ::: 'Table Table]
  setup = createTable #tab
    (nullable int `as` #a :* nullable real `as` #b) Nil
in printSQL setup
:}
CREATE TABLE "tab" ("a" int NULL, "b" real NULL);

createTableIfNotExists creates a table if it doesn't exist, but does not add it to the schema. Instead, the schema already has the table so if the table did not yet exist, the schema was wrong. createTableIfNotExists fixes this. Interestingly, this property makes it an idempotent in the Category of Definitions.

>>> :set -XOverloadedLabels -XTypeApplications
>>> :{
type Table = '[] :=>
  '[ "a" ::: 'NoDef :=> 'Null 'PGint4
   , "b" ::: 'NoDef :=> 'Null 'PGfloat4 ]
:}

>>> type Schema = '["tab" ::: 'Table Table]

>>> :{
let
  setup :: Definition Schema Schema
  setup = createTableIfNotExists #tab
    (nullable int `as` #a :* nullable real `as` #b) Nil
in printSQL setup
:}
CREATE TABLE IF NOT EXISTS "tab" ("a" int NULL, "b" real NULL);

Data types are a way to limit the kind of data that can be stored in a table. For many applications, however, the constraint they provide is too coarse. For example, a column containing a product price should probably only accept positive values. But there is no standard data type that accepts only positive numbers. Another issue is that you might want to constrain column data with respect to other columns or rows. For example, in a table containing product information, there should be only one row for each product number. TableConstraints give you as much control over the data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error is raised. This applies even if the value came from the default value definition.

A check constraint is the most generic TableConstraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression.

>>> :{
type Schema = '[
  "tab" ::: 'Table ('[ "inequality" ::: 'Check '["a","b"]] :=> '[
    "a" ::: 'NoDef :=> 'NotNull 'PGint4,
    "b" ::: 'NoDef :=> 'NotNull 'PGint4
  ])]
:}

>>> :{
let
  definition :: Definition '[] Schema
  definition = createTable #tab
    ( (int & notNullable) `as` #a :*
      (int & notNullable) `as` #b )
    ( check (#a :* #b) (#a .> #b) `as` #inequality )
:}

>>> printSQL definition
CREATE TABLE "tab" ("a" int NOT NULL, "b" int NOT NULL, CONSTRAINT "inequality" CHECK (("a" > "b")));

A unique constraint ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table.

>>> :{
type Schema = '[
  "tab" ::: 'Table( '[ "uq_a_b" ::: 'Unique '["a","b"]] :=> '[
    "a" ::: 'NoDef :=> 'Null 'PGint4,
    "b" ::: 'NoDef :=> 'Null 'PGint4
  ])]
:}

>>> :{
let
  definition :: Definition '[] Schema
  definition = createTable #tab
    ( (int & nullable) `as` #a :*
      (int & nullable) `as` #b )
    ( unique (#a :* #b) `as` #uq_a_b )
:}

>>> printSQL definition
CREATE TABLE "tab" ("a" int NULL, "b" int NULL, CONSTRAINT "uq_a_b" UNIQUE ("a", "b"));

A primaryKey constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null.

>>> :{
type Schema = '[
  "tab" ::: 'Table ('[ "pk_id" ::: 'PrimaryKey '["id"]] :=> '[
    "id" ::: 'Def :=> 'NotNull 'PGint4,
    "name" ::: 'NoDef :=> 'NotNull 'PGtext
  ])]
:}

>>> :{
let
  definition :: Definition '[] Schema
  definition = createTable #tab
    ( serial `as` #id :*
      (text & notNullable) `as` #name )
    ( primaryKey #id `as` #pk_id )
:}

>>> printSQL definition
CREATE TABLE "tab" ("id" serial, "name" text NOT NULL, CONSTRAINT "pk_id" PRIMARY KEY ("id"));

A foreignKey specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.

>>> :{
type Schema =
  '[ "users" ::: 'Table (
       '[ "pk_users" ::: 'PrimaryKey '["id"] ] :=>
       '[ "id" ::: 'Def :=> 'NotNull 'PGint4
        , "name" ::: 'NoDef :=> 'NotNull 'PGtext
        ])
   , "emails" ::: 'Table (
       '[  "pk_emails" ::: 'PrimaryKey '["id"]
        , "fk_user_id" ::: 'ForeignKey '["user_id"] "users" '["id"]
        ] :=>
       '[ "id" ::: 'Def :=> 'NotNull 'PGint4
        , "user_id" ::: 'NoDef :=> 'NotNull 'PGint4
        , "email" ::: 'NoDef :=> 'Null 'PGtext
        ])
   ]
:}

>>> :{
let
  setup :: Definition '[] Schema
  setup =
   createTable #users
     ( serial `as` #id :*
       (text & notNullable) `as` #name )
     ( primaryKey #id `as` #pk_users ) >>>
   createTable #emails
     ( serial `as` #id :*
       (int & notNullable) `as` #user_id :*
       (text & nullable) `as` #email )
     ( primaryKey #id `as` #pk_emails :*
       foreignKey #user_id #users #id
         OnDeleteCascade OnUpdateCascade `as` #fk_user_id )
in printSQL setup
:}
CREATE TABLE "users" ("id" serial, "name" text NOT NULL, CONSTRAINT "pk_users" PRIMARY KEY ("id"));
CREATE TABLE "emails" ("id" serial, "user_id" int NOT NULL, "email" text NULL, CONSTRAINT "pk_emails" PRIMARY KEY ("id"), CONSTRAINT "fk_user_id" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE);

A foreignKey can even be a table self-reference.

>>> :{
type Schema =
  '[ "employees" ::: 'Table (
       '[ "employees_pk"          ::: 'PrimaryKey '["id"]
        , "employees_employer_fk" ::: 'ForeignKey '["employer_id"] "employees" '["id"]
        ] :=>
       '[ "id"          :::   'Def :=> 'NotNull 'PGint4
        , "name"        ::: 'NoDef :=> 'NotNull 'PGtext
        , "employer_id" ::: 'NoDef :=>    'Null 'PGint4
        ])
   ]
:}

>>> :{
let
  setup :: Definition '[] Schema
  setup =
   createTable #employees
     ( serial `as` #id :*
       (text & notNullable) `as` #name :*
       (integer & nullable) `as` #employer_id )
     ( primaryKey #id `as` #employees_pk :*
       foreignKey #employer_id #employees #id
         OnDeleteCascade OnUpdateCascade `as` #employees_employer_fk )
in printSQL setup
:}
CREATE TABLE "employees" ("id" serial, "name" text NOT NULL, "employer_id" integer NULL, CONSTRAINT "employees_pk" PRIMARY KEY ("id"), CONSTRAINT "employees_employer_fk" FOREIGN KEY ("employer_id") REFERENCES "employees" ("id") ON DELETE CASCADE ON UPDATE CASCADE);

A constraint synonym between types involved in a foreign key constraint.

OnDeleteClause indicates what to do with rows that reference a deleted row.

Render OnDeleteClause.

Analagous to OnDeleteClause there is also OnUpdateClause which is invoked when a referenced column is changed (updated).

Render OnUpdateClause.

dropTable removes a table from the schema.

>>> :{
let
  definition :: Definition '["muh_table" ::: 'Table t] '[]
  definition = dropTable #muh_table
:}

>>> printSQL definition
DROP TABLE "muh_table";

alterTable changes the definition of a table from the schema.

alterTableRename changes the name of a table from the schema.

>>> printSQL $ alterTableRename #foo #bar
ALTER TABLE "foo" RENAME TO "bar";

An AlterTable describes the alteration to perform on the columns of a table.

An addConstraint adds a table constraint.

>>> :{
let
  definition :: Definition
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]
    '["tab" ::: 'Table ('["positive" ::: Check '["col"]] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]
  definition = alterTable #tab (addConstraint #positive (check #col (#col .> 0)))
in printSQL definition
:}
ALTER TABLE "tab" ADD CONSTRAINT "positive" CHECK (("col" > 0));

A dropConstraint drops a table constraint.

>>> :{
let
  definition :: Definition
    '["tab" ::: 'Table ('["positive" ::: Check '["col"]] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]
  definition = alterTable #tab (dropConstraint #positive)
in printSQL definition
:}
ALTER TABLE "tab" DROP CONSTRAINT "positive";

An AddColumn is either NULL or has DEFAULT.

A dropColumn removes a column. Whatever data was in the column disappears. Table constraints involving the column are dropped, too. However, if the column is referenced by a foreign key constraint of another table, PostgreSQL will not silently drop that constraint.

>>> :{
let
  definition :: Definition
    '["tab" ::: 'Table ('[] :=>
       '[ "col1" ::: 'NoDef :=> 'Null 'PGint4
        , "col2" ::: 'NoDef :=> 'Null 'PGtext ])]
    '["tab" ::: 'Table ('[] :=> '["col1" ::: 'NoDef :=> 'Null 'PGint4])]
  definition = alterTable #tab (dropColumn #col2)
in printSQL definition
:}
ALTER TABLE "tab" DROP COLUMN "col2";

A renameColumn renames a column.

>>> :{
let
  definition :: Definition
    '["tab" ::: 'Table ('[] :=> '["foo" ::: 'NoDef :=> 'Null 'PGint4])]
    '["tab" ::: 'Table ('[] :=> '["bar" ::: 'NoDef :=> 'Null 'PGint4])]
  definition = alterTable #tab (renameColumn #foo #bar)
in printSQL definition
:}
ALTER TABLE "tab" RENAME COLUMN "foo" TO "bar";

An alterColumn alters a single column.

An AlterColumn describes the alteration to perform on a single column.

A setDefault sets a new default for a column. Note that this doesn't affect any existing rows in the table, it just changes the default for future insert and update commands.

>>> :{
let
  definition :: Definition
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'Def :=> 'Null 'PGint4])]
  definition = alterTable #tab (alterColumn #col (setDefault 5))
in printSQL definition
:}
ALTER TABLE "tab" ALTER COLUMN "col" SET DEFAULT 5;

A dropDefault removes any default value for a column.

>>> :{
let
  definition :: Definition
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'Def :=> 'Null 'PGint4])]
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
  definition = alterTable #tab (alterColumn #col dropDefault)
in printSQL definition
:}
ALTER TABLE "tab" ALTER COLUMN "col" DROP DEFAULT;

A setNotNull adds a NOT NULL constraint to a column. The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.

>>> :{
let
  definition :: Definition
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]
  definition = alterTable #tab (alterColumn #col setNotNull)
in printSQL definition
:}
ALTER TABLE "tab" ALTER COLUMN "col" SET NOT NULL;

A dropNotNull drops a NOT NULL constraint from a column.

>>> :{
let
  definition :: Definition
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'Null 'PGint4])]
  definition = alterTable #tab (alterColumn #col dropNotNull)
in printSQL definition
:}
ALTER TABLE "tab" ALTER COLUMN "col" DROP NOT NULL;

An alterType converts a column to a different data type. This will succeed only if each existing entry in the column can be converted to the new type by an implicit cast.

>>> :{
let
  definition :: Definition
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGint4])]
    '["tab" ::: 'Table ('[] :=> '["col" ::: 'NoDef :=> 'NotNull 'PGnumeric])]
  definition =
    alterTable #tab (alterColumn #col (alterType (numeric & notNullable)))
in printSQL definition
:}
ALTER TABLE "tab" ALTER COLUMN "col" TYPE numeric NOT NULL;

Create a view.

>>> :{
let
  definition :: Definition
    '[ "abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])]
    '[ "abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])
     , "bc"  ::: 'View ('["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4])]
  definition =
    createView #bc (select (#b :* #c) (from (table #abc)))
in printSQL definition
:}
CREATE VIEW "bc" AS SELECT "b" AS "b", "c" AS "c" FROM "abc" AS "abc";

Drop a view.

>>> :{
let
  definition :: Definition
    '[ "abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])
     , "bc"  ::: 'View ('["b" ::: 'Null 'PGint4, "c" ::: 'Null 'PGint4])]
    '[ "abc" ::: 'Table ('[] :=> '["a" ::: 'NoDef :=> 'Null 'PGint4, "b" ::: 'NoDef :=> 'Null 'PGint4, "c" ::: 'NoDef :=> 'Null 'PGint4])]
  definition = dropView #bc
in printSQL definition
:}
DROP VIEW "bc";

Enumerated types are created using the createTypeEnum command, for example

>>> printSQL $ createTypeEnum #mood (label @"sad" :* label @"ok" :* label @"happy")
CREATE TYPE "mood" AS ENUM ('sad', 'ok', 'happy');

Enumerated types can also be generated from a Haskell type, for example

>>> data Schwarma = Beef | Lamb | Chicken deriving GHC.Generic
>>> instance SOP.Generic Schwarma
>>> instance SOP.HasDatatypeInfo Schwarma
>>> printSQL $ createTypeEnumFrom @Schwarma #schwarma
CREATE TYPE "schwarma" AS ENUM ('Beef', 'Lamb', 'Chicken');

createTypeComposite creates a composite type. The composite type is specified by a list of attribute names and data types.

>>> type PGcomplex = 'PGcomposite '["real" ::: 'PGfloat8, "imaginary" ::: 'PGfloat8]

>>> :{
let
  setup :: Definition '[] '["complex" ::: 'Typedef PGcomplex]
  setup = createTypeComposite #complex
    (float8 `as` #real :* float8 `as` #imaginary)
in printSQL setup
:}
CREATE TYPE "complex" AS ("real" float8, "imaginary" float8);

Composite types can also be generated from a Haskell type, for example

>>> data Complex = Complex {real :: Maybe Double, imaginary :: Maybe Double} deriving GHC.Generic
>>> instance SOP.Generic Complex
>>> instance SOP.HasDatatypeInfo Complex
>>> printSQL $ createTypeCompositeFrom @Complex #complex
CREATE TYPE "complex" AS ("real" float8, "imaginary" float8);

Drop a type.

>>> data Schwarma = Beef | Lamb | Chicken deriving GHC.Generic
>>> instance SOP.Generic Schwarma
>>> instance SOP.HasDatatypeInfo Schwarma
>>> printSQL (dropType #schwarma :: Definition '["schwarma" ::: 'Typedef (EnumFrom Schwarma)] '[])
DROP TYPE "schwarma";

ColumnTypeExpressions are used in createTable commands.

used in createTable commands as a column constraint to note that NULL may be present in a column

used in createTable commands as a column constraint to ensure NULL is not present in a column

used in createTable commands as a column constraint to give a default

not a true type, but merely a notational convenience for creating unique identifier columns with type PGint2

not a true type, but merely a notational convenience for creating unique identifier columns with type PGint2

not a true type, but merely a notational convenience for creating unique identifier columns with type PGint4

not a true type, but merely a notational convenience for creating unique identifier columns with type PGint4

not a true type, but merely a notational convenience for creating unique identifier columns with type PGint8

not a true type, but merely a notational convenience for creating unique identifier columns with type PGint8