PostgreSQL - How to create a new table?

CREATE TABLE statement

Here is the generic syntax to create a table in PostgreSQL:

CREATE TABLE table_name ( [
{ column_name data_type [ column_constraint ]
| [ table_constraint ] }
] )

where column_constraint includes:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL |
  CHECK ( expression ) |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint includes:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

For data types in PostgreSQL, see the full list of supported data types here: PostgreSQL Data Types

Example

CREATE TABLE city (
    id int4 NOT NULL,
    name varchar(30) NOT NULL,
    state varchar(20),
    population int4,
    PRIMARY KEY (id)
);

This statement creates a table city with 4 columns: id, name, state, and population. The primary key is defined on the column id. You can also define the primary key when describing the column:

CREATE TABLE city (
    id int4 NOT NULL PRIMARY KEY,
    name varchar(30) NOT NULL,
    state varchar(20),
    population int4
);

---

Create a table using TablePlus

You can create a new table with TablePlus GUI tool.

When viewing the items tab in the left sidebar, right-click on the sidebar and choose New > Table, or you can use + button near the bottom left to create a new table.

• Insert new column by double clicking on an empty row or clicking on the + Column button at the bottom.
• Defind the new column using the given attibute in each field.
• Name the table at the top text box and define the primary key next to it.

While creating a new table, you can also preview the SQL CREATE TABLE query generated by TablePlus by clicking on the eye icon at the top left.

When you finish, remember to click on the Commit icon near the top left segment panel or use ⌘ + S to commit the changes to the database.

---

New to TablePlus? It’s a modern, native tool with an elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.


Download TablePlus free.