PostgreSQL Create Table

Table consists of columns and rows. Column specifies to store the type of data in rows. Rows contains actual data respective to column data type. Each column has name and data type. We can define maximum 1600 columns in a PostgreSQL table.

Syntax:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ … ] ]
| table_constraint
| LIKE parent_table [ like_option … ] }
[, … ]
] )
[ INHERITS ( parent_table [, … ] ) ]
[ WITH ( storage_parameter [= value] [, … ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
OF type_name [ (
{ column_name WITH OPTIONS [ column_constraint [ … ] ]
| table_constraint }
[, … ]
) ]
[ WITH ( storage_parameter [= value] [, … ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

Examples:

We are creating student table in the database ‘pinfo’.

CREATE TABLE student (
student_name varchar(80),
student_id int,
class int,
dob date
);

Output:

PostgreSQL Create Table

Below examples creates a table to store electricity poles geographical information.

CREATE TABLE poles (
name varchar(80),
location point
);

To get the list of tables in present connected database type \d and press enter like below.

PostgreSQL Create Table

If you want table anymore, then drop table
If you want table data anymore, then delete table

Powered by k2schools