1 <REFENTRY ID="SQL-CREATETABLE-1">
6 <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
18 <DATE>1998-04-15</DATE>
21 CREATE TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> (
22 <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE> [DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>] [CONSTRAINT <REPLACEABLE CLASS="PARAMETER">column_constraint</REPLACEABLE>] [, ...]
23 [, <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> ...]
24 [, CONSTRAINT <REPLACEABLE CLASS="PARAMETER">table_constraint</REPLACEABLE>]
25 ) [INHERITS ( <REPLACEABLE CLASS="PARAMETER">inherited_table</REPLACEABLE> [, ...] )]
28 <REFSECT2 ID="R2-SQL-CREATETABLE-1">
30 <DATE>1998-04-15</DATE>
41 <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
45 The name of a new table to be created.
52 <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>
63 <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE>
67 The type of the column.
68 (Refer to the <ProductName>Postgres</ProductName> User's Guide for
69 further information about data types).
76 <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>
80 A default value for a column.
81 See the DEFAULT clause for more information.
88 <REPLACEABLE CLASS="PARAMETER">inherited_table</REPLACEABLE>
92 The optional INHERITS clause specifies a collection of table
93 names from which this table automatically inherits all fields.
94 If any inherited field name appears more than once, PostgreSQL
95 reports an error. PostgreSQL automatically allows the created
96 table to inherit functions on tables above it in the inheritance
97 hierarchy. Inheritance of functions is done according
98 to the conventions of the Common Lisp Object System (CLOS).
105 <REPLACEABLE CLASS="PARAMETER">column_constraint</REPLACEABLE>
106 <REPLACEABLE CLASS="PARAMETER">table_constraint</REPLACEABLE>
110 The optional CONSTRAINT clause specifies a list of integrity
111 constraints which new or updated entries must satisfy for
112 an insert or update operation to succeed. Each constraint
113 must evaluate to a boolean expression. Multiple columns
114 may be referenced within a single constraint.
115 See CONSTRAINT clause for more information.
124 <REFSECT2 ID="R2-SQL-CREATETABLE-2">
126 <DATE>1998-04-15</DATE>
136 <ReturnValue>status</ReturnValue>
143 <ReturnValue>CREATE</ReturnValue>
147 Message returned if table is successfully created.
154 <ReturnValue>ERROR</ReturnValue>
158 Message returned if table creation failed.
159 This is usually accompanied by some descriptive text, such as:
161 amcreate: "<replaceable class="parameter">table</replaceable>" relation already exists
163 which occurs at runtime, if the table specified already exists
176 <REFSECT1 ID="R1-SQL-CREATETABLE-1">
178 <DATE>1998-04-15</DATE>
184 CREATE TABLE will enter a new table into the current data
185 base. The table will be "owned" by the user issuing the
189 The new table is created as a heap with no initial data.
190 A table can have no more than 1600 columns (realistically,
191 this is limited by the fact that tuple sizes must
192 be less than 8192 bytes), but this limit may be configured
193 lower at some sites. A table cannot have the same name as
194 a system catalog table.
197 <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-1">
199 <DATE>1998-04-15</DATE>
206 DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>
209 The DEFAULT clause assigns a default data value to a column.
220 <ReturnValue><replaceable class="parameter">value</replaceable></ReturnValue>
224 The possible values for expression are:
248 <ReturnValue>ERROR: DEFAULT: type mismatched</ReturnValue>
252 if data type of default value doesn't match the
253 column definition's data type.
259 The DEFAULT clause assigns a default data value to a column
260 (via a column definition in the CREATE TABLE statement).
261 The data type of a default value must match the column definition's
265 An INSERT operation that includes a column without a specified
266 default value will assign the NULL value to the column
267 if no explicit data value is provided for it.
268 Default <replaceable class="parameter">literal</replaceable> means
269 that the default is the specified constant value.
270 Default <replaceable class="parameter">niladic-function</replaceable>
271 or <replaceable class="parameter">user-function</replaceable> means
273 is the value of the specified function at the time of the INSERT.
276 There are two types of niladic functions:<variablelist>
278 <term>niladic USER</term>
282 <term>CURRENT_USER / USER</term>
284 <simpara>See CURRENT_USER function</simpara>
288 <term>SESSION_USER</term>
290 <simpara>not yet supported</simpara>
294 <term>SYSTEM_USER</term>
296 <simpara>not yet supported</simpara>
303 <term>niladic datetime</term>
307 <term> CURRENT_DATE</term>
309 <simpara>See CURRENT_DATE function</simpara>
313 <term>CURRENT_TIME</term>
315 <simpara>See CURRENT_TIME function</simpara>
319 <term>CURRENT_TIMESTAMP</term>
321 <simpara>See CURRENT_TIMESTAMP function</simpara>
333 <REFSECT2 ID="R2-SQL-NOTNULL-1">
335 <DATE>1998-04-15</DATE>
341 [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] NOT NULL
344 The NOT NULL constraint specifies a rule that a column may
345 contain only non-null values.
348 The NOT NULL constraint is a column constraint.
359 <ReturnValue><replaceable class="parameter">name</replaceable></ReturnValue>
363 The optional name of a constraint.
372 <REFSECT3 ID="R3-SQL-NOTNULL-1">
374 <DATE>1998-04-15</DATE>
390 <ReturnValue>ERROR: ExecAppend: Fail to add null value in not
391 null attribute "<replaceable class="parameter">column</replaceable>".</ReturnValue>
395 This error occurs at runtime if one tries to insert a null value
396 into a column which has a NOT NULL constraint.
407 <REFSECT2 ID="R2-SQL-UNIQUECLAUSE-1">
409 <DATE>1998-04-15</DATE>
415 Table Constraint definition
418 [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ...] )
421 Column Constraint definition
424 [ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE
427 <title>Parameters</title>
431 <replaceable class="parameter">name</replaceable>
435 An arbitrary name given to a constraint.
441 <replaceable class="parameter">column</replaceable>
445 A name of a column in a table.
452 <title>Outputs</title>
464 ERROR: Cannot insert a duplicate key into a unique index.
468 This error occurs at runtime if one tries to insert a
469 duplicate value into a column.
473 </variablelist></para>
479 <title>Description</title>
481 The UNIQUE constraint specifies a rule that a group of one or
482 more distinct columns of a table may contain only unique values.
485 The column definitions of the specified columns do not have to
486 include a NOT NULL constraint to be included in a UNIQUE
487 constraint. Having more than one null value in a column without a
488 NOT NULL constraint, does not violate a UNIQUE constraint.
491 Each UNIQUE constraint must name a set of columns that is
492 different from the set of columns named by any other UNIQUE or
493 PRIMARY KEY constraint defined for the Table.
497 PostgreSQL automatically creates a unique index for each UNIQUE
498 constraint, to assure
499 data integrity. See CREATE INDEX for more information.
504 <REFSECT2 ID="R2-SQL-CONSTRAINT-1">
506 <DATE>1998-04-15</DATE>
512 Table constraint definition
516 { PRIMARY KEY constraint |
521 Column constraint definition
525 { NOT NULL constraint |
526 PRIMARY KEY constraint |
542 <replaceable class="parameter">name</replaceable>
547 An arbitrary name given to an integrity constraint.
554 <replaceable class="parameter">constraint</replaceable>
559 The definition of the constraint.
568 A Constraint is a named rule: a SQL object which helps define
569 valid sets of values by putting limits on the results of INSERT,
570 UPDATE or DELETE operations performed on a Base table.
573 There are two ways to define integrity constraints:
574 Table constraint and Column constraint.
577 A Table Constraint is an integrity Constraint defined on one or
578 more Columns of a Base table. The four variations of "Table
580 <simplelist columns="1">
581 <member>PRIMARY KEY</member>
582 <member>FOREIGN KEY</member>
583 <member>UNIQUE</member>
584 <member>CHECK</member>
588 A column constraint is an integrity constraint defined as part
589 of a column definition, and logically becomes a table
590 constraint as soon as it is created. The column
591 constraints available are:
592 <simplelist columns="1">
593 <member>PRIMARY KEY</member>
594 <member>REFERENCES</member>
595 <member>UNIQUE</member>
596 <member>CHECK</member>
597 <member>NOT NULL</member>
601 PostgreSQL does not yet (at release 6.3.2) support the FOREIGN KEY or
602 REFERENCES integrity constraints, although the parser will accept them.
603 Foreign keys may be partially emulated by triggers (See CREATE TRIGGER
609 PostgreSQL does not yet support either DOMAINs or ASSERTIONs.
615 <REFSECT2 ID="R2-SQL-CHECK-1">
617 <DATE>1998-04-15</DATE>
619 <title>The CHECK constraint</title>
621 [ CONSTRAINT name ] CHECK ( condition [, ...] )
623 <refsect3 id="R3-SQL-CHECK-1">
624 <title>Inputs</title>
629 <ReturnValue><replaceable class="parameter">name</replaceable></ReturnValue>
633 An arbitrary name given to a constraint.
639 <ReturnValue>condition</ReturnValue>
643 Any valid conditional expression.
650 <REFSECT3 ID="R3-SQL-CHECK-2">
652 <DATE>1998-04-15</DATE>
662 ERROR: ExecAppend: rejected due to CHECK constraint
663 "<replaceable class="parameter">table_column</replaceable>".
668 This error occurs at runtime if one tries to insert an illegal
669 value into a column subject to a CHECK constraint.
676 <title>Description</title>
678 The CHECK constraint specifies a rule that a group of one or
679 more columns of a table may contain only those values allowed by
681 The CHECK constraint is either a table constraint or a column
685 PostgreSQL automatically creates an unique index to assure
686 data integrity (See CREATE INDEX statement).
687 The SQL92 CHECK column constraints can only be defined on, and
688 refer to, one column of the table. PostgreSQL does not have
694 <REFSECT2 ID="R2-SQL-PRIMARYKEY-1">
696 <DATE>1998-04-15</DATE>
702 Table constraint definition
705 [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] )
708 Column constraint definition
711 [ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY
715 <title>Parameters</title>
720 <ReturnValue><REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE></ReturnValue>
724 An arbitrary name for the constraint.
730 <ReturnValue><REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE></ReturnValue>
734 The name of a column in the table.
742 <title>Outputs</title>
745 <term>ERROR: Cannot insert a duplicate key into a unique index.</term>
748 This occurs at run-time if one tries to insert a duplicate value into
749 a column subject to a PRIMARY KEY constraint.
756 <title>Description</title>
758 The PRIMARY KEY constraint specifies a rule that a group of one
759 or more distinct columns of a table may contain only unique,
760 (not duplicates), non-null values. The column definitions of
761 the specified columns do not have to include a NOT NULL
762 constraint to be included in a PRIMARY KEY constraint.
765 A table's set of valid values may be constrained by only one
766 PRIMARY KEY constraint at a time.
769 The PRIMARY KEY constraint must name a set of columns that is
770 different from the set of columns named by any UNIQUE constraint
771 defined for the same table.
775 <REFSECT3 ID="R3-SQL-PRIMARYKEY-3">
777 <DATE>1998-04-15</DATE>
783 PostgreSQL automatically creates an unique index to assure
784 data integrity. (See CREATE INDEX statement)
791 <REFSECT1 ID="R1-SQL-CREATETABLE-2">
796 Create table films and table distributors
800 code CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
801 title CHARACTER VARYING(40) NOT NULL,
802 did DECIMAL(3) NOT NULL,
805 len INTERVAL HOUR TO MINUTE
810 CREATE TABLE distributors (
811 did DECIMAL(03) PRIMARY KEY DEFAULT NEXTVAL('serial'),
812 name VARCHAR(40) NOT NULL CHECK (name <> '')
817 Create a table with a 2-dimensional array
826 Define two NOT NULL column constraints on the table distributors
829 CREATE TABLE distributors (
830 did DECIMAL(3) CONSTRAINT no_null NOT NULL,
831 name VARCHAR(40) NOT NULL
836 Define a UNIQUE table constraint for the table films.
837 UNIQUE table constraints can be defined on one or more
847 len INTERVAL HOUR TO MINUTE,
848 CONSTRAINT production UNIQUE(date_prod)
853 Defines a UNIQUE column constraint for the table distributors.
854 UNIQUE column constraints can only be defined on one column
855 of the table (the following two examples are equivalents).
858 CREATE TABLE distributors (
860 name VARCHAR(40) UNIQUE,
866 CREATE TABLE distributors (
868 name VARCHAR(40) UNIQUE
873 Define a CHECK column constraint.
876 CREATE TABLE distributors (
877 did DECIMAL(3) CHECK (did > 100),
883 Define a CHECK table constraint
886 CREATE TABLE distributors (
889 CONSTRAINT con1 CHECK (did > 100 AND name > '')
894 Define a PRIMARY KEY table constraint for the table films.
895 PRIMARY KEY table constraints can be defined on one or more
905 len INTERVAL HOUR TO MINUTE,
906 CONSTRAINT code_title PRIMARY KEY(code,title)
911 Defines a PRIMARY KEY column constraint for table distributors.
912 PRIMARY KEY column constraints can only be defined on one column
913 of the table (the following two examples are equivalents)
916 CREATE TABLE distributors (
918 name CHAR VARYING(40),
922 CREATE TABLE distributors (
923 did DECIMAL(03) PRIMARY KEY,
928 To assign a sequence as the default for the column did,
929 and a literal to the column name
933 CREATE TABLE distributors (
934 did DECIMAL(3) DEFAULT NEXTVAL('serial'),
935 name VARCHAR(40) DEFAULT 'luso films'
939 <REFSECT2 ID="R2-SQL-CREATETABLE-3">
941 <DATE>1998-04-15</DATE>
947 CREATE TABLE/INHERITS is a PostgreSQL language extension.
953 <REFSECT1 ID="R1-SQL-CREATETABLE-3">
960 <REFSECT2 ID="R2-SQL-CREATETABLE-4">
962 <DATE>1998-04-15</DATE>
968 In addition to normal CREATE TABLE, SQL92 also defines a
969 CREATE TEMPORARY TABLE statement:
972 CREATE [ {GLOBAL | LOCAL} ] TEMPORARY TABLE table (
973 column type [DEFAULT value] [CONSTRAINT column_constraint] [, ...] )
974 [CONSTRAINT table_constraint ]
975 [ ON COMMIT {DELETE | PRESERVE} ROWS ]
978 For temporary tables, the CREATE TEMPORARY TABLE statement
979 names a new table and defines the table's columns and
983 The optional ON COMMIT clause of CREATE TEMPORARY TABLE
984 specifies whether or not the temporary table should be emptied of
985 rows whenever COMMIT is executed. If the ON COMMIT clause is
986 omitted, the default option, ON COMMIT DELETE ROWS, is assumed.
989 To create a temporary table:
992 CREATE TEMPORARY TABLE actors (
995 CONSTRAINT actor_id CHECK (id < 150)
996 ) ON COMMIT DELETE ROWS
999 Temporary tables are not currently available in <productname>Postgres</productname>.
1002 In the current release of <productname>Postgres</productname> (v6.4), to create a temporary
1003 table you must create and drop the table by explicit commands.
1006 <REFSECT3 ID="R3-SQL-UNIQUECLAUSE-1">
1008 <DATE>1998-04-15</DATE>
1014 SQL92 specifies some additional capabilities for UNIQUE:
1016 Table Constraint definition
1020 UNIQUE ( column [, ...] )
1021 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1022 [ [ NOT ] DEFERRABLE ]
1025 Column Constraint definition
1030 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1031 [ [ NOT ] DEFERRABLE ]
1035 <REFSECT3 ID="R3-SQL-NOTNULL-4">
1037 <DATE>1998-04-15</DATE>
1044 SQL92 specifies some additional capabilities for NOT NULL:
1047 [ CONSTRAINT name ] NOT NULL
1048 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1049 [ [ NOT ] DEFERRABLE ]
1054 I can't figure out why DEFAULT clause is different from what we already have.
1055 Perhaps because CURRENT_USER and CURRENT_DATE have specific types (currently
1056 the "name" type), if you aren't careful then the types won't match up with
1057 the column. Not our problem...
1060 <REFSECT3 ID="R3-SQL-DEFAULTCLAUSE-1">
1062 <DATE>1998-04-15</DATE>
1068 SQL92 specifies some additional capabilities for the DEFAULT clause.
1069 A DEFAULT clause is used to set the default value for a column
1073 DEFAULT niladic USER function |
1074 niladic datetime function |
1080 <REFSECT3 ID="R3-SQL-CONSTRAINT-3">
1082 <DATE>1998-04-15</DATE>
1088 SQL92 specifies some additional capabilities for CONSTRAINTs,
1089 and also defines assertions and domain constraints.
1092 An assertion is a special type of integrity constraint and share
1093 the same namespace as other constraints.
1094 However, an assertion is not necessarily dependent on one
1095 particular base table as constraints are, so SQL-92 provides the
1096 CREATE ASSERTION statement as an alternate method for defining a
1100 CREATE ASSERTION name CHECK ( condition )
1104 Domain constraints are defined by CREATE DOMAIN or ALTER DOMAIN
1113 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1114 [ [ NOT ] DEFERRABLE ]
1117 Table constraint definition:
1121 { PRIMARY KEY constraint |
1122 FOREIGN KEY constraint |
1125 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1126 [ [ NOT ] DEFERRABLE ]</synopsis>
1128 Column constraint definition:
1132 { NOT NULL constraint |
1133 PRIMARY KEY constraint |
1134 FOREIGN KEY constraint |
1137 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1138 [ [ NOT ] DEFERRABLE ]
1141 A CONSTRAINT definition may contain one deferment attribute
1142 clause and/or one initial constraint mode clause, in any order.
1145 <term>NOT DEFERRABLE</term>
1148 means that the Constraint must be checked for
1149 violation of its rule after the execution of every SQL statement.
1154 <term>DEFERRABLE</term>
1157 means that checking of the Constraint may be deferred
1158 until some later time, but no later than the end of the current
1166 The constraint mode for every Constraint always has an initial
1167 default value which is set for that Constraint at the beginning
1171 <term>INITIALLY IMMEDIATE</term>
1174 means that, as of the start of the transaction,
1175 the Constraint must be checked for violation of its rule after the
1176 execution of every SQL statement.
1181 <term>INITIALLY DEFERRED</term>
1184 means that, as of the start of the transaction,
1185 checking of the Constraint may be deferred until some later time,
1186 but no later than the end of the current transaction.</para>
1194 <REFSECT3 ID="R3-SQL-CHECK-4">
1196 <DATE>1998-04-15</DATE>
1202 SQL92 specifies some additional capabilities for CHECK in either
1203 table or column constraints.
1206 Constraints associated with domains do not need to be mentioned here,
1207 even though it is the case that a domain constraint may possibly
1208 affect a column or a table.
1211 A CHECK constraint is either a table constraint, a column
1212 constraint or a domain constraint.
1216 table constraint definition:
1220 CHECK ( VALUE condition )
1221 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1222 [ [ NOT ] DEFERRABLE ]
1225 column constraint definition:
1229 CHECK ( VALUE condition )
1230 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1231 [ [ NOT ] DEFERRABLE ]
1235 domain constraint definition:
1239 CHECK ( VALUE condition )
1240 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1241 [ [ NOT ] DEFERRABLE ]
1244 CHECK domain constraints can be defined in either
1245 a CREATE DOMAIN statement or an ALTER DOMAIN statement:
1248 CREATE DOMAIN duration AS SMALLINT
1249 CONSTRAINT minutes CHECK (VALUE IN (90,120,180,240));
1252 ADD CONSTRAINT new_city CHECK (VALUE LIKE 'L%');
1258 <REFSECT3 ID="R3-SQL-PRIMARYKEY-1">
1260 <DATE>1998-04-15</DATE>
1266 SQL92 specifies some additional capabilities for PRIMARY KEY:
1269 Table Constraint definition:
1273 PRIMARY KEY ( column [, ...] )
1274 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1275 [ [ NOT ] DEFERRABLE ]
1278 Column Constraint definition:
1283 [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
1284 [ [ NOT ] DEFERRABLE ]
1291 <!-- Keep this comment at the end of the file
1296 sgml-minimize-attributes:nil
1297 sgml-always-quote-attributes:t
1300 sgml-parent-document:nil
1301 sgml-default-dtd-file:"../reference.ced"
1302 sgml-exposed-tags:nil
1303 sgml-local-catalogs:"/usr/lib/sgml/catalog"
1304 sgml-local-ecat-files:nil