1CREATE TABLE(7)                  SQL Commands                  CREATE TABLE(7)
2
3
4

NAME

6       CREATE TABLE - define a new table
7
8

SYNOPSIS

10       CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
11         { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
12           | table_constraint
13           | LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES } ] ... }
14           [, ... ]
15       ] )
16       [ INHERITS ( parent_table [, ... ] ) ]
17       [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
18       [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
19       [ TABLESPACE tablespace ]
20
21       where column_constraint is:
22
23       [ CONSTRAINT constraint_name ]
24       { NOT NULL |
25         NULL |
26         UNIQUE index_parameters |
27         PRIMARY KEY index_parameters |
28         CHECK ( expression ) |
29         REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
30           [ ON DELETE action ] [ ON UPDATE action ] }
31       [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
32
33       and table_constraint is:
34
35       [ CONSTRAINT constraint_name ]
36       { UNIQUE ( column_name [, ... ] ) index_parameters |
37         PRIMARY KEY ( column_name [, ... ] ) index_parameters |
38         CHECK ( expression ) |
39         FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
40           [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
41       [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
42
43       index_parameters in UNIQUE and PRIMARY KEY constraints are:
44
45       [ WITH ( storage_parameter [= value] [, ... ] ) ]
46       [ USING INDEX TABLESPACE tablespace ]
47
48

DESCRIPTION

50       CREATE  TABLE  will  create a new, initially empty table in the current
51       database. The table will be owned by the user issuing the command.
52
53       If a schema name is given (for example, CREATE  TABLE  myschema.mytable
54       ...) then the table is created in the specified schema. Otherwise it is
55       created in the current schema. Temporary  tables  exist  in  a  special
56       schema,  so a schema name cannot be given when creating a temporary ta‐
57       ble. The name of the table must be distinct from the name of any  other
58       table, sequence, index, or view in the same schema.
59
60       CREATE TABLE also automatically creates a data type that represents the
61       composite type corresponding to one row of the table. Therefore, tables
62       cannot have the same name as any existing data type in the same schema.
63
64       The optional constraint clauses specify constraints (tests) that new or
65       updated rows must satisfy for an insert or update operation to succeed.
66       A constraint is an SQL object that helps define the set of valid values
67       in the table in various ways.
68
69       There are two ways to define constraints: table constraints and  column
70       constraints. A column constraint is defined as part of a column defini‐
71       tion. A table constraint definition is not tied to a particular column,
72       and it can encompass more than one column.  Every column constraint can
73       also be written as a table constraint; a column constraint  is  only  a
74       notational  convenience  for  use  when the constraint only affects one
75       column.
76

PARAMETERS

78       TEMPORARY or TEMP
79              If specified, the table is created as a temporary table.  Tempo‐
80              rary  tables  are automatically dropped at the end of a session,
81              or optionally at the end of the current transaction (see ON COM‐
82              MIT below). Existing permanent tables with the same name are not
83              visible to the current session while the temporary table exists,
84              unless  they  are  referenced  with  schema-qualified names. Any
85              indexes created on a temporary table are automatically temporary
86              as well.
87
88              Optionally,  GLOBAL  or LOCAL can be written before TEMPORARY or
89              TEMP.  This makes no difference in PostgreSQL, but see  Compati‐
90              bility [create_table(7)].
91
92       table_name
93              The  name  (optionally schema-qualified) of the table to be cre‐
94              ated.
95
96       column_name
97              The name of a column to be created in the new table.
98
99       data_type
100              The data type of the column. This can include array  specifiers.
101              For  more information on the data types supported by PostgreSQL,
102              refer to in the documentation.
103
104       DEFAULT
105              The DEFAULT clause assigns a default data value for  the  column
106              whose  column  definition  it  appears  within. The value is any
107              variable-free expression  (subqueries  and  cross-references  to
108              other  columns  in  the current table are not allowed). The data
109              type of the default expression must match the data type  of  the
110              column.
111
112              The default expression will be used in any insert operation that
113              does not specify a value for the column. If there is no  default
114              for a column, then the default is null.
115
116       INHERITS ( parent_table [, ... ] )
117              The  optional  INHERITS  clause  specifies a list of tables from
118              which the new table automatically inherits all columns.
119
120              Use of INHERITS creates a persistent  relationship  between  the
121              new child table and its parent table(s). Schema modifications to
122              the parent(s) normally propagate to children  as  well,  and  by
123              default  the data of the child table is included in scans of the
124              parent(s).
125
126              If the same column name exists in more than one parent table, an
127              error  is reported unless the data types of the columns match in
128              each of the parent tables. If there is  no  conflict,  then  the
129              duplicate  columns are merged to form a single column in the new
130              table. If the column name list of the new table contains a  col‐
131              umn  name  that  is  also inherited, the data type must likewise
132              match the inherited column(s), and the  column  definitions  are
133              merged into one. If the new table explicitly specifies a default
134              value for the column, this default overrides any  defaults  from
135              inherited  declarations  of  the  column. Otherwise, any parents
136              that specify default values for the column must all specify  the
137              same default, or an error will be reported.
138
139              CHECK constraints are merged in essentially the same way as col‐
140              umns: if multiple parent tables and/or the new table  definition
141              contain  identically-named  CHECK constraints, these constraints
142              must all have the same check expression, or  an  error  will  be
143              reported.  Constraints  having the same name and expression will
144              be merged into one copy. Notice that an unnamed CHECK constraint
145              in  the new table will never be merged, since a unique name will
146              always be chosen for it.
147
148       LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS |  CONSTRAINTS
149       | INDEXES } ]
150              The LIKE clause specifies a table from which the new table auto‐
151              matically copies all column names, their data types,  and  their
152              not-null constraints.
153
154              Unlike INHERITS, the new table and original table are completely
155              decoupled after creation is complete. Changes  to  the  original
156              table will not be applied to the new table, and it is not possi‐
157              ble to include data of the new table in scans  of  the  original
158              table.
159
160              Default  expressions for the copied column definitions will only
161              be copied if INCLUDING DEFAULTS is specified. The default behav‐
162              ior  is  to exclude default expressions, resulting in the copied
163              columns in the new table having null defaults.
164
165              Not-null constraints are always copied to the new table.   CHECK
166              constraints  will  only  be  copied  if INCLUDING CONSTRAINTS is
167              specified; other types of  constraints  will  never  be  copied.
168              Also,  no distinction is made between column constraints and ta‐
169              ble constraints — when constraints are requested, all check con‐
170              straints are copied.
171
172              Any indexes on the original table will not be created on the new
173              table, unless the INCLUDING INDEXES clause is specified.
174
175              Note also that unlike INHERITS, copied columns  and  constraints
176              are not merged with similarly named columns and constraints.  If
177              the same name is specified explicitly or in another LIKE clause,
178              an error is signalled.
179
180       CONSTRAINT constraint_name
181              An  optional  name for a column or table constraint. If the con‐
182              straint is violated, the constraint name  is  present  in  error
183              messages,  so  constraint names like col must be positive can be
184              used to communicate helpful  constraint  information  to  client
185              applications.   (Double-quotes  are needed to specify constraint
186              names that contain spaces.)  If a constraint name is not  speci‐
187              fied, the system generates a name.
188
189       NOT NULL
190              The column is not allowed to contain null values.
191
192       NULL   The  column  is  allowed  to  contain  null  values. This is the
193              default.
194
195              This clause is only provided for compatibility with non-standard
196              SQL databases. Its use is discouraged in new applications.
197
198       UNIQUE (column constraint)
199
200       UNIQUE ( column_name [, ... ] ) (table constraint)
201              The UNIQUE constraint specifies that a group of one or more col‐
202              umns of a table can contain only unique values. The behavior  of
203              the  unique table constraint is the same as that for column con‐
204              straints, with the additional capability to span  multiple  col‐
205              umns.
206
207              For the purpose of a unique constraint, null values are not con‐
208              sidered equal.
209
210              Each unique table constraint must name a set of columns that  is
211              different  from  the set of columns named by any other unique or
212              primary key constraint defined  for  the  table.  (Otherwise  it
213              would just be the same constraint listed twice.)
214
215       PRIMARY KEY (column constraint)
216
217       PRIMARY KEY ( column_name [, ... ] ) (table constraint)
218              The primary key constraint specifies that a column or columns of
219              a table can contain only unique (non-duplicate), nonnull values.
220              Technically,  PRIMARY  KEY is merely a combination of UNIQUE and
221              NOT NULL, but identifying a set of columns as primary  key  also
222              provides  metadata  about the design of the schema, as a primary
223              key implies that other tables can rely on this set of columns as
224              a unique identifier for rows.
225
226              Only  one primary key can be specified for a table, whether as a
227              column constraint or a table constraint.
228
229              The primary key constraint should name a set of columns that  is
230              different  from  other  sets of columns named by any unique con‐
231              straint defined for the same table.
232
233       CHECK ( expression )
234              The CHECK clause specifies an  expression  producing  a  Boolean
235              result  which  new or updated rows must satisfy for an insert or
236              update operation to succeed. Expressions evaluating to  TRUE  or
237              UNKNOWN succeed. Should any row of an insert or update operation
238              produce a FALSE result an error  exception  is  raised  and  the
239              insert or update does not alter the database. A check constraint
240              specified as a column constraint should reference that  column's
241              value  only, while an expression appearing in a table constraint
242              can reference multiple columns.
243
244              Currently, CHECK expressions cannot contain subqueries nor refer
245              to variables other than columns of the current row.
246
247       REFERENCES  reftable  [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
248       action ] [ ON UPDATE action ] (column constraint)
249
250       FOREIGN KEY ( column [, ... ] )
251              These clauses specify a foreign key constraint,  which  requires
252              that  a  group of one or more columns of the new table must only
253              contain values that match values in the referenced column(s)  of
254              some  row  of the referenced table. If refcolumn is omitted, the
255              primary key of the reftable is used. The referenced columns must
256              be the columns of a unique or primary key constraint in the ref‐
257              erenced table. Note  that  foreign  key  constraints  cannot  be
258              defined between temporary tables and permanent tables.
259
260              A  value  inserted  into  the  referencing  column(s) is matched
261              against the values of the referenced table and  referenced  col‐
262              umns  using  the  given match type. There are three match types:
263              MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is  also  the
264              default.  MATCH  FULL will not allow one column of a multicolumn
265              foreign key to be null unless all foreign key columns are  null.
266              MATCH  SIMPLE  allows  some foreign key columns to be null while
267              other parts of the foreign key are not null.  MATCH  PARTIAL  is
268              not yet implemented.
269
270              In addition, when the data in the referenced columns is changed,
271              certain actions are performed on the data in this  table's  col‐
272              umns.  The ON DELETE clause specifies the action to perform when
273              a referenced row in the referenced table is being deleted. Like‐
274              wise,  the ON UPDATE clause specifies the action to perform when
275              a referenced column in the referenced table is being updated  to
276              a new value. If the row is updated, but the referenced column is
277              not actually changed, no action  is  done.  Referential  actions
278              other  than  the NO ACTION check cannot be deferred, even if the
279              constraint is declared deferrable. There are the following  pos‐
280              sible actions for each clause:
281
282              NO ACTION
283                     Produce  an  error indicating that the deletion or update
284                     would create a foreign key constraint violation.  If  the
285                     constraint  is  deferred,  this error will be produced at
286                     constraint check time if there still exist any  referenc‐
287                     ing rows. This is the default action.
288
289              RESTRICT
290                     Produce  an  error indicating that the deletion or update
291                     would create a foreign key constraint violation.  This is
292                     the  same  as  NO  ACTION  except  that  the check is not
293                     deferrable.
294
295              CASCADE
296                     Delete any rows referencing the deleted  row,  or  update
297                     the  value  of the referencing column to the new value of
298                     the referenced column, respectively.
299
300              SET NULL
301                     Set the referencing column(s) to null.
302
303              SET DEFAULT
304                     Set the referencing column(s) to their default values.
305
306       If the referenced column(s) are changed frequently, it might be wise to
307       add  an  index  to  the  foreign key column so that referential actions
308       associated with the foreign key column  can  be  performed  more  effi‐
309       ciently.
310
311       DEFERRABLE
312
313       NOT DEFERRABLE
314              This  controls  whether  the  constraint can be deferred. A con‐
315              straint that is not deferrable will be checked immediately after
316              every  command.  Checking of constraints that are deferrable can
317              be postponed until the end of the  transaction  (using  the  SET
318              CONSTRAINTS  [set_constraints(7)]  command).   NOT DEFERRABLE is
319              the default. Only foreign key constraints currently accept  this
320              clause. All other constraint types are not deferrable.
321
322       INITIALLY IMMEDIATE
323
324       INITIALLY DEFERRED
325              If a constraint is deferrable, this clause specifies the default
326              time to check the constraint. If  the  constraint  is  INITIALLY
327              IMMEDIATE,  it  is  checked  after  each  statement. This is the
328              default. If the constraint is INITIALLY DEFERRED, it is  checked
329              only  at  the  end of the transaction. The constraint check time
330              can be altered with  the  SET  CONSTRAINTS  [set_constraints(7)]
331              command.
332
333       WITH ( storage_parameter [= value] [, ... ] )
334              This clause specifies optional storage parameters for a table or
335              index; see Storage Parameters [create_table(7)] for more  infor‐
336              mation.  The  WITH clause for a table can also include OIDS=TRUE
337              (or just OIDS) to specify that rows of the new table should have
338              OIDs  (object  identifiers)  assigned  to them, or OIDS=FALSE to
339              specify that the rows should not have  OIDs.   If  OIDS  is  not
340              specified,    the    default    setting    depends    upon   the
341              default_with_oids configuration parameter.  (If  the  new  table
342              inherits  from  any  tables  that  have  OIDs, then OIDS=TRUE is
343              forced even if the command says OIDS=FALSE.)
344
345              If OIDS=FALSE is specified or implied, the new  table  does  not
346              store  OIDs  and no OID will be assigned for a row inserted into
347              it. This is  generally  considered  worthwhile,  since  it  will
348              reduce  OID  consumption  and thereby postpone the wraparound of
349              the 32-bit OID counter. Once the counter wraps around, OIDs  can
350              no longer be assumed to be unique, which makes them considerably
351              less useful. In addition, excluding OIDs from  a  table  reduces
352              the space required to store the table on disk by 4 bytes per row
353              (on most machines), slightly improving performance.
354
355              To remove OIDs from a table after it has been created, use ALTER
356              TABLE [alter_table(7)].
357
358       WITH OIDS
359
360       WITHOUT OIDS
361              These  are  obsolescent  syntaxes  equivalent to WITH (OIDS) and
362              WITH (OIDS=FALSE), respectively. If you wish  to  give  both  an
363              OIDS setting and storage parameters, you must use the WITH ( ...
364              ) syntax; see above.
365
366       ON COMMIT
367              The behavior of temporary tables at the  end  of  a  transaction
368              block can be controlled using ON COMMIT.  The three options are:
369
370              PRESERVE ROWS
371                     No  special  action is taken at the ends of transactions.
372                     This is the default behavior.
373
374              DELETE ROWS
375                     All rows in the temporary table will be  deleted  at  the
376                     end  of each transaction block. Essentially, an automatic
377                     TRUNCATE [truncate(7)] is done at each commit.
378
379              DROP   The temporary table will be dropped at  the  end  of  the
380                     current transaction block.
381
382       TABLESPACE tablespace
383              The  tablespace  is  the name of the tablespace in which the new
384              table is to be created.  If not specified, default_tablespace is
385              consulted, or temp_tablespaces if the table is temporary.
386
387       USING INDEX TABLESPACE tablespace
388              This  clause  allows  selection  of  the tablespace in which the
389              index associated with a UNIQUE or PRIMARY KEY constraint will be
390              created.   If not specified, default_tablespace is consulted, or
391              temp_tablespaces if the table is temporary.
392
393   STORAGE PARAMETERS
394       The WITH clause can specify storage  parameters  for  tables,  and  for
395       indexes  associated  with  a  UNIQUE or PRIMARY KEY constraint. Storage
396       parameters  for  indexes  are  documented   in   CREATE   INDEX   [cre‐
397       ate_index(7)].  The  storage  parameters currently available for tables
398       are listed below. For each parameter, unless noted, there is  an  addi‐
399       tional  parameter with the same name prefixed with toast., which can be
400       used to control the behavior of the table's secondary TOAST  table,  if
401       any  (see in the documentation for more information about TOAST).  Note
402       that the TOAST table inherits the autovacuum_* values from  its  parent
403       table, if there are no toast.autovacuum_* settings set.
404
405       fillfactor (integer)
406              The  fillfactor  for a table is a percentage between 10 and 100.
407              100 (complete packing) is the default. When a smaller fillfactor
408              is  specified,  INSERT  operations  pack table pages only to the
409              indicated percentage;  the  remaining  space  on  each  page  is
410              reserved  for  updating  rows  on that page. This gives UPDATE a
411              chance to place the updated copy of a row on the  same  page  as
412              the  original, which is more efficient than placing it on a dif‐
413              ferent page.  For a table whose entries are never updated,  com‐
414              plete  packing is the best choice, but in heavily updated tables
415              smaller fillfactors are appropriate. This  parameter  cannot  be
416              set for TOAST tables.
417
418       autovacuum_enabled, toast.autovacuum_enabled (boolean)
419              Enables or disables the autovacuum daemon on a particular table.
420              If true, the autovacuum daemon will initiate a VACUUM  operation
421              on  a  particular  table  when  the number of updated or deleted
422              tuples exceeds autovacuum_vacuum_threshold plus  autovacuum_vac‐
423              uum_scale_factor times the number of live tuples currently esti‐
424              mated to be in the relation.  Similarly,  it  will  initiate  an
425              ANALYZE  operation  when  the  number  of  inserted,  updated or
426              deleted tuples exceeds autovacuum_analyze_threshold  plus  auto‐
427              vacuum_analyze_scale_factor times the number of live tuples cur‐
428              rently estimated to be in the relation.  If  false,  this  table
429              will not be autovacuumed, except to prevent transaction Id wrap‐
430              around. See in the documentation for more about wraparound  pre‐
431              vention.  Observe that this variable inherits its value from the
432              autovacuum setting.
433
434       autovacuum_vacuum_threshold,  toast.autovacuum_vacuum_threshold  (inte‐
435       ger)
436              Minimum  number  of  updated or deleted tuples before initiate a
437              VACUUM operation on a particular table.
438
439       autovacuum_vacuum_scale_factor,    toast.autovacuum_vacuum_scale_factor
440       (float4)
441              Multiplier for reltuples to add to autovacuum_vacuum_threshold.
442
443       autovacuum_analyze_threshold (integer)
444              Minimum  number  of  inserted, updated, or deleted tuples before
445              initiate an ANALYZE operation on a particular table.
446
447       autovacuum_analyze_scale_factor (float4)
448              Multiplier for reltuples to add to autovacuum_analyze_threshold.
449
450       autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay (inte‐
451       ger)
452              Custom autovacuum_vacuum_cost_delay parameter.
453
454       autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit (inte‐
455       ger)
456              Custom autovacuum_vacuum_cost_limit parameter.
457
458       autovacuum_freeze_min_age, toast.autovacuum_freeze_min_age (integer)
459              Custom vacuum_freeze_min_age  parameter.  Note  that  autovacuum
460              will    ignore    attempts   to   set   a   per-table   autovac‐
461              uum_freeze_min_age larger than  the  half  system-wide  autovac‐
462              uum_freeze_max_age setting.
463
464       autovacuum_freeze_max_age, toast.autovacuum_freeze_max_age (integer)
465              Custom autovacuum_freeze_max_age parameter. Note that autovacuum
466              will   ignore   attempts   to   set   a    per-table    autovac‐
467              uum_freeze_max_age  larger  than the system-wide setting (it can
468              only be set smaller). Note  that  while  you  can  set  autovac‐
469              uum_freeze_max_age  very  small,  or  even zero, this is usually
470              unwise since it will force frequent vacuuming.
471
472       autovacuum_freeze_table_age,  toast.autovacuum_freeze_table_age  (inte‐
473       ger)
474              Custom vacuum_freeze_table_age parameter.
475

NOTES

477       Using  OIDs  in  new  applications  is not recommended: where possible,
478       using a SERIAL or other sequence generator as the table's  primary  key
479       is  preferred.  However,  if  your application does make use of OIDs to
480       identify specific rows of a table, it is recommended to create a unique
481       constraint  on the oid column of that table, to ensure that OIDs in the
482       table will indeed uniquely identify rows even after counter wraparound.
483       Avoid  assuming that OIDs are unique across tables; if you need a data‐
484       base-wide unique identifier, use the combination of  tableoid  and  row
485       OID for the purpose.
486
487              Tip: The use of OIDS=FALSE is not recommended for tables with no
488              primary key, since without either an OID or a unique  data  key,
489              it is difficult to identify specific rows.
490
491
492       PostgreSQL  automatically  creates  an index for each unique constraint
493       and primary key constraint to enforce uniqueness. Thus, it is not  nec‐
494       essary to create an index explicitly for primary key columns. (See CRE‐
495       ATE INDEX [create_index(7)] for more information.)
496
497       Unique constraints and primary keys are not inherited  in  the  current
498       implementation.  This  makes  the combination of inheritance and unique
499       constraints rather dysfunctional.
500
501       A table cannot have more than 1600 columns. (In practice, the effective
502       limit is usually lower because of tuple-length constraints.)
503

EXAMPLES

505       Create table films and table distributors:
506
507       CREATE TABLE films (
508           code        char(5) CONSTRAINT firstkey PRIMARY KEY,
509           title       varchar(40) NOT NULL,
510           did         integer NOT NULL,
511           date_prod   date,
512           kind        varchar(10),
513           len         interval hour to minute
514       );
515
516
517       CREATE TABLE distributors (
518            did    integer PRIMARY KEY DEFAULT nextval('serial'),
519            name   varchar(40) NOT NULL CHECK (name <> '')
520       );
521
522
523       Create a table with a 2-dimensional array:
524
525       CREATE TABLE array_int (
526           vector  int[][]
527       );
528
529
530       Define a unique table constraint for the table films. Unique table con‐
531       straints can be defined on one or more columns of the table:
532
533       CREATE TABLE films (
534           code        char(5),
535           title       varchar(40),
536           did         integer,
537           date_prod   date,
538           kind        varchar(10),
539           len         interval hour to minute,
540           CONSTRAINT production UNIQUE(date_prod)
541       );
542
543
544       Define a check column constraint:
545
546       CREATE TABLE distributors (
547           did     integer CHECK (did > 100),
548           name    varchar(40)
549       );
550
551
552       Define a check table constraint:
553
554       CREATE TABLE distributors (
555           did     integer,
556           name    varchar(40)
557           CONSTRAINT con1 CHECK (did > 100 AND name <> '')
558       );
559
560
561       Define a primary key table constraint for the table films:
562
563       CREATE TABLE films (
564           code        char(5),
565           title       varchar(40),
566           did         integer,
567           date_prod   date,
568           kind        varchar(10),
569           len         interval hour to minute,
570           CONSTRAINT code_title PRIMARY KEY(code,title)
571       );
572
573
574       Define a primary key constraint for table distributors.  The  following
575       two  examples are equivalent, the first using the table constraint syn‐
576       tax, the second the column constraint syntax:
577
578       CREATE TABLE distributors (
579           did     integer,
580           name    varchar(40),
581           PRIMARY KEY(did)
582       );
583
584
585       CREATE TABLE distributors (
586           did     integer PRIMARY KEY,
587           name    varchar(40)
588       );
589
590
591       Assign a literal constant default value for the  column  name,  arrange
592       for  the  default  value of column did to be generated by selecting the
593       next value of a sequence object, and make the default value of  modtime
594       be the time at which the row is inserted:
595
596       CREATE TABLE distributors (
597           name      varchar(40) DEFAULT 'Luso Films',
598           did       integer DEFAULT nextval('distributors_serial'),
599           modtime   timestamp DEFAULT current_timestamp
600       );
601
602
603       Define  two  NOT NULL column constraints on the table distributors, one
604       of which is explicitly given a name:
605
606       CREATE TABLE distributors (
607           did     integer CONSTRAINT no_null NOT NULL,
608           name    varchar(40) NOT NULL
609       );
610
611
612       Define a unique constraint for the name column:
613
614       CREATE TABLE distributors (
615           did     integer,
616           name    varchar(40) UNIQUE
617       );
618
619       The same, specified as a table constraint:
620
621       CREATE TABLE distributors (
622           did     integer,
623           name    varchar(40),
624           UNIQUE(name)
625       );
626
627
628       Create the same table, specifying 70% fill factor for  both  the  table
629       and its unique index:
630
631       CREATE TABLE distributors (
632           did     integer,
633           name    varchar(40),
634           UNIQUE(name) WITH (fillfactor=70)
635       )
636       WITH (fillfactor=70);
637
638
639       Create table cinemas in tablespace diskvol1:
640
641       CREATE TABLE cinemas (
642               id serial,
643               name text,
644               location text
645       ) TABLESPACE diskvol1;
646
647

COMPATIBILITY

649       The  CREATE TABLE command conforms to the SQL standard, with exceptions
650       listed below.
651
652   TEMPORARY TABLES
653       Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
654       standard, the effect is not the same. In the standard, temporary tables
655       are defined just once and automatically exist (starting with empty con‐
656       tents)  in  every session that needs them.  PostgreSQL instead requires
657       each session to issue its own CREATE TEMPORARY TABLE command  for  each
658       temporary  table  to be used. This allows different sessions to use the
659       same temporary table name for different  purposes,  whereas  the  stan‐
660       dard's  approach  constrains  all  instances of a given temporary table
661       name to have the same table structure.
662
663       The standard's definition of the behavior of temporary tables is widely
664       ignored. PostgreSQL's behavior on this point is similar to that of sev‐
665       eral other SQL databases.
666
667       The standard's distinction between global and local temporary tables is
668       not  in  PostgreSQL,  since  that distinction depends on the concept of
669       modules, which PostgreSQL does not  have.   For  compatibility's  sake,
670       PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary ta‐
671       ble declaration, but they have no effect.
672
673       The ON COMMIT clause for temporary tables also resembles the SQL  stan‐
674       dard,  but  has  some differences.  If the ON COMMIT clause is omitted,
675       SQL specifies that the default behavior is ON COMMIT DELETE ROWS.  How‐
676       ever,  the  default  behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
677       The ON COMMIT DROP option does not exist in SQL.
678
679   COLUMN CHECK CONSTRAINTS
680       The SQL standard says that CHECK column constraints can only  refer  to
681       the  column  they  apply  to; only CHECK table constraints can refer to
682       multiple columns.  PostgreSQL does not  enforce  this  restriction;  it
683       treats column and table check constraints alike.
684
685   NULL ``CONSTRAINT''
686       The  NULL  ``constraint''  (actually  a non-constraint) is a PostgreSQL
687       extension to the SQL standard that is included for  compatibility  with
688       some  other  database  systems (and for symmetry with the NOT NULL con‐
689       straint). Since it is the default for any column, its presence is  sim‐
690       ply noise.
691
692   INHERITANCE
693       Multiple  inheritance  via the INHERITS clause is a PostgreSQL language
694       extension.  SQL:1999 and later define single inheritance using  a  dif‐
695       ferent  syntax  and  different semantics. SQL:1999-style inheritance is
696       not yet supported by PostgreSQL.
697
698   ZERO-COLUMN TABLES
699       PostgreSQL allows a table of no columns to  be  created  (for  example,
700       CREATE TABLE foo();). This is an extension from the SQL standard, which
701       does not allow zero-column tables. Zero-column tables are not in  them‐
702       selves  very useful, but disallowing them creates odd special cases for
703       ALTER TABLE DROP COLUMN, so  it  seems  cleaner  to  ignore  this  spec
704       restriction.
705
706   WITH CLAUSE
707       The  WITH  clause is a PostgreSQL extension; neither storage parameters
708       nor OIDs are in the standard.
709
710   TABLESPACES
711       The PostgreSQL concept of tablespaces is  not  part  of  the  standard.
712       Hence,  the  clauses  TABLESPACE  and USING INDEX TABLESPACE are exten‐
713       sions.
714

SEE ALSO

716       ALTER  TABLE  [alter_table(7)],  DROP  TABLE  [drop_table(7)],   CREATE
717       TABLESPACE [create_tablespace(7)]
718
719
720
721SQL - Language Statements         2014-02-17                   CREATE TABLE(7)
Impressum