PostgreSQL CREATE TABLE statement

Preamble

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:

• Because it is the table’s primary key, the first column, order detail id, is constructed as an integer data type and cannot contain a NULL value.
• Order id is the second column, an integer data type that cannot include NULL values.
• Order date is the third field, which is a date data type that can contain NULL values.
• The fourth column, amount, is an integer data type that can contain NULL values.
• The fifth column, notes, is a varchar data type with a maximum length of 200 characters and can contain NULL values.

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.

PostgreSQL Database and Table creation and Inserting data to the table

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.