PostgreSQL CREATE TABLE statement

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

The easiest syntax for CREATE TABLE statement in PostgreSQL

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

The full syntax for CREATE TABLE in PostgreSQL

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 | INDEXES | STORAGE | COMMENTS | ALL }

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

);

Statement parameters and arguments

• GLOBAL TEMPORARY and GLOBAL TEMP – Optional. If one of them is specified, the table is a global time table.
• LOCAL TEMPORARY and LOCAL TEMP – Optional. If one of them is specified, the table is a local time table.
• UNLOGGED – Optional. If specified, the data in the table is not recorded in the pre-recorded log. This improves the performance of the table, but the data in this table will be lost in case of failure.
• IF NOT EXISTS – Optional. If specified, the CREATE TABLE instruction will not cause an error if the tables already exist.
• table_name – The name of the table you want to create.
• column1, column2 – The columns you want to create in the table.
• datatype – The data type for a column.
• CONSTRAINT constraint_name – Optional. Name of the constraint.
• NULL or NOT NULL – Each column must be defined as NULL or NOT NULL. If this parameter is omitted, the database accepts NULL as the default value.
• DEFAULT default_value – Optional. This is the value assigned to the column if it is left blank or NULL.

Consider the PostgreSQL example CREATE TABLE

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

This PostgreSQL CREATE TABLE example creates a table with the name order_details, which has 5 columns and one primary key:

• The first column is called order_detail_id, which is created as an integer data type and cannot contain a NULL value because it is the primary key of the table.
• The second column is called order_id, which is an integer data type and cannot contain NULL values.
• The third column is called order_date, which is the date data type and can contain NULL values.
• The fourth column is called amount, which is an integer data type and can contain values NULL.
• The fifth column is called notes, which is a varchar data type (max. 200 characters long) and can contain values NULL.

The primary key is called order_details_pk and has a value for the order_detail_id column.

Alternatively, you could write the CREATE TABLE operator as follows

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

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

Next, let's create a table that has the NOTICE of APPLICATION.

CREATE TABLE order_details
( order_detail_id integer NOT NULL,
order_id integer NOT NULL,
order_date date,
size 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 with the name order_details, which has 5 columns and one primary key:

• The first column is called order_detail_id, which is created as an integer data type and cannot contain the value NULL.
• The second column is called order_id, which is an integer data type and cannot contain a value of NULL.
• The third column is called order_date, which is a data type date and can contain values NULL.
• The fourth column is called amount, which is an integer data type and can contain values NULL.
• The fifth column is called notes, which is a varchar data type (max. 200 characters long) and can not contain values NULL. If no value is specified for this column, the NOTE is ‘Standard shipping’.

The primary key is called order_details_pk and has a value for the order_detail_id column.

PostgreSQL Database and Table creation and Inserting data to the table