1 <!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.52 2006/02/04 23:03:19 tgl Exp $ -->
4 <title>Data Definition</title>
7 This chapter covers how one creates the database structures that
8 will hold one's data. In a relational database, the raw data is
9 stored in tables, so the majority of this chapter is devoted to
10 explaining how tables are created and modified and what features are
11 available to control what data is stored in the tables.
12 Subsequently, we discuss how tables can be organized into
13 schemas, and how privileges can be assigned to tables. Finally,
14 we will briefly look at other features that affect the data storage,
15 such as inheritance, views, functions, and triggers.
18 <sect1 id="ddl-basics">
19 <title>Table Basics</title>
21 <indexterm zone="ddl-basics">
22 <primary>table</primary>
26 <primary>row</primary>
30 <primary>column</primary>
34 A table in a relational database is much like a table on paper: It
35 consists of rows and columns. The number and order of the columns
36 is fixed, and each column has a name. The number of rows is
37 variable -- it reflects how much data is stored at a given moment.
38 SQL does not make any guarantees about the order of the rows in a
39 table. When a table is read, the rows will appear in random order,
40 unless sorting is explicitly requested. This is covered in <xref
41 linkend="queries">. Furthermore, SQL does not assign unique
42 identifiers to rows, so it is possible to have several completely
43 identical rows in a table. This is a consequence of the
44 mathematical model that underlies SQL but is usually not desirable.
45 Later in this chapter we will see how to deal with this issue.
49 Each column has a data type. The data type constrains the set of
50 possible values that can be assigned to a column and assigns
51 semantics to the data stored in the column so that it can be used
52 for computations. For instance, a column declared to be of a
53 numerical type will not accept arbitrary text strings, and the data
54 stored in such a column can be used for mathematical computations.
55 By contrast, a column declared to be of a character string type
56 will accept almost any kind of data but it does not lend itself to
57 mathematical calculations, although other operations such as string
58 concatenation are available.
62 <productname>PostgreSQL</productname> includes a sizable set of
63 built-in data types that fit many applications. Users can also
64 define their own data types. Most built-in data types have obvious
65 names and semantics, so we defer a detailed explanation to <xref
66 linkend="datatype">. Some of the frequently used data types are
67 <type>integer</type> for whole numbers, <type>numeric</type> for
68 possibly fractional numbers, <type>text</type> for character
69 strings, <type>date</type> for dates, <type>time</type> for
70 time-of-day values, and <type>timestamp</type> for values
71 containing both date and time.
75 <primary>table</primary>
76 <secondary>creating</secondary>
80 To create a table, you use the aptly named <command>CREATE
81 TABLE</command> command. In this command you specify at least a
82 name for the new table, the names of the columns and the data type
83 of each column. For example:
85 CREATE TABLE my_first_table (
90 This creates a table named <literal>my_first_table</literal> with
91 two columns. The first column is named
92 <literal>first_column</literal> and has a data type of
93 <type>text</type>; the second column has the name
94 <literal>second_column</literal> and the type <type>integer</type>.
95 The table and column names follow the identifier syntax explained
96 in <xref linkend="sql-syntax-identifiers">. The type names are
97 usually also identifiers, but there are some exceptions. Note that the
98 column list is comma-separated and surrounded by parentheses.
102 Of course, the previous example was heavily contrived. Normally,
103 you would give names to your tables and columns that convey what
104 kind of data they store. So let's look at a more realistic
107 CREATE TABLE products (
113 (The <type>numeric</type> type can store fractional components, as
114 would be typical of monetary amounts.)
119 When you create many interrelated tables it is wise to choose a
120 consistent naming pattern for the tables and columns. For
121 instance, there is a choice of using singular or plural nouns for
122 table names, both of which are favored by some theorist or other.
127 There is a limit on how many columns a table can contain.
128 Depending on the column types, it is between 250 and 1600.
129 However, defining a table with anywhere near this many columns is
130 highly unusual and often a questionable design.
134 <primary>table</primary>
135 <secondary>removing</secondary>
139 If you no longer need a table, you can remove it using the
140 <command>DROP TABLE</command> command. For example:
142 DROP TABLE my_first_table;
145 Attempting to drop a table that does not exist is an error.
146 Nevertheless, it is common in SQL script files to unconditionally
147 try to drop each table before creating it, ignoring the error
152 If you need to modify a table that already exists look into <xref
153 linkend="ddl-alter"> later in this chapter.
157 With the tools discussed so far you can create fully functional
158 tables. The remainder of this chapter is concerned with adding
159 features to the table definition to ensure data integrity,
160 security, or convenience. If you are eager to fill your tables with
161 data now you can skip ahead to <xref linkend="dml"> and read the
162 rest of this chapter later.
166 <sect1 id="ddl-default">
167 <title>Default Values</title>
169 <indexterm zone="ddl-default">
170 <primary>default value</primary>
174 A column can be assigned a default value. When a new row is
175 created and no values are specified for some of the columns, the
176 columns will be filled with their respective default values. A
177 data manipulation command can also request explicitly that a column
178 be set to its default value, without having to know what that value is.
179 (Details about data manipulation commands are in <xref linkend="dml">.)
183 <indexterm><primary>null value</primary><secondary>default value</secondary></indexterm>
184 If no default value is declared explicitly, the default value is the
185 null value. This usually makes sense because a null value can
186 be considered to represent unknown data.
190 In a table definition, default values are listed after the column
191 data type. For example:
193 CREATE TABLE products (
196 price numeric <emphasis>DEFAULT 9.99</emphasis>
202 The default value may be an expression, which will be
203 evaluated whenever the default value is inserted
204 (<emphasis>not</emphasis> when the table is created). A common example
205 is that a <type>timestamp</type> column may have a default of <literal>now()</>,
206 so that it gets set to the time of row insertion. Another common
207 example is generating a <quote>serial number</> for each row.
208 In <productname>PostgreSQL</productname> this is typically done by
211 CREATE TABLE products (
212 product_no integer <emphasis>DEFAULT nextval('products_product_no_seq')</emphasis>,
216 where the <literal>nextval()</> function supplies successive values
217 from a <firstterm>sequence object</> (see <xref
218 linkend="functions-sequence">). This arrangement is sufficiently common
219 that there's a special shorthand for it:
221 CREATE TABLE products (
222 product_no <emphasis>SERIAL</emphasis>,
226 The <literal>SERIAL</> shorthand is discussed further in <xref
227 linkend="datatype-serial">.
231 <sect1 id="ddl-constraints">
232 <title>Constraints</title>
234 <indexterm zone="ddl-constraints">
235 <primary>constraint</primary>
239 Data types are a way to limit the kind of data that can be stored
240 in a table. For many applications, however, the constraint they
241 provide is too coarse. For example, a column containing a product
242 price should probably only accept positive values. But there is no
243 standard data type that accepts only positive numbers. Another issue is
244 that you might want to constrain column data with respect to other
245 columns or rows. For example, in a table containing product
246 information, there should only be one row for each product number.
250 To that end, SQL allows you to define constraints on columns and
251 tables. Constraints give you as much control over the data in your
252 tables as you wish. If a user attempts to store data in a column
253 that would violate a constraint, an error is raised. This applies
254 even if the value came from the default value definition.
258 <title>Check Constraints</title>
261 <primary>check constraint</primary>
265 <primary>constraint</primary>
266 <secondary>check</secondary>
270 A check constraint is the most generic constraint type. It allows
271 you to specify that the value in a certain column must satisfy a
272 Boolean (truth-value) expression. For instance, to require positive
273 product prices, you could use:
275 CREATE TABLE products (
278 price numeric <emphasis>CHECK (price > 0)</emphasis>
284 As you see, the constraint definition comes after the data type,
285 just like default value definitions. Default values and
286 constraints can be listed in any order. A check constraint
287 consists of the key word <literal>CHECK</literal> followed by an
288 expression in parentheses. The check constraint expression should
289 involve the column thus constrained, otherwise the constraint
290 would not make too much sense.
294 <primary>constraint</primary>
295 <secondary>name</secondary>
299 You can also give the constraint a separate name. This clarifies
300 error messages and allows you to refer to the constraint when you
301 need to change it. The syntax is:
303 CREATE TABLE products (
306 price numeric <emphasis>CONSTRAINT positive_price</emphasis> CHECK (price > 0)
309 So, to specify a named constraint, use the key word
310 <literal>CONSTRAINT</literal> followed by an identifier followed
311 by the constraint definition. (If you don't specify a constraint
312 name in this way, the system chooses a name for you.)
316 A check constraint can also refer to several columns. Say you
317 store a regular price and a discounted price, and you want to
318 ensure that the discounted price is lower than the regular price.
320 CREATE TABLE products (
323 price numeric CHECK (price > 0),
324 discounted_price numeric CHECK (discounted_price > 0),
325 <emphasis>CHECK (price > discounted_price)</emphasis>
331 The first two constraints should look familiar. The third one
332 uses a new syntax. It is not attached to a particular column,
333 instead it appears as a separate item in the comma-separated
334 column list. Column definitions and these constraint
335 definitions can be listed in mixed order.
339 We say that the first two constraints are column constraints, whereas the
340 third one is a table constraint because it is written separately
341 from any one column definition. Column constraints can also be
342 written as table constraints, while the reverse is not necessarily
343 possible, since a column constraint is supposed to refer to only the
344 column it is attached to. (<productname>PostgreSQL</productname> doesn't
345 enforce that rule, but you should follow it if you want your table
346 definitions to work with other database systems.) The above example could
349 CREATE TABLE products (
353 CHECK (price > 0),
354 discounted_price numeric,
355 CHECK (discounted_price > 0),
356 CHECK (price > discounted_price)
361 CREATE TABLE products (
364 price numeric CHECK (price > 0),
365 discounted_price numeric,
366 CHECK (discounted_price > 0 AND price > discounted_price)
369 It's a matter of taste.
373 Names can be assigned to table constraints in just the same way as
374 for column constraints:
376 CREATE TABLE products (
380 CHECK (price > 0),
381 discounted_price numeric,
382 CHECK (discounted_price > 0),
383 <emphasis>CONSTRAINT valid_discount</> CHECK (price > discounted_price)
389 <primary>null value</primary>
390 <secondary sortas="check constraints">with check constraints</secondary>
394 It should be noted that a check constraint is satisfied if the
395 check expression evaluates to true or the null value. Since most
396 expressions will evaluate to the null value if any operand is null,
397 they will not prevent null values in the constrained columns. To
398 ensure that a column does not contain null values, the not-null
399 constraint described in the next section can be used.
403 Check constraints can be useful for enhancing the performance of
404 partitioned tables. For details see <xref linkend="ddl-partitioning">.
409 <title>Not-Null Constraints</title>
412 <primary>not-null constraint</primary>
416 <primary>constraint</primary>
417 <secondary>NOT NULL</secondary>
421 A not-null constraint simply specifies that a column must not
422 assume the null value. A syntax example:
424 CREATE TABLE products (
425 product_no integer <emphasis>NOT NULL</emphasis>,
426 name text <emphasis>NOT NULL</emphasis>,
433 A not-null constraint is always written as a column constraint. A
434 not-null constraint is functionally equivalent to creating a check
435 constraint <literal>CHECK (<replaceable>column_name</replaceable>
436 IS NOT NULL)</literal>, but in
437 <productname>PostgreSQL</productname> creating an explicit
438 not-null constraint is more efficient. The drawback is that you
439 cannot give explicit names to not-null constraints created this
444 Of course, a column can have more than one constraint. Just write
445 the constraints one after another:
447 CREATE TABLE products (
448 product_no integer NOT NULL,
450 price numeric NOT NULL CHECK (price > 0)
453 The order doesn't matter. It does not necessarily determine in which
454 order the constraints are checked.
458 The <literal>NOT NULL</literal> constraint has an inverse: the
459 <literal>NULL</literal> constraint. This does not mean that the
460 column must be null, which would surely be useless. Instead, this
461 simply selects the default behavior that the column may be null.
462 The <literal>NULL</literal> constraint is not defined in the SQL
463 standard and should not be used in portable applications. (It was
464 only added to <productname>PostgreSQL</productname> to be
465 compatible with some other database systems.) Some users, however,
466 like it because it makes it easy to toggle the constraint in a
467 script file. For example, you could start with
469 CREATE TABLE products (
470 product_no integer NULL,
475 and then insert the <literal>NOT</literal> key word where desired.
480 In most database designs the majority of columns should be marked
487 <title>Unique Constraints</title>
490 <primary>unique constraint</primary>
494 <primary>constraint</primary>
495 <secondary>unique</secondary>
499 Unique constraints ensure that the data contained in a column or a
500 group of columns is unique with respect to all the rows in the
503 CREATE TABLE products (
504 product_no integer <emphasis>UNIQUE</emphasis>,
509 when written as a column constraint, and
511 CREATE TABLE products (
515 <emphasis>UNIQUE (product_no)</emphasis>
518 when written as a table constraint.
522 If a unique constraint refers to a group of columns, the columns
523 are listed separated by commas:
525 CREATE TABLE example (
529 <emphasis>UNIQUE (a, c)</emphasis>
532 This specifies that the combination of values in the indicated columns
533 is unique across the whole table, though any one of the columns
534 need not be (and ordinarily isn't) unique.
538 You can assign your own name for a unique constraint, in the usual way:
540 CREATE TABLE products (
541 product_no integer <emphasis>CONSTRAINT must_be_different</emphasis> UNIQUE,
549 <primary>null value</primary>
550 <secondary sortas="unique constraints">with unique constraints</secondary>
554 In general, a unique constraint is violated when there are two or
555 more rows in the table where the values of all of the
556 columns included in the constraint are equal.
557 However, null values are not considered equal in this
558 comparison. That means even in the presence of a
559 unique constraint it is possible to store duplicate
560 rows that contain a null value in at least one of the constrained
561 columns. This behavior conforms to the SQL standard, but we have
562 heard that other SQL databases may not follow this rule. So be
563 careful when developing applications that are intended to be
569 <title>Primary Keys</title>
572 <primary>primary key</primary>
576 <primary>constraint</primary>
577 <secondary>primary key</secondary>
581 Technically, a primary key constraint is simply a combination of a
582 unique constraint and a not-null constraint. So, the following
583 two table definitions accept the same data:
585 CREATE TABLE products (
586 product_no integer UNIQUE NOT NULL,
593 CREATE TABLE products (
594 product_no integer <emphasis>PRIMARY KEY</emphasis>,
602 Primary keys can also constrain more than one column; the syntax
603 is similar to unique constraints:
605 CREATE TABLE example (
609 <emphasis>PRIMARY KEY (a, c)</emphasis>
615 A primary key indicates that a column or group of columns can be
616 used as a unique identifier for rows in the table. (This is a
617 direct consequence of the definition of a primary key. Note that
618 a unique constraint does not, by itself, provide a unique identifier
619 because it does not exclude null values.) This is useful both for
620 documentation purposes and for client applications. For example,
621 a GUI application that allows modifying row values probably needs
622 to know the primary key of a table to be able to identify rows
627 A table can have at most one primary key (while it can have many
628 unique and not-null constraints). Relational database theory
629 dictates that every table must have a primary key. This rule is
630 not enforced by <productname>PostgreSQL</productname>, but it is
631 usually best to follow it.
635 <sect2 id="ddl-constraints-fk">
636 <title>Foreign Keys</title>
639 <primary>foreign key</primary>
643 <primary>constraint</primary>
644 <secondary>foreign key</secondary>
648 <primary>referential integrity</primary>
652 A foreign key constraint specifies that the values in a column (or
653 a group of columns) must match the values appearing in some row
655 We say this maintains the <firstterm>referential
656 integrity</firstterm> between two related tables.
660 Say you have the product table that we have used several times already:
662 CREATE TABLE products (
663 product_no integer PRIMARY KEY,
668 Let's also assume you have a table storing orders of those
669 products. We want to ensure that the orders table only contains
670 orders of products that actually exist. So we define a foreign
671 key constraint in the orders table that references the products
674 CREATE TABLE orders (
675 order_id integer PRIMARY KEY,
676 product_no integer <emphasis>REFERENCES products (product_no)</emphasis>,
680 Now it is impossible to create orders with
681 <structfield>product_no</structfield> entries that do not appear in the
686 We say that in this situation the orders table is the
687 <firstterm>referencing</firstterm> table and the products table is
688 the <firstterm>referenced</firstterm> table. Similarly, there are
689 referencing and referenced columns.
693 You can also shorten the above command to
695 CREATE TABLE orders (
696 order_id integer PRIMARY KEY,
697 product_no integer <emphasis>REFERENCES products</emphasis>,
701 because in absence of a column list the primary key of the
702 referenced table is used as the referenced column(s).
706 A foreign key can also constrain and reference a group of columns.
707 As usual, it then needs to be written in table constraint form.
708 Here is a contrived syntax example:
711 a integer PRIMARY KEY,
714 <emphasis>FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)</emphasis>
717 Of course, the number and type of the constrained columns need to
718 match the number and type of the referenced columns.
722 You can assign your own name for a foreign key constraint,
727 A table can contain more than one foreign key constraint. This is
728 used to implement many-to-many relationships between tables. Say
729 you have tables about products and orders, but now you want to
730 allow one order to contain possibly many products (which the
731 structure above did not allow). You could use this table structure:
733 CREATE TABLE products (
734 product_no integer PRIMARY KEY,
739 CREATE TABLE orders (
740 order_id integer PRIMARY KEY,
741 shipping_address text,
745 CREATE TABLE order_items (
746 product_no integer REFERENCES products,
747 order_id integer REFERENCES orders,
749 PRIMARY KEY (product_no, order_id)
752 Notice that the primary key overlaps with the foreign keys in
757 <primary>CASCADE</primary>
758 <secondary>foreign key action</secondary>
762 <primary>RESTRICT</primary>
763 <secondary>foreign key action</secondary>
767 We know that the foreign keys disallow creation of orders that
768 do not relate to any products. But what if a product is removed
769 after an order is created that references it? SQL allows you to
770 handle that as well. Intuitively, we have a few options:
771 <itemizedlist spacing="compact">
772 <listitem><para>Disallow deleting a referenced product</para></listitem>
773 <listitem><para>Delete the orders as well</para></listitem>
774 <listitem><para>Something else?</para></listitem>
779 To illustrate this, let's implement the following policy on the
780 many-to-many relationship example above: when someone wants to
781 remove a product that is still referenced by an order (via
782 <literal>order_items</literal>), we disallow it. If someone
783 removes an order, the order items are removed as well.
785 CREATE TABLE products (
786 product_no integer PRIMARY KEY,
791 CREATE TABLE orders (
792 order_id integer PRIMARY KEY,
793 shipping_address text,
797 CREATE TABLE order_items (
798 product_no integer REFERENCES products <emphasis>ON DELETE RESTRICT</emphasis>,
799 order_id integer REFERENCES orders <emphasis>ON DELETE CASCADE</emphasis>,
801 PRIMARY KEY (product_no, order_id)
807 Restricting and cascading deletes are the two most common options.
808 <literal>RESTRICT</literal> prevents deletion of a
809 referenced row. <literal>NO ACTION</literal> means that if any
810 referencing rows still exist when the constraint is checked, an error
811 is raised; this is the default behavior if you do not specify anything.
812 (The essential difference between these two choices is that
813 <literal>NO ACTION</literal> allows the check to be deferred until
814 later in the transaction, whereas <literal>RESTRICT</literal> does not.)
815 <literal>CASCADE</> specifies that when a referenced row is deleted,
816 row(s) referencing it should be automatically deleted as well.
817 There are two other options:
818 <literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
819 These cause the referencing columns to be set to nulls or default
820 values, respectively, when the referenced row is deleted.
821 Note that these do not excuse you from observing any constraints.
822 For example, if an action specifies <literal>SET DEFAULT</literal>
823 but the default value would not satisfy the foreign key, the
828 Analogous to <literal>ON DELETE</literal> there is also
829 <literal>ON UPDATE</literal> which is invoked when a referenced
830 column is changed (updated). The possible actions are the same.
834 More information about updating and deleting data is in <xref
839 Finally, we should mention that a foreign key must reference
840 columns that either are a primary key or form a unique constraint.
841 If the foreign key references a unique constraint, there are some
842 additional possibilities regarding how null values are matched.
843 These are explained in the reference documentation for
844 <xref linkend="sql-createtable" endterm="sql-createtable-title">.
849 <sect1 id="ddl-system-columns">
850 <title>System Columns</title>
853 Every table has several <firstterm>system columns</> that are
854 implicitly defined by the system. Therefore, these names cannot be
855 used as names of user-defined columns. (Note that these
856 restrictions are separate from whether the name is a key word or
857 not; quoting a name will not allow you to escape these
858 restrictions.) You do not really need to be concerned about these
859 columns, just know they exist.
863 <primary>column</primary>
864 <secondary>system column</secondary>
869 <term><structfield>oid</></term>
873 <primary>OID</primary>
874 <secondary>column</secondary>
876 The object identifier (object ID) of a row. This column is only
877 present if the table was created using <literal>WITH
878 OIDS</literal>, or if the <xref linkend="guc-default-with-oids">
879 configuration variable was set. This column is of type
880 <type>oid</type> (same name as the column); see <xref
881 linkend="datatype-oid"> for more information about the type.
887 <term><structfield>tableoid</></term>
890 <primary>tableoid</primary>
894 The OID of the table containing this row. This column is
895 particularly handy for queries that select from inheritance
896 hierarchies (see <xref linkend="ddl-inherit">), since without it,
897 it's difficult to tell which individual table a row came from. The
898 <structfield>tableoid</structfield> can be joined against the
899 <structfield>oid</structfield> column of
900 <structname>pg_class</structname> to obtain the table name.
906 <term><structfield>xmin</></term>
909 <primary>xmin</primary>
913 The identity (transaction ID) of the inserting transaction for
914 this row version. (A row version is an individual state of a
915 row; each update of a row creates a new row version for the same
922 <term><structfield>cmin</></term>
925 <primary>cmin</primary>
929 The command identifier (starting at zero) within the inserting
936 <term><structfield>xmax</></term>
939 <primary>xmax</primary>
943 The identity (transaction ID) of the deleting transaction, or
944 zero for an undeleted row version. It is possible for this column to
945 be nonzero in a visible row version. That usually indicates that the
946 deleting transaction hasn't committed yet, or that an attempted
947 deletion was rolled back.
953 <term><structfield>cmax</></term>
956 <primary>cmax</primary>
960 The command identifier within the deleting transaction, or zero.
966 <term><structfield>ctid</></term>
969 <primary>ctid</primary>
973 The physical location of the row version within its table. Note that
974 although the <structfield>ctid</structfield> can be used to
975 locate the row version very quickly, a row's
976 <structfield>ctid</structfield> will change each time it is
977 updated or moved by <command>VACUUM FULL</>. Therefore
978 <structfield>ctid</structfield> is useless as a long-term row
979 identifier. The OID, or even better a user-defined serial
980 number, should be used to identify logical rows.
987 OIDs are 32-bit quantities and are assigned from a single
988 cluster-wide counter. In a large or long-lived database, it is
989 possible for the counter to wrap around. Hence, it is bad
990 practice to assume that OIDs are unique, unless you take steps to
991 ensure that this is the case. If you need to identify the rows in
992 a table, using a sequence generator is strongly recommended.
993 However, OIDs can be used as well, provided that a few additional
994 precautions are taken:
999 A unique constraint should be created on the OID column of each
1000 table for which the OID will be used to identify rows. When such
1001 a unique constraint (or unique index) exists, the system takes
1002 care not to generate an OID matching an already-existing row.
1003 (Of course, this is only possible if the table contains fewer
1004 than 2<superscript>32</> (4 billion) rows, and in practice the
1005 table size had better be much less than that, or performance
1011 OIDs should never be assumed to be unique across tables; use
1012 the combination of <structfield>tableoid</> and row OID if you
1013 need a database-wide identifier.
1018 The tables in question should be created using <literal>WITH
1019 OIDS</literal>. As of <productname>PostgreSQL</productname> 8.1,
1020 <literal>WITHOUT OIDS</> is the default.
1027 Transaction identifiers are also 32-bit quantities. In a
1028 long-lived database it is possible for transaction IDs to wrap
1029 around. This is not a fatal problem given appropriate maintenance
1030 procedures; see <xref linkend="maintenance"> for details. It is
1031 unwise, however, to depend on the uniqueness of transaction IDs
1032 over the long term (more than one billion transactions).
1037 identifiers are also 32-bit quantities. This creates a hard limit
1038 of 2<superscript>32</> (4 billion) <acronym>SQL</acronym> commands
1039 within a single transaction. In practice this limit is not a
1040 problem — note that the limit is on number of
1041 <acronym>SQL</acronym> commands, not number of rows processed.
1045 <sect1 id="ddl-alter">
1046 <title>Modifying Tables</title>
1048 <indexterm zone="ddl-alter">
1049 <primary>table</primary>
1050 <secondary>modifying</secondary>
1054 When you create a table and you realize that you made a mistake, or
1055 the requirements of the application change, then you can drop the
1056 table and create it again. But this is not a convenient option if
1057 the table is already filled with data, or if the table is
1058 referenced by other database objects (for instance a foreign key
1059 constraint). Therefore <productname>PostgreSQL</productname>
1060 provides a family of commands to make modifications to existing
1061 tables. Note that this is conceptually distinct from altering
1062 the data contained in the table: here we are interested in altering
1063 the definition, or structure, of the table.
1068 <itemizedlist spacing="compact">
1070 <para>Add columns,</para>
1073 <para>Remove columns,</para>
1076 <para>Add constraints,</para>
1079 <para>Remove constraints,</para>
1082 <para>Change default values,</para>
1085 <para>Change column data types,</para>
1088 <para>Rename columns,</para>
1091 <para>Rename tables.</para>
1095 All these actions are performed using the
1096 <xref linkend="sql-altertable" endterm="sql-altertable-title">
1101 <title>Adding a Column</title>
1104 <primary>column</primary>
1105 <secondary>adding</secondary>
1109 To add a column, use a command like this:
1111 ALTER TABLE products ADD COLUMN description text;
1113 The new column is initially filled with whatever default
1114 value is given (null if you don't specify a <literal>DEFAULT</> clause).
1118 You can also define constraints on the column at the same time,
1119 using the usual syntax:
1121 ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
1123 In fact all the options that can be applied to a column description
1124 in <command>CREATE TABLE</> can be used here. Keep in mind however
1125 that the default value must satisfy the given constraints, or the
1126 <literal>ADD</> will fail. Alternatively, you can add
1127 constraints later (see below) after you've filled in the new column
1133 <title>Removing a Column</title>
1136 <primary>column</primary>
1137 <secondary>removing</secondary>
1141 To remove a column, use a command like this:
1143 ALTER TABLE products DROP COLUMN description;
1145 Whatever data was in the column disappears. Table constraints involving
1146 the column are dropped, too. However, if the column is referenced by a
1147 foreign key constraint of another table,
1148 <productname>PostgreSQL</productname> will not silently drop that
1149 constraint. You can authorize dropping everything that depends on
1150 the column by adding <literal>CASCADE</>:
1152 ALTER TABLE products DROP COLUMN description CASCADE;
1154 See <xref linkend="ddl-depend"> for a description of the general
1155 mechanism behind this.
1160 <title>Adding a Constraint</title>
1163 <primary>constraint</primary>
1164 <secondary>adding</secondary>
1168 To add a constraint, the table constraint syntax is used. For example:
1170 ALTER TABLE products ADD CHECK (name <> '');
1171 ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
1172 ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
1174 To add a not-null constraint, which cannot be written as a table
1175 constraint, use this syntax:
1177 ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
1182 The constraint will be checked immediately, so the table data must
1183 satisfy the constraint before it can be added.
1188 <title>Removing a Constraint</title>
1191 <primary>constraint</primary>
1192 <secondary>removing</secondary>
1196 To remove a constraint you need to know its name. If you gave it
1197 a name then that's easy. Otherwise the system assigned a
1198 generated name, which you need to find out. The
1199 <application>psql</application> command <literal>\d
1200 <replaceable>tablename</replaceable></literal> can be helpful
1201 here; other interfaces might also provide a way to inspect table
1202 details. Then the command is:
1204 ALTER TABLE products DROP CONSTRAINT some_name;
1206 (If you are dealing with a generated constraint name like <literal>$2</>,
1207 don't forget that you'll need to double-quote it to make it a valid
1212 As with dropping a column, you need to add <literal>CASCADE</> if you
1213 want to drop a constraint that something else depends on. An example
1214 is that a foreign key constraint depends on a unique or primary key
1215 constraint on the referenced column(s).
1219 This works the same for all constraint types except not-null
1220 constraints. To drop a not null constraint use
1222 ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
1224 (Recall that not-null constraints do not have names.)
1229 <title>Changing a Column's Default Value</title>
1232 <primary>default value</primary>
1233 <secondary>changing</secondary>
1237 To set a new default for a column, use a command like this:
1239 ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
1241 Note that this doesn't affect any existing rows in the table, it
1242 just changes the default for future <command>INSERT</> commands.
1246 To remove any default value, use
1248 ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
1250 This is effectively the same as setting the default to null.
1251 As a consequence, it is not an error
1252 to drop a default where one hadn't been defined, because the
1253 default is implicitly the null value.
1258 <title>Changing a Column's Data Type</title>
1261 <primary>column data type</primary>
1262 <secondary>changing</secondary>
1266 To convert a column to a different data type, use a command like this:
1268 ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
1270 This will succeed only if each existing entry in the column can be
1271 converted to the new type by an implicit cast. If a more complex
1272 conversion is needed, you can add a <literal>USING</> clause that
1273 specifies how to compute the new values from the old.
1277 <productname>PostgreSQL</> will attempt to convert the column's
1278 default value (if any) to the new type, as well as any constraints
1279 that involve the column. But these conversions may fail, or may
1280 produce surprising results. It's often best to drop any constraints
1281 on the column before altering its type, and then add back suitably
1282 modified constraints afterwards.
1287 <title>Renaming a Column</title>
1290 <primary>column</primary>
1291 <secondary>renaming</secondary>
1297 ALTER TABLE products RENAME COLUMN product_no TO product_number;
1303 <title>Renaming a Table</title>
1306 <primary>table</primary>
1307 <secondary>renaming</secondary>
1313 ALTER TABLE products RENAME TO items;
1319 <sect1 id="ddl-priv">
1320 <title>Privileges</title>
1322 <indexterm zone="ddl-priv">
1323 <primary>privilege</primary>
1327 <primary>permission</primary>
1328 <see>privilege</see>
1332 When you create a database object, you become its owner. By
1333 default, only the owner of an object can do anything with the
1334 object. In order to allow other users to use it,
1335 <firstterm>privileges</firstterm> must be granted. (However,
1336 users that have the superuser attribute can always
1341 There are several different privileges: <literal>SELECT</>,
1342 <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
1343 <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
1344 <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>, and
1345 <literal>USAGE</>. The privileges applicable to a particular
1346 object vary depending on the object's type (table, function, etc).
1347 For complete information on the different types of privileges
1348 supported by <productname>PostgreSQL</productname>, refer to the
1349 <xref linkend="sql-grant" endterm="sql-grant-title"> reference
1350 page. The following sections and chapters will also show you how
1351 those privileges are used.
1355 The right to modify or destroy an object is always the privilege of
1361 To change the owner of a table, index, sequence, or view, use the
1362 <xref linkend="sql-altertable" endterm="sql-altertable-title">
1363 command. There are corresponding <literal>ALTER</> commands for
1369 To assign privileges, the <command>GRANT</command> command is
1370 used. For example, if <literal>joe</literal> is an existing user, and
1371 <literal>accounts</literal> is an existing table, the privilege to
1372 update the table can be granted with
1374 GRANT UPDATE ON accounts TO joe;
1376 To grant a privilege to a group, use this syntax:
1378 GRANT SELECT ON accounts TO GROUP staff;
1380 The special <quote>user</quote> name <literal>PUBLIC</literal> can
1381 be used to grant a privilege to every user on the system. Writing
1382 <literal>ALL</literal> in place of a specific privilege grants all
1383 privileges that are relevant for the object type.
1387 To revoke a privilege, use the fittingly named
1388 <command>REVOKE</command> command:
1390 REVOKE ALL ON accounts FROM PUBLIC;
1392 The special privileges of the object owner (i.e., the right to do
1393 <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc.)
1394 are always implicit in being the owner,
1395 and cannot be granted or revoked. But the object owner can choose
1396 to revoke his own ordinary privileges, for example to make a
1397 table read-only for himself as well as others.
1401 Ordinarily, only the object's owner (or a superuser) can grant or
1402 revoke privileges on an object. However, it is possible to grant a
1403 privilege <quote>with grant option</>, which gives the recipient
1404 the right to grant it in turn to others. If the grant option is
1405 subsequently revoked then all who received the privilege from that
1406 recipient (directly or through a chain of grants) will lose the
1407 privilege. For details see the <xref linkend="sql-grant"
1408 endterm="sql-grant-title"> and <xref linkend="sql-revoke"
1409 endterm="sql-revoke-title"> reference pages.
1413 <sect1 id="ddl-schemas">
1414 <title>Schemas</title>
1416 <indexterm zone="ddl-schemas">
1417 <primary>schema</primary>
1421 A <productname>PostgreSQL</productname> database cluster
1422 contains one or more named databases. Users and groups of users are
1423 shared across the entire cluster, but no other data is shared across
1424 databases. Any given client connection to the server can access
1425 only the data in a single database, the one specified in the connection
1431 Users of a cluster do not necessarily have the privilege to access every
1432 database in the cluster. Sharing of user names means that there
1433 cannot be different users named, say, <literal>joe</> in two databases
1434 in the same cluster; but the system can be configured to allow
1435 <literal>joe</> access to only some of the databases.
1440 A database contains one or more named <firstterm>schemas</>, which
1441 in turn contain tables. Schemas also contain other kinds of named
1442 objects, including data types, functions, and operators. The same
1443 object name can be used in different schemas without conflict; for
1444 example, both <literal>schema1</> and <literal>myschema</> may
1445 contain tables named <literal>mytable</>. Unlike databases,
1446 schemas are not rigidly separated: a user may access objects in any
1447 of the schemas in the database he is connected to, if he has
1448 privileges to do so.
1452 There are several reasons why one might want to use schemas:
1457 To allow many users to use one database without interfering with
1464 To organize database objects into logical groups to make them
1471 Third-party applications can be put into separate schemas so
1472 they cannot collide with the names of other objects.
1477 Schemas are analogous to directories at the operating system level,
1478 except that schemas cannot be nested.
1481 <sect2 id="ddl-schemas-create">
1482 <title>Creating a Schema</title>
1484 <indexterm zone="ddl-schemas-create">
1485 <primary>schema</primary>
1486 <secondary>creating</secondary>
1490 To create a schema, use the command <command>CREATE
1491 SCHEMA</command>. Give the schema a name of your choice. For
1494 CREATE SCHEMA myschema;
1499 <primary>qualified name</primary>
1503 <primary>name</primary>
1504 <secondary>qualified</secondary>
1508 To create or access objects in a schema, write a
1509 <firstterm>qualified name</> consisting of the schema name and
1510 table name separated by a dot:
1512 <replaceable>schema</><literal>.</><replaceable>table</>
1514 This works anywhere a table name is expected, including the table
1515 modification commands and the data access commands discussed in
1516 the following chapters.
1517 (For brevity we will speak of tables only, but the same ideas apply
1518 to other kinds of named objects, such as types and functions.)
1522 Actually, the even more general syntax
1524 <replaceable>database</><literal>.</><replaceable>schema</><literal>.</><replaceable>table</>
1526 can be used too, but at present this is just for <foreignphrase>pro
1527 forma</> compliance with the SQL standard. If you write a database name,
1528 it must be the same as the database you are connected to.
1532 So to create a table in the new schema, use
1534 CREATE TABLE myschema.mytable (
1541 <primary>schema</primary>
1542 <secondary>removing</secondary>
1546 To drop a schema if it's empty (all objects in it have been
1549 DROP SCHEMA myschema;
1551 To drop a schema including all contained objects, use
1553 DROP SCHEMA myschema CASCADE;
1555 See <xref linkend="ddl-depend"> for a description of the general
1556 mechanism behind this.
1560 Often you will want to create a schema owned by someone else
1561 (since this is one of the ways to restrict the activities of your
1562 users to well-defined namespaces). The syntax for that is:
1564 CREATE SCHEMA <replaceable>schemaname</replaceable> AUTHORIZATION <replaceable>username</replaceable>;
1566 You can even omit the schema name, in which case the schema name
1567 will be the same as the user name. See <xref
1568 linkend="ddl-schemas-patterns"> for how this can be useful.
1572 Schema names beginning with <literal>pg_</> are reserved for
1573 system purposes and may not be created by users.
1577 <sect2 id="ddl-schemas-public">
1578 <title>The Public Schema</title>
1580 <indexterm zone="ddl-schemas-public">
1581 <primary>schema</primary>
1582 <secondary>public</secondary>
1586 In the previous sections we created tables without specifying any
1587 schema names. By default, such tables (and other objects) are
1588 automatically put into a schema named <quote>public</quote>. Every new
1589 database contains such a schema. Thus, the following are equivalent:
1591 CREATE TABLE products ( ... );
1595 CREATE TABLE public.products ( ... );
1600 <sect2 id="ddl-schemas-path">
1601 <title>The Schema Search Path</title>
1604 <primary>search path</primary>
1608 <primary>unqualified name</primary>
1612 <primary>name</primary>
1613 <secondary>unqualified</secondary>
1617 Qualified names are tedious to write, and it's often best not to
1618 wire a particular schema name into applications anyway. Therefore
1619 tables are often referred to by <firstterm>unqualified names</>,
1620 which consist of just the table name. The system determines which table
1621 is meant by following a <firstterm>search path</>, which is a list
1622 of schemas to look in. The first matching table in the search path
1623 is taken to be the one wanted. If there is no match in the search
1624 path, an error is reported, even if matching table names exist
1625 in other schemas in the database.
1629 <primary>schema</primary>
1630 <secondary>current</secondary>
1634 The first schema named in the search path is called the current schema.
1635 Aside from being the first schema searched, it is also the schema in
1636 which new tables will be created if the <command>CREATE TABLE</>
1637 command does not specify a schema name.
1641 <primary>search_path</primary>
1645 To show the current search path, use the following command:
1649 In the default setup this returns:
1655 The first element specifies that a schema with the same name as
1656 the current user is to be searched. If no such schema exists,
1657 the entry is ignored. The second element refers to the
1658 public schema that we have seen already.
1662 The first schema in the search path that exists is the default
1663 location for creating new objects. That is the reason that by
1664 default objects are created in the public schema. When objects
1665 are referenced in any other context without schema qualification
1666 (table modification, data modification, or query commands) the
1667 search path is traversed until a matching object is found.
1668 Therefore, in the default configuration, any unqualified access
1669 again can only refer to the public schema.
1673 To put our new schema in the path, we use
1675 SET search_path TO myschema,public;
1677 (We omit the <literal>$user</literal> here because we have no
1678 immediate need for it.) And then we can access the table without
1679 schema qualification:
1683 Also, since <literal>myschema</literal> is the first element in
1684 the path, new objects would by default be created in it.
1688 We could also have written
1690 SET search_path TO myschema;
1692 Then we no longer have access to the public schema without
1693 explicit qualification. There is nothing special about the public
1694 schema except that it exists by default. It can be dropped, too.
1698 See also <xref linkend="functions-info"> for other ways to manipulate
1699 the schema search path.
1703 The search path works in the same way for data type names, function names,
1704 and operator names as it does for table names. Data type and function
1705 names can be qualified in exactly the same way as table names. If you
1706 need to write a qualified operator name in an expression, there is a
1707 special provision: you must write
1709 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operator</><literal>)</>
1711 This is needed to avoid syntactic ambiguity. An example is
1713 SELECT 3 OPERATOR(pg_catalog.+) 4;
1715 In practice one usually relies on the search path for operators,
1716 so as not to have to write anything so ugly as that.
1720 <sect2 id="ddl-schemas-priv">
1721 <title>Schemas and Privileges</title>
1723 <indexterm zone="ddl-schemas-priv">
1724 <primary>privilege</primary>
1725 <secondary sortas="schemas">for schemas</secondary>
1729 By default, users cannot access any objects in schemas they do not
1730 own. To allow that, the owner of the schema needs to grant the
1731 <literal>USAGE</literal> privilege on the schema. To allow users
1732 to make use of the objects in the schema, additional privileges
1733 may need to be granted, as appropriate for the object.
1737 A user can also be allowed to create objects in someone else's
1738 schema. To allow that, the <literal>CREATE</literal> privilege on
1739 the schema needs to be granted. Note that by default, everyone
1740 has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
1742 <literal>public</literal>. This allows all users that are able to
1743 connect to a given database to create objects in its
1744 <literal>public</literal> schema. If you do
1745 not want to allow that, you can revoke that privilege:
1747 REVOKE CREATE ON SCHEMA public FROM PUBLIC;
1749 (The first <quote>public</quote> is the schema, the second
1750 <quote>public</quote> means <quote>every user</quote>. In the
1751 first sense it is an identifier, in the second sense it is a
1752 key word, hence the different capitalization; recall the
1753 guidelines from <xref linkend="sql-syntax-identifiers">.)
1757 <sect2 id="ddl-schemas-catalog">
1758 <title>The System Catalog Schema</title>
1760 <indexterm zone="ddl-schemas-catalog">
1761 <primary>system catalog</primary>
1762 <secondary>schema</secondary>
1766 In addition to <literal>public</> and user-created schemas, each
1767 database contains a <literal>pg_catalog</> schema, which contains
1768 the system tables and all the built-in data types, functions, and
1769 operators. <literal>pg_catalog</> is always effectively part of
1770 the search path. If it is not named explicitly in the path then
1771 it is implicitly searched <emphasis>before</> searching the path's
1772 schemas. This ensures that built-in names will always be
1773 findable. However, you may explicitly place
1774 <literal>pg_catalog</> at the end of your search path if you
1775 prefer to have user-defined names override built-in names.
1779 In <productname>PostgreSQL</productname> versions before 7.3,
1780 table names beginning with <literal>pg_</> were reserved. This is
1781 no longer true: you may create such a table name if you wish, in
1782 any non-system schema. However, it's best to continue to avoid
1783 such names, to ensure that you won't suffer a conflict if some
1784 future version defines a system table named the same as your
1785 table. (With the default search path, an unqualified reference to
1786 your table name would be resolved as the system table instead.)
1787 System tables will continue to follow the convention of having
1788 names beginning with <literal>pg_</>, so that they will not
1789 conflict with unqualified user-table names so long as users avoid
1790 the <literal>pg_</> prefix.
1794 <sect2 id="ddl-schemas-patterns">
1795 <title>Usage Patterns</title>
1798 Schemas can be used to organize your data in many ways. There are
1799 a few usage patterns that are recommended and are easily supported by
1800 the default configuration:
1804 If you do not create any schemas then all users access the
1805 public schema implicitly. This simulates the situation where
1806 schemas are not available at all. This setup is mainly
1807 recommended when there is only a single user or a few cooperating
1808 users in a database. This setup also allows smooth transition
1809 from the non-schema-aware world.
1815 You can create a schema for each user with the same name as
1816 that user. Recall that the default search path starts with
1817 <literal>$user</literal>, which resolves to the user name.
1818 Therefore, if each user has a separate schema, they access their
1819 own schemas by default.
1823 If you use this setup then you might also want to revoke access
1824 to the public schema (or drop it altogether), so users are
1825 truly constrained to their own schemas.
1831 To install shared applications (tables to be used by everyone,
1832 additional functions provided by third parties, etc.), put them
1833 into separate schemas. Remember to grant appropriate
1834 privileges to allow the other users to access them. Users can
1835 then refer to these additional objects by qualifying the names
1836 with a schema name, or they can put the additional schemas into
1837 their search path, as they choose.
1844 <sect2 id="ddl-schemas-portability">
1845 <title>Portability</title>
1848 In the SQL standard, the notion of objects in the same schema
1849 being owned by different users does not exist. Moreover, some
1850 implementations do not allow you to create schemas that have a
1851 different name than their owner. In fact, the concepts of schema
1852 and user are nearly equivalent in a database system that
1853 implements only the basic schema support specified in the
1854 standard. Therefore, many users consider qualified names to
1856 <literal><replaceable>username</>.<replaceable>tablename</></literal>.
1857 This is how <productname>PostgreSQL</productname> will effectively
1858 behave if you create a per-user schema for every user.
1862 Also, there is no concept of a <literal>public</> schema in the
1863 SQL standard. For maximum conformance to the standard, you should
1864 not use (perhaps even remove) the <literal>public</> schema.
1868 Of course, some SQL database systems might not implement schemas
1869 at all, or provide namespace support by allowing (possibly
1870 limited) cross-database access. If you need to work with those
1871 systems, then maximum portability would be achieved by not using
1877 <sect1 id="ddl-inherit">
1878 <title>Inheritance</title>
1881 <primary>inheritance</primary>
1885 <primary>table</primary>
1886 <secondary>inheritance</secondary>
1890 <productname>PostgreSQL</productname> implements table inheritance
1891 which can be a useful tool for database designers. (SQL:1999 and
1892 later define a type inheritance feature, which differs in many
1893 respects from the features described here.)
1897 Let's start with an example: suppose we are trying to build a data
1898 model for cities. Each state has many cities, but only one
1899 capital. We want to be able to quickly retrieve the capital city
1900 for any particular state. This can be done by creating two tables,
1901 one for state capitals and one for cities that are not
1902 capitals. However, what happens when we want to ask for data about
1903 a city, regardless of whether it is a capital or not? The
1904 inheritance feature can help to resolve this problem. We define the
1905 <structname>capitals</structname> table so that it inherits from
1906 <structname>cities</structname>:
1909 CREATE TABLE cities (
1912 altitude int -- in feet
1915 CREATE TABLE capitals (
1917 ) INHERITS (cities);
1920 In this case, the <structname>capitals</> table <firstterm>inherits</>
1921 all the columns of its parent table, <structname>cities</>. State
1922 capitals also have an extra column, <structfield>state</>, that shows
1927 In <productname>PostgreSQL</productname>, a table can inherit from
1928 zero or more other tables, and a query can reference either all
1929 rows of a table or all rows of a table plus all of its descendant tables.
1930 The latter behavior is the default.
1931 For example, the following query finds the names of all cities,
1932 including state capitals, that are located at an altitude over
1936 SELECT name, altitude
1938 WHERE altitude > 500;
1941 Given the sample data from the <productname>PostgreSQL</productname>
1942 tutorial (see <xref linkend="tutorial-sql-intro">), this returns:
1946 -----------+----------
1954 On the other hand, the following query finds all the cities that
1955 are not state capitals and are situated at an altitude over 500ft:
1958 SELECT name, altitude
1960 WHERE altitude > 500;
1963 -----------+----------
1970 Here the <literal>ONLY</literal> keyword indicates that the query
1971 should apply only to <structname>cities</structname>, and not any tables
1972 below <structname>cities</structname> in the inheritance hierarchy. Many
1973 of the commands that we have already discussed —
1974 <command>SELECT</command>, <command>UPDATE</command> and
1975 <command>DELETE</command> — support the
1976 <literal>ONLY</literal> keyword.
1980 In some cases you may wish to know which table a particular row
1981 originated from. There is a system column called
1982 <structfield>tableoid</structfield> in each table which can tell you the
1986 SELECT c.tableoid, c.name, c.altitude
1988 WHERE c.altitude > 500;
1994 tableoid | name | altitude
1995 ----------+-----------+----------
1996 139793 | Las Vegas | 2174
1997 139793 | Mariposa | 1953
1998 139798 | Madison | 845
2001 (If you try to reproduce this example, you will probably get
2002 different numeric OIDs.) By doing a join with
2003 <structname>pg_class</> you can see the actual table names:
2006 SELECT p.relname, c.name, c.altitude
2007 FROM cities c, pg_class p
2008 WHERE c.altitude > 500 and c.tableoid = p.oid;
2014 relname | name | altitude
2015 ----------+-----------+----------
2016 cities | Las Vegas | 2174
2017 cities | Mariposa | 1953
2018 capitals | Madison | 845
2023 Inheritance does not automatically propagate data from
2024 <command>INSERT</command> or <command>COPY</command> commands to
2025 other tables in the inheritance hierarchy. In our example, the
2026 following <command>INSERT</command> statement will fail:
2028 INSERT INTO cities (name, population, altitude, state)
2029 VALUES ('New York', NULL, NULL, 'NY');
2031 We might hope that the data would somehow be routed to the
2032 <structname>capitals</structname> table, but this does not happen:
2033 <command>INSERT</command> always inserts into exactly the table
2034 specified. In some cases it is possible to redirect the insertion
2035 using a rule (see <xref linkend="rules">). However that does not
2036 help for the above case because the <structname>cities</> table
2037 does not contain the column <structfield>state</>, and so the
2038 command will be rejected before the rule can be applied.
2042 Check constraints can be defined on tables within an inheritance
2043 hierarchy. All check constraints on a parent table are
2044 automatically inherited by all of its children. Other types of
2045 constraints are not inherited, however.
2049 A table can inherit from more than one parent table, in which case it has
2050 the union of the columns defined by the parent tables. Any columns
2051 declared in the child table's definition are added to these. If the
2052 same column name appears in multiple parent tables, or in both a parent
2053 table and the child's definition, then these columns are <quote>merged</>
2054 so that there is only one such column in the child table. To be merged,
2055 columns must have the same data types, else an error is raised. The
2056 merged column will have copies of all the check constraints coming from
2057 any one of the column definitions it came from.
2061 Table inheritance can currently only be defined using the <xref
2062 linkend="sql-createtable" endterm="sql-createtable-title">
2063 statement. The related statement <command>CREATE TABLE AS</command> does
2064 not allow inheritance to be specified. There
2065 is no way to add an inheritance link to make an existing table into
2066 a child table. Similarly, there is no way to remove an inheritance
2067 link from a child table once it has been defined, other than by dropping
2068 the table completely. A parent table cannot be dropped
2069 while any of its children remain. If you wish to remove a table and
2070 all of its descendants, one easy way is to drop the parent table with
2071 the <literal>CASCADE</literal> option.
2075 <xref linkend="sql-altertable" endterm="sql-altertable-title"> will
2076 propagate any changes in column data definitions and check
2077 constraints down the inheritance hierarchy. Again, dropping
2078 columns or constraints on parent tables is only possible when using
2079 the <literal>CASCADE</literal> option. <command>ALTER
2080 TABLE</command> follows the same rules for duplicate column merging
2081 and rejection that apply during <command>CREATE TABLE</command>.
2084 <sect2 id="ddl-inherit-caveats">
2085 <title>Caveats</title>
2088 Table access permissions are not automatically inherited. Therefore,
2089 a user attempting to access a parent table must either have permissions
2090 to do the operation on all its child tables as well, or must use the
2091 <literal>ONLY</literal> notation. When adding a new child table to
2092 an existing inheritance hierarchy, be careful to grant all the needed
2097 A serious limitation of the inheritance feature is that indexes (including
2098 unique constraints) and foreign key constraints only apply to single
2099 tables, not to their inheritance children. This is true on both the
2100 referencing and referenced sides of a foreign key constraint. Thus,
2101 in the terms of the above example:
2106 If we declared <structname>cities</>.<structfield>name</> to be
2107 <literal>UNIQUE</> or a <literal>PRIMARY KEY</>, this would not stop the
2108 <structname>capitals</> table from having rows with names duplicating
2109 rows in <structname>cities</>. And those duplicate rows would by
2110 default show up in queries from <structname>cities</>. In fact, by
2111 default <structname>capitals</> would have no unique constraint at all,
2112 and so could contain multiple rows with the same name.
2113 You could add a unique constraint to <structname>capitals</>, but this
2114 would not prevent duplication compared to <structname>cities</>.
2120 Similarly, if we were to specify that
2121 <structname>cities</>.<structfield>name</> <literal>REFERENCES</> some
2122 other table, this constraint would not automatically propagate to
2123 <structname>capitals</>. In this case you could work around it by
2124 manually adding the same <literal>REFERENCES</> constraint to
2125 <structname>capitals</>.
2131 Specifying that another table's column <literal>REFERENCES
2132 cities(name)</> would allow the other table to contain city names, but
2133 not capital names. There is no good workaround for this case.
2138 These deficiencies will probably be fixed in some future release,
2139 but in the meantime considerable care is needed in deciding whether
2140 inheritance is useful for your problem.
2144 <title>Deprecated</title>
2146 In previous versions of <productname>PostgreSQL</productname>, the
2147 default behavior was not to include child tables in queries. This was
2148 found to be error prone and is also in violation of the SQL
2149 standard. Under the old syntax, to include the child tables you append
2150 <literal>*</literal> to the table name. For example:
2152 SELECT * from cities*;
2154 You can still explicitly specify scanning child tables by
2155 appending <literal>*</literal>, as well as explicitly specify not
2156 scanning child tables by writing <literal>ONLY</literal>. But
2157 beginning in version 7.1, the default behavior for an undecorated
2158 table name is to scan its child tables too, whereas before the
2159 default was not to do so. To get the old default behavior,
2160 disable the <xref linkend="guc-sql-inheritance"> configuration
2168 <sect1 id="ddl-partitioning">
2169 <title>Partitioning</title>
2172 <primary>partitioning</primary>
2176 <primary>table</primary>
2177 <secondary>partitioning</secondary>
2181 <productname>PostgreSQL</productname> supports basic table
2182 partitioning. This section describes why and how to implement
2183 partitioning as part of your database design.
2186 <sect2 id="ddl-partitioning-overview">
2187 <title>Overview</title>
2190 Partitioning refers to splitting what is logically one large table
2191 into smaller physical pieces.
2192 Partitioning can provide several benefits:
2196 Query performance can be improved dramatically for certain kinds
2203 Update performance can be improved too, since each piece of the table
2204 has indexes smaller than an index on the entire data set would be.
2205 When an index no longer fits easily
2206 in memory, both read and write operations on the index take
2207 progressively more disk accesses.
2213 Bulk deletes may be accomplished by simply removing one of the
2214 partitions, if that requirement is planned into the partitioning design.
2215 <command>DROP TABLE</> is far faster than a bulk <command>DELETE</>,
2216 to say nothing of the ensuing <command>VACUUM</> overhead.
2222 Seldom-used data can be migrated to cheaper and slower storage media.
2227 The benefits will normally be worthwhile only when a table would
2228 otherwise be very large. The exact point at which a table will
2229 benefit from partitioning depends on the application, although a
2230 rule of thumb is that the size of the table should exceed the physical
2231 memory of the database server.
2235 Currently, <productname>PostgreSQL</productname> supports partitioning
2236 via table inheritance. Each partition must be created as a child
2237 table of a single parent table. The parent table itself is normally
2238 empty; it exists just to represent the entire data set. You should be
2239 familiar with inheritance (see <xref linkend="ddl-inherit">) before
2240 attempting to set up partitioning.
2244 The following forms of partitioning can be implemented in
2245 <productname>PostgreSQL</productname>:
2249 <term>Range Partitioning</term>
2253 The table is partitioned into <quote>ranges</quote> defined
2254 by a key column or set of columns, with no overlap between
2255 the ranges of values assigned to different partitions. For
2256 example one might partition by date ranges, or by ranges of
2257 identifiers for particular business objects.
2263 <term>List Partitioning</term>
2267 The table is partitioned by explicitly listing which key values
2268 appear in each partition.
2274 Hash partitioning is not currently supported.
2278 <sect2 id="ddl-partitioning-implementation">
2279 <title>Implementing Partitioning</title>
2282 To set up a partitioned table, do the following:
2283 <orderedlist spacing=compact>
2286 Create the <quote>master</quote> table, from which all of the
2287 partitions will inherit.
2290 This table will contain no data. Do not define any check
2291 constraints on this table, unless you intend them to
2292 be applied equally to all partitions. There is no point
2293 in defining any indexes or unique constraints on it, either.
2299 Create several <quote>child</quote> tables that each inherit from
2300 the master table. Normally, these tables will not add any columns
2301 to the set inherited from the master.
2305 We will refer to the child tables as partitions, though they
2306 are in every way normal <productname>PostgreSQL</> tables.
2312 Add table constraints to the partition tables to define the
2313 allowed key values in each partition.
2317 Typical examples would be:
2320 CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' ))
2321 CHECK ( outletID >= 100 AND outletID < 200 )
2323 Ensure that the constraints guarantee that there is no overlap
2324 between the key values permitted in different partitions. A common
2325 mistake is to set up range constraints like this:
2327 CHECK ( outletID BETWEEN 100 AND 200 )
2328 CHECK ( outletID BETWEEN 200 AND 300 )
2330 This is wrong since it is not clear which partition the key value
2335 Note that there is no difference in
2336 syntax between range and list partitioning; those terms are
2343 For each partition, create an index on the key column(s),
2344 as well as any other indexes you might want. (The key index is
2345 not strictly necessary, but in most scenarios it is helpful.
2346 If you intend the key values to be unique then you should
2347 always create a unique or primary-key constraint for each
2354 Optionally, define a rule or trigger to redirect modifications
2355 of the master table to the appropriate partition.
2361 Ensure that the <xref linkend="guc-constraint-exclusion">
2363 parameter is enabled in <filename>postgresql.conf</>. Without
2364 this, queries will not be optimized as desired.
2372 For example, suppose we are constructing a database for a large
2373 ice cream company. The company measures peak temperatures every
2374 day as well as ice cream sales in each region. Conceptually,
2375 we want a table like this:
2378 CREATE TABLE measurement (
2379 city_id int not null,
2380 logdate date not null,
2386 We know that most queries will access just the last week's, month's or
2387 quarter's data, since the main use of this table will be to prepare
2388 online reports for management.
2389 To reduce the amount of old data that needs to be stored, we
2390 decide to only keep the most recent 3 years worth of data. At the
2391 beginning of each month we will remove the oldest month's data.
2395 In this situation we can use partitioning to help us meet all of our
2396 different requirements for the measurements table. Following the
2397 steps outlined above, partitioning can be set up as follows:
2401 <orderedlist spacing=compact>
2404 The master table is the <structname>measurement</> table, declared
2411 Next we create one partition for each active month:
2414 CREATE TABLE measurement_yy04mm02 ( ) INHERITS (measurement);
2415 CREATE TABLE measurement_yy04mm03 ( ) INHERITS (measurement);
2417 CREATE TABLE measurement_yy05mm11 ( ) INHERITS (measurement);
2418 CREATE TABLE measurement_yy05mm12 ( ) INHERITS (measurement);
2419 CREATE TABLE measurement_yy06mm01 ( ) INHERITS (measurement);
2422 Each of the partitions are complete tables in their own right,
2423 but they inherit their definition from the
2424 <structname>measurement</> table.
2428 This solves one of our problems: deleting old data. Each
2429 month, all we will need to do is perform a <command>DROP
2430 TABLE</command> on the oldest child table and create a new
2431 child table for the new month's data.
2437 We must add non-overlapping table constraints, so that our
2438 table creation script becomes:
2441 CREATE TABLE measurement_yy04mm02 (
2442 CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
2443 ) INHERITS (measurement);
2444 CREATE TABLE measurement_yy04mm03 (
2445 CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
2446 ) INHERITS (measurement);
2448 CREATE TABLE measurement_yy05mm11 (
2449 CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
2450 ) INHERITS (measurement);
2451 CREATE TABLE measurement_yy05mm12 (
2452 CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
2453 ) INHERITS (measurement);
2454 CREATE TABLE measurement_yy06mm01 (
2455 CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
2456 ) INHERITS (measurement);
2463 We probably need indexes on the key columns too:
2466 CREATE INDEX measurement_yy04mm02_logdate ON measurement_yy04mm02 (logdate);
2467 CREATE INDEX measurement_yy04mm03_logdate ON measurement_yy04mm03 (logdate);
2469 CREATE INDEX measurement_yy05mm11_logdate ON measurement_yy05mm11 (logdate);
2470 CREATE INDEX measurement_yy05mm12_logdate ON measurement_yy05mm12 (logdate);
2471 CREATE INDEX measurement_yy06mm01_logdate ON measurement_yy06mm01 (logdate);
2474 We choose not to add further indexes at this time.
2480 If data will be added only to the latest partition, we can
2481 set up a very simple rule to insert data. We must
2482 redefine this each month so that it always points to the
2486 CREATE OR REPLACE RULE measurement_current_partition AS
2487 ON INSERT TO measurement
2489 INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
2495 We might want to insert data and have the server automatically
2496 locate the partition into which the row should be added. We
2497 could do this with a more complex set of rules as shown below.
2500 CREATE RULE measurement_insert_yy04mm02 AS
2501 ON INSERT TO measurement WHERE
2502 ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
2504 INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id,
2509 CREATE RULE measurement_insert_yy05mm12 AS
2510 ON INSERT TO measurement WHERE
2511 ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
2513 INSERT INTO measurement_yy05mm12 VALUES ( NEW.city_id,
2517 CREATE RULE measurement_insert_yy06mm01 AS
2518 ON INSERT TO measurement WHERE
2519 ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
2521 INSERT INTO measurement_yy06mm01 VALUES ( NEW.city_id,
2527 Note that the <literal>WHERE</literal> clause in each rule
2528 exactly matches the the <literal>CHECK</literal>
2529 constraint for its partition.
2536 As we can see, a complex partitioning scheme could require a
2537 substantial amount of DDL. In the above example we would be
2538 creating a new partition each month, so it may be wise to write a
2539 script that generates the required DDL automatically.
2543 The following caveats apply:
2547 There is currently no way to verify that all of the
2548 <literal>CHECK</literal> constraints are mutually
2549 exclusive. Care is required by the database designer.
2555 There is currently no simple way to specify that rows must not be
2556 inserted into the master table. A <literal>CHECK (false)</literal>
2557 constraint on the master table would be inherited by all child
2558 tables, so that cannot be used for this purpose. One possibility is
2559 to set up an <literal>ON INSERT</> trigger on the master table that
2560 always raises an error. (Alternatively, such a trigger could be
2561 used to redirect the data into the proper child table, instead of
2562 using a set of rules as suggested above.)
2569 Partitioning can also be arranged using a <literal>UNION ALL</literal>
2573 CREATE VIEW measurement AS
2574 SELECT * FROM measurement_yy04mm02
2575 UNION ALL SELECT * FROM measurement_yy04mm03
2577 UNION ALL SELECT * FROM measurement_yy05mm11
2578 UNION ALL SELECT * FROM measurement_yy05mm12
2579 UNION ALL SELECT * FROM measurement_yy06mm01;
2582 However, the need to
2583 recreate the view adds an extra step to adding and dropping
2584 individual partitions of the dataset.
2588 <sect2 id="ddl-partitioning-constraint-exclusion">
2589 <title>Partitioning and Constraint Exclusion</title>
2592 <primary>constraint exclusion</primary>
2596 <firstterm>Constraint exclusion</> is a query optimization technique
2597 that improves performance for partitioned tables defined in the
2598 fashion described above. As an example:
2601 SET constraint_exclusion = on;
2602 SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
2605 Without constraint exclusion, the above query would scan each of
2606 the partitions of the <structname>measurement</> table. With constraint
2607 exclusion enabled, the planner will examine the constraints of each
2608 partition and try to prove that the partition need not
2609 be scanned because it could not contain any rows meeting the query's
2610 <literal>WHERE</> clause. When the planner can prove this, it
2611 excludes the partition from the query plan.
2615 You can use the <command>EXPLAIN</> command to show the difference
2616 between a plan with <varname>constraint_exclusion</> on and a plan
2617 with it off. A typical default plan for this type of table setup is:
2620 SET constraint_exclusion = off;
2621 EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
2624 -----------------------------------------------------------------------------------------------
2625 Aggregate (cost=158.66..158.68 rows=1 width=0)
2626 -> Append (cost=0.00..151.88 rows=2715 width=0)
2627 -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
2628 Filter: (logdate >= '2006-01-01'::date)
2629 -> Seq Scan on measurement_yy04mm02 measurement (cost=0.00..30.38 rows=543 width=0)
2630 Filter: (logdate >= '2006-01-01'::date)
2631 -> Seq Scan on measurement_yy04mm03 measurement (cost=0.00..30.38 rows=543 width=0)
2632 Filter: (logdate >= '2006-01-01'::date)
2634 -> Seq Scan on measurement_yy05mm12 measurement (cost=0.00..30.38 rows=543 width=0)
2635 Filter: (logdate >= '2006-01-01'::date)
2636 -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0)
2637 Filter: (logdate >= '2006-01-01'::date)
2640 Some or all of the partitions might use index scans instead of
2641 full-table sequential scans, but the point here is that there
2642 is no need to scan the older partitions at all to answer this query.
2643 When we enable constraint exclusion, we get a significantly
2644 reduced plan that will deliver the same answer:
2647 SET constraint_exclusion = on;
2648 EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';
2650 -----------------------------------------------------------------------------------------------
2651 Aggregate (cost=63.47..63.48 rows=1 width=0)
2652 -> Append (cost=0.00..60.75 rows=1086 width=0)
2653 -> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)
2654 Filter: (logdate >= '2006-01-01'::date)
2655 -> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0)
2656 Filter: (logdate >= '2006-01-01'::date)
2661 Note that constraint exclusion is driven only by <literal>CHECK</>
2662 constraints, not by the presence of indexes. Therefore it isn't
2663 necessary to define indexes on the key columns. Whether an index
2664 needs to be created for a given partition depends on whether you
2665 expect that queries that scan the partition will generally scan
2666 a large part of the partition or just a small part. An index will
2667 be helpful in the latter case but not the former.
2671 The following caveats apply:
2676 Constraint exclusion only works when the query's <literal>WHERE</>
2677 clause contains constants. A parameterized query will not be
2678 optimized, since the planner cannot know what partitions the
2679 parameter value might select at runtime. For the same reason,
2680 <quote>stable</> functions such as <function>CURRENT_DATE</function>
2687 Avoid cross-datatype comparisons in the <literal>CHECK</>
2688 constraints, as the planner will currently fail to prove such
2689 conditions false. For example, the following constraint
2690 will work if <varname>x</varname> is an <type>integer</type>
2691 column, but not if <varname>x</varname> is a
2692 <type>bigint</type>:
2696 For a <type>bigint</type> column we must use a constraint like:
2698 CHECK ( x = 1::bigint )
2700 The problem is not limited to the <type>bigint</type> data type
2701 — it can occur whenever the default data type of the
2702 constant does not match the data type of the column to which it
2703 is being compared. Cross-datatype comparisons in the supplied
2704 queries are usually OK, just not in the <literal>CHECK</> conditions.
2710 All constraints on all partitions of the master table are considered for
2711 constraint exclusion, so large numbers of partitions are likely to
2712 increase query planning time considerably.
2718 Don't forget that you still need to run <command>ANALYZE</command>
2719 on each partition individually. A command like
2721 ANALYZE measurement;
2723 will only process the master table.
2732 <sect1 id="ddl-others">
2733 <title>Other Database Objects</title>
2736 Tables are the central objects in a relational database structure,
2737 because they hold your data. But they are not the only objects
2738 that exist in a database. Many other kinds of objects can be
2739 created to make the use and management of the data more efficient
2740 or convenient. They are not discussed in this chapter, but we give
2741 you a list here so that you are aware of what is possible.
2753 Functions and operators
2759 Data types and domains
2765 Triggers and rewrite rules
2771 Detailed information on
2772 these topics appears in <xref linkend="server-programming">.
2776 <sect1 id="ddl-depend">
2777 <title>Dependency Tracking</title>
2779 <indexterm zone="ddl-depend">
2780 <primary>CASCADE</primary>
2781 <secondary sortas="DROP">with DROP</secondary>
2784 <indexterm zone="ddl-depend">
2785 <primary>RESTRICT</primary>
2786 <secondary sortas="DROP">with DROP</secondary>
2790 When you create complex database structures involving many tables
2791 with foreign key constraints, views, triggers, functions, etc. you
2792 will implicitly create a net of dependencies between the objects.
2793 For instance, a table with a foreign key constraint depends on the
2794 table it references.
2798 To ensure the integrity of the entire database structure,
2799 <productname>PostgreSQL</productname> makes sure that you cannot
2800 drop objects that other objects still depend on. For example,
2801 attempting to drop the products table we had considered in <xref
2802 linkend="ddl-constraints-fk">, with the orders table depending on
2803 it, would result in an error message such as this:
2805 DROP TABLE products;
2807 NOTICE: constraint orders_product_no_fkey on table orders depends on table products
2808 ERROR: cannot drop table products because other objects depend on it
2809 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2811 The error message contains a useful hint: if you do not want to
2812 bother deleting all the dependent objects individually, you can run
2814 DROP TABLE products CASCADE;
2816 and all the dependent objects will be removed. In this case, it
2817 doesn't remove the orders table, it only removes the foreign key
2818 constraint. (If you want to check what <command>DROP ... CASCADE</> will do,
2819 run <command>DROP</> without <literal>CASCADE</> and read the <literal>NOTICE</> messages.)
2823 All drop commands in <productname>PostgreSQL</productname> support
2824 specifying <literal>CASCADE</literal>. Of course, the nature of
2825 the possible dependencies varies with the type of the object. You
2826 can also write <literal>RESTRICT</literal> instead of
2827 <literal>CASCADE</literal> to get the default behavior, which is to
2828 prevent drops of objects that other objects depend on.
2833 According to the SQL standard, specifying either
2834 <literal>RESTRICT</literal> or <literal>CASCADE</literal> is
2835 required. No database system actually enforces that rule, but
2836 whether the default behavior is <literal>RESTRICT</literal> or
2837 <literal>CASCADE</literal> varies across systems.
2843 Foreign key constraint dependencies and serial column dependencies
2844 from <productname>PostgreSQL</productname> versions prior to 7.3
2845 are <emphasis>not</emphasis> maintained or created during the
2846 upgrade process. All other dependency types will be properly
2847 created during an upgrade from a pre-7.3 database.