Creating Tables

Introduction to Table Creation in Postmodern

Postmodern offers multiple ways to create postgresql database tables. Which one you choose depends on two things:

  • Do you start by creating dao classes first or are you even using dao classes? If yes, then also see Creating Tables using dao classes
  • Databases are data and transaction management systems, not just data storage. How much detailed table specification do you need?

If you typically start with defining your classes and only need relatively straight-forward tables with simple primary and foreign keys, then you would define your daos first, then let postmodern's deftable and create-table functions to create the database classes.

If you do not use daos or need more control and you want to use postmodern's more lispy syntax as compared to postgresql's never ending natural language statements, you can reach for one of two additional tools: the sql-operators :create-table and :create-extended-table. The api signature differs slightly in order to ensure backwards compatibility and, as you might expect, :create-extended-table gives you more options.

But first we should talk about basic database table creation and why do we care about different options.

Basic Postgresql Database Tables

The full postgresql sql specification can be found here.

At a bare minimum, a database table needs a name. If you actually want to store data in it, you need to define the columns (some people call them fields). Again, at a minimum, a column needs a name, a datatype and whether the column can be a null or whether the database will require that the field have a value.

Once you provide these bare minimum definitions to the database, then you can start inserting rows (or tuples) into the table.

For purposes of all the examples below, we will assume that you have a connection to a database. That can be a toplevel connection or wrapping a connection around your query, and either of these can be directly to the database or through a connection pooling mechanism. This page will just be dealing with the queries required to create a postgresql database table.

Postmodern Create-Table

S-SQL Create-Table

At this minimum level, the :create-table and :create-extended-table calls are exactly the same. Let's walk through a simple example: 

(query (:create-table 'films-at-eleven
          ((code :type (or (string 5) db-null) :constraint 'firstkey :primary-key t)
           (title :type (varchar 40))
           (did :type integer)
           (date-prod :type (or date db-null))
           (kind :type (or (varchar 10) db-null))
           (len :type (or interval db-null) :interval :hour-to-minute))))

The parameter specification is :create-table (name (&rest columns) &rest options)

The first parameter is the name of the new table. You could make the name a quoted string, e.g. "films-at-eleven", but postmodern will handle either a string or the unquoted parameter provided above. Postmodern will also automatically handle the conversion of the hyphens so beloved by common lisp programmers to the underscores demanded by the sql specification.

The next form contains subforms for each column in the table.

The first parameter being the name of the column.

As you know, postgresql will enforce the data type for the column. So, the next keyword specifies that type to follow. You can see several different types specified in this table. Look at the type for 'code'. It specifies that the data will be either NULL or a string with a maximum length of 5 characters.

Compare that against the type specifiers for title or did. In both of those cases, the column entry is not allowed to be NULL, it must have a value. In this example the title must be a string with a length no greater than 40 characters. (For most purposes, you can treat a string specification the same as a varchar specification.

The did type specification mandates an integer - trying to put a float in that column will trigger a database type error.

The date-prod column obviously has a date data type and the len column has a interval data type, intervals being a period of time rather than a specific time.

The code column also has a constraint - It is the primary key for indexing the table and that contraint has a name - in this case 'firstkey. If you do not name the constraint, the system will name it for you.

The len column also has additional information. It is an interval of time; That could encompass years as well as seconds. The specification given here, :hour-to-minute means that the database will only keep the hours to minutes part of the potential time interval.

Table Names Parameter

The following discussion on table names is the same for both :create-table or :create-extended table.

Create-table works with either the simple name for the table parameter or the name can be wrapped in a form. There are a few acceptable ways to pass the table-name parameters. We recommend a single quote as in the first example below, but for backwards compatibility, you can also provide the table-name with no quotes at all. The last example below shows the table name preceded by the desired schema name. e.g. 'public.tablename1. :create-table does not accept the table-name as a variable. 

(query (:create-table 'distributors-in-hell
         ((did :type (or integer db-null)))))

(query (:create-table (distributors-in-hell)
         ((did :type (or integer db-null)))))

(query (:create-table ("distributors-in-hell")
         ((did :type (or integer db-null)))))

(query (:create-table 'public.distributors-in-hell
         ((did :type (or integer db-null)))))

Using the name wrapped in a form provides the opportunity to add additional qualifiers - if-not-exists, temp, and unlogged. 

(query (:create-table (:if-not-exists 'distributors-in-hell)
          ((did :type (or integer db-null)))))

Temporary and Unlogged Tables

Temporary tables exists only for the duration of the session and are unlogged. To create them using s-sql in postmodern, the first parameter to :create-table should be a form where the first atom in the form is the keyword :temp., then the name of the table. You can optionally use the :if-not-exists keyword in a either sequentially or in a subform wrapping the name of the temporary table. E.g. 

(query (:create-table (:temp 'distributors-in-hell)
         ((did :type (or integer db-null)))))

(query (:create-table (:temp (:if-not-exists 'distributors-in-hell))
         ((did :type (or integer db-null)))))

(query (:create-table (:temp :if-not-exists 'distributors-in-hell)
         ((did :type (or integer db-null)))))

Unlogged tables do not have their data written to the write-ahead log. As a result they are faster, but not crash safe. Any indexes created on an unlogged table are unlogged as well. The parameter signature is similar to a temporary table. E.g. 

(query (:create-table (:unlogged 'distributors-in-hell)
         ((did :type (or integer db-null)))))

Using Identity Columns

Postgresql version 10 added identity columns. These are auto-incrementing columns that use the standard SQL syntax instead of the serial and bigserial datatypes which are still available, but are not SQL standard syntax. The serial and bigserial datatypes also generate sequences that may have different usage permissions which can be problematic if you need to make changes to the sequence.

There are two keyword alternatives that need to be considered:

  • :generated-as-identity-by-default (or more simply :identity-by-default)
  • :generated-as-identity-always (or more simply :identity-always)

The difference between the two alternatives is that if you try to insert or update a value in the column and the generated-always option is chosen, postgresql will raise an error. If you use the by-default option, postgresql will use your value rather than the automatically generated value.

Note: The data type used for identity columns must be one of smallint, int or bigint. 

(query (:create-table 'color
         ((color-id :type int :generated-as-identity-always t)
          (color-name :type varchar))))

(query (:create-table 'color
         ((color-id :type int :generated-as-identity-by-default t)
          (color-name :type varchar))))

(query (:create-table 'color
         ((color-id :type int :identity-always t)
          (color-name :type varchar))))

(query (:create-table 'color
         ((color-id :type int :identity-by-default t)
          (color-name :type varchar))))

Array Columns

The following shows the creation of a table with a two dimensional array 

(query (:create-table 'array-int
       ((vector :type (or int[][] db-null)))))

Check Constraints

You can put a contraint on a table row that specifies values must meet certain requirements. In the following examples, the first puts a check constraint on a row, the second places at check constraint at the table level. 

(query (:create-table 'distributors
         ((did :type (or integer db-null) :check (:> 'did 100))
          (name :type (or (varchar 40) db-null)))))

(query (:create-table 'distributors
         ((did :type (or integer db-null))
          (name :type (or (varchar 40) db-null)))
         (:constraint con1 :check (:and (:> 'did 100) (:<> 'name "")))))

Unique Constraints

You can ensure that a column or a combination of columns is unique without making that column or columns the primary key for the table.

The first example sets a unique constraint at the column level of email, the second example sets a unique constraint at the table level.

Please note the need to set :unique to t in the column constraint version. 

(query (:create-table 'person
       ((id :type serial :primary-key t)
        (first-name :type (varchar 50))
        (last-name :type (varchar 50))
        (email :type (varchar 50) :unique t))))

(query (:create-table 'films
         ((code :type (or (string 5) db-null))
          (title :type (or (varchar 40) db-null))
          (did :type (or integer db-null))
          (date-prod :type (or date db-null))
          (kind :type (or (varchar 10) db-null))
          (len :type (or interval db-null) :interval :hour-to-minute))
         (:constraint production :unique 'date-prod)))

This can get more complex if so desired. See the following example from the postgresql documentation, translated into s-sql. This level of complexity, however, requires the :create-extended-table method. 

(query (:create-extended-table 'distributors
         ((did :type (or integer db-null))
          (name :type (or (varchar 40) db-null)))
         ((:unique did :with (:= 'fillfactor 70)))))

In case you are wondering, fillfactor is a storage parameter described in the postgresql documentation as:

"The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables."

Timestamps with and without timezones

Ah, the bane of many programmers' existence - dates and times. At least the table creation is fairly straightforward.

The following examples are fairly self explanatory. 

(query (:create-table 'account-role
         ((user-id :type integer)
          (role-id :type integer)
          (grant-date :type (or timestamp-without-time-zone db-null)))))

(query (:create-table 'account-role
         ((user-id :type integer)
          (role-id :type integer)
          (grant-date :type (or timestamp-with-time-zone db-null)))))

(query (:create-table 'account-role
         ((user-id :type integer)
          (role-id :type integer)
          (grant-date :type (or timestamptz db-null)))))

(query (:create-table 'account-role
         ((user-id :type integer)
          (role-id :type integer)
          (grant-date :type (or timestamp db-null)))))

(query (:create-table 'account-role
         ((user-id :type integer)
          (role-id :type integer)
          (grant-date :type (or time db-null)))))

Adding Foreign Keys

Foreign keys are a constraint. If a column is set with a foreign key referencing another table, Postgresql will prevent you from inserting any value in that row unless there is a corresponding value in the foreign table. In other words, if you have a countries table with a region-id foreign key referencing a regions table, you cannot insert a country with a reference to a region-id or region-name unless that region-id or region-name already exists in the regions table. This is part of maintaining data integrity.

There are a few different ways to add foreign keys in postgresql when defining a table.

Defining Foreign Keys at the Column Level

First with foreign key on the column. Use the keyword :references and specify the table name and the relevant column within the subform. You will see why the subform in a minute. 

(query (:create-table 'so-items
          ((item-id :type integer)
           (so-id :type (or integer db-null) :references ((so-headers id)))
           (product-id :type (or integer db-null))
           (qty :type (or integer db-null))
           (net-price :type (or numeric db-null)))
          (:primary-key item-id so-id)))

If the foreign key references a group of columns in the foreign table, those fit into that same subform. 

(query (:create-table 'so-items
          ((item-id :type integer)
           (so-id :type (or integer db-null) :references ((so-headers id p1 p2)))
           (product-id :type (or integer db-null))
           (qty :type (or integer db-null))
           (net-price :type (or numeric db-null)))
          (:primary-key item-id so-id)))

You can specify the actions to be taken if a row in the foreign table is deleted or updated. Per the postgresql documentation:

"… when the data in the referenced columns is changed, certain actions are performed on the data in this table's columns. The ON DELETE clause specifies the action to perform when a referenced row in the referenced table is being deleted. Likewise, the ON UPDATE clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value. If the row is updated, but the referenced column is not actually changed, no action is done. Referential actions other than the NO ACTION check cannot be deferred, even if the constraint is declared deferrable. There are the following possible actions for each clause:

  • NO ACTION

Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action in postgresql but the default in postmodern is restrict.

  • RESTRICT

Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable.

  • CASCADE

Delete any rows referencing the deleted row, or update the values of the referencing column(s) to the new values of the referenced columns, respectively.

  • SET NULL

Set the referencing column(s) to null.

  • SET DEFAULT

Set the referencing column(s) to their default values. (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.)

If the referenced column(s) are changed frequently, it might be wise to add an index to the referencing column(s) so that referential actions associated with the foreign key constraint can be performed more efficiently."

So now examples with specifying the on-delete and on-update actions. 

(query (:create-table 'so-items
         ((item-id :type integer)
          (so-id :type (or integer db-null)
                 :references ((so-headers id) :no-action :no-action))
          (product-id :type (or integer db-null))
          (qty :type (or integer db-null))
          (net-price :type (or numeric db-null)))
         (:primary-key item-id so-id)))

Defining Foreign Keys at the Table Level

Instead of specifying the foreign keys at the column level, you can specify them at the table level, but of course that means you have to additionally specify which column in the current table is a foreign key.

The following example creates a named constraint and a foreign key at column role-id with a single column in the foreign table. The first example uses the default actions for on-delete and on-default. The second example shows non-default actions specified. 

(query (:create-table 'account-role
         ((user-id :type integer)
          (role-id :type integer)
          (grant-date :type (or timestamp-without-time-zone db-null)))
         (:primary-key user-id role-id)
         (:constraint account-role-role-id-fkey
                      :foreign-key (role-id) (role role-id))))


(query (:create-table 'account-role
         ((user-id :type integer)
          (role-id :type integer)
          (grant-date :type (or timestamp-without-time-zone db-null)))
         (:primary-key user-id role-id)
         (:constraint account-role-role-id-fkey
                      :foreign-key (role-id) (role role-id) :no-action :no-action)))

Additional foreign keys can easily be added: 

(query (:create-table 'account-role
         ((user-id :type integer)
          (role-id :type integer)
          (grant-date :type (or timestamp-without-time-zone db-null)))
         (:primary-key user-id role-id)
         (:constraint account-role-role-id-fkey
                      :foreign-key (role-id) (role role-id))
         (:constraint account-role-user-id-fkey
                      :foreign-key (user-id) (users user-id))))

Using Tablespaces

Tablespaces require use of the create-extended-table method. Note that the create-extended-table method has a slightly different signature. It expects a table name and two or three forms. The first form relates to the columns, the second form relates to table level constraints. The third form, if present, refers to specific postgresql storage options such as tablespaces and partitions and storage parameters. This is only partially implemented.

Tablespaces are documented at https://www.postgresql.org/docs/current/manage-ag-tablespaces.html 

(query (:create-extended-table 'cinemas
       ((id :type serial) (name :type (or text db-null))
        (location :type (or text db-null)))
       ()
       ((:tablespace diskvol1))))

Partitions (not fully implemented)

The following are two very simple examples of use of partitions. You should not consider partition functionality to be fully implemented past these examples. 

(query (:create-extended-table 'measurement
         ((logdate :type date)
          (peaktemp :type (or integer db-null))
          (unitsales :type (or integer db-null)))
         ()
         ((:partition-by-range 'logdate))))

;; Create a range partitioned table with multiple columns in the partition key:

(query (:create-extended-table 'measurement-year-month
         ((logdate :type date)
          (peaktemp :type (or integer db-null))
          (unitsales :type (or integer db-null)))
         ()
         ((:partition-by-range (:extract 'year 'logdate)(:extract 'month 'logdate)))))

Table Creation With Dao Classes

See: Creating Tables using dao classes