PostgreSQL – CREATE TABLE

To create in PostgreSQL tables, the CREATE TABLE command is used, after which the table name is specified. Also with this command, you can use a number of operators that define the table columns and their attributes.

PostgreSQL operator CREATE TABLE allows you to create and define a table

The simplest syntax for the CREATE TABLE statement in PostgreSQL:

CREATE TABLE name_table
(column_name1 type_data attribute_column1,
Column_name2 attribute_data type2,
................................................
name_columnN type_data attribute_columnN,
attributes_tables
);

After the table name, the specification for all columns is listed in brackets. And for each column you should specify the name and data type it will represent. The data type determines what data (numbers, rows, etc.) the column can contain.

For example, let’s create a table in the database via pgAdmin. To do this, first select the target database in pgAdmin, right-click on it and select Query Tool in the context menu…:

After that a field for SQL code input will open. And the table will be created exactly for the database for which we open this field for entering SQL.

Then in the field opened in the central part of the program we will enter the following set of expressions:

CREATE TABLE customers
(
Id SERIAL PRIMARY KEY,
FirstName CHARACTER VARYING(30),
LastName CHARACTER VARYING(30),
Email CHARACTER VARYING(30),
Age INTEGER
);

In this case the Customers table defines five columns: Id, FirstName, LastName, Age, Email. The first column – Id represents the client ID, it serves as the primary key and therefore is of SERIAL type. In fact, this column will store a numerical value of 1, 2, 3, etc., which for each new row will automatically increase by one.

The next three columns represent the client’s name, surname and email address and are CHARACTER VARYING(30) type, i.e. they represent a line no longer than 30 characters long.

The last column Age represents the user age and is of type INTEGER, i.e. stores the numbers.

And after running this command, a table of customers will be added to the selected database.

Deleting tables

To delete tables, the DROP TABLE command is used, which has the following syntax:

DROP TABLE table1 [, table2, ...];

For example, deleting a table:

DROP TABLE customers;

However, the full syntax for the CREATE TABLE statement 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 ( ) [ 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 ( ) [ 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 ( ) [ 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 ]
Consider the PostgreSQL example CREATE TABLE .);

Parameters or arguments

• GLOBAL TEMPORARY and GLOBAL TEMP
  It’s optional. If one of them is specified, the table is a global time table.
• LOCAL TEMPORARY and LOCAL TEMP
  It’s optional. If one of them is specified, the table is a local time table.
• UNLOGGED
  It’s optional. If specified, the data in the table is not recorded in the pre-recording log. This improves the performance of the table, but the data in this table will be lost in the event of a failure.
• IF NOT EXISTS
  It’s optional. If specified, the CREATE TABLE instruction will not cause an error if tables already exist.
• table_name
  The name of the table you want to create.
• column1, column2
  The columns that you want to create in the table.
• datatype
  Data type for a column.
• CONSTRAINT constraint_name
  It’s optional. Name of the restriction.
• 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
  It’s optional. This is the value assigned to the column if it is left blank or equal to NULL.

Example:

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,
quantity integer,
notes varchar(200)
);

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

• The first column is called order_detail_id, which is created as 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 integer data type and can not contain NULL values.
• The third column is called order_date, which is the 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 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,
quantity integer,
notes varchar(200),
CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)
);

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

Next, let’s create a table that has APPLICATION.

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 named order_details, which has 5 columns and one primary key:

• The first column is called order_detail_id, which is created as 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 NULL value.
• The third column is called order_date, which is the 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 the data type varchar (max 200 characters long) and can not contain values NULL. If no value is specified for this column, the NOTE will be ‘Standard shipping’.
• The primary key is called order_details_pk and has a value for the order_detail_id column.

Creating database and table with PostgreSQL (pgAdmin)