Practical PostgreSQL

Create Table

Creates a new table.

Synopsis

CREATE [ TEMPORARY | TEMP ] TABLE table_name (
            { column_name type [ column_constraint [...] ] |
              table_constraint }
            [, ...]
       )
       [ INHERITS ( inherited_table [,...] ) ]

column_constraint ::=
  [ CONSTRAINT column_constraint_name ]
  { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
    DEFAULT default_value |
    CHECK (condition |
    REFERENCES foreign_table [ ( foreign_column ) ]
      [ MATCH FULL | MATCH PARTIAL ]
      [ ON DELETE action ]
      [ ON UPDATE action ]
      [ DEFERRABLE | NOT DEFERRABLE ]
      [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
   }

table constraint ::=
  [ CONSTRAINT table_constraint_name ]
  { UNIQUE ( column_name [, ... ] ) |
    PRIMARY KEY ( column_name [, ... ] ) |
    CHECK ( condition ) |
    FOREIGN KEY ( column_name [, ... ] )
      REFERENCES foreign_table
      [ ( foreign_column [, ... ] ) ]
      [ MATCH FULL | MATCH PARTIAL ]
      [ ON DELETE action ]
      [ ON UPDATE action ]
      [ DEFERRABLE | NOT DEFERRABLE ]
      [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
   }

action  ::= { NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT }

Parameters

TEMPORARY | TEMP

The keyword which defines a table as having a temporary lifespan. Such a table will be destroyed after the user’s session has ended. Any table-related constructions (such as indices and constraints) will also be destroyed with the table at the end of the session.

If a temporary table is given the same name as an existing permanent table, only the temporary table will be accessible by the session which created it. This will cause problems, since it will ...