PostgreSQL: CREATE TABLE Statement

In this PostgreSQL post explains how to use the PostgreSQL CREATE TABLE statement with syntax and examples.

Description

The PostgreSQL CREATE TABLE statement allows you to create and define a table.

Syntax

In its simplest form, the syntax for the CREATE TABLE statement in PostgreSQL is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

However, the full syntax for the PostgreSQL CREATE TABLE statement is:

CREATE [ [ GLOBAL TEMPORARY
         | GLOBAL TEMP
         | LOCAL TEMPORARY
         | LOCAL TEMP
         | UNLOGGED ]
  TABLE [IF NOT EXISTS] table_name
( 
  column1 datatype [ COLLATE collation ]
                   [ CONSTRAINT constraint_name ]
                   { NULL
                   | NOT NULL
                   | CHECK ( expression ) [ NO INHERIT ]
                   | DEFAULT default_value
                   | UNIQUE index_parameters
                   | PRIMARY KEY index_parameters
                   | REFERENCES ref_table [ ( ref_column ) ]
                      [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
                      [ ON DELETE action ]
                      [ ON UPDATE action ] }
                   [ DEFERRABLE | NOT DEFERRABLE ]
                   [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ],

  column2 datatype [ COLLATE collation ]
                   [ CONSTRAINT constraint_name ]
                   { NULL
                   | NOT NULL
                   | CHECK ( expression ) [ NO INHERIT ]
                   | DEFAULT default_value
                   | UNIQUE index_parameters
                   | PRIMARY KEY index_parameters
                   | REFERENCES ref_table [ ( ref_column ) ]
                      [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
                      [ ON DELETE action ]
                      [ ON UPDATE action ] }
                   [ DEFERRABLE | NOT DEFERRABLE ]
                   [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ],

  ...

 | [ CONSTRAINT constraint_name ]
     { CHECK ( expression ) [ NO INHERIT ]
     | UNIQUE ( index_col_name, ... )
     | PRIMARY KEY ( index_col_name, ... )
     | FOREIGN KEY ( index_col_name, ... )
         REFERENCES another_table_name (index_col_name, ...)
            [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
            [ ON DELETE action ]
            [ ON UPDATE action ]

 | LIKE source_table
     { INCLUDING | EXCLUDING }
     { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }

 [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
 [ TABLESPACE tablespace_name ]

);

Parameters or Arguments

GLOBAL TEMPORARY and GLOBAL TEMP

Optional. If either of these are specified, he table is a global temporary table.

LOCAL TEMPORARY and LOCAL TEMP

Optional. If either of these are specified, the table is a local temporary table.

UNLOGGED

Optional. If specified, the data in the table is not written to the write-ahead log. This improves performance on the table, however, the data in this table will be lost if a crash occurs.

IF NOT EXISTS

Optional. If specified, the CREATE TABLE statement will not raise an error if the tables already exists.

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

datatype

The data type for the column.

CONSTRAINT constraint_name

Optional. The name of the constraint.

NULL or NOT NULL

Each column should be defined as NULL or NOT NULL. If this parameter is omitted, the database assumes NULL as the default.

DEFAULT default_value

Optional. It is the value to assign to the column if left blank or NULL.

Example

Let's look at a PostgreSQL CREATE TABLE example.

CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200)
);

This PostgreSQL CREATE TABLE example creates a table called order_details which has 5 columns and one primary key:

• The first column is called order_detail_id which is created as an integer datatype and can not contain NULL values, since it is the primary key for the table.
• The second column is called order_id which is an integer datatype and can not contain NULL values.
• The third column is called order_date which is a date datatype and can contain NULL values.
• The fourth column is called quantity which is an integer datatype and can contain NULL values.
• The fifth column is called notes which is a varchar datatype (maximum 200 characters in length) and can contain NULL values.
• The primary key is called order_details_pk and is set to the order_detail_id column.

You could alternatively have written the CREATE TABLE statement as follows:

CREATE TABLE order_details
( order_detail_id integer NOT NULL,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200),
  CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)
);

The difference between the two CREATE TABLE statements is how the PRIMARY KEY is defined. Both methods are acceptable in PostgreSQL.

Next, let's create a table that has a DEFAULT VALUE.

CREATE TABLE order_details
( order_detail_id integer NOT NULL,
  order_id integer NOT NULL,
  order_date date,
  quantity integer,
  notes varchar(200) NOT NULL DEFAULT 'Standard shipping',
  CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)
);

This PostgreSQL CREATE TABLE example creates a table called order_details which has 5 columns and one primary key:

• The first column is called order_detail_id which is created as an integer datatype and can not contain NULL value.
• The second column is called order_id which is an integer datatype and can not contain NULL values.
• The third column is called order_date which is a date datatype and can contain NULL values.
• The fourth column is called quantity which is an integer datatype and can contain NULL values.
• The fifth column is called notes which is a varchar datatype (maximum 200 characters in length) and can not contain NULL values. If no value is provided for this column, the DEFAULT VALUE will be 'Standard shipping'.
• The primary key is called order_details_pk and is set to the order_detail_id column.