1CREATE TABLE() SQL Commands CREATE TABLE()
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 } ] ... }
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 may not 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 may 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. However, inherited and new column declarations
134 of the same name need not specify identical constraints: all
135 constraints provided from any declaration are merged together
136 and all are applied to the new table. If the new table explic‐
137 itly specifies a default value for the column, this default
138 overrides any defaults from inherited declarations of the col‐
139 umn. Otherwise, any parents that specify default values for the
140 column must all specify the same default, or an error will be
141 reported.
142 LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS
144 } ]
145 The LIKE clause specifies a table from which the new table auto‐
146 matically copies all column names, their data types, and their
147 not-null constraints.
148 Unlike INHERITS, the new table and original table are completely
150 decoupled after creation is complete. Changes to the original
151 table will not be applied to the new table, and it is not possi‐
152 ble to include data of the new table in scans of the original
153 table.
154 Default expressions for the copied column definitions will only
156 be copied if INCLUDING DEFAULTS is specified. The default behav‐
157 ior is to exclude default expressions, resulting in the copied
158 columns in the new table having null defaults.
159 Not-null constraints are always copied to the new table. CHECK
161 constraints will only be copied if INCLUDING CONSTRAINTS is
162 specified; other types of constraints will never be copied.
163 Also, no distinction is made between column constraints and ta‐
164 ble constraints — when constraints are requested, all check con‐
165 straints are copied.
166 Note also that unlike INHERITS, copied columns and constraints
168 are not merged with similarly named columns and constraints. If
169 the same name is specified explicitly or in another LIKE clause
170 an error is signalled.
171 CONSTRAINT constraint_name
173 An optional name for a column or table constraint. If the con‐
174 straint is violated, the constraint name is present in error
175 messages, so constraint names like col must be positive can be
176 used to communicate helpful constraint information to client
177 applications. (Double-quotes are needed to specify constraint
178 names that contain spaces.) If a constraint name is not speci‐
179 fied, the system generates a name.
180 NOT NULL
182 The column is not allowed to contain null values.
183 NULL The column is allowed to contain null values. This is the
185 default.
186 This clause is only provided for compatibility with non-standard
188 SQL databases. Its use is discouraged in new applications.
189 UNIQUE (column constraint)
191 UNIQUE ( column_name [, ... ] ) (table constraint)
193 The UNIQUE constraint specifies that a group of one or more col‐
194 umns of a table may contain only unique values. The behavior of
195 the unique table constraint is the same as that for column con‐
196 straints, with the additional capability to span multiple col‐
197 umns.
198 For the purpose of a unique constraint, null values are not con‐
200 sidered equal.
201 Each unique table constraint must name a set of columns that is
203 different from the set of columns named by any other unique or
204 primary key constraint defined for the table. (Otherwise it
205 would just be the same constraint listed twice.)
206 PRIMARY KEY (column constraint)
208 PRIMARY KEY ( column_name [, ... ] ) (table constraint)
210 The primary key constraint specifies that a column or columns of
211 a table may contain only unique (non-duplicate), nonnull values.
212 Technically, PRIMARY KEY is merely a combination of UNIQUE and
213 NOT NULL, but identifying a set of columns as primary key also
214 provides metadata about the design of the schema, as a primary
215 key implies that other tables may rely on this set of columns as
216 a unique identifier for rows.
217 Only one primary key can be specified for a table, whether as a
219 column constraint or a table constraint.
220 The primary key constraint should name a set of columns that is
222 different from other sets of columns named by any unique con‐
223 straint defined for the same table.
224 CHECK ( expression )
226 The CHECK clause specifies an expression producing a Boolean
227 result which new or updated rows must satisfy for an insert or
228 update operation to succeed. Expressions evaluating to TRUE or
229 UNKNOWN succeed. Should any row of an insert or update operation
230 produce a FALSE result an error exception is raised and the
231 insert or update does not alter the database. A check constraint
232 specified as a column constraint should reference that column's
233 value only, while an expression appearing in a table constraint
234 may reference multiple columns.
235 Currently, CHECK expressions cannot contain subqueries nor refer
237 to variables other than columns of the current row.
238 REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE
240 action ] [ ON UPDATE action ] (column constraint)
241 FOREIGN KEY ( column [, ... ] )
243 These clauses specify a foreign key constraint, which requires
244 that a group of one or more columns of the new table must only
245 contain values that match values in the referenced column(s) of
246 some row of the referenced table. If refcolumn is omitted, the
247 primary key of the reftable is used. The referenced columns must
248 be the columns of a unique or primary key constraint in the ref‐
249 erenced table. Note that foreign key constraints may not be
250 defined between temporary tables and permanent tables.
251 A value inserted into the referencing column(s) is matched
253 against the values of the referenced table and referenced col‐
254 umns using the given match type. There are three match types:
255 MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the
256 default. MATCH FULL will not allow one column of a multicolumn
257 foreign key to be null unless all foreign key columns are null.
258 MATCH SIMPLE allows some foreign key columns to be null while
259 other parts of the foreign key are not null. MATCH PARTIAL is
260 not yet implemented.
261 In addition, when the data in the referenced columns is changed,
263 certain actions are performed on the data in this table's col‐
264 umns. The ON DELETE clause specifies the action to perform when
265 a referenced row in the referenced table is being deleted. Like‐
266 wise, the ON UPDATE clause specifies the action to perform when
267 a referenced column in the referenced table is being updated to
268 a new value. If the row is updated, but the referenced column is
269 not actually changed, no action is done. Referential actions
270 other than the NO ACTION check cannot be deferred, even if the
271 constraint is declared deferrable. There are the following pos‐
272 sible actions for each clause:
273 NO ACTION
275 Produce an error indicating that the deletion or update
276 would create a foreign key constraint violation. If the
277 constraint is deferred, this error will be produced at
278 constraint check time if there still exist any referenc‐
279 ing rows. This is the default action.
280 RESTRICT
282 Produce an error indicating that the deletion or update
283 would create a foreign key constraint violation. This is
284 the same as NO ACTION except that the check is not
285 deferrable.
286 CASCADE
288 Delete any rows referencing the deleted row, or update
289 the value of the referencing column to the new value of
290 the referenced column, respectively.
291 SET NULL
293 Set the referencing column(s) to null.
294 SET DEFAULT
296 Set the referencing column(s) to their default values.
297 If the referenced column(s) are changed frequently, it may be wise to
299 add an index to the foreign key column so that referential actions
300 associated with the foreign key column can be performed more effi‐
301 ciently.
302 DEFERRABLE
304 NOT DEFERRABLE
306 This controls whether the constraint can be deferred. A con‐
307 straint that is not deferrable will be checked immediately after
308 every command. Checking of constraints that are deferrable may
309 be postponed until the end of the transaction (using the SET
310 CONSTRAINTS [set_constraints(7)] command). NOT DEFERRABLE is
311 the default. Only foreign key constraints currently accept this
312 clause. All other constraint types are not deferrable.
313 INITIALLY IMMEDIATE
315 INITIALLY DEFERRED
317 If a constraint is deferrable, this clause specifies the default
318 time to check the constraint. If the constraint is INITIALLY
319 IMMEDIATE, it is checked after each statement. This is the
320 default. If the constraint is INITIALLY DEFERRED, it is checked
321 only at the end of the transaction. The constraint check time
322 can be altered with the SET CONSTRAINTS [set_constraints(7)]
323 command.
324 WITH ( storage_parameter [= value] [, ... ] )
326 This clause specifies optional storage parameters for a table or
327 index; see Storage Parameters [create_table(7)] for more infor‐
328 mation. The WITH clause for a table can also include OIDS=TRUE
329 (or just OIDS) to specify that rows of the new table should have
330 OIDs (object identifiers) assigned to them, or OIDS=FALSE to
331 specify that the rows should not have OIDs. If OIDS is not
332 specified, the default setting depends upon the
333 default_with_oids configuration parameter. (If the new table
334 inherits from any tables that have OIDs, then OIDS=TRUE is
335 forced even if the command says OIDS=FALSE.)
336 If OIDS=FALSE is specified or implied, the new table does not
338 store OIDs and no OID will be assigned for a row inserted into
339 it. This is generally considered worthwhile, since it will
340 reduce OID consumption and thereby postpone the wraparound of
341 the 32-bit OID counter. Once the counter wraps around, OIDs can
342 no longer be assumed to be unique, which makes them considerably
343 less useful. In addition, excluding OIDs from a table reduces
344 the space required to store the table on disk by 4 bytes per row
345 (on most machines), slightly improving performance.
346 To remove OIDs from a table after it has been created, use ALTER
348 TABLE [alter_table(7)].
349 WITH OIDS
351 WITHOUT OIDS
353 These are obsolescent syntaxes equivalent to WITH (OIDS) and
354 WITH (OIDS=FALSE), respectively. If you wish to give both an
355 OIDS setting and storage parameters, you must use the WITH ( ...
356 ) syntax; see above.
357 ON COMMIT
359 The behavior of temporary tables at the end of a transaction
360 block can be controlled using ON COMMIT. The three options are:
361 PRESERVE ROWS
363 No special action is taken at the ends of transactions.
364 This is the default behavior.
365 DELETE ROWS
367 All rows in the temporary table will be deleted at the
368 end of each transaction block. Essentially, an automatic
369 TRUNCATE [truncate(7)] is done at each commit.
370 DROP The temporary table will be dropped at the end of the
372 current transaction block.
373 TABLESPACE tablespace
375 The tablespace is the name of the tablespace in which the new
376 table is to be created. If not specified, default_tablespace is
377 used, or the database's default tablespace if default_tablespace
378 is an empty string.
379 USING INDEX TABLESPACE tablespace
381 This clause allows selection of the tablespace in which the
382 index associated with a UNIQUE or PRIMARY KEY constraint will be
383 created. If not specified, default_tablespace is used, or the
384 database's default tablespace if default_tablespace is an empty
385 string.
386 STORAGE PARAMETERS
388 The WITH clause can specify storage parameters for tables, and for
389 indexes associated with a UNIQUE or PRIMARY KEY constraint. Storage
390 parameters for indexes are documented in CREATE INDEX [cre‐
391 ate_index(7)]. The only storage parameter currently available for
392 tables is:
393 FILLFACTOR
395 The fillfactor for a table is a percentage between 10 and 100.
396 100 (complete packing) is the default. When a smaller fillfactor
397 is specified, INSERT operations pack table pages only to the
398 indicated percentage; the remaining space on each page is
399 reserved for updating rows on that page. This gives UPDATE a
400 chance to place the updated copy of a row on the same page as
401 the original, which is more efficient than placing it on a dif‐
402 ferent page. For a table whose entries are never updated, com‐
403 plete packing is the best choice, but in heavily updated tables
404 smaller fillfactors are appropriate.
405
407 Using OIDs in new applications is not recommended: where possible,
408 using a SERIAL or other sequence generator as the table's primary key
409 is preferred. However, if your application does make use of OIDs to
410 identify specific rows of a table, it is recommended to create a unique
411 constraint on the oid column of that table, to ensure that OIDs in the
412 table will indeed uniquely identify rows even after counter wraparound.
413 Avoid assuming that OIDs are unique across tables; if you need a data‐
414 base-wide unique identifier, use the combination of tableoid and row
415 OID for the purpose.
416 Tip: The use of OIDS=FALSE is not recommended for tables with no
418 primary key, since without either an OID or a unique data key,
419 it is difficult to identify specific rows.
420 PostgreSQL automatically creates an index for each unique constraint
423 and primary key constraint to enforce uniqueness. Thus, it is not nec‐
424 essary to create an index explicitly for primary key columns. (See CRE‐
425 ATE INDEX [create_index(7)] for more information.)
426 Unique constraints and primary keys are not inherited in the current
428 implementation. This makes the combination of inheritance and unique
429 constraints rather dysfunctional.
430 A table cannot have more than 1600 columns. (In practice, the effective
432 limit is usually lower because of tuple-length constraints.)
433
435 Create table films and table distributors:
436 CREATE TABLE films (
438 code char(5) CONSTRAINT firstkey PRIMARY KEY,
439 title varchar(40) NOT NULL,
440 did integer NOT NULL,
441 date_prod date,
442 kind varchar(10),
443 len interval hour to minute
444 );
445 CREATE TABLE distributors (
448 did integer PRIMARY KEY DEFAULT nextval('serial'),
449 name varchar(40) NOT NULL CHECK (name <> '')
450 );
451 Create a table with a 2-dimensional array:
454 CREATE TABLE array_int (
456 vector int[][]
457 );
458 Define a unique table constraint for the table films. Unique table con‐
461 straints can be defined on one or more columns of the table.
462 CREATE TABLE films (
464 code char(5),
465 title varchar(40),
466 did integer,
467 date_prod date,
468 kind varchar(10),
469 len interval hour to minute,
470 CONSTRAINT production UNIQUE(date_prod)
471 );
472 Define a check column constraint:
475 CREATE TABLE distributors (
477 did integer CHECK (did > 100),
478 name varchar(40)
479 );
480 Define a check table constraint:
483 CREATE TABLE distributors (
485 did integer,
486 name varchar(40)
487 CONSTRAINT con1 CHECK (did > 100 AND name <> '')
488 );
489 Define a primary key table constraint for the table films:
492 CREATE TABLE films (
494 code char(5),
495 title varchar(40),
496 did integer,
497 date_prod date,
498 kind varchar(10),
499 len interval hour to minute,
500 CONSTRAINT code_title PRIMARY KEY(code,title)
501 );
502 Define a primary key constraint for table distributors. The following
505 two examples are equivalent, the first using the table constraint syn‐
506 tax, the second the column constraint syntax:
507 CREATE TABLE distributors (
509 did integer,
510 name varchar(40),
511 PRIMARY KEY(did)
512 );
513 CREATE TABLE distributors (
516 did integer PRIMARY KEY,
517 name varchar(40)
518 );
519 Assign a literal constant default value for the column name, arrange
522 for the default value of column did to be generated by selecting the
523 next value of a sequence object, and make the default value of modtime
524 be the time at which the row is inserted:
525 CREATE TABLE distributors (
527 name varchar(40) DEFAULT 'Luso Films',
528 did integer DEFAULT nextval('distributors_serial'),
529 modtime timestamp DEFAULT current_timestamp
530 );
531 Define two NOT NULL column constraints on the table distributors, one
534 of which is explicitly given a name:
535 CREATE TABLE distributors (
537 did integer CONSTRAINT no_null NOT NULL,
538 name varchar(40) NOT NULL
539 );
540 Define a unique constraint for the name column:
543 CREATE TABLE distributors (
545 did integer,
546 name varchar(40) UNIQUE
547 );
548 The same, specified as a table constraint:
550 CREATE TABLE distributors (
552 did integer,
553 name varchar(40),
554 UNIQUE(name)
555 );
556 Create the same table, specifying 70% fill factor for both the table
559 and its unique index:
560 CREATE TABLE distributors (
562 did integer,
563 name varchar(40),
564 UNIQUE(name) WITH (fillfactor=70)
565 )
566 WITH (fillfactor=70);
567 Create table cinemas in tablespace diskvol1:
570 CREATE TABLE cinemas (
572 id serial,
573 name text,
574 location text
575 ) TABLESPACE diskvol1;
576
579 The CREATE TABLE command conforms to the SQL standard, with exceptions
580 listed below.
581 TEMPORARY TABLES
583 Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL
584 standard, the effect is not the same. In the standard, temporary tables
585 are defined just once and automatically exist (starting with empty con‐
586 tents) in every session that needs them. PostgreSQL instead requires
587 each session to issue its own CREATE TEMPORARY TABLE command for each
588 temporary table to be used. This allows different sessions to use the
589 same temporary table name for different purposes, whereas the stan‐
590 dard's approach constrains all instances of a given temporary table
591 name to have the same table structure.
592 The standard's definition of the behavior of temporary tables is widely
594 ignored. PostgreSQL's behavior on this point is similar to that of sev‐
595 eral other SQL databases.
596 The standard's distinction between global and local temporary tables is
598 not in PostgreSQL, since that distinction depends on the concept of
599 modules, which PostgreSQL does not have. For compatibility's sake,
600 PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary ta‐
601 ble declaration, but they have no effect.
602 The ON COMMIT clause for temporary tables also resembles the SQL stan‐
604 dard, but has some differences. If the ON COMMIT clause is omitted,
605 SQL specifies that the default behavior is ON COMMIT DELETE ROWS. How‐
606 ever, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS.
607 The ON COMMIT DROP option does not exist in SQL.
608 COLUMN CHECK CONSTRAINTS
610 The SQL standard says that CHECK column constraints may only refer to
611 the column they apply to; only CHECK table constraints may refer to
612 multiple columns. PostgreSQL does not enforce this restriction; it
613 treats column and table check constraints alike.
614 NULL ``CONSTRAINT''
616 The NULL ``constraint'' (actually a non-constraint) is a PostgreSQL
617 extension to the SQL standard that is included for compatibility with
618 some other database systems (and for symmetry with the NOT NULL con‐
619 straint). Since it is the default for any column, its presence is sim‐
620 ply noise.
621 INHERITANCE
623 Multiple inheritance via the INHERITS clause is a PostgreSQL language
624 extension. SQL:1999 and later define single inheritance using a dif‐
625 ferent syntax and different semantics. SQL:1999-style inheritance is
626 not yet supported by PostgreSQL.
627 ZERO-COLUMN TABLES
629 PostgreSQL allows a table of no columns to be created (for example,
630 CREATE TABLE foo();). This is an extension from the SQL standard, which
631 does not allow zero-column tables. Zero-column tables are not in them‐
632 selves very useful, but disallowing them creates odd special cases for
633 ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec
634 restriction.
635 WITH CLAUSE
637 The WITH clause is a PostgreSQL extension; neither storage parameters
638 nor OIDs are in the standard.
639 TABLESPACES
641 The PostgreSQL concept of tablespaces is not part of the standard.
642 Hence, the clauses TABLESPACE and USING INDEX TABLESPACE are exten‐
643 sions.
644
646 ALTER TABLE [alter_table(7)], DROP TABLE [drop_table(l)], CREATE
647 TABLESPACE [create_tablespace(l)]
648SQL - Language Statements 2008-06-08 CREATE TABLE()