Skip to main content
The CREATE TABLE statement creates a new table in a database.

​
Required privileges

To create a table, you must have one of the following:
  • Membership to the admin role for the cluster
  • Membership to the owner role for the database
  • The CREATE privilege on the database

​
Syntax

CREATE [TEMPORARY | TEMP] TABLE [IF NOT EXISTS] table_name (
  column_def [, ...]
  [, constraint_def [, ...]]
) [WITH storage_parameter_list]

​
Parameters

​
TEMPORARY | TEMP
keyword
Defines the table as a session-scoped temporary table.Note: The LOCAL, GLOBAL, and UNLOGGED options are no-ops, allowed by the parser for PostgreSQL compatibility.
​
IF NOT EXISTS
keyword
Create a new table only if a table of the same name does not already exist in the database. If one does exist, do not return an error.Note: IF NOT EXISTS checks the table name only; it does not check if an existing table has the same columns, indexes, constraints, etc., of the new table.
​
table_name
identifier
The name of the table to create, which must be unique within its database. When the parent database is not set as the default, the name must be formatted as database.name.Avoid using the name excluded for any of your tables, as it’s used internally by UPSERT and INSERT ON CONFLICT statements.
​
column_def
definition
A comma-separated list of column definitions. Each column requires a name/identifier and data type.You can optionally specify column qualifications such as:
  • Column-level constraints
  • Collations
  • Column family assignments
  • DEFAULT expressions
  • ON UPDATE expressions
  • Identity columns
  • NOT VISIBLE property
​
constraint_def
definition
An optional, comma-separated list of table-level constraints. Constraint names must be unique within the table.Supported constraints:
  • PRIMARY KEY
  • UNIQUE
  • CHECK
  • FOREIGN KEY
​
index_def
definition
An optional, comma-separated list of index definitions. For each index, the column(s) to index must be specified; optionally, a name can be specified.Index names must be unique within the table.

​
Examples

​
Create a table

CREATE TABLE users (
  id UUID PRIMARY KEY,
  city STRING,
  name STRING,
  address STRING,
  credit_card STRING,
  dl STRING
);

SHOW COLUMNS FROM users;

  column_name | data_type | is_nullable | column_default | indices  
--------------+-----------+-------------+----------------+-----------
  id          | UUID      |    false    | NULL           | {primary}
  city        | VARCHAR   |    false    | NULL           | {primary}
  name        | VARCHAR   |    true     | NULL           | {primary}
  address     | VARCHAR   |    true     | NULL           | {primary}
  credit_card | VARCHAR   |    true     | NULL           | {primary}
  dl          | STRING    |    true     | NULL           | {primary}

​
Create a table with secondary and GIN indexes

CREATE TABLE vehicles (
  id UUID NOT NULL,
  city STRING NOT NULL,
  type STRING,
  owner_id UUID,
  creation_time TIMESTAMP,
  status STRING,
  current_location STRING,
  ext JSONB,
  CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
  INDEX index_status (status),
  INVERTED INDEX ix_vehicle_ext (ext)
);

​
Create a table with a foreign key constraint

Foreign key constraints guarantee a column uses only values that already exist in the column it references. 
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  city STRING,
  name STRING,
  address STRING,
  credit_card STRING,
  dl STRING UNIQUE CHECK (LENGTH(dl) < 8)
);

CREATE TABLE vehicles (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  city STRING NOT NULL,
  type STRING,
  owner_id UUID REFERENCES users(id) ON DELETE CASCADE,
  creation_time TIMESTAMP,
  status STRING,
  current_location STRING,
  ext JSONB,
  CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);
In this example, ON DELETE CASCADE specifies that when a row referenced by a foreign key constraint is deleted, all dependent rows are also deleted.

​
Create a table with a check constraint

CREATE TABLE users (
  id UUID PRIMARY KEY,
  city STRING,
  name STRING,
  address STRING,
  credit_card STRING,
  dl STRING UNIQUE CHECK (LENGTH(dl) < 8)
);

​
Create a table with an identity column

Identity columns are populated with values from a sequence: 
CREATE TABLE bank (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  order_index INT8 UNIQUE,
  balance INT8,
  payload STRING,
  numerical INT8 GENERATED BY DEFAULT AS IDENTITY (INCREMENT 1 MINVALUE 0 START 0)
);

​
Create a table from a SELECT statement

Use CREATE TABLE AS to create a new table from the results of a SELECT statement: 
CREATE TABLE users_ny AS 
  SELECT * FROM users 
  WHERE city = 'new york';

​
See also

  • ALTER TABLE
  • DROP TABLE
  • CREATE INDEX
  • SHOW TABLES
  • SHOW COLUMNS

Build docs developers (and LLMs) love

Get started for freeTalk to us