1 <!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.30 2004/08/08 21:33:11 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 parent, cities. State
1001 capitals have an extra attribute, state, that shows their state. In
1002 <productname>PostgreSQL</productname>, a table can inherit from zero or
1003 more other tables, and a query can reference either all rows of a table or
1004 all rows of a table plus all of its descendants.
1008 The inheritance hierarchy is actually a directed acyclic graph.
1014 For example, the following query finds the names of all cities,
1015 including state capitals, that are located at an altitude
1019 SELECT name, altitude
1021 WHERE altitude > 500;
1028 -----------+----------
1036 On the other hand, the following query finds
1037 all the cities that are not state capitals and
1038 are situated at an altitude over 500ft:
1041 SELECT name, altitude
1043 WHERE altitude > 500;
1046 -----------+----------
1053 Here the <quote>ONLY</quote> before cities indicates that the query should
1054 be run over only cities and not tables below cities in the
1055 inheritance hierarchy. Many of the commands that we
1056 have already discussed -- <command>SELECT</command>,
1057 <command>UPDATE</command> and <command>DELETE</command> --
1058 support this <quote>ONLY</quote> notation.
1062 <title>Deprecated</title>
1064 In previous versions of <productname>PostgreSQL</productname>, the
1065 default behavior was not to include child tables in queries. This was
1066 found to be error prone and is also in violation of the SQL99
1067 standard. Under the old syntax, to get the sub-tables you append
1068 <literal>*</literal> to the table name.
1071 SELECT * from cities*;
1073 You can still explicitly specify scanning child tables by appending
1074 <literal>*</literal>, as well as explicitly specify not scanning child tables by
1075 writing <quote>ONLY</quote>. But beginning in version 7.1, the default
1076 behavior for an undecorated table name is to scan its child tables
1077 too, whereas before the default was not to do so. To get the old
1078 default behavior, set the configuration option
1079 <literal>SQL_Inheritance</literal> to off, e.g.,
1081 SET SQL_Inheritance TO OFF;
1083 or add a line in your <filename>postgresql.conf</filename> file.
1088 In some cases you may wish to know which table a particular row
1089 originated from. There is a system column called
1090 <structfield>TABLEOID</structfield> in each table which can tell you the
1094 SELECT c.tableoid, c.name, c.altitude
1096 WHERE c.altitude > 500;
1102 tableoid | name | altitude
1103 ----------+-----------+----------
1104 139793 | Las Vegas | 2174
1105 139793 | Mariposa | 1953
1106 139798 | Madison | 845
1109 (If you try to reproduce this example, you will probably get
1110 different numeric OIDs.) By doing a join with
1111 <structname>pg_class</> you can see the actual table names:
1114 SELECT p.relname, c.name, c.altitude
1115 FROM cities c, pg_class p
1116 WHERE c.altitude > 500 and c.tableoid = p.oid;
1122 relname | name | altitude
1123 ----------+-----------+----------
1124 cities | Las Vegas | 2174
1125 cities | Mariposa | 1953
1126 capitals | Madison | 845
1132 A serious limitation of the inheritance feature is that indexes (including
1133 unique constraints) and foreign key constraints only apply to single
1134 tables, not to their inheritance children. This is true on both the
1135 referencing and referenced sides of a foreign key constraint. Thus,
1136 in the terms of the above example:
1141 If we declared <structname>cities</>.<structfield>name</> to be
1142 <literal>UNIQUE</> or a <literal>PRIMARY KEY</>, this would not stop the
1143 <structname>capitals</> table from having rows with names duplicating
1144 rows in <structname>cities</>. And those duplicate rows would by
1145 default show up in SELECTs from <structname>cities</>. In fact, by
1146 default <structname>capitals</> would have no unique constraint at all,
1147 and so could contain multiple rows with the same name.
1148 You could add a unique constraint to <structname>capitals</>, but this
1149 would not prevent duplication compared to <structname>cities</>.
1155 Similarly, if we were to specify that
1156 <structname>cities</>.<structfield>name</> <literal>REFERENCES</> some
1157 other table, this constraint would not automatically propagate to
1158 <structname>capitals</>. In this case you could work around it by
1159 manually adding the same <literal>REFERENCES</> constraint to
1160 <structname>capitals</>.
1166 Specifying that another table's column <literal>REFERENCES
1167 cities(name)</> would allow the other table to contain city names, but
1168 not capital names. There is no good workaround for this case.
1173 These deficiencies will probably be fixed in some future release,
1174 but in the meantime considerable care is needed in deciding whether
1175 inheritance is useful for your problem.
1179 <sect1 id="ddl-alter">
1180 <title>Modifying Tables</title>
1182 <indexterm zone="ddl-alter">
1183 <primary>table</primary>
1184 <secondary>modifying</secondary>
1188 When you create a table and you realize that you made a mistake, or
1189 the requirements of the application changed, then you can drop the
1190 table and create it again. But this is not a convenient option if
1191 the table is already filled with data, or if the table is
1192 referenced by other database objects (for instance a foreign key
1193 constraint). Therefore <productname>PostgreSQL</productname>
1194 provides a family of commands to make modifications on existing
1200 <itemizedlist spacing="compact">
1202 <para>Add columns,</para>
1205 <para>Remove columns,</para>
1208 <para>Add constraints,</para>
1211 <para>Remove constraints,</para>
1214 <para>Change default values,</para>
1217 <para>Rename columns,</para>
1220 <para>Rename tables.</para>
1224 All these actions are performed using the <literal>ALTER
1225 TABLE</literal> command.
1229 <title>Adding a Column</title>
1232 <primary>column</primary>
1233 <secondary>adding</secondary>
1237 To add a column, use this command:
1239 ALTER TABLE products ADD COLUMN description text;
1241 The new column will initially be filled with null values in the
1242 existing rows of the table.
1246 You can also define a constraint on the column at the same time,
1247 using the usual syntax:
1249 ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
1251 A new column cannot have a not-null constraint since the column
1252 initially has to contain null values. But you can add a not-null
1253 constraint later. Also, you cannot define a default value on a
1254 new column. According to the SQL standard, this would have to
1255 fill the new columns in the existing rows with the default value,
1256 which is not implemented yet. But you can adjust the column
1262 <title>Removing a Column</title>
1265 <primary>column</primary>
1266 <secondary>removing</secondary>
1270 To remove a column, use this command:
1272 ALTER TABLE products DROP COLUMN description;
1278 <title>Adding a Constraint</title>
1281 <primary>constraint</primary>
1282 <secondary>adding</secondary>
1286 To add a constraint, the table constraint syntax is used. For example:
1288 ALTER TABLE products ADD CHECK (name <> '');
1289 ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
1290 ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
1292 To add a not-null constraint, which cannot be written as a table
1293 constraint, use this syntax:
1295 ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
1300 The constraint will be checked immediately, so the table data must
1301 satisfy the constraint before it can be added.
1306 <title>Removing a Constraint</title>
1309 <primary>constraint</primary>
1310 <secondary>removing</secondary>
1314 To remove a constraint you need to know its name. If you gave it
1315 a name then that's easy. Otherwise the system assigned a
1316 generated name, which you need to find out. The
1317 <application>psql</application> command <literal>\d
1318 <replaceable>tablename</replaceable></literal> can be helpful
1319 here; other interfaces might also provide a way to inspect table
1320 details. Then the command is:
1322 ALTER TABLE products DROP CONSTRAINT some_name;
1324 (If you are dealing with a generated constraint name like <literal>$2</>,
1325 don't forget that you'll need to double-quote it to make it a valid
1330 This works the same for all constraint types except not-null
1331 constraints. To drop a not null constraint use
1333 ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
1335 (Recall that not-null constraints do not have names.)
1340 <title>Changing the Default</title>
1343 <primary>default value</primary>
1344 <secondary>changing</secondary>
1348 To set a new default for a column, use a command like this:
1350 ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
1352 To remove any default value, use
1354 ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
1356 This is equivalent to setting the default to null, at least in
1357 <productname>PostgreSQL</>. As a consequence, it is not an error
1358 to drop a default where one hadn't been defined, because the
1359 default is implicitly the null value.
1364 <title>Renaming a Column</title>
1367 <primary>column</primary>
1368 <secondary>renaming</secondary>
1374 ALTER TABLE products RENAME COLUMN product_no TO product_number;
1380 <title>Renaming a Table</title>
1383 <primary>table</primary>
1384 <secondary>renaming</secondary>
1390 ALTER TABLE products RENAME TO items;
1396 <sect1 id="ddl-priv">
1397 <title>Privileges</title>
1399 <indexterm zone="ddl-priv">
1400 <primary>privilege</primary>
1404 <primary>permission</primary>
1405 <see>privilege</see>
1409 When you create a database object, you become its owner. By
1410 default, only the owner of an object can do anything with the
1411 object. In order to allow other users to use it,
1412 <firstterm>privileges</firstterm> must be granted. (However,
1413 users that have the superuser attribute can always
1418 There are several different privileges: <literal>SELECT</>,
1419 <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
1420 <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
1421 <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
1422 and <literal>USAGE</>. The privileges applicable to a particular
1423 object vary depending on the object's type (table, function, etc).
1425 information on the different types of privileges supported by
1426 <productname>PostgreSQL</productname>, refer to the
1427 <xref linkend="sql-grant"> reference page. The following sections
1428 and chapters will also show you how those privileges are used.
1432 The right to modify or destroy an object is always the privilege of
1438 To change the owner of a table, index, sequence, or view, use the
1439 <xref linkend="sql-altertable"> command. There are corresponding
1440 <literal>ALTER</> commands for other object types.
1445 To assign privileges, the <command>GRANT</command> command is
1446 used. For example, if <literal>joe</literal> is an existing user, and
1447 <literal>accounts</literal> is an existing table, the privilege to
1448 update the table can be granted with
1450 GRANT UPDATE ON accounts TO joe;
1452 To grant a privilege to a group, use this syntax:
1454 GRANT SELECT ON accounts TO GROUP staff;
1456 The special <quote>user</quote> name <literal>PUBLIC</literal> can
1457 be used to grant a privilege to every user on the system. Writing
1458 <literal>ALL</literal> in place of a specific privilege grants all
1459 privileges that are relevant for the object type.
1463 To revoke a privilege, use the fittingly named
1464 <command>REVOKE</command> command:
1466 REVOKE ALL ON accounts FROM PUBLIC;
1468 The special privileges of the object owner (i.e., the right to do
1469 <command>DROP</>, <command>GRANT</>, <command>REVOKE</>, etc.)
1470 are always implicit in being the owner,
1471 and cannot be granted or revoked. But the object owner can choose
1472 to revoke his own ordinary privileges, for example to make a
1473 table read-only for himself as well as others.
1477 Ordinarily, only the object's owner (or a superuser) can grant or revoke
1478 privileges on an object. However, it is possible to grant a privilege
1479 <quote>with grant option</>, which gives the recipient the right to
1480 grant it in turn to others. If the grant option is subsequently revoked
1481 then all who received the privilege from that recipient (directly or
1482 through a chain of grants) will lose the privilege. For details see
1483 the <xref linkend="sql-grant"> and <xref linkend="sql-revoke"> reference
1488 <sect1 id="ddl-schemas">
1489 <title>Schemas</title>
1491 <indexterm zone="ddl-schemas">
1492 <primary>schema</primary>
1496 A <productname>PostgreSQL</productname> database cluster
1497 contains one or more named databases. Users and groups of users are
1498 shared across the entire cluster, but no other data is shared across
1499 databases. Any given client connection to the server can access
1500 only the data in a single database, the one specified in the connection
1506 Users of a cluster do not necessarily have the privilege to access every
1507 database in the cluster. Sharing of user names means that there
1508 cannot be different users named, say, <literal>joe</> in two databases
1509 in the same cluster; but the system can be configured to allow
1510 <literal>joe</> access to only some of the databases.
1515 A database contains one or more named <firstterm>schemas</>, which
1516 in turn contain tables. Schemas also contain other kinds of named
1517 objects, including data types, functions, and operators. The same
1518 object name can be used in different schemas without conflict; for
1519 example, both <literal>schema1</> and <literal>myschema</> may
1520 contain tables named <literal>mytable</>. Unlike databases,
1521 schemas are not rigidly separated: a user may access objects in any
1522 of the schemas in the database he is connected to, if he has
1523 privileges to do so.
1527 There are several reasons why one might want to use schemas:
1532 To allow many users to use one database without interfering with
1539 To organize database objects into logical groups to make them
1546 Third-party applications can be put into separate schemas so
1547 they cannot collide with the names of other objects.
1552 Schemas are analogous to directories at the operating system level,
1553 except that schemas cannot be nested.
1556 <sect2 id="ddl-schemas-create">
1557 <title>Creating a Schema</title>
1559 <indexterm zone="ddl-schemas-create">
1560 <primary>schema</primary>
1561 <secondary>creating</secondary>
1565 To create a separate schema, use the command <literal>CREATE
1566 SCHEMA</literal>. Give the schema a name of your choice. For
1569 CREATE SCHEMA myschema;
1574 <primary>qualified name</primary>
1578 <primary>name</primary>
1579 <secondary>qualified</secondary>
1583 To create or access objects in a schema, write a
1584 <firstterm>qualified name</> consisting of the schema name and
1585 table name separated by a dot:
1587 <replaceable>schema</><literal>.</><replaceable>table</>
1589 (For brevity we will speak of tables only, but the same ideas apply
1590 to other kinds of named objects, such as types and functions.)
1594 Actually, the even more general syntax
1596 <replaceable>database</><literal>.</><replaceable>schema</><literal>.</><replaceable>table</>
1598 can be used too, but at present this is just for pro-forma compliance
1599 with the SQL standard. If you write a database name, it must be the
1600 same as the database you are connected to.
1604 So to create a table in the new schema, use
1606 CREATE TABLE myschema.mytable (
1610 This works anywhere a table name is expected, including the table
1611 modification commands and the data access commands discussed in
1612 the following chapters.
1616 <primary>schema</primary>
1617 <secondary>removing</secondary>
1621 To drop a schema if it's empty (all objects in it have been
1624 DROP SCHEMA myschema;
1626 To drop a schema including all contained objects, use
1628 DROP SCHEMA myschema CASCADE;
1630 See <xref linkend="ddl-depend"> for a description of the general
1631 mechanism behind this.
1635 Often you will want to create a schema owned by someone else
1636 (since this is one of the ways to restrict the activities of your
1637 users to well-defined namespaces). The syntax for that is:
1639 CREATE SCHEMA <replaceable>schemaname</replaceable> AUTHORIZATION <replaceable>username</replaceable>;
1641 You can even omit the schema name, in which case the schema name
1642 will be the same as the user name. See <xref
1643 linkend="ddl-schemas-patterns"> for how this can be useful.
1647 Schema names beginning with <literal>pg_</> are reserved for
1648 system purposes and may not be created by users.
1652 <sect2 id="ddl-schemas-public">
1653 <title>The Public Schema</title>
1655 <indexterm zone="ddl-schemas-public">
1656 <primary>schema</primary>
1657 <secondary>public</secondary>
1661 In the previous sections we created tables without specifying any
1662 schema names. By default, such tables (and other objects) are
1663 automatically put into a schema named <quote>public</quote>. Every new
1664 database contains such a schema. Thus, the following are equivalent:
1666 CREATE TABLE products ( ... );
1670 CREATE TABLE public.products ( ... );
1675 <sect2 id="ddl-schemas-path">
1676 <title>The Schema Search Path</title>
1679 <primary>search path</primary>
1683 <primary>unqualified name</primary>
1687 <primary>name</primary>
1688 <secondary>unqualified</secondary>
1692 Qualified names are tedious to write, and it's often best not to
1693 wire a particular schema name into applications anyway. Therefore
1694 tables are often referred to by <firstterm>unqualified names</>,
1695 which consist of just the table name. The system determines which table
1696 is meant by following a <firstterm>search path</>, which is a list
1697 of schemas to look in. The first matching table in the search path
1698 is taken to be the one wanted. If there is no match in the search
1699 path, an error is reported, even if matching table names exist
1700 in other schemas in the database.
1704 <primary>schema</primary>
1705 <secondary>current</secondary>
1709 The first schema named in the search path is called the current schema.
1710 Aside from being the first schema searched, it is also the schema in
1711 which new tables will be created if the <command>CREATE TABLE</>
1712 command does not specify a schema name.
1716 <primary>search_path</primary>
1720 To show the current search path, use the following command:
1724 In the default setup this returns:
1730 The first element specifies that a schema with the same name as
1731 the current user is to be searched. If no such schema exists,
1732 the entry is ignored. The second element refers to the
1733 public schema that we have seen already.
1737 The first schema in the search path that exists is the default
1738 location for creating new objects. That is the reason that by
1739 default objects are created in the public schema. When objects
1740 are referenced in any other context without schema qualification
1741 (table modification, data modification, or query commands) the
1742 search path is traversed until a matching object is found.
1743 Therefore, in the default configuration, any unqualified access
1744 again can only refer to the public schema.
1748 To put our new schema in the path, we use
1750 SET search_path TO myschema,public;
1752 (We omit the <literal>$user</literal> here because we have no
1753 immediate need for it.) And then we can access the table without
1754 schema qualification:
1758 Also, since <literal>myschema</literal> is the first element in
1759 the path, new objects would by default be created in it.
1763 We could also have written
1765 SET search_path TO myschema;
1767 Then we no longer have access to the public schema without
1768 explicit qualification. There is nothing special about the public
1769 schema except that it exists by default. It can be dropped, too.
1773 See also <xref linkend="functions-info"> for other ways to access
1774 the schema search path.
1778 The search path works in the same way for data type names, function names,
1779 and operator names as it does for table names. Data type and function
1780 names can be qualified in exactly the same way as table names. If you
1781 need to write a qualified operator name in an expression, there is a
1782 special provision: you must write
1784 <literal>OPERATOR(</><replaceable>schema</><literal>.</><replaceable>operator</><literal>)</>
1786 This is needed to avoid syntactic ambiguity. An example is
1788 SELECT 3 OPERATOR(pg_catalog.+) 4;
1790 In practice one usually relies on the search path for operators,
1791 so as not to have to write anything so ugly as that.
1795 <sect2 id="ddl-schemas-priv">
1796 <title>Schemas and Privileges</title>
1798 <indexterm zone="ddl-schemas-priv">
1799 <primary>privilege</primary>
1800 <secondary sortas="schemas">for schemas</secondary>
1804 By default, users cannot access any objects in schemas they do not
1805 own. To allow that, the owner of the schema needs to grant the
1806 <literal>USAGE</literal> privilege on the schema. To allow users
1807 to make use of the objects in the schema, additional privileges
1808 may need to be granted, as appropriate for the object.
1812 A user can also be allowed to create objects in someone else's
1813 schema. To allow that, the <literal>CREATE</literal> privilege on
1814 the schema needs to be granted. Note that by default, everyone
1815 has <literal>CREATE</literal> and <literal>USAGE</literal> privileges on
1817 <literal>public</literal>. This allows all users that are able to
1818 connect to a given database to create objects in its
1819 <literal>public</literal> schema. If you do
1820 not want to allow that, you can revoke that privilege:
1822 REVOKE CREATE ON SCHEMA public FROM PUBLIC;
1824 (The first <quote>public</quote> is the schema, the second
1825 <quote>public</quote> means <quote>every user</quote>. In the
1826 first sense it is an identifier, in the second sense it is a
1827 reserved word, hence the different capitalization; recall the
1828 guidelines from <xref linkend="sql-syntax-identifiers">.)
1832 <sect2 id="ddl-schemas-catalog">
1833 <title>The System Catalog Schema</title>
1835 <indexterm zone="ddl-schemas-catalog">
1836 <primary>system catalog</primary>
1837 <secondary>schema</secondary>
1841 In addition to <literal>public</> and user-created schemas, each
1842 database contains a <literal>pg_catalog</> schema, which contains
1843 the system tables and all the built-in data types, functions, and
1844 operators. <literal>pg_catalog</> is always effectively part of
1845 the search path. If it is not named explicitly in the path then
1846 it is implicitly searched <emphasis>before</> searching the path's
1847 schemas. This ensures that built-in names will always be
1848 findable. However, you may explicitly place
1849 <literal>pg_catalog</> at the end of your search path if you
1850 prefer to have user-defined names override built-in names.
1854 In <productname>PostgreSQL</productname> versions before 7.3,
1855 table names beginning with <literal>pg_</> were reserved. This is
1856 no longer true: you may create such a table name if you wish, in
1857 any non-system schema. However, it's best to continue to avoid
1858 such names, to ensure that you won't suffer a conflict if some
1859 future version defines a system table named the same as your
1860 table. (With the default search path, an unqualified reference to
1861 your table name would be resolved as the system table instead.)
1862 System tables will continue to follow the convention of having
1863 names beginning with <literal>pg_</>, so that they will not
1864 conflict with unqualified user-table names so long as users avoid
1865 the <literal>pg_</> prefix.
1869 <sect2 id="ddl-schemas-patterns">
1870 <title>Usage Patterns</title>
1873 Schemas can be used to organize your data in many ways. There are
1874 a few usage patterns that are recommended and are easily supported by
1875 the default configuration:
1879 If you do not create any schemas then all users access the
1880 public schema implicitly. This simulates the situation where
1881 schemas are not available at all. This setup is mainly
1882 recommended when there is only a single user or a few cooperating
1883 users in a database. This setup also allows smooth transition
1884 from the non-schema-aware world.
1890 You can create a schema for each user with the same name as
1891 that user. Recall that the default search path starts with
1892 <literal>$user</literal>, which resolves to the user name.
1893 Therefore, if each user has a separate schema, they access their
1894 own schemas by default.
1898 If you use this setup then you might also want to revoke access
1899 to the public schema (or drop it altogether), so users are
1900 truly constrained to their own schemas.
1906 To install shared applications (tables to be used by everyone,
1907 additional functions provided by third parties, etc.), put them
1908 into separate schemas. Remember to grant appropriate
1909 privileges to allow the other users to access them. Users can
1910 then refer to these additional objects by qualifying the names
1911 with a schema name, or they can put the additional schemas into
1912 their search path, as they choose.
1919 <sect2 id="ddl-schemas-portability">
1920 <title>Portability</title>
1923 In the SQL standard, the notion of objects in the same schema
1924 being owned by different users does not exist. Moreover, some
1925 implementations do not allow you to create schemas that have a
1926 different name than their owner. In fact, the concepts of schema
1927 and user are nearly equivalent in a database system that
1928 implements only the basic schema support specified in the
1929 standard. Therefore, many users consider qualified names to
1931 <literal><replaceable>username</>.<replaceable>tablename</></literal>.
1932 This is how <productname>PostgreSQL</productname> will effectively
1933 behave if you create a per-user schema for every user.
1937 Also, there is no concept of a <literal>public</> schema in the
1938 SQL standard. For maximum conformance to the standard, you should
1939 not use (perhaps even remove) the <literal>public</> schema.
1943 Of course, some SQL database systems might not implement schemas
1944 at all, or provide namespace support by allowing (possibly
1945 limited) cross-database access. If you need to work with those
1946 systems, then maximum portability would be achieved by not using
1952 <sect1 id="ddl-others">
1953 <title>Other Database Objects</title>
1956 Tables are the central objects in a relational database structure,
1957 because they hold your data. But they are not the only objects
1958 that exist in a database. Many other kinds of objects can be
1959 created to make the use and management of the data more efficient
1960 or convenient. They are not discussed in this chapter, but we give
1961 you a list here so that you are aware of what is possible.
1973 Functions, operators, data types, domains
1979 Triggers and rewrite rules
1985 Detailed information on
1986 these topics appears in <xref linkend="server-programming">.
1990 <sect1 id="ddl-depend">
1991 <title>Dependency Tracking</title>
1993 <indexterm zone="ddl-depend">
1994 <primary>CASCADE</primary>
1995 <secondary sortas="DROP">with DROP</secondary>
1998 <indexterm zone="ddl-depend">
1999 <primary>RESTRICT</primary>
2000 <secondary sortas="DROP">with DROP</secondary>
2004 When you create complex database structures involving many tables
2005 with foreign key constraints, views, triggers, functions, etc. you
2006 will implicitly create a net of dependencies between the objects.
2007 For instance, a table with a foreign key constraint depends on the
2008 table it references.
2012 To ensure the integrity of the entire database structure,
2013 <productname>PostgreSQL</productname> makes sure that you cannot
2014 drop objects that other objects still depend on. For example,
2015 attempting to drop the products table we had considered in <xref
2016 linkend="ddl-constraints-fk">, with the orders table depending on
2017 it, would result in an error message such as this:
2019 DROP TABLE products;
2021 NOTICE: constraint orders_product_no_fkey on table orders depends on table products
2022 ERROR: cannot drop table products because other objects depend on it
2023 HINT: Use DROP ... CASCADE to drop the dependent objects too.
2025 The error message contains a useful hint: if you do not want to
2026 bother deleting all the dependent objects individually, you can run
2028 DROP TABLE products CASCADE;
2030 and all the dependent objects will be removed. In this case, it
2031 doesn't remove the orders table, it only removes the foreign key
2032 constraint. (If you want to check what <literal>DROP ... CASCADE</> will do,
2033 run <command>DROP</> without <literal>CASCADE</> and read the <literal>NOTICE</> messages.)
2037 All drop commands in <productname>PostgreSQL</productname> support
2038 specifying <literal>CASCADE</literal>. Of course, the nature of
2039 the possible dependencies varies with the type of the object. You
2040 can also write <literal>RESTRICT</literal> instead of
2041 <literal>CASCADE</literal> to get the default behavior, which is to
2042 prevent drops of objects that other objects depend on.
2047 According to the SQL standard, specifying either
2048 <literal>RESTRICT</literal> or <literal>CASCADE</literal> is
2049 required. No database system actually implements it that way, but
2050 whether the default behavior is <literal>RESTRICT</literal> or
2051 <literal>CASCADE</literal> varies across systems.
2057 Foreign key constraint dependencies and serial column dependencies
2058 from <productname>PostgreSQL</productname> versions prior to 7.3
2059 are <emphasis>not</emphasis> maintained or created during the
2060 upgrade process. All other dependency types will be properly
2061 created during an upgrade.
projects / postgresql / blob