1CREATE TABLE(7) SQL Commands CREATE TABLE(7)
2
6 CREATE TABLE - define a new table
7
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 where column_constraint is:
22 [ 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 and table_constraint is:
34 [ 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 index_parameters in UNIQUE and PRIMARY KEY constraints are:
44 [ WITH ( storage_parameter [= value] [, ... ] ) ]
46 [ USING INDEX TABLESPACE tablespace ]
47
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 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 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 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 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
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 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 table_name
93 The name (optionally schema-qualified) of the table to be cre‐
94 ated.
95 column_name
97 The name of a column to be created in the new table.
98 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 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 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 INHERITS ( parent_table [, ... ] )
117 The optional INHERITS clause specifies a list of tables from
118 which the new table automatically inherits all columns.
119 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 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 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 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 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 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 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 Any indexes on the original table will not be created on the new
173 table, unless the INCLUDING INDEXES clause is specified.
174 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 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 NOT NULL
190 The column is not allowed to contain null values.
191 NULL The column is allowed to contain null values. This is the
193 default.
194 This clause is only provided for compatibility with non-standard
196 SQL databases. Its use is discouraged in new applications.
197 UNIQUE (column constraint)
199 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 For the purpose of a unique constraint, null values are not con‐
208 sidered equal.
209 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 PRIMARY KEY (column constraint)
216 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 Only one primary key can be specified for a table, whether as a
227 column constraint or a table constraint.
228 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 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 Currently, CHECK expressions cannot contain subqueries nor refer
245 to variables other than columns of the current row.
246 REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
248 action ] [ ON UPDATE action ] (column constraint)
249 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 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 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 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 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 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 SET NULL
301 Set the referencing column(s) to null.
302 SET DEFAULT
304 Set the referencing column(s) to their default values.
305 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 DEFERRABLE
312 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 INITIALLY IMMEDIATE
323 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 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 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 To remove OIDs from a table after it has been created, use ALTER
356 TABLE [alter_table(7)].
357 WITH OIDS
359 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 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 PRESERVE ROWS
371 No special action is taken at the ends of transactions.
372 This is the default behavior.
373 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 DROP The temporary table will be dropped at the end of the
380 current transaction block.
381 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 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 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 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 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 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 autovacuum_vacuum_scale_factor, toast.autovacuum_vacuum_scale_factor
440 (float4)
441 Multiplier for reltuples to add to autovacuum_vacuum_threshold.
442 autovacuum_analyze_threshold (integer)
444 Minimum number of inserted, updated, or deleted tuples before
445 initiate an ANALYZE operation on a particular table.
446 autovacuum_analyze_scale_factor (float4)
448 Multiplier for reltuples to add to autovacuum_analyze_threshold.
449 autovacuum_vacuum_cost_delay, toast.autovacuum_vacuum_cost_delay (inte‐
451 ger)
452 Custom autovacuum_vacuum_cost_delay parameter.
453 autovacuum_vacuum_cost_limit, toast.autovacuum_vacuum_cost_limit (inte‐
455 ger)
456 Custom autovacuum_vacuum_cost_limit parameter.
457 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 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 autovacuum_freeze_table_age, toast.autovacuum_freeze_table_age (inte‐
473 ger)
474 Custom vacuum_freeze_table_age parameter.
475
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 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 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 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 A table cannot have more than 1600 columns. (In practice, the effective
502 limit is usually lower because of tuple-length constraints.)
503
505 Create table films and table distributors:
506 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 CREATE TABLE distributors (
518 did integer PRIMARY KEY DEFAULT nextval('serial'),
519 name varchar(40) NOT NULL CHECK (name <> '')
520 );
521 Create a table with a 2-dimensional array:
524 CREATE TABLE array_int (
526 vector int[][]
527 );
528 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 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 Define a check column constraint:
545 CREATE TABLE distributors (
547 did integer CHECK (did > 100),
548 name varchar(40)
549 );
550 Define a check table constraint:
553 CREATE TABLE distributors (
555 did integer,
556 name varchar(40)
557 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
558 );
559 Define a primary key table constraint for the table films:
562 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 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 CREATE TABLE distributors (
579 did integer,
580 name varchar(40),
581 PRIMARY KEY(did)
582 );
583 CREATE TABLE distributors (
586 did integer PRIMARY KEY,
587 name varchar(40)
588 );
589 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 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 Define two NOT NULL column constraints on the table distributors, one
604 of which is explicitly given a name:
605 CREATE TABLE distributors (
607 did integer CONSTRAINT no_null NOT NULL,
608 name varchar(40) NOT NULL
609 );
610 Define a unique constraint for the name column:
613 CREATE TABLE distributors (
615 did integer,
616 name varchar(40) UNIQUE
617 );
618 The same, specified as a table constraint:
620 CREATE TABLE distributors (
622 did integer,
623 name varchar(40),
624 UNIQUE(name)
625 );
626 Create the same table, specifying 70% fill factor for both the table
629 and its unique index:
630 CREATE TABLE distributors (
632 did integer,
633 name varchar(40),
634 UNIQUE(name) WITH (fillfactor=70)
635 )
636 WITH (fillfactor=70);
637 Create table cinemas in tablespace diskvol1:
640 CREATE TABLE cinemas (
642 id serial,
643 name text,
644 location text
645 ) TABLESPACE diskvol1;
646
649 The CREATE TABLE command conforms to the SQL standard, with exceptions
650 listed below.
651 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 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 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 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 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 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 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 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 WITH CLAUSE
707 The WITH clause is a PostgreSQL extension; neither storage parameters
708 nor OIDs are in the standard.
709 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
716 ALTER TABLE [alter_table(7)], DROP TABLE [drop_table(7)], CREATE
717 TABLESPACE [create_tablespace(7)]
718SQL - Language Statements 2011-09-22 CREATE TABLE(7)