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
>>> :{
renderDefinition $
  createTable #tab (int `As` #a :* real `As` #b :* Nil) []
:}
"CREATE TABLE tab (a int, b real);"

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.

>>> :{
renderDefinition $
  createTable #tab
    ( (int & notNull) `As` #a :*
      (int & notNull) `As` #b :* Nil )
    [ check (#a .> #b) ]
:}
"CREATE TABLE tab (a int NOT NULL, b int NOT NULL, 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.

>>> :{
renderDefinition $
  createTable #tab
    ( int `As` #a :*
      int `As` #b :* Nil )
    [ unique (Column #a :* Column #b :* Nil) ]
:}
"CREATE TABLE tab (a int, b int, 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.

>>> :{
renderDefinition $
  createTable #tab
    ( serial `As` #id :*
      (text & notNull) `As` #name :* Nil )
    [ primaryKey (Column #id :* Nil) ]
:}
"CREATE TABLE tab (id serial, name text NOT NULL, 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.

>>> :{
let
  definition :: Definition '[]
    '[ "users" :::
       '[ "id" ::: 'Optional ('NotNull 'PGint4)
        , "username" ::: 'Required ('NotNull 'PGtext)
        ]
     , "emails" :::
       '[ "id" ::: 'Optional ('NotNull 'PGint4)
        , "userid" ::: 'Required ('NotNull 'PGint4)
        , "email" ::: 'Required ('NotNull 'PGtext)
        ]
     ]
  definition =
    createTable #users
      (serial `As` #id :* (text & notNull) `As` #username :* Nil)
      [primaryKey (Column #id :* Nil)] >>>
    createTable #emails
      ( serial `As` #id :*
        (integer & notNull) `As` #userid :*
        (text & notNull) `As` #email :* Nil )
      [ primaryKey (Column #id :* Nil)
      , foreignKey (Column #userid :* Nil) #users (Column #id :* Nil)
        OnDeleteCascade OnUpdateRestrict
      ]
in renderDefinition definition
:}
"CREATE TABLE users (id serial, username text NOT NULL, PRIMARY KEY (id)); CREATE TABLE emails (id serial, userid integer NOT NULL, email text NOT NULL, PRIMARY KEY (id), FOREIGN KEY (userid) REFERENCES users (id) ON DELETE CASCADE ON UPDATE RESTRICT);"

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

Render OnDeleteClause.

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

Render OnUpdateClause.

dropTable removes a table from the schema.

>>> renderDefinition $ dropTable #muh_table
"DROP TABLE muh_table;"

alterTable changes the definition of a table from the schema.

alterTableRename changes the name of a table from the schema.

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

An alterTableAddConstraint adds a table constraint.

>>> :{
let
  definition :: Definition
    '["tab" ::: '["col" ::: 'Required ('NotNull 'PGint4)]]
    '["tab" ::: '["col" ::: 'Required ('NotNull 'PGint4)]]
  definition = alterTableAddConstraint #tab (check (#col .> 0))
in renderDefinition definition
:}
"ALTER TABLE tab ADD CHECK ((col > 0));"

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

An addColumnDefault adds a new Optional column. The new column is initially filled with whatever default value is given.

>>> :{
let
  definition :: Definition
    '["tab" ::: '["col1" ::: 'Required ('Null 'PGint4)]]
    '["tab" :::
       '[ "col1" ::: 'Required ('Null 'PGint4)
        , "col2" ::: 'Optional ('Null 'PGtext) ]]
  definition = alterTable #tab
    (addColumnDefault #col2 (text & default_ "foo"))
in renderDefinition definition
:}
"ALTER TABLE tab ADD COLUMN col2 text DEFAULT E'foo';"

An addColumnDefault adds a new Null column. The new column is initially filled with %NULL%s.

>>> :{
let
  definition :: Definition
    '["tab" ::: '["col1" ::: 'Required ('Null 'PGint4)]]
    '["tab" :::
       '[ "col1" ::: 'Required ('Null 'PGint4)
        , "col2" ::: 'Required ('Null 'PGtext) ]]
  definition = alterTable #tab (addColumnNull #col2 text)
in renderDefinition definition
:}
"ALTER TABLE tab ADD COLUMN col2 text;"

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" :::
       '[ "col1" ::: 'Required ('Null 'PGint4)
        , "col2" ::: 'Required ('Null 'PGtext) ]]
    '["tab" ::: '["col1" ::: 'Required ('Null 'PGint4)]]
  definition = alterTable #tab (dropColumn #col2)
in renderDefinition definition
:}
"ALTER TABLE tab DROP COLUMN col2;"

Like dropColumn but authorizes dropping everything that depends on the column.

>>> :{
let
  definition :: Definition
    '["tab" :::
       '[ "col1" ::: 'Required ('Null 'PGint4)
        , "col2" ::: 'Required ('Null 'PGtext) ]]
    '["tab" ::: '["col1" ::: 'Required ('Null 'PGint4)]]
  definition = alterTable #tab (dropColumnCascade #col2)
in renderDefinition definition
:}
"ALTER TABLE tab DROP COLUMN col2 CASCADE;"

A renameColumn renames a column.

>>> :{
let
  definition :: Definition
    '["tab" ::: '["foo" ::: 'Required ('Null 'PGint4)]]
    '["tab" ::: '["bar" ::: 'Required ('Null 'PGint4)]]
  definition = alterTable #tab (renameColumn #foo #bar)
in renderDefinition 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 insertTable and updateTable commands.

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

A dropDefault removes any default value for a column.

>>> :{
let
  definition :: Definition
    '["tab" ::: '["col" ::: 'Optional ('Null 'PGint4)]]
    '["tab" ::: '["col" ::: 'Required ('Null 'PGint4)]]
  definition = alterTable #tab (alterColumn #col dropDefault)
in renderDefinition 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" ::: '["col" ::: 'Required ('Null 'PGint4)]]
    '["tab" ::: '["col" ::: 'Required ('NotNull 'PGint4)]]
  definition = alterTable #tab (alterColumn #col setNotNull)
in renderDefinition definition
:}
"ALTER TABLE tab ALTER COLUMN col SET NOT NULL;"

A dropNotNull drops a NOT NULL constraint from a column.

>>> :{
let
  definition :: Definition
    '["tab" ::: '["col" ::: 'Required ('NotNull 'PGint4)]]
    '["tab" ::: '["col" ::: 'Required ('Null 'PGint4)]]
  definition = alterTable #tab (alterColumn #col dropNotNull)
in renderDefinition 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" ::: '["col" ::: 'Required ('NotNull 'PGint4)]]
    '["tab" ::: '["col" ::: 'Required ('NotNull 'PGnumeric)]]
  definition =
    alterTable #tab (alterColumn #col (alterType (numeric & notNull)))
in renderDefinition definition
:}
"ALTER TABLE tab ALTER COLUMN col TYPE numeric NOT NULL;"