1 <!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.27 2004/08/03 20:32:30 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 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-system-columns">
167 <title>System Columns</title>
170 Every table has several <firstterm>system columns</> that are
171 implicitly defined by the system. Therefore, these names cannot be
172 used as names of user-defined columns. (Note that these
173 restrictions are separate from whether the name is a key word or
174 not; quoting a name will not allow you to escape these
175 restrictions.) You do not really need to be concerned about these
176 columns, just know they exist.
180 <primary>column</primary>
181 <secondary>system column</secondary>
186 <term><structfield>oid</></term>
190 <primary>OID</primary>
191 <secondary>column</secondary>
193 The object identifier (object ID) of a row. This is a serial
194 number that is automatically added by
195 <productname>PostgreSQL</productname> to all table rows (unless
196 the table was created using <literal>WITHOUT OIDS</literal>, in which
197 case this column is not present). This column is of type
198 <type>oid</type> (same name as the column); see <xref
199 linkend="datatype-oid"> for more information about the type.
205 <term><structfield>tableoid</></term>
208 <primary>tableoid</primary>
212 The OID of the table containing this row. This column is
213 particularly handy for queries that select from inheritance
214 hierarchies, since without it, it's difficult to tell which
215 individual table a row came from. The
216 <structfield>tableoid</structfield> can be joined against the
217 <structfield>oid</structfield> column of
218 <structname>pg_class</structname> to obtain the table name.
224 <term><structfield>xmin</></term>
227 <primary>xmin</primary>
231 The identity (transaction ID) of the inserting transaction for
232 this row version. (A row version is an individual state of a
233 row; each update of a row creates a new row version for the same
240 <term><structfield>cmin</></term>
243 <primary>cmin</primary>
247 The command identifier (starting at zero) within the inserting
254 <term><structfield>xmax</></term>
257 <primary>xmax</primary>
261 The identity (transaction ID) of the deleting transaction, or
262 zero for an undeleted row version. It is possible for this column to
263 be nonzero in a visible row version: That usually indicates that the
264 deleting transaction hasn't committed yet, or that an attempted
265 deletion was rolled back.
271 <term><structfield>cmax</></term>
274 <primary>cmax</primary>
278 The command identifier within the deleting transaction, or zero.
284 <term><structfield>ctid</></term>
287 <primary>ctid</primary>
291 The physical location of the row version within its table. Note that
292 although the <structfield>ctid</structfield> can be used to
293 locate the row version very quickly, a row's
294 <structfield>ctid</structfield> will change each time it is
295 updated or moved by <command>VACUUM FULL</>. Therefore
296 <structfield>ctid</structfield> is useless as a long-term row
297 identifier. The OID, or even better a user-defined serial
298 number, should be used to identify logical rows.
305 OIDs are 32-bit quantities and are assigned from a single
306 cluster-wide counter. In a large or long-lived database, it is
307 possible for the counter to wrap around. Hence, it is bad
308 practice to assume that OIDs are unique, unless you take steps to
309 ensure that this is the case. If you need to identify the rows in
310 a table, using a sequence generator is strongly recommended.
311 However, OIDs can be used as well, provided that a few additional
312 precautions are taken:
317 A unique constraint should be created on the OID column of each
318 table for which the OID will be used to identify rows.
323 OIDs should never be assumed to be unique across tables; use
324 the combination of <structfield>tableoid</> and row OID if you
325 need a database-wide identifier.
330 The tables in question should be created using <literal>WITH
331 OIDS</literal> to ensure forward compatibility with future
332 releases of <productname>PostgreSQL</productname> in which OIDs
333 are not included in all tables by default.
340 Transaction identifiers are also 32-bit quantities. In a
341 long-lived database it is possible for transaction IDs to wrap
342 around. This is not a fatal problem given appropriate maintenance
343 procedures; see <xref linkend="maintenance"> for details. It is
344 unwise, however, to depend on the uniqueness of transaction IDs
345 over the long term (more than one billion transactions).
350 identifiers are also 32-bit quantities. This creates a hard limit
351 of 2<superscript>32</> (4 billion) <acronym>SQL</acronym> commands
352 within a single transaction. In practice this limit is not a
353 problem --- note that the limit is on number of
354 <acronym>SQL</acronym> commands, not number of rows processed.
358 <sect1 id="ddl-default">
359 <title>Default Values</title>
361 <indexterm zone="ddl-default">
362 <primary>default value</primary>
366 A column can be assigned a default value. When a new row is
367 created and no values are specified for some of the columns, the
368 columns will be filled with their respective default values. A
369 data manipulation command can also request explicitly that a column
370 be set to its default value, without knowing what this value is.
371 (Details about data manipulation commands are in <xref linkend="dml">.)
375 <indexterm><primary>null value</primary><secondary>default value</secondary></indexterm>
376 If no default value is declared explicitly, the null value is the
377 default value. This usually makes sense because a null value can
378 be thought to represent unknown data.
382 In a table definition, default values are listed after the column
383 data type. For example:
385 CREATE TABLE products (
388 price numeric <emphasis>DEFAULT 9.99</emphasis>
394 The default value may be a scalar expression, which will be
395 evaluated whenever the default value is inserted
396 (<emphasis>not</emphasis> when the table is created).
400 <sect1 id="ddl-constraints">
401 <title>Constraints</title>
403 <indexterm zone="ddl-constraints">
404 <primary>constraint</primary>
408 Data types are a way to limit the kind of data that can be stored
409 in a table. For many applications, however, the constraint they
410 provide is too coarse. For example, a column containing a product
411 price should probably only accept positive values. But there is no
412 data type that accepts only positive numbers. Another issue is
413 that you might want to constrain column data with respect to other
414 columns or rows. For example, in a table containing product
415 information, there should only be one row for each product number.
419 To that end, SQL allows you to define constraints on columns and
420 tables. Constraints give you as much control over the data in your
421 tables as you wish. If a user attempts to store data in a column
422 that would violate a constraint, an error is raised. This applies
423 even if the value came from the default value definition.
427 <title>Check Constraints</title>
430 <primary>check constraint</primary>
434 <primary>constraint</primary>
435 <secondary>check</secondary>
439 A check constraint is the most generic constraint type. It allows
440 you to specify that the value in a certain column must satisfy an
441 arbitrary expression. For instance, to require positive product
442 prices, you could use:
444 CREATE TABLE products (
447 price numeric <emphasis>CHECK (price > 0)</emphasis>
453 As you see, the constraint definition comes after the data type,
454 just like default value definitions. Default values and
455 constraints can be listed in any order. A check constraint
456 consists of the key word <literal>CHECK</literal> followed by an
457 expression in parentheses. The check constraint expression should
458 involve the column thus constrained, otherwise the constraint
459 would not make too much sense.
463 <primary>constraint</primary>
464 <secondary>name</secondary>
468 You can also give the constraint a separate name. This clarifies
469 error messages and allows you to refer to the constraint when you
470 need to change it. The syntax is:
472 CREATE TABLE products (
475 price numeric <emphasis>CONSTRAINT positive_price</emphasis> CHECK (price > 0)
478 So, to specify a named constraint, use the key word
479 <literal>CONSTRAINT</literal> followed by an identifier followed
480 by the constraint definition.
484 A check constraint can also refer to several columns. Say you
485 store a regular price and a discounted price, and you want to
486 ensure that the discounted price is lower than the regular price.
488 CREATE TABLE products (
491 price numeric CHECK (price > 0),
492 discounted_price numeric CHECK (discounted_price > 0),
493 CHECK (price > discounted_price)
499 The first two constraints should look familiar. The third one
500 uses a new syntax. It is not attached to a particular column,
501 instead it appears as a separate item in the comma-separated
502 column list. Column definitions and these constraint
503 definitions can be listed in mixed order.
507 We say that the first two constraints are column constraints, whereas the
508 third one is a table constraint because it is written separately
509 from the column definitions. Column constraints can also be
510 written as table constraints, while the reverse is not necessarily
511 possible. The above example could also be written as
513 CREATE TABLE products (
518 discounted_price numeric,
519 CHECK (discounted_price > 0),
520 CHECK (price > discounted_price)
525 CREATE TABLE products (
528 price numeric CHECK (price > 0),
529 discounted_price numeric,
530 CHECK (discounted_price > 0 AND price > discounted_price)
533 It's a matter of taste.
537 <primary>null value</primary>
538 <secondary sortas="check constraints">with check constraints</secondary>
542 It should be noted that a check constraint is satisfied if the
543 check expression evaluates to true or the null value. Since most
544 expressions will evaluate to the null value if one operand is null,
545 they will not prevent null values in the constrained columns. To
546 ensure that a column does not contain null values, the not-null
547 constraint described in the next section should be used.
552 <title>Not-Null Constraints</title>
555 <primary>not-null constraint</primary>
559 <primary>constraint</primary>
560 <secondary>NOT NULL</secondary>
564 A not-null constraint simply specifies that a column must not
565 assume the null value. A syntax example:
567 CREATE TABLE products (
568 product_no integer <emphasis>NOT NULL</emphasis>,
569 name text <emphasis>NOT NULL</emphasis>,
576 A not-null constraint is always written as a column constraint. A
577 not-null constraint is functionally equivalent to creating a check
578 constraint <literal>CHECK (<replaceable>column_name</replaceable>
579 IS NOT NULL)</literal>, but in
580 <productname>PostgreSQL</productname> creating an explicit
581 not-null constraint is more efficient. The drawback is that you
582 cannot give explicit names to not-null constraints created that
587 Of course, a column can have more than one constraint. Just write
588 the constraints after one another:
590 CREATE TABLE products (
591 product_no integer NOT NULL,
593 price numeric NOT NULL CHECK (price > 0)
596 The order doesn't matter. It does not necessarily determine in which
597 order the constraints are checked.
601 The <literal>NOT NULL</literal> constraint has an inverse: the
602 <literal>NULL</literal> constraint. This does not mean that the
603 column must be null, which would surely be useless. Instead, this
604 simply defines the default behavior that the column may be null.
605 The <literal>NULL</literal> constraint is not defined in the SQL
606 standard and should not be used in portable applications. (It was
607 only added to <productname>PostgreSQL</productname> to be
608 compatible with some other database systems.) Some users, however,
609 like it because it makes it easy to toggle the constraint in a
610 script file. For example, you could start with
612 CREATE TABLE products (
613 product_no integer NULL,
618 and then insert the <literal>NOT</literal> key word where desired.
623 In most database designs the majority of columns should be marked
630 <title>Unique Constraints</title>
633 <primary>unique constraint</primary>
637 <primary>constraint</primary>
638 <secondary>unique</secondary>
642 Unique constraints ensure that the data contained in a column or a
643 group of columns is unique with respect to all the rows in the
646 CREATE TABLE products (
647 product_no integer <emphasis>UNIQUE</emphasis>,
652 when written as a column constraint, and
654 CREATE TABLE products (
658 <emphasis>UNIQUE (product_no)</emphasis>
661 when written as a table constraint.
665 If a unique constraint refers to a group of columns, the columns
666 are listed separated by commas:
668 CREATE TABLE example (
672 <emphasis>UNIQUE (a, c)</emphasis>
678 It is also possible to assign names to unique constraints:
680 CREATE TABLE products (
681 product_no integer <emphasis>CONSTRAINT must_be_different</emphasis> UNIQUE,
689 <primary>null value</primary>
690 <secondary sortas="unique constraints">with unique constraints</secondary>
694 In general, a unique constraint is violated when there are (at
695 least) two rows in the table where the values of each of the
696 corresponding columns that are part of the constraint are equal.
697 However, null values are not considered equal in this
698 consideration. That means even in the presence of a
699 unique constraint it is possible to store an unlimited number of
700 rows that contain a null value in at least one of the constrained
701 columns. This behavior conforms to the SQL standard, but we have
702 heard that other SQL databases may not follow this rule. So be
703 careful when developing applications that are intended to be
709 <title>Primary Keys</title>
712 <primary>primary key</primary>
716 <primary>constraint</primary>
717 <secondary>primary key</secondary>
721 Technically, a primary key constraint is simply a combination of a
722 unique constraint and a not-null constraint. So, the following
723 two table definitions accept the same data:
725 CREATE TABLE products (
726 product_no integer UNIQUE NOT NULL,
733 CREATE TABLE products (
734 product_no integer <emphasis>PRIMARY KEY</emphasis>,
742 Primary keys can also constrain more than one column; the syntax
743 is similar to unique constraints:
745 CREATE TABLE example (
749 <emphasis>PRIMARY KEY (a, c)</emphasis>
755 A primary key indicates that a column or group of columns can be
756 used as a unique identifier for rows in the table. (This is a
757 direct consequence of the definition of a primary key. Note that
758 a unique constraint does not, by itself, provide a unique identifier
759 because it does not exclude null values.) This is useful both for
760 documentation purposes and for client applications. For example,
761 a GUI application that allows modifying row values probably needs
762 to know the primary key of a table to be able to identify rows
767 A table can have at most one primary key (while it can have many
768 unique and not-null constraints). Relational database theory
769 dictates that every table must have a primary key. This rule is
770 not enforced by <productname>PostgreSQL</productname>, but it is
771 usually best to follow it.
775 <sect2 id="ddl-constraints-fk">
776 <title>Foreign Keys</title>
779 <primary>foreign key</primary>
783 <primary>constraint</primary>
784 <secondary>foreign key</secondary>
788 <primary>referential integrity</primary>
792 A foreign key constraint specifies that the values in a column (or
793 a group of columns) must match the values appearing in some row
795 We say this maintains the <firstterm>referential
796 integrity</firstterm> between two related tables.
800 Say you have the product table that we have used several times already:
802 CREATE TABLE products (
803 product_no integer PRIMARY KEY,
808 Let's also assume you have a table storing orders of those
809 products. We want to ensure that the orders table only contains
810 orders of products that actually exist. So we define a foreign
811 key constraint in the orders table that references the products
814 CREATE TABLE orders (
815 order_id integer PRIMARY KEY,
816 product_no integer <emphasis>REFERENCES products (product_no)</emphasis>,
820 Now it is impossible to create orders with
821 <structfield>product_no</structfield> entries that do not appear in the
826 We say that in this situation the orders table is the
827 <firstterm>referencing</firstterm> table and the products table is
828 the <firstterm>referenced</firstterm> table. Similarly, there are
829 referencing and referenced columns.
833 You can also shorten the above command to
835 CREATE TABLE orders (
836 order_id integer PRIMARY KEY,
837 product_no integer REFERENCES products,
841 because in absence of a column list the primary key of the
842 referenced table is used as the referenced column.
846 A foreign key can also constrain and reference a group of columns.
847 As usual, it then needs to be written in table constraint form.
848 Here is a contrived syntax example:
851 a integer PRIMARY KEY,
854 <emphasis>FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)</emphasis>
857 Of course, the number and type of the constrained columns needs to
858 match the number and type of the referenced columns.
862 A table can contain more than one foreign key constraint. This is
863 used to implement many-to-many relationships between tables. Say
864 you have tables about products and orders, but now you want to
865 allow one order to contain possibly many products (which the
866 structure above did not allow). You could use this table structure:
868 CREATE TABLE products (
869 product_no integer PRIMARY KEY,
874 CREATE TABLE orders (
875 order_id integer PRIMARY KEY,
876 shipping_address text,
880 CREATE TABLE order_items (
881 product_no integer REFERENCES products,
882 order_id integer REFERENCES orders,
884 PRIMARY KEY (product_no, order_id)
887 Note also that the primary key overlaps with the foreign keys in
892 <primary>CASCADE</primary>
893 <secondary>foreign key action</secondary>
897 <primary>RESTRICT</primary>
898 <secondary>foreign key action</secondary>
902 We know that the foreign keys disallow creation of orders that
903 do not relate to any products. But what if a product is removed
904 after an order is created that references it? SQL allows you to
905 specify that as well. Intuitively, we have a few options:
906 <itemizedlist spacing="compact">
907 <listitem><para>Disallow deleting a referenced product</para></listitem>
908 <listitem><para>Delete the orders as well</para></listitem>
909 <listitem><para>Something else?</para></listitem>
914 To illustrate this, let's implement the following policy on the
915 many-to-many relationship example above: when someone wants to
916 remove a product that is still referenced by an order (via
917 <literal>order_items</literal>), we disallow it. If someone
918 removes an order, the order items are removed as well.
920 CREATE TABLE products (
921 product_no integer PRIMARY KEY,
926 CREATE TABLE orders (
927 order_id integer PRIMARY KEY,
928 shipping_address text,
932 CREATE TABLE order_items (
933 product_no integer REFERENCES products <emphasis>ON DELETE RESTRICT</emphasis>,
934 order_id integer REFERENCES orders <emphasis>ON DELETE CASCADE</emphasis>,
936 PRIMARY KEY (product_no, order_id)
942 Restricting and cascading deletes are the two most common options.
943 <literal>RESTRICT</literal> can also be written as <literal>NO
944 ACTION</literal> and it's also the default if you do not specify
945 anything. There are two other options for what should happen with
946 the foreign key columns when a primary key is deleted:
947 <literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.
948 Note that these do not excuse you from observing any constraints.
949 For example, if an action specifies <literal>SET DEFAULT</literal>
950 but the default value would not satisfy the foreign key, the
951 deletion of the primary key will fail.
955 Analogous to <literal>ON DELETE</literal> there is also
956 <literal>ON UPDATE</literal> which is invoked when a primary key
957 is changed (updated). The possible actions are the same.
961 More information about updating and deleting data is in <xref
966 Finally, we should mention that a foreign key must reference
967 columns that are either a primary key or form a unique constraint.
968 If the foreign key references a unique constraint, there are some
969 additional possibilities regarding how null values are matched.
970 These are explained in the reference documentation for
971 <xref linkend="sql-createtable" endterm="sql-createtable-title">.
976 <sect1 id="ddl-inherit">
977 <title>Inheritance</title>
979 <remark>This section needs to be rethought. Some of the
980 information should go into the following chapters.</remark>
983 Let's create two tables. The capitals table contains
984 state capitals which are also cities. Naturally, the
985 capitals table should inherit from cities.
988 CREATE TABLE cities (
991 altitude int -- (in ft)
994 CREATE TABLE capitals (
999 In this case, a row of capitals <firstterm>inherits</firstterm> all
1000 attributes (name, population, and altitude) from its
1001 parent, cities. The type of the attribute name is
1002 <type>text</type>, a native <productname>PostgreSQL</productname> type
1003 for variable length character strings. The type of the attribute
1005 <type>float</type>, a native <productname>PostgreSQL</productname> type for double precision
1006 floating-point numbers. State capitals have an extra
1007 attribute, state, that shows their state. In <productname>PostgreSQL</productname>,
1008 a table can inherit from zero or more other tables,
1009 and a query can reference either all rows of a
1010 table or all rows of a table plus all of its
1015 The inheritance hierarchy is actually a directed acyclic graph.
1021 For example, the following query finds the names of all cities,
1022 including state capitals, that are located at an altitude
1026 SELECT name, altitude
1028 WHERE altitude > 500;
1035 -----------+----------
1043 On the other hand, the following query finds
1044 all the cities that are not state capitals and
1045 are situated at an altitude over 500ft:
1048 SELECT name, altitude
1050 WHERE altitude > 500;
1053 -----------+----------
1060 Here the <quote>ONLY</quote> before cities indicates that the query should
1061 be run over only cities and not tables below cities in the
1062 inheritance hierarchy. Many of the commands that we
1063 have already discussed -- <command>SELECT</command>,
1064 <command>UPDATE</command> and <command>DELETE</command> --
1065 support this <quote>ONLY</quote> notation.
1069 In some cases you may wish to know which table a particular row
1070 originated from. There is a system column called
1071 <structfield>TABLEOID</structfield> in each table which can tell you the
1075 SELECT c.tableoid, c.name, c.altitude
1077 WHERE c.altitude > 500;
1083 tableoid | name | altitude
1084 ----------+-----------+----------
1085 139793 | Las Vegas | 2174
1086 139793 | Mariposa | 1953
1087 139798 | Madison | 845
1090 (If you try to reproduce this example, you will probably get
1091 different numeric OIDs.) By doing a join with
1092 <structname>pg_class</> you can see the actual table names:
1095 SELECT p.relname, c.name, c.altitude
1096 FROM cities c, pg_class p
1097 WHERE c.altitude > 500 and c.tableoid = p.oid;
1103 relname | name | altitude
1104 ----------+-----------+----------
1105 cities | Las Vegas | 2174
1106 cities | Mariposa | 1953
1107 capitals | Madison | 845
1113 <title>Deprecated</title>
1115 In previous versions of <productname>PostgreSQL</productname>, the
1116 default behavior was not to include child tables in queries. This was
1117 found to be error prone and is also in violation of the SQL99
1118 standard. Under the old syntax, to get the sub-tables you append
1119 <literal>*</literal> to the table name.
1122 SELECT * from cities*;
1124 You can still explicitly specify scanning child tables by appending
1125 <literal>*</literal>, as well as explicitly specify not scanning child tables by
1126 writing <quote>ONLY</quote>. But beginning in version 7.1, the default
1127 behavior for an undecorated table name is to scan its child tables
1128 too, whereas before the default was not to do so. To get the old
1129 default behavior, set the configuration option
1130 <literal>SQL_Inheritance</literal> to off, e.g.,
1132 SET SQL_Inheritance TO OFF;
1134 or add a line in your <filename>postgresql.conf</filename> file.
1139 A limitation of the inheritance feature is that indexes (including
1140 unique constraints) and foreign key constraints only apply to single
1141 tables, not to their inheritance children. Thus, in the above example,
1142 specifying that another table's column <literal>REFERENCES cities(name)</>
1143 would allow the other table to contain city names but not capital names.
1144 This deficiency will probably be fixed in some future release.
1148 <sect1 id="ddl-alter">
1149 <title>Modifying Tables</title>
1151 <indexterm zone="ddl-alter">
1152 <primary>table</primary>
1153 <secondary>modifying</secondary>
1157 When you create a table and you realize that you made a mistake, or
1158 the requirements of the application changed, then you can drop the
1159 table and create it again. But this is not a convenient option if
1160 the table is already filled with data, or if the table is
1161 referenced by other database objects (for instance a foreign key
1162 constraint). Therefore <productname>PostgreSQL</productname>
1163 provides a family of commands to make modifications on existing
1169 <itemizedlist spacing="compact">
1171 <para>Add columns,</para>
1174 <para>Remove columns,</para>
1177 <para>Add constraints,</para>
1180 <para>Remove constraints,</para>
1183 <para>Change default values,</para>
1186 <para>Rename columns,</para>
1189 <para>Rename tables.</para>
1193 All these actions are performed using the <literal>ALTER
1194 TABLE</literal> command.
1198 <title>Adding a Column</title>
1201 <primary>column</primary>
1202 <secondary>adding</secondary>
1206 To add a column, use this command:
1208 ALTER TABLE products ADD COLUMN description text;
1210 The new column will initially be filled with null values in the
1211 existing rows of the table.
1215 You can also define a constraint on the column at the same time,
1216 using the usual syntax:
1218 ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
1220 A new column cannot have a not-null constraint since the column
1221 initially has to contain null values. But you can add a not-null
1222 constraint later. Also, you cannot define a default value on a
1223 new column. According to the SQL standard, this would have to
1224 fill the new columns in the existing rows with the default value,
1225 which is not implemented yet. But you can adjust the column
1231 <title>Removing a Column</title>
1234 <primary>column</primary>
1235 <secondary>removing</secondary>
1239 To remove a column, use this command:
1241 ALTER TABLE products DROP COLUMN description;
1247 <title>Adding a Constraint</title>
1250 <primary>constraint</primary>
1251 <secondary>adding</secondary>
1255 To add a constraint, the table constraint syntax is used. For example:
1257 ALTER TABLE products ADD CHECK (name <> '');
1258 ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
1259 ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
1261 To add a not-null constraint, which cannot be written as a table
1262 constraint, use this syntax:
1264 ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
1269 The constraint will be checked immediately, so the table data must
1270 satisfy the constraint before it can be added.
1275 <title>Removing a Constraint</title>
1278 <primary>constraint</primary>
1279 <secondary>removing</secondary>
1283 To remove a constraint you need to know its name. If you gave it
1284 a name then that's easy. Otherwise the system assigned a
1285 generated name, which you need to find out. The
1286 <application>psql</application> command <literal>\d
1287 <replaceable>tablename</replaceable></literal> can be helpful
1288 here; other interfaces might also provide a way to inspect table
1289 details. Then the command is:
1291 ALTER TABLE products DROP CONSTRAINT some_name;
1293 (If you are dealing with a generated constraint name like <literal>$2</>,
1294 don't forget that you'll need to double-quote it to make it a valid
1299 This works the same for all constraint types except not-null
1300 constraints. To drop a not null constraint use
1302 ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
1304 (Recall that not-null constraints do not have names.)
1309 <title>Changing the Default</title>
1312 <primary>default value</primary>
1313 <secondary>changing</secondary>
1317 To set a new default for a column, use a command like this:
1319 ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
1321 To remove any default value, use
1323 ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
1325 This is equivalent to setting the default to null, at least in
1326 <productname>PostgreSQL</>. As a consequence, it is not an error
1327 to drop a default where one hadn't been defined, because the
1328 default is implicitly the null value.
1333 <title>Renaming a Column</title>
1336 <primary>column</primary>
1337 <secondary>renaming</secondary>
1343 ALTER TABLE products RENAME COLUMN product_no TO product_number;
1349 <title>Renaming a Table</title>
1352 <primary>table</primary>
1353 <secondary>renaming</secondary>
1359 ALTER TABLE products RENAME TO items;
1365 <sect1 id="ddl-priv">
1366 <title>Privileges</title>
1368 <indexterm zone="ddl-priv">
1369 <primary>privilege</primary>
1373 <primary>permission</primary>
1374 <see>privilege</see>
1378 When you create a database object, you become its owner. By
1379 default, only the owner of an object can do anything with the
1380 object. In order to allow other users to use it,
1381 <firstterm>privileges</firstterm> must be granted. (There are also
1382 users that have the superuser privilege. Those users can always
1388 To change the owner of a table, index, sequence, or view, use the
1389 <xref linkend="sql-altertable" endterm="sql-altertable-title">
1395 There are several different privileges: <literal>SELECT</>,
1396 <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
1397 <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
1398 <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
1399 <literal>USAGE</>, and <literal>ALL PRIVILEGES</>. For complete
1400 information on the different types of privileges supported by
1401 <productname>PostgreSQL</productname>, refer to the
1402 <xref linkend="sql-grant" endterm="sql-grant-title">
1403 reference page. The following sections
1404 and chapters will also show you how those privileges are used.
1408 The right to modify or destroy an object is always the privilege of
1413 To assign privileges, the <command>GRANT</command> command is
1414 used. So, if <literal>joe</literal> is an existing user, and
1415 <literal>accounts</literal> is an existing table, the privilege to
1416 update the table can be granted with
1418 GRANT UPDATE ON accounts TO joe;
1420 The user executing this command must be the owner of the table. To
1421 grant a privilege to a group, use
1423 GRANT SELECT ON accounts TO GROUP staff;
1425 The special <quote>user</quote> name <literal>PUBLIC</literal> can
1426 be used to grant a privilege to every user on the system. Writing
1427 <literal>ALL</literal> in place of a specific privilege specifies that all
1428 privileges will be granted.
1432 To revoke a privilege, use the fittingly named
1433 <command>REVOKE</command> command:
1435 REVOKE ALL ON accounts FROM PUBLIC;
1437 The special privileges of the table owner (i.e., the right to do
1438 <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc.)
1439 are always implicit in being the owner,
1440 and cannot be granted or revoked. But the table owner can choose
1441 to revoke his own ordinary privileges, for example to make a
1442 table read-only for himself as well as others.
1446 <sect1 id="ddl-schemas">
1447 <title>Schemas</title>
1449 <indexterm zone="ddl-schemas">
1450 <primary>schema</primary>
1454 A <productname>PostgreSQL</productname> database cluster
1455 contains one or more named databases. Users and groups of users are
1456 shared across the entire cluster, but no other data is shared across
1457 databases. Any given client connection to the server can access
1458 only the data in a single database, the one specified in the connection
1464 Users of a cluster do not necessarily have the privilege to access every
1465 database in the cluster. Sharing of user names means that there
1466 cannot be different users named, say, <literal>joe</> in two databases
1467 in the same cluster; but the system can be configured to allow
1468 <literal>joe</> access to only some of the databases.
1473 A database contains one or more named <firstterm>schemas</>, which
1474 in turn contain tables. Schemas also contain other kinds of named
1475 objects, including data types, functions, and operators. The same
1476 object name can be used in different schemas without conflict; for
1477 example, both <literal>schema1</> and <literal>myschema</> may
1478 contain tables named <literal>mytable</>. Unlike databases,
1479 schemas are not rigidly separated: a user may access objects in any
1480 of the schemas in the database he is connected to, if he has
1481 privileges to do so.
1485 There are several reasons why one might want to use schemas:
1490 To allow many users to use one database without interfering with
1497 To organize database objects into logical groups to make them
1504 Third-party applications can be put into separate schemas so
1505 they cannot collide with the names of other objects.
1510 Schemas are analogous to directories at the operating system level,
1511 except that schemas cannot be nested.
1514 <sect2 id="ddl-schemas-create">
1515 <title>Creating a Schema</title>
1517 <indexterm zone="ddl-schemas-create">
1518 <primary>schema</primary>
1519 <secondary>creating</secondary>
1523 To create a separate schema, use the command <literal>CREATE
1524 SCHEMA</literal>. Give the schema a name of your choice. For
1527 CREATE SCHEMA myschema;
1532 <primary>qualified name</primary>
1536 <primary>name</primary>
1537 <secondary>qualified</secondary>
1541 To create or access objects in a schema, write a
1542 <firstterm>qualified name</> consisting of the schema name and
1543 table name separated by a dot:
1545 <replaceable>schema</><literal>.</><replaceable>table</>
1547 Actually, the even more general syntax
1549 <replaceable>database</><literal>.</><replaceable>schema</><literal>.</><replaceable>table</>
1551 can be used too, but at present this is just for pro-forma compliance
1552 with the SQL standard; if you write a database name it must be the
1553 same as the database you are connected to.
1557 So to create a table in the new schema, use
1559 CREATE TABLE myschema.mytable (
1563 This works anywhere a table name is expected, including the table
1564 modification commands and the data access commands discussed in
1565 the following chapters.
1569 <primary>schema</primary>
1570 <secondary>removing</secondary>
1574 To drop a schema if it's empty (all objects in it have been
1577 DROP SCHEMA myschema;
1579 To drop a schema including all contained objects, use
1581 DROP SCHEMA myschema CASCADE;
1583 See <xref linkend="ddl-depend"> for a description of the general
1584 mechanism behind this.
1588 Often you will want to create a schema owned by someone else
1589 (since this is one of the ways to restrict the activities of your
1590 users to well-defined namespaces). The syntax for that is:
1592 CREATE SCHEMA <replaceable>schemaname</replaceable> AUTHORIZATION <replaceable>username</replaceable>;
1594 You can even omit the schema name, in which case the schema name
1595 will be the same as the user name. See <xref
1596 linkend="ddl-schemas-patterns"> for how this can be useful.
1600 Schema names beginning with <literal>pg_</> are reserved for
1601 system purposes and may not be created by users.
1605 <sect2 id="ddl-schemas-public">
1606 <title>The Public Schema</title>
1608 <indexterm zone="ddl-schemas-public">
1609 <primary>schema</primary>
1610 <secondary>public</secondary>
1614 In the previous sections we created tables without specifying any
1615 schema names. By default, such tables (and other objects) are
1616 automatically put into a schema named <quote>public</quote>. Every new
1617 database contains such a schema. Thus, the following are equivalent:
1619 CREATE TABLE products ( ... );
1623 CREATE TABLE public.products ( ... );
1628 <sect2 id="ddl-schemas-path">
1629 <title>The Schema Search Path</title>
1632 <primary>search path</primary>
1636 <primary>unqualified name</primary>
1640 <primary>name</primary>
1641 <secondary>unqualified</secondary>
1645 Qualified names are tedious to write, and it's often best not to
1646 wire a particular schema name into applications anyway. Therefore
1647 tables are often referred to by <firstterm>unqualified names</>,
1648 which consist of just the table name. The system determines which table
1649 is meant by following a <firstterm>search path</>, which is a list
1650 of schemas to look in. The first matching table in the search path
1651 is taken to be the one wanted. If there is no match in the search
1652 path, an error is reported, even if matching table names exist
1653 in other schemas in the database.
1657 <primary>schema</primary>
1658 <secondary>current</secondary>
1662 The first schema named in the search path is called the current schema.
1663 Aside from being the first schema searched, it is also the schema in
1664 which new tables will be created if the <command>CREATE TABLE</>
1665 command does not specify a schema name.
1669 <primary>search_path</primary>
1673 To show the current search path, use the following command:
1677 In the default setup this returns:
1683 The first element specifies that a schema with the same name as
1684 the current user is to be searched. If no such schema exists,
1685 the entry is ignored. The second element refers to the
1686 public schema that we have seen already.
1690 The first schema in the search path that exists is the default
1691 location for creating new objects. That is the reason that by
1692 default objects are created in the public schema. When objects
1693 are referenced in any other context without schema qualification
1694 (table modification, data modification, or query commands) the
1695 search path is traversed until a matching object is found.
1696 Therefore, in the default configuration, any unqualified access
1697 again can only refer to the public schema.
1701 To put our new schema in the path, we use
1703 SET search_path TO myschema,public;
1705 (We omit the <literal>$user</literal> here because we have no
1706 immediate need for it.) And then we can access the table without
1707 schema qualification:
1711 Also, since <literal>myschema</literal> is the first element in
1712 the path, new objects would by default be created in it.
1716 We could also have written
1718 SET search_path TO myschema;
1720 Then we no longer have access to the public schema without
1721 explicit qualification. There is nothing special about the public
1722 schema except that it exists by default. It can be dropped, too.
1726 See also <xref linkend="functions-info"> for other ways to access
1727 the schema search path.
1731 The search path works in the same way for data type names, function names,
1732 and operator names as it does for table names. Data type and function
1733 names can be qualified in exactly the same way as table names. If you
1734 need to write a qualified operator name in an expression, there is a
1735 special provision: you must write
1737 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operator</><literal>)</>
1739 This is needed to avoid syntactic ambiguity. An example is
1741 SELECT 3 OPERATOR(pg_catalog.+) 4;
1743 In practice one usually relies on the search path for operators,
1744 so as not to have to write anything so ugly as that.
1748 <sect2 id="ddl-schemas-priv">
1749 <title>Schemas and Privileges</title>
1751 <indexterm zone="ddl-schemas-priv">
1752 <primary>privilege</primary>
1753 <secondary sortas="schemas">for schemas</secondary>
1757 By default, users cannot access any objects in schemas they do not
1758 own. To allow that, the owner of the schema needs to grant the
1759 <literal>USAGE</literal> privilege on the schema. To allow users
1760 to make use of the objects in the schema, additional privileges
1761 may need to be granted, as appropriate for the object.
1765 A user can also be allowed to create objects in someone else's
1766 schema. To allow that, the <literal>CREATE</literal> privilege on
1767 the schema needs to be granted. Note that by default, everyone
1768 has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
1770 <literal>public</literal>. This allows all users that are able to
1771 connect to a given database to create objects in its
1772 <literal>public</literal> schema. If you do
1773 not want to allow that, you can revoke that privilege:
1775 REVOKE CREATE ON SCHEMA public FROM PUBLIC;
1777 (The first <quote>public</quote> is the schema, the second
1778 <quote>public</quote> means <quote>every user</quote>. In the
1779 first sense it is an identifier, in the second sense it is a
1780 reserved word, hence the different capitalization; recall the
1781 guidelines from <xref linkend="sql-syntax-identifiers">.)
1785 <sect2 id="ddl-schemas-catalog">
1786 <title>The System Catalog Schema</title>
1788 <indexterm zone="ddl-schemas-catalog">
1789 <primary>system catalog</primary>
1790 <secondary>schema</secondary>
1794 In addition to <literal>public</> and user-created schemas, each
1795 database contains a <literal>pg_catalog</> schema, which contains
1796 the system tables and all the built-in data types, functions, and
1797 operators. <literal>pg_catalog</> is always effectively part of
1798 the search path. If it is not named explicitly in the path then
1799 it is implicitly searched <emphasis>before</> searching the path's
1800 schemas. This ensures that built-in names will always be
1801 findable. However, you may explicitly place
1802 <literal>pg_catalog</> at the end of your search path if you
1803 prefer to have user-defined names override built-in names.
1807 In <productname>PostgreSQL</productname> versions before 7.3,
1808 table names beginning with <literal>pg_</> were reserved. This is
1809 no longer true: you may create such a table name if you wish, in
1810 any non-system schema. However, it's best to continue to avoid
1811 such names, to ensure that you won't suffer a conflict if some
1812 future version defines a system table named the same as your
1813 table. (With the default search path, an unqualified reference to
1814 your table name would be resolved as the system table instead.)
1815 System tables will continue to follow the convention of having
1816 names beginning with <literal>pg_</>, so that they will not
1817 conflict with unqualified user-table names so long as users avoid
1818 the <literal>pg_</> prefix.
1822 <sect2 id="ddl-schemas-patterns">
1823 <title>Usage Patterns</title>
1826 Schemas can be used to organize your data in many ways. There are
1827 a few usage patterns that are recommended and are easily supported by
1828 the default configuration:
1832 If you do not create any schemas then all users access the
1833 public schema implicitly. This simulates the situation where
1834 schemas are not available at all. This setup is mainly
1835 recommended when there is only a single user or a few cooperating
1836 users in a database. This setup also allows smooth transition
1837 from the non-schema-aware world.
1843 You can create a schema for each user with the same name as
1844 that user. Recall that the default search path starts with
1845 <literal>$user</literal>, which resolves to the user name.
1846 Therefore, if each user has a separate schema, they access their
1847 own schemas by default.
1851 If you use this setup then you might also want to revoke access
1852 to the public schema (or drop it altogether), so users are
1853 truly constrained to their own schemas.
1859 To install shared applications (tables to be used by everyone,
1860 additional functions provided by third parties, etc.), put them
1861 into separate schemas. Remember to grant appropriate
1862 privileges to allow the other users to access them. Users can
1863 then refer to these additional objects by qualifying the names
1864 with a schema name, or they can put the additional schemas into
1865 their path, as they choose.
1872 <sect2 id="ddl-schemas-portability">
1873 <title>Portability</title>
1876 In the SQL standard, the notion of objects in the same schema
1877 being owned by different users does not exist. Moreover, some
1878 implementations do not allow you to create schemas that have a
1879 different name than their owner. In fact, the concepts of schema
1880 and user are nearly equivalent in a database system that
1881 implements only the basic schema support specified in the
1882 standard. Therefore, many users consider qualified names to
1884 <literal><replaceable>username</>.<replaceable>tablename</></literal>.
1885 This is how <productname>PostgreSQL</productname> will effectively
1886 behave if you create a per-user schema for every user.
1890 Also, there is no concept of a <literal>public</> schema in the
1891 SQL standard. For maximum conformance to the standard, you should
1892 not use (perhaps even remove) the <literal>public</> schema.
1896 Of course, some SQL database systems might not implement schemas
1897 at all, or provide namespace support by allowing (possibly
1898 limited) cross-database access. If you need to work with those
1899 systems, then maximum portability would be achieved by not using
1905 <sect1 id="ddl-others">
1906 <title>Other Database Objects</title>
1909 Tables are the central objects in a relational database structure,
1910 because they hold your data. But they are not the only objects
1911 that exist in a database. Many other kinds of objects can be
1912 created to make the use and management of the data more efficient
1913 or convenient. They are not discussed in this chapter, but we give
1914 you a list here so that you are aware of what is possible.
1926 Functions, operators, data types, domains
1932 Triggers and rewrite rules
1938 Detailed information on
1939 these topics appears in <xref linkend="server-programming">.
1943 <sect1 id="ddl-depend">
1944 <title>Dependency Tracking</title>
1946 <indexterm zone="ddl-depend">
1947 <primary>CASCADE</primary>
1948 <secondary sortas="DROP">with DROP</secondary>
1951 <indexterm zone="ddl-depend">
1952 <primary>RESTRICT</primary>
1953 <secondary sortas="DROP">with DROP</secondary>
1957 When you create complex database structures involving many tables
1958 with foreign key constraints, views, triggers, functions, etc. you
1959 will implicitly create a net of dependencies between the objects.
1960 For instance, a table with a foreign key constraint depends on the
1961 table it references.
1965 To ensure the integrity of the entire database structure,
1966 <productname>PostgreSQL</productname> makes sure that you cannot
1967 drop objects that other objects still depend on. For example,
1968 attempting to drop the products table we had considered in <xref
1969 linkend="ddl-constraints-fk">, with the orders table depending on
1970 it, would result in an error message such as this:
1972 DROP TABLE products;
1974 NOTICE: constraint $1 on table orders depends on table products
1975 ERROR: cannot drop table products because other objects depend on it
1976 HINT: Use DROP ... CASCADE to drop the dependent objects too.
1978 The error message contains a useful hint: if you do not want to
1979 bother deleting all the dependent objects individually, you can run
1981 DROP TABLE products CASCADE;
1983 and all the dependent objects will be removed. In this case, it
1984 doesn't remove the orders table, it only removes the foreign key
1985 constraint. (If you want to check what <literal>DROP ... CASCADE</> will do,
1986 run <command>DROP</> without <literal>CASCADE</> and read the <literal>NOTICE</> messages.)
1990 All drop commands in <productname>PostgreSQL</productname> support
1991 specifying <literal>CASCADE</literal>. Of course, the nature of
1992 the possible dependencies varies with the type of the object. You
1993 can also write <literal>RESTRICT</literal> instead of
1994 <literal>CASCADE</literal> to get the default behavior, which is to
1995 prevent drops of objects that other objects depend on.
2000 According to the SQL standard, specifying either
2001 <literal>RESTRICT</literal> or <literal>CASCADE</literal> is
2002 required. No database system actually implements it that way, but
2003 whether the default behavior is <literal>RESTRICT</literal> or
2004 <literal>CASCADE</literal> varies across systems.
2010 Foreign key constraint dependencies and serial column dependencies
2011 from <productname>PostgreSQL</productname> versions prior to 7.3
2012 are <emphasis>not</emphasis> maintained or created during the
2013 upgrade process. All other dependency types will be properly
2014 created during an upgrade.
projects / postgresql / blob