From 35cd432b185938c33967c9fa48223ce33e1c66bd Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 5 Aug 2002 19:44:58 +0000 Subject: [PATCH] Forgot to add/remove files. --- doc/src/sgml/ddl.sgml | 992 +++++++++++++++++++++++++++++++++++++ doc/src/sgml/dml.sgml | 199 ++++++++ doc/src/sgml/entities.sgml | 20 + doc/src/sgml/inherit.sgml | 188 ------- 4 files changed, 1211 insertions(+), 188 deletions(-) create mode 100644 doc/src/sgml/ddl.sgml create mode 100644 doc/src/sgml/dml.sgml create mode 100644 doc/src/sgml/entities.sgml delete mode 100644 doc/src/sgml/inherit.sgml diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml new file mode 100644 index 0000000000..2a1d15706e --- /dev/null +++ b/doc/src/sgml/ddl.sgml @@ -0,0 +1,992 @@ + + + + Data Definition + + + This chapter covers how one creates the database structures that + will hold one's data. In a relational database, the raw data is + stored in tables, so the majority of this chapter is devoted to + explaining how tables are created and modified and what features are + available to control what data is stored in the tables. + Subsequently, we discuss how tables can be organized into + namespaces, and how privileges can be assigned to tables. Finally, + we will briefly look at other features that affect the data storage, + such as views, functions, and triggers. Detailed information on + these topics is found in &cite-programmer;. + + + + Table Basics + + + A table in a relational database is much like a table on paper: It + consists of rows and columns. The number and order of the columns + is fixed, and each column has a name. The number of rows is + variable -- it reflects how much data is stored at a given moment. + SQL does not make any guarantees about the order of the rows in a + table. When a table is read, the rows will appear in random order, + unless sorting is explicitly requested. This is covered in . Furthermore, SQL does not assign unique + identifiers to rows, so it is possible to have several completely + identical rows in a table. This is a consequence of the + mathematical model that underlies SQL but is usually not desirable. + Later in this chapter we will see how to deal with this issue. + + + + Each column has a data type. The data type constrains the set of + possible values that can be assigned to a column and assigns + semantics to the data stored in the column so that it can be used + for computations. For instance, a column declared to be of a + numerical type will not accept arbitrary text strings, and the data + stored in such a column can be used for mathematical computations. + By contrast, a column declared to be of a character string type + will accept almost any kind of data but it does not lend itself to + mathematical calculations, although other operations such as string + concatenation are available. + + + + PostgreSQL includes a sizable set of + built-in data types that fit many applications. Users can also + define their own data types. Most built-in data types have obvious + names and semantics, so we defer a detailed explanation to . Some of the frequently used data types are + integer for whole numbers, numeric for + possibly fractional numbers, text for character + strings, date for dates, time for + time-of-day values, and timestamp for values + containing both date and time. + + + + To create a table, you use the aptly named CREATE + TABLE command. In this command you specify at least a + name for the new table, the names of the columns and the data type + of each column. For example: + +CREATE TABLE my_first_table ( + first_column text, + second_column integer +); + + This creates a table named my_first_table with + two columns. The first column is named + first_column and has a data type of + text; the second column has the name + second_column and the type integer. + The table and column names follow the identifier syntax explained + in . The type names are + also identifiers, but there are some exceptions. Note that the + column list is comma-separated and surrounded by parentheses. + + + + Of course, the previous example was heavily contrived. Normally, + you would give names to your tables and columns that convey what + kind of data they store. So let's look at a more realistic + example: + +CREATE TABLE products ( + product_no integer, + name text, + price numeric +); + + (The numeric type can store fractional components, as + would be typical of monetary amounts.) + + + + + When you create many interrelated tables it is wise to choose a + consistent naming patter for the tables and columns. For + instance, there is a choice of using singular or plural nouns for + table names, both of which are favored by some theorist or other. + + + + + There is a limit on how many columns a table can contain. + Depending on the column types, it is between 250 and 1600. + However, defining a table with anywhere near this many columns is + highly unusual and often a questionable design. + + + + If you don't need a table anymore, you can remove it using the + DROP TABLE command. For example: + +DROP TABLE my_first_table; +DROP TABLE products; + + Attempting to drop a table that does not exist is an error. + Nevertheless, it is common in SQL script files to unconditionally + try to drop each table before creating it, ignoring the error + messages. + + + + If you need to modify a table that already exists look into later in this chapter. + + + + With the tools discussed so far you can create fully functional + tables. The remainder of this chapter is concerned with adding + features to the table definition to ensure data integrity, + security, or convenience. If you are eager to fill your tables with + data now you can skip ahead to and read the + rest of this chapter later. + + + + + Default Values + + + A column can be assigned a default value. When a new row is + created and no values are specified for some of the columns, the + columns will be filled with their respective default values. A + data manipulation command can also request explicitly that a column + be set to its default value, without knowing what this value is. + (Details about data manipulation commands are in the next chapter.) + + + + If no default value is declared explicitly, the null value is the + default value. This usually makes sense because a null value can + be thought to represent unknown data. + + + + In a table definition, default values are listed after the column + data type. For example: + +CREATE TABLE products ( + product_no integer PRIMARY KEY, + name text, + price numeric DEFAULT 9.99 +); + + + + + The default value may be a scalar expression, which well be + evaluated whenever the default value is inserted + (not when the table is created). + + + + + Constraints + + + Data types are a way to limit the kind of data that can be stored + in a table. For many applications, however, the constraint they + provide is too coarse. For example, a column containing a product + price should probably only accept positive values. But there is no + data type that accepts only positive numbers. Another issue is + that you might want to constrain column data with respect to other + columns or rows. For example, in a table containing product + information, there should only be one row for each product number. + + + + To that end, SQL allows you to define constraints on columns and + tables. Constraints give you as much control over the data in your + tables as you wish. If a user attempts to store data in a column + that would violate a constraint, an error is raised. This applies + even if the value came from the default value definition. + + + + Check Constraints + + + A check constraint is the most generic constraint type. It allows + you to specify that the value in a certain column must satisfy an + arbitrary expression. For instance, to require positive product + prices, you could use: + +CREATE TABLE products ( + product_no integer, + name text, + price numeric CHECK (price > 0) +); + + + + + As you see, the constraint definition comes after the data type, + just like default value definitions. Default values and + constraints can be listed in any order. A check constraint + consists of the key word CHECK followed by an + expression in parentheses. The check constraint expression should + involve the column thus constrained, otherwise the constraint + would not make too much sense. + + + + You can also give the constraint a separate name. This clarifies + error messages and allows you to refer to the constraint when you + need to change it. The syntax is: + +CREATE TABLE products ( + product_no integer, + name text, + price numeric CONSTRAINT positive_price CHECK (price > 0) +); + + To specify a named constraint, use the key word + CONSTRAINT followed by an identifier followed + by the constraint definition. + + + + A check constraint can also refer to several columns. Say you + store a regular price and a discounted price, and you want to + ensure that the discounted price is lower than the regular price. + +CREATE TABLE products ( + product_no integer, + name text, + price numeric CHECK (price > 0), + discounted_price numeric CHECK (discounted_price > 0), + CHECK (price > discounted_price) +); + + + + + The first two constraints should look familiar. The third one + uses a new syntax. It is not attached to a particular column, + instead it appears as a separate item in the comma-separated + column list. In general, column definitions and constraint + definitions can be listed in mixed order. + + + + We say that the first two are column constraints, whereas the + third one is a table constraint because it is written separately + from the column definitions. Column constraints can also be + written as table constraints, while the reverse is not necessarily + possible. The above example could also be written as + +CREATE TABLE products ( + product_no integer, + name text, + price numeric, + CHECK (price > 0), + discounted_price numeric, + CHECK (discounted_price > 0), + CHECK (price > discounted_price) +); + + or even + +CREATE TABLE products ( + product_no integer, + name text, + price numeric CHECK (price > 0), + discounted_price numeric, + CHECK (discounted_price > 0 AND price > discounted_price) +); + + It's a matter of taste. + + + + It should be noted that a check constraint is satisfied if the + check expression evaluates to true or the null value. To ensure + that a column does not contain null values, the not-null + constraint described in the next section should be used. + + + + + Not-Null Constraints + + + A not-null constraint simply specifies that a column must not + assume the null value. A syntax example: + +CREATE TABLE products ( + product_no integer NOT NULL, + name text NOT NULL, + price numeric +); + + + + + A not-null constraint is always written as a column constraint. A + not-null constraint is equivalent to creating a check constraint + CHECK (column_name IS NOT + NULL), but in PostgreSQL + creating an explicit not-null constraint is more efficient. The + drawback is that you cannot give explicit names to not-null + constraints created that way. + + + + Of course, a column can have more than one constraint. Just write + the constraints after one another: + +CREATE TABLE products ( + product_no integer NOT NULL, + name text NOT NULL, + price numeric NOT NULL CHECK (price > 0) +); + + The order doesn't matter. It does not necessarily affect in which + order the constraints are checked. + + + + The NOT NULL constraint has an inverse: the + NULL constraint. This does not mean that the + column must be null, which would surely be useless. Instead, this + simply defines the default behavior that the column may be null. + The NULL constraint is not defined in the SQL + standard and should not be used in portable applications. (It was + only added to PostgreSQL to be + compatible with other database systems.) Some users, however, + like it because it makes it easy to toggle the constraint in a + script file. For example, you could start with + +CREATE TABLE products ( + product_no integer NULL, + name text NULL, + price numeric NULL +); + + and then insert the NOT key word where desired. + + + + + In most database designs the majority of columns should be marked + not null. + + + + + + Unique Constraints + + + Unique constraints ensure that the data contained in a column or a + group of columns is unique with respect to all the rows in the + table. The syntax is + +CREATE TABLE products ( + product_no integer UNIQUE, + name text, + price numeric +); + + when written as a column constraint, and + +CREATE TABLE products ( + product_no integer, + name text, + price numeric, + UNIQUE (product_no) +); + + when written as a table constraint. + + + + If a unique constraint refers to a group of columns, the columns + are listed separated by commas: + +CREATE TABLE example ( + a integer, + b integer, + c integer, + UNIQUE (a, c) +); + + + + + It is also possible to assign names to unique constraints: + +CREATE TABLE products ( + product_no integer CONSTRAINT must_be_different UNIQUE, + name text, + price numeric +); + + + + + In general, a unique constraint is violated when there are (at + least) two rows in the table where the values of each of the + corresponding columns that are part of the constraint are equal. + However, null values are not considered equal in this + consideration. That means, in the presence of a multicolumn + unique constraint it is possible to store an unlimited number of + rows that contain a null value in at least one of the constrained + columns. This behavior conforms to the SQL standard, but we have + heard that other SQL databases may not follow this rule. So be + careful when developing applications that are intended to be + portable. + + + + + Primary Keys + + + Technically, a primary key constraint is simply a combination of a + unique constraint and a not-null constraint. So, the following + two table definitions accept the same data: + +CREATE TABLE products ( + product_no integer UNIQUE NOT NULL, + name text, + price numeric +); + + + +CREATE TABLE products ( + product_no integer PRIMARY KEY, + name text, + price numeric +); + + + + + Primary keys can also constrain more than one column; the syntax + is similar to unique constraints: + +CREATE TABLE example ( + a integer, + b integer, + c integer, + PRIMARY KEY (a, c) +); + + + + + A primary key indicates that a column or group of columns can be + used as a unique identifier for rows in the table. (This is a + direct consequence of the definition of a primary key. Note that + a unique constraint does not, in fact, provide a unique identifier + because it does not exclude null values.) This is useful both for + documentation purposes and for client applications. For example, + a GUI application that allows modifying row values probably needs + to know the primary key of a table to be able to identify rows + uniquely. + + + + A table can have at most one primary key (while it can have many + unique and not-null constraints). Relational database theory + dictates that every table must have a primary key. This rule is + not enforced by PostgreSQL, but it is + usually best to follow it. + + + + + Foreign Keys + + + A foreign key constraint specifies that the values in a column (or + a group of columns) must match the values in some other column. + We say this maintains the referential + integrity between two related tables. + + + + Say you have the product table that we have used several times already: + +CREATE TABLE products ( + product_no integer PRIMARY KEY, + name text, + price numeric +); + + Let's also assume you have a table storing orders of those + products. We want to ensure that the orders table only contains + orders of products that actually exist. So we define a foreign + key constraint in the orders table that references the products + table: + +CREATE TABLE orders ( + order_id integer PRIMARY KEY, + product_no integer REFERENCES products (product_no), + quantity integer +); + + Now it is impossible to create orders with + product_no entries that do not appear in the + products table. + + + + We say that in this situation the orders table is the + referencing table and the products table is + the referenced table. Similarly, there are + referencing and referenced columns. + + + + You can also shorten the above command to + +CREATE TABLE orders ( + order_id integer PRIMARY KEY, + product_no integer REFERENCES products, + quantity integer +); + + because in absence of a column list the primary key of the + referenced table is used as referenced column. + + + + A foreign key can also constrain and reference a group of columns. + As usual, it then needs to be written in table constraint form. + Here is a contrived syntax example: + +CREATE TABLE t1 ( + a integer PRIMARY KEY, + b integer, + c integer, + FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) +); + + Of course, the number and type of constrained columns needs to + match the number and type of referenced columns. + + + + A table can contain more than one foreign key constraint. This is + used to implement many-to-many relationships between tables. Say + you have tables about products and orders, but now you want to + allow one order to contain possibly many products (which the + structure above did not allow). You could use this table structure: + +CREATE TABLE products ( + product_no integer PRIMARY KEY, + name text, + price numeric +); + +CREATE TABLE orders ( + order_id integer PRIMARY KEY, + shipping_address text, + ... +); + +CREATE TABLE order_items ( + product_no integer REFERENCES products, + order_id integer REFERENCES orders, + quantity integer, + PRIMARY KEY (product_no, order_id) +); + + Note also that the primary key overlaps with the foreign keys in + the last table. + + + + We know that the foreign keys disallow creation of orders that + don't relate to any products. But what if a product is removed + after an order is created that references it? SQL allows you to + specify that as well. Intuitively, we have a few options: + + Disallow deleting a referenced product + Delete the orders as well + Something else? + + + + + To illustrate this, let's implement the following policy on the + many-to-many relationship example above: When someone wants to + remove a product that is still referenced by an order (via + order_items), we disallow it. If someone + removes an order, the order items are removed as well. + +CREATE TABLE products ( + product_no integer PRIMARY KEY, + name text, + price numeric +); + +CREATE TABLE orders ( + order_id integer PRIMARY KEY, + shipping_address text, + ... +); + +CREATE TABLE order_items ( + product_no integer REFERENCES products ON DELETE RESTRICT, + order_id integer REFERENCES orders ON DELETE CASCADE, + quantity integer, + PRIMARY KEY (product_no, order_id) +); + + + + + Restricting and cascading deletes are the two most common options. + RESTRICT can also be written as NO + ACTON and it's also the default if you don't specify + anything. There are two other options for what should happen with + the foreign key columns when a primary key is deleted: + SET NULL and SET DEFAULT. + Note that these do not excuse you from observing any constraints. + For example, if an action specifies SET DEFAULT + but the default value would not satisfy the foreign key, the + deletion of the primary key wil fail. + + + + Analogous to ON DELETE there is also + ON UPDATE which is invoked when a primary key + is changed (updated). The possible actions are the same. + + + + More information about updating and deleting data is in . + + + + Finally, we should mention that a foreign key must reference + columns that are either a primary key or form a unique constraint. + If the foreign key references a unique constraint, there are some + additional possibilities regarding how null values are matched. + These are explained in the CREATE TABLE entry + in &cite-reference;. + + + + + + Inheritance + + This section needs to be rethought. Some of the + information should go into the following chapters. + + + Let's create two tables. The capitals table contains + state capitals which are also cities. Naturally, the + capitals table should inherit from cities. + + +CREATE TABLE cities ( + name text, + population float, + altitude int -- (in ft) +); + +CREATE TABLE capitals ( + state char(2) +) INHERITS (cities); + + + In this case, a row of capitals inherits all + attributes (name, population, and altitude) from its + parent, cities. The type of the attribute name is + text, a native PostgreSQL type for variable length + ASCII strings. The type of the attribute population is + float, a native PostgreSQL type for double precision + floating-point numbers. State capitals have an extra + attribute, state, that shows their state. In PostgreSQL, + a table can inherit from zero or more other tables, + and a query can reference either all rows of a + table or all rows of a table plus all of its + descendants. + + + + The inheritance hierarchy is actually a directed acyclic graph. + + + + + + For example, the following query finds the names of all cities, + including state capitals, that are located at an altitude + over 500ft: + + +SELECT name, altitude + FROM cities + WHERE altitude > 500; + + + which returns: + + + name | altitude +-----------+---------- + Las Vegas | 2174 + Mariposa | 1953 + Madison | 845 + + + + + On the other hand, the following query finds + all the cities that are not state capitals and + are situated at an altitude over 500ft: + + +SELECT name, altitude + FROM ONLY cities + WHERE altitude > 500; + + name | altitude +-----------+---------- + Las Vegas | 2174 + Mariposa | 1953 + + + + + Here the ONLY before cities indicates that the query should + be run over only cities and not tables below cities in the + inheritance hierarchy. Many of the commands that we + have already discussed -- SELECT, + UPDATE and DELETE -- + support this ONLY notation. + + + + In some cases you may wish to know which table a particular tuple + originated from. There is a system column called + TABLEOID in each table which can tell you the + originating table: + + +SELECT c.tableoid, c.name, c.altitude +FROM cities c +WHERE c.altitude > 500; + + + which returns: + + + tableoid | name | altitude +----------+-----------+---------- + 139793 | Las Vegas | 2174 + 139793 | Mariposa | 1953 + 139798 | Madison | 845 + + + (If you try to reproduce this example, you will probably get different + numeric OIDs.) By doing a join with pg_class you can see the actual table + names: + + +SELECT p.relname, c.name, c.altitude +FROM cities c, pg_class p +WHERE c.altitude > 500 and c.tableoid = p.oid; + + + which returns: + + + relname | name | altitude +----------+-----------+---------- + cities | Las Vegas | 2174 + cities | Mariposa | 1953 + capitals | Madison | 845 + + + + + + Deprecated + + In previous versions of PostgreSQL, the + default was not to get access to child tables. This was found to + be error prone and is also in violation of SQL99. Under the old + syntax, to get the sub-tables you append * to the table name. + For example + +SELECT * from cities*; + + You can still explicitly specify scanning child tables by appending + *, as well as explicitly specify not scanning child tables by + writing ONLY. But beginning in version 7.1, the default + behavior for an undecorated table name is to scan its child tables + too, whereas before the default was not to do so. To get the old + default behavior, set the configuration option + SQL_Inheritance to off, e.g., + +SET SQL_Inheritance TO OFF; + + or add a line in your postgresql.conf file. + + + + + A limitation of the inheritance feature is that indexes (including + unique constraints) and foreign key constraints only apply to single + tables, not to their inheritance children. Thus, in the above example, + specifying that another table's column REFERENCES cities(name) + would allow the other table to contain city names but not capital names. + This deficiency will probably be fixed in some future release. + + + + + Modifying Tables + + + When you create a table and you realize that you made a mistake, + then you can drop the table and create it again. But this is not a + convenient option if the table is already filled with data, or if + the table is referenced by other database objects (for instance a + foreign key constraint). Therefore + PostgreSQL provides a family of commands + to make modifications on existing tables. + + + + You can + + + Add columns, + + + Add constraints, + + + Remove constraints, + + + Change default values, + + + Rename a column, + + + Rename the table. + + + + In the current implementation you cannot + + + Remove a column, + + + Change the data type of a column. + + + These may be possible in a future release. + + + + OK, now explain how to do this. There's currently so much activity + on ALTER TABLE that I'm holding off a bit. + + + + + Schemas + + to be filled in + + + + Other Database Objects + + + Tables are the central objects in a relational database structure, + because they hold your data. But they are not the only objects + that exist in a database. Many other kinds of objects can be + created to make the use and management of the data more efficient + or convenient. They are not discussed in this chapter, but we give + you a list here so that you are aware of what is possible. + + + + + + Views + + + + + + Functions, operators, data types, domains + + + + + + Triggers and rewrite rules + + + + + + + Dependency Tracking + + + When you create complex database structures involving many tables + with foreign key constraints, views, triggers, functions, etc. you + will implicitly create a net of dependencies between the objects. + For instance, a table with a foreign key constraint depends on the + table it references. + + + + To ensure the integrity of the entire database structure, + PostgreSQL makes sure that you cannot + drop objects that other objects still depend on. For example, + attempting to drop the products table we had considered in , with the orders table depending on + it, would result in an error message such as this: + +DROP TABLE products; +NOTICE: constraint $1 on table orders depends on table products +ERROR: Cannot drop table products because other objects depend on it + Use DROP ... CASCADE to drop the dependent objects too + + The error message contains a useful hint: If you don't want to + bother deleting all the dependent objects individually, you can run + +DROP TABLE products CASCADE; + + and all the dependent objects will be removed. Actually, this + doesn't remove the orders table, it only removes the foreign key + constraint. + + + + All drop commands in PostgreSQL support + specifying CASCADE. Of course, the nature of + the possible dependencies varies with the type of the object. You + can also write RESTRICT instead of + CASCADE to get the default behavior which is to + restrict drops of objects that other objects depend on. + + + + + According to the SQL standard, specifying either + RESTRICT or CASCADE is + required. No database system actually implements it that way, but + the defaults might be different. + + + + + diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml new file mode 100644 index 0000000000..5bcec13975 --- /dev/null +++ b/doc/src/sgml/dml.sgml @@ -0,0 +1,199 @@ + + + + Data Manipulation + + + This chapter is still quite incomplete. + + + + The previous chapter discussed how to create tables and other + structures to hold your data. Now it is time to fill the tables + with data. This chapter covers how to insert, update, and delete + table data. We also introduce ways to effect automatic data changes + when certain events occur: triggers and rewrite rules. The chapter + after this will finally explain how to extract your long-lost data + back out of the database. + + + + Inserting Data + + + When a table is created, it contains no data. The first thing to + do before a database can be of much use is to insert data. Data is + inserted one row at a time. This does not mean that there are no + means to bulk load many rows efficiently. But there + is no way to insert less than one row at a time. Even if you know + only some column values, a complete row must be created. + + + + To create a new row, use the INSERT command. + The command requires the table name and a value for each of the + columns of the table. For example, consider the products table + from : + +CREATE TABLE products ( + product_no integer, + name text, + price numeric +); + + An example command to insert a row would be: + +INSERT INTO products VALUES (1, 'Cheese', 9.99); + + The data values are listed in the order in which the columns appear + in the table, separated by commas. Usually, the data values will + be literals (constants), but scalar expressions are also allowed. + + + + The above syntax has the drawback that you need to know the order + of the columns in the table. To avoid that you can also list the + columns explicitly. For example, both of the following commands + have the same effect as the one above: + +INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99); +INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1); + + Many users consider it good practice to always list the column + names. + + + + If you don't have values for all the columns, you can omit some of + them. In that case, the columns will be filled with their default + values. For example, + +INSERT INTO products (product_no, name) VALUES (1, 'Cheese'); +INSERT INTO products VALUES (1, 'Cheese'); + + The second form is a PostgreSQL + extension. It fills the columns from the left with as many values + as are given, and the rest will be defaulted. + + + + For clarity, you can also request default values explicitly, for + individual columns or for the entire row: + +INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT); +INSERT INTO products DEFAULT VALUES; + + + + + + Updating Data + + + The modification of data that is already in the database is + referred to as updating. You can update individual rows, all the + rows in a table, or a subset of all rows. Each column can be + updated separately; the other columns are not affected. + + + + To perform an update, you need three pieces of information: + + + The name of the table and column to update, + + + + The new value of the column, + + + + Which row(s) to update. + + + + + + Recall from that SQL does not, in general, + provide a unique identifier for rows. Therefore it is not + necessarily possible to directly specify which row to update. + Instead, you specify which conditions a row must meet in order to + be updated. Only if you have a primary key in the table (no matter + whether you declared it or not) you can address rows individually + by choosing a condition that matches the primary key only. + Graphical database access tools rely on this fact to allow you to + update rows individually. + + + + For example, this command updates all products that have a price of + 5 to have a price of 10: + +UPDATE products SET price = 10 WHERE price = 5; + + This may cause zero, one, or many rows to be updated. It is not + an error to attempt an update that does not match any rows. + + + + Let's look at that command in detail: First is the key word + UPDATE followed by the table name. As usual, + the table name may be schema-qualified, otherwise it is looked up + in the path. Next is the key word SET followed + by the column name, an equals sign and the new column value. The + new column value can be any scalar expression, not just a constant. + For example, if you want to raise the price of all products by 10% + you could use: + +UPDATE products SET price = price * 1.10; + + As you see, the expression for the new value can also refer to the + old value. We also left out the WHERE clause. + If it is omitted, it means that all rows in the table are updated. + If it is present, only those rows that match the condition after + the WHERE are updated. Note that the equals + sign in the SET clause is an assignment while + the one in the WHERE clause is a comparison, but + this does not create any ambiguity. Of course, the condition does + not have to be an equality test. Many other operators are + available (see ). But the expression + needs to evaluate to a Boolean result. + + + + You can also update more than one column in an + UPDATE command by listing more than one + assignment in the SET clause. For example: + +UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0; + + + + + + Deleting Data + + + So far we have explained how to add data to tables and how to + change data. What remains is to discuss how to remove data that is + no longer needed. Just as adding data is only possible in whole + rows, you can only remove entire rows from a table. In the + previous section we discussed that SQL does not provide a way to + directly address individual rows. Therefore, removing rows can + only be done by specifying conditions that the rows to be removed + have to match. If you have a primary key in the table then you can + specify the exact row. But you can also remove groups of rows + matching a condition, or you can remove all rows in the table at + once. + + + + You use the DELETE command to remove rows; the + syntax is very similar to the UPDATE command. + For instance, to remove all rows from the products table that have a price of 10, use + +DELETE FROM products WHERE price = 10; + + + + diff --git a/doc/src/sgml/entities.sgml b/doc/src/sgml/entities.sgml new file mode 100644 index 0000000000..14f201fae6 --- /dev/null +++ b/doc/src/sgml/entities.sgml @@ -0,0 +1,20 @@ +PostgreSQL Administrator's Guide"> +PostgreSQL Developer's Guide"> +PostgreSQL Programmer's Guide"> +PostgreSQL Reference Manual"> +PostgreSQL Tutorial"> +PostgreSQL User's Guide"> + +]]> +"> +"> +"> +"> +"> +"> + +]]> diff --git a/doc/src/sgml/inherit.sgml b/doc/src/sgml/inherit.sgml deleted file mode 100644 index a0d27b0768..0000000000 --- a/doc/src/sgml/inherit.sgml +++ /dev/null @@ -1,188 +0,0 @@ - - - - Inheritance - - - Let's create two tables. The capitals table contains - state capitals which are also cities. Naturally, the - capitals table should inherit from cities. - - -CREATE TABLE cities ( - name text, - population float, - altitude int -- (in ft) -); - -CREATE TABLE capitals ( - state char(2) -) INHERITS (cities); - - - In this case, a row of capitals inherits all - attributes (name, population, and altitude) from its - parent, cities. The type of the attribute name is - text, a native PostgreSQL type for variable length - ASCII strings. The type of the attribute population is - float, a native PostgreSQL type for double precision - floating-point numbers. State capitals have an extra - attribute, state, that shows their state. In PostgreSQL, - a table can inherit from zero or more other tables, - and a query can reference either all rows of a - table or all rows of a table plus all of its - descendants. - - - - The inheritance hierarchy is actually a directed acyclic graph. - - - - - - For example, the following query finds the names of all cities, - including state capitals, that are located at an altitude - over 500ft: - - -SELECT name, altitude - FROM cities - WHERE altitude > 500; - - - which returns: - - - name | altitude ------------+---------- - Las Vegas | 2174 - Mariposa | 1953 - Madison | 845 - - - - - On the other hand, the following query finds - all the cities that are not state capitals and - are situated at an altitude over 500ft: - - -SELECT name, altitude - FROM ONLY cities - WHERE altitude > 500; - - name | altitude ------------+---------- - Las Vegas | 2174 - Mariposa | 1953 - - - - - Here the ONLY before cities indicates that the query should - be run over only cities and not tables below cities in the - inheritance hierarchy. Many of the commands that we - have already discussed -- SELECT, - UPDATE and DELETE -- - support this ONLY notation. - - - - In some cases you may wish to know which table a particular tuple - originated from. There is a system column called - TABLEOID in each table which can tell you the - originating table: - - - SELECT c.tableoid, c.name, c.altitude - FROM cities c - WHERE c.altitude > 500; - - - which returns: - - - tableoid | name | altitude -----------+-----------+---------- - 139793 | Las Vegas | 2174 - 139793 | Mariposa | 1953 - 139798 | Madison | 845 - - - (If you try to reproduce this example, you will probably get different - numeric OIDs.) By doing a join with pg_class you can see the actual table - names: - - - SELECT p.relname, c.name, c.altitude - FROM cities c, pg_class p - WHERE c.altitude > 500 and c.tableoid = p.oid; - - - which returns: - - - relname | name | altitude -----------+-----------+---------- - cities | Las Vegas | 2174 - cities | Mariposa | 1953 - capitals | Madison | 845 - - - - - - Deprecated - - In previous versions of PostgreSQL, the - default was not to get access to child tables. This was found to - be error prone and is also in violation of SQL99. Under the old - syntax, to get the sub-tables you append * to the table name. - For example - -SELECT * from cities*; - - You can still explicitly specify scanning child tables by appending - *, as well as explicitly specify not scanning child tables by - writing ONLY. But beginning in version 7.1, the default - behavior for an undecorated table name is to scan its child tables - too, whereas before the default was not to do so. To get the old - default behavior, set the configuration option - SQL_Inheritance to off, e.g., - -SET SQL_Inheritance TO OFF; - - or add a line in your postgresql.conf file. - - - - - A limitation of the inheritance feature is that indexes (including - unique constraints) and foreign key constraints only apply to single - tables, not to their inheritance children. Thus, in the above example, - specifying that another table's column REFERENCES cities(name) - would allow the other table to contain city names but not capital names. - This deficiency will probably be fixed in some future release. - - - - - -- 2.40.0