DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

/usr/man2/cat.l/create_table.l.Z





NAME

       CREATE TABLE - define a new table


SYNOPSIS

       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
         { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
           | table_constraint
           | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }
           [, ... ]
       ] )
       [ INHERITS ( parent_table [, ... ] ) ]
       [ WITH OIDS | WITHOUT OIDS ]
       [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
       [ TABLESPACE tablespace ]

       where column_constraint is:

       [ CONSTRAINT constraint_name ]
       { NOT NULL |
         NULL |
         UNIQUE [ USING INDEX TABLESPACE tablespace ] |
         PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
         CHECK (expression) |
         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 is:

       [ CONSTRAINT constraint_name ]
       { UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
         PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
         CHECK ( expression ) |
         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 ]


DESCRIPTION

       CREATE  TABLE  will  create a new, initially empty table in the current
       database. The table will be owned by the user issuing the command.

       If a schema name is given (for example, CREATE  TABLE  myschema.mytable
       ...) then the table is created in the specified schema. Otherwise it is
       created in the current schema. Temporary  tables  exist  in  a  special
       schema, so a schema name may not be given when creating a temporary ta-
       ble. The name of the table must be distinct from the name of any  other
       table, sequence, index, or view in the same schema.

       CREATE TABLE also automatically creates a data type that represents the
       composite type corresponding to one row of the table. Therefore, tables
       cannot have the same name as any existing data type in the same schema.

       The optional constraint clauses specify constraints (tests) that new or
       updated rows must satisfy for an insert or update operation to succeed.
       A constraint is an SQL object that helps define the set of valid values
       in the table in various ways.

       There  are two ways to define constraints: table constraints and column
       constraints. A column constraint is defined as part of a column defini-
       tion. A table constraint definition is not tied to a particular column,
       and it can encompass more than one column.  Every column constraint can
       also  be  written  as a table constraint; a column constraint is only a
       notational convenience for use when the  constraint  only  affects  one
       column.


PARAMETERS

       TEMPORARY or TEMP
              If specified, the table is created as a temporary table.  Tempo-
              rary tables are automatically dropped at the end of  a  session,
              or optionally at the end of the current transaction (see ON COM-
              MIT below). Existing permanent tables with the same name are not
              visible to the current session while the temporary table exists,
              unless they are  referenced  with  schema-qualified  names.  Any
              indexes created on a temporary table are automatically temporary
              as well.

              Optionally, GLOBAL or LOCAL can be written before  TEMPORARY  or
              TEMP.   This makes no difference in PostgreSQL, but see Compati-
              bility [create_table(l)].

       table_name
              The name (optionally schema-qualified) of the table to  be  cre-
              ated.

       column_name
              The name of a column to be created in the new table.

       data_type
              The  data type of the column. This may include array specifiers.
              For more information on the data types supported by  PostgreSQL,
              refer to the documentation.

       DEFAULT
              The  DEFAULT  clause assigns a default data value for the column
              whose column definition it appears  within.  The  value  is  any
              variable-free  expression  (subqueries  and  cross-references to
              other columns in the current table are not  allowed).  The  data
              type  of  the default expression must match the data type of the
              column.

              The default expression will be used in any insert operation that
              does  not specify a value for the column. If there is no default
              for a column, then the default is null.

       INHERITS ( parent_table [, ... ] )
              The optional INHERITS clause specifies a  list  of  tables  from
              which the new table automatically inherits all columns.

              Use  of  INHERITS  creates a persistent relationship between the
              new child table and its parent table(s). Schema modifications to
              the  parent(s)  normally  propagate  to children as well, and by
              default the data of the child table is included in scans of  the
              parent(s).

              If the same column name exists in more than one parent table, an
              error is reported unless the data types of the columns match  in
              each  of  the  parent  tables. If there is no conflict, then the
              duplicate columns are merged to form a single column in the  new
              table.  If the column name list of the new table contains a col-
              umn name that is also inherited, the  data  type  must  likewise
              match  the  inherited  column(s), and the column definitions are
              merged into one. However, inherited and new column  declarations
              of  the  same  name  need not specify identical constraints: all
              constraints provided from any declaration  are  merged  together
              and  all  are applied to the new table. If the new table explic-
              itly specifies a default value  for  the  column,  this  default
              overrides  any  defaults from inherited declarations of the col-
              umn. Otherwise, any parents that specify default values for  the
              column  must  all  specify the same default, or an error will be
              reported.

       LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ]
              The LIKE clause specifies a table from which the new table auto-
              matically  copies  all column names, their data types, and their
              not-null constraints.

              Unlike INHERITS, the new table and original table are completely
              decoupled  after  creation  is complete. Changes to the original
              table will not be applied to the new table, and it is not possi-
              ble  to  include  data of the new table in scans of the original
              table.

              Default expressions for the copied column definitions will  only
              be copied if INCLUDING DEFAULTS is specified. The default behav-
              ior is to exclude default expressions, resulting in all  columns
              of the new table having null defaults.

       WITH OIDS

       WITHOUT OIDS
              This  optional  clause  specifies  whether rows of the new table
              should have OIDs (object identifiers) assigned to them. If  nei-
              ther  WITH OIDS nor WITHOUT OIDS is specified, the default value
              depends upon the default_with_oids configuration parameter.  (If
              the new table inherits from any tables that have OIDs, then WITH
              OIDS is forced even if the command says WITHOUT OIDS.)

              If WITHOUT OIDS is specified or implied, the new table does  not
              store  OIDs  and no OID will be assigned for a row inserted into
              it. This is  generally  considered  worthwhile,  since  it  will
              reduce  OID  consumption  and thereby postpone the wraparound of
              the 32-bit OID counter. Once the counter wraps around, OIDs  can
              no longer be assumed to be unique, which makes them considerably
              less useful. In addition, excluding OIDs from  a  table  reduces
              the space required to store the table on disk by 4 bytes per row
              (on most machines), slightly improving performance.

              To remove OIDs from a table after it has been created, use ALTER
              TABLE [alter_table(l)].

       CONSTRAINT constraint_name
              An optional name for a column or table constraint. If not speci-
              fied, the system generates a name.

       NOT NULL
              The column is not allowed to contain null values.

       NULL   The column is allowed  to  contain  null  values.  This  is  the
              default.

              This clause is only provided for compatibility with non-standard
              SQL databases. Its use is discouraged in new applications.

       UNIQUE (column constraint)

       UNIQUE ( column_name [, ... ] ) (table constraint)
              The UNIQUE constraint specifies that a group of one or more col-
              umns  of a table may contain only unique values. The behavior of
              the unique table constraint is the same as that for column  con-
              straints,  with  the additional capability to span multiple col-
              umns.

              For the purpose of a unique constraint, null values are not con-
              sidered equal.

              Each  unique table constraint must name a set of columns that is
              different from the set of columns named by any other  unique  or
              primary  key  constraint  defined  for  the table. (Otherwise it
              would just be the same constraint listed twice.)

       PRIMARY KEY (column constraint)

       PRIMARY KEY ( column_name [, ... ] ) (table constraint)
              The primary key constraint specifies that a column or columns of
              a table may contain only unique (non-duplicate), nonnull values.
              Technically, PRIMARY KEY is merely a combination of  UNIQUE  and
              NOT  NULL,  but identifying a set of columns as primary key also
              provides metadata about the design of the schema, as  a  primary
              key implies that other tables may rely on this set of columns as
              a unique identifier for rows.

              Only one primary key can be specified for a table, whether as  a
              column constraint or a table constraint.

              The  primary key constraint should name a set of columns that is
              different from other sets of columns named by  any  unique  con-
              straint defined for the same table.

       CHECK (expression)
              The  CHECK  clause  specifies  an expression producing a Boolean
              result which new or updated rows must satisfy for an  insert  or
              update  operation  to succeed. Expressions evaluating to TRUE or
              UNKNOWN succeed. Should any row of an insert or update operation
              produce  a  FALSE  result  an  error exception is raised and the
              insert or update does not alter the database. A check constraint
              specified  as a column constraint should reference that column's
              value only, while an expression appearing in a table  constraint
              may reference multiple columns.

              Currently, CHECK expressions cannot contain subqueries nor refer
              to variables other than columns of the current row.

       REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [  ON  DELETE
       action ] [ ON UPDATE action ] (column constraint)

       FOREIGN KEY ( column [, ... ] )
              These clauses specify a foreign key constraint,  which  requires
              that  a  group of one or more columns of the new table must only
              contain values that match values in the referenced column(s)  of
              some  row  of the referenced table. If refcolumn is omitted, the
              primary key of the reftable is used. The referenced columns must
              be the columns of a unique or primary key constraint in the ref-
              erenced table. Note that foreign  key  constraints  may  not  be
              defined between temporary tables and permanent tables.

              A  value  inserted  into  the  referencing  column(s) is matched
              against the values of the referenced table and  referenced  col-
              umns  using  the  given match type. There are three match types:
              MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is  also  the
              default.  MATCH  FULL will not allow one column of a multicolumn
              foreign key to be null unless all foreign key columns are  null.
              MATCH  SIMPLE  allows  some foreign key columns to be null while
              other parts of the foreign key are not null.  MATCH  PARTIAL  is
              not yet implemented.

              In addition, when the data in the referenced columns is changed,
              certain actions are performed on the data in this  table's  col-
              umns.  The ON DELETE clause specifies the action to perform when
              a referenced row in the referenced table is being deleted. Like-
              wise,  the ON UPDATE clause specifies the action to perform when
              a referenced column in the referenced table is being updated  to
              a new value. If the row is updated, but the referenced column is
              not actually changed, no action  is  done.  Referential  actions
              other  than  the NO ACTION check cannot be deferred, even if the
              constraint is declared deferrable. There are the following  pos-
              sible actions for each clause:

              NO ACTION
                     Produce  an  error indicating that the deletion or update
                     would create a foreign key constraint violation.  If  the
                     constraint  is  deferred,  this error will be produced at
                     constraint check time if there still exist any  referenc-
                     ing rows. This is the default action.

              RESTRICT
                     Produce  an  error indicating that the deletion or update
                     would create a foreign key constraint violation.  This is
                     the  same  as  NO  ACTION  except  that  the check is not
                     deferrable.

              CASCADE
                     Delete any rows referencing the deleted  row,  or  update
                     the  value  of the referencing column to the new value of
                     the referenced column, respectively.

              SET NULL
                     Set the referencing column(s) to null.

              SET DEFAULT
                     Set the referencing column(s) to their default values.

       If the referenced column(s) are changed frequently, it may be  wise  to
       add  an  index  to  the  foreign key column so that referential actions
       associated with the foreign key column  can  be  performed  more  effi-
       ciently.

       DEFERRABLE

       NOT DEFERRABLE
              This  controls  whether  the  constraint can be deferred. A con-
              straint that is not deferrable will be checked immediately after
              every  command.  Checking of constraints that are deferrable may
              be postponed until the end of the  transaction  (using  the  SET
              CONSTRAINTS  [set_constraints(l)]  command).   NOT DEFERRABLE is
              the default. Only foreign key constraints currently accept  this
              clause. All other constraint types are not deferrable.

       INITIALLY IMMEDIATE

       INITIALLY DEFERRED
              If a constraint is deferrable, this clause specifies the default
              time to check the constraint. If  the  constraint  is  INITIALLY
              IMMEDIATE,  it  is  checked  after  each  statement. This is the
              default. If the constraint is INITIALLY DEFERRED, it is  checked
              only  at  the  end of the transaction. The constraint check time
              can be altered with  the  SET  CONSTRAINTS  [set_constraints(l)]
              command.

       ON COMMIT
              The  behavior  of  temporary  tables at the end of a transaction
              block can be controlled using ON COMMIT.  The three options are:

              PRESERVE ROWS
                     No  special  action is taken at the ends of transactions.
                     This is the default behavior.

              DELETE ROWS
                     All rows in the temporary table will be  deleted  at  the
                     end  of each transaction block. Essentially, an automatic
                     truncate(l) is done at each commit.

              DROP   The temporary table will be dropped at  the  end  of  the
                     current transaction block.

       TABLESPACE tablespace
              The  tablespace  is  the name of the tablespace in which the new
              table is to be created.  If not specified, default_tablespace is
              used, or the database's default tablespace if default_tablespace
              is an empty string.

       USING INDEX TABLESPACE tablespace
              This clause allows selection of  the  tablespace  in  which  the
              index associated with a UNIQUE or PRIMARY KEY constraint will be
              created.  If not specified, default_tablespace is used,  or  the
              database's  default tablespace if default_tablespace is an empty
              string.


NOTES

       Using OIDs in new applications  is  not  recommended:  where  possible,
       using  a  SERIAL or other sequence generator as the table's primary key
       is preferred. However, if your application does make  use  of  OIDs  to
       identify specific rows of a table, it is recommended to create a unique
       constraint on the oid column of that table, to ensure that OIDs in  the
       table will indeed uniquely identify rows even after counter wraparound.
       Avoid assuming that OIDs are unique across tables; if you need a  data-
       base-wide  unique  identifier,  use the combination of tableoid and row
       OID for the purpose.

              Tip: The use of WITHOUT OIDS is not recommended for tables  with
              no  primary  key,  since  without either an OID or a unique data
              key, it is difficult to identify specific rows.

       PostgreSQL automatically creates an index for  each  unique  constraint
       and  primary key constraint to enforce uniqueness. Thus, it is not nec-
       essary to create an index explicitly for primary key columns. (See CRE-
       ATE INDEX [create_index(l)] for more information.)

       Unique  constraints  and  primary keys are not inherited in the current
       implementation. This makes the combination of  inheritance  and  unique
       constraints rather dysfunctional.

       A table cannot have more than 1600 columns. (In practice, the effective
       limit is lower because of tuple-length constraints.)


EXAMPLES

       Create table films and table distributors:

       CREATE TABLE films (
           code        char(5) CONSTRAINT firstkey PRIMARY KEY,
           title       varchar(40) NOT NULL,
           did         integer NOT NULL,
           date_prod   date,
           kind        varchar(10),
           len         interval hour to minute
       );

       CREATE TABLE distributors (
            did    integer PRIMARY KEY DEFAULT nextval('serial'),
            name   varchar(40) NOT NULL CHECK (name <> '')
       );

       Create a table with a 2-dimensional array:

       CREATE TABLE array_int (
           vector  int[][]
       );

       Define a unique table constraint for the table films. Unique table con-
       straints can be defined on one or more columns of the table.

       CREATE TABLE films (
           code        char(5),
           title       varchar(40),
           did         integer,
           date_prod   date,
           kind        varchar(10),
           len         interval hour to minute,
           CONSTRAINT production UNIQUE(date_prod)
       );

       Define a check column constraint:

       CREATE TABLE distributors (
           did     integer CHECK (did > 100),
           name    varchar(40)
       );

       Define a check table constraint:

       CREATE TABLE distributors (
           did     integer,
           name    varchar(40)
           CONSTRAINT con1 CHECK (did > 100 AND name <> '')
       );

       Define  a primary key table constraint for the table films. Primary key
       table constraints can be defined on one or more columns of the table.

       CREATE TABLE films (
           code        char(5),
           title       varchar(40),
           did         integer,
           date_prod   date,
           kind        varchar(10),
           len         interval hour to minute,
           CONSTRAINT code_title PRIMARY KEY(code,title)
       );

       Define a primary key constraint for table distributors.  The  following
       two  examples are equivalent, the first using the table constraint syn-
       tax, the second the column constraint syntax.

       CREATE TABLE distributors (
           did     integer,
           name    varchar(40),
           PRIMARY KEY(did)
       );

       CREATE TABLE distributors (
           did     integer PRIMARY KEY,
           name    varchar(40)
       );

       This assigns a literal constant default  value  for  the  column  name,
       arranges for the default value of column did to be generated by select-
       ing the next value of a sequence object, and makes the default value of
       modtime be the time at which the row is inserted.

       CREATE TABLE distributors (
           name      varchar(40) DEFAULT 'Luso Films',
           did       integer DEFAULT nextval('distributors_serial'),
           modtime   timestamp DEFAULT current_timestamp
       );

       Define  two  NOT NULL column constraints on the table distributors, one
       of which is explicitly given a name:

       CREATE TABLE distributors (
           did     integer CONSTRAINT no_null NOT NULL,
           name    varchar(40) NOT NULL
       );

       Define a unique constraint for the name column:

       CREATE TABLE distributors (
           did     integer,
           name    varchar(40) UNIQUE
       );

       The above is equivalent to the following  specified  as  a  table  con-
       straint:

       CREATE TABLE distributors (
           did     integer,
           name    varchar(40),
           UNIQUE(name)
       );

       Create table cinemas in tablespace diskvol1:

       CREATE TABLE cinemas (
               id serial,
               name text,
               location text
       ) TABLESPACE diskvol1;


COMPATIBILITY

       The  CREATE TABLE command conforms to the SQL standard, with exceptions
       listed below.

   TEMPORARY TABLES
       Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
       standard, the effect is not the same. In the standard, temporary tables
       are defined just once and automatically exist (starting with empty con-
       tents)  in  every session that needs them.  PostgreSQL instead requires
       each session to issue its own CREATE TEMPORARY TABLE command  for  each
       temporary  table  to be used. This allows different sessions to use the
       same temporary table name for different  purposes,  whereas  the  stan-
       dard's  approach  constrains  all  instances of a given temporary table
       name to have the same table structure.

       The standard's definition of the behavior of temporary tables is widely
       ignored. PostgreSQL's behavior on this point is similar to that of sev-
       eral other SQL databases.

       The standard's distinction between global and local temporary tables is
       not  in  PostgreSQL,  since  that distinction depends on the concept of
       modules, which PostgreSQL does not  have.   For  compatibility's  sake,
       PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary ta-
       ble declaration, but they have no effect.

       The ON COMMIT clause for temporary tables also resembles the SQL  stan-
       dard,  but  has  some differences.  If the ON COMMIT clause is omitted,
       SQL specifies that the default behavior is ON COMMIT DELETE ROWS.  How-
       ever,  the  default  behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
       The ON COMMIT DROP option does not exist in SQL.

   COLUMN CHECK CONSTRAINTS
       The SQL standard says that CHECK column constraints may only  refer  to
       the  column  they  apply  to; only CHECK table constraints may refer to
       multiple columns.  PostgreSQL does not  enforce  this  restriction;  it
       treats column and table check constraints alike.

   NULL ``CONSTRAINT''
       The  NULL  ``constraint''  (actually  a non-constraint) is a PostgreSQL
       extension to the SQL standard that is included for  compatibility  with
       some  other  database  systems (and for symmetry with the NOT NULL con-
       straint). Since it is the default for any column, its presence is  sim-
       ply noise.

   INHERITANCE
       Multiple  inheritance  via the INHERITS clause is a PostgreSQL language
       extension.  SQL:1999 and later define single inheritance using  a  dif-
       ferent  syntax  and  different semantics. SQL:1999-style inheritance is
       not yet supported by PostgreSQL.

   OBJECT IDS
       The PostgreSQL concept of OIDs is not standard.

   ZERO-COLUMN TABLES
       PostgreSQL allows a table of no columns to  be  created  (for  example,
       CREATE TABLE foo();). This is an extension from the SQL standard, which
       does not allow zero-column tables. Zero-column tables are not in  them-
       selves  very useful, but disallowing them creates odd special cases for
       ALTER TABLE DROP COLUMN, so  it  seems  cleaner  to  ignore  this  spec
       restriction.

   TABLESPACES
       The  PostgreSQL  concept  of  tablespaces  is not part of the standard.
       Hence, the clauses TABLESPACE and USING  INDEX  TABLESPACE  are  exten-
       sions.


SEE ALSO

       ALTER   TABLE  [alter_table(l)],  DROP  TABLE  [drop_table(l)],  CREATE
       TABLESPACE [create_tablespace(l)]

SQL - Language Statements         2005-11-05                    CREATE TABLE()

Man(1) output converted with man2html