PostgreSQL tutorial of I: detailed data table
- 2020-05-06 11:54:45
- OfStack
1. Definition of table:
For any relational database, tables are the most central and fundamental object units of data storage. Let's start here.
1. Create a table:
CREATE TABLE products (
product_no integer,
name text,
price numeric
);
2. Delete the table:
DROP TABLE products;
3. Create a table with default values:
CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99 --DEFAULT Is the keyword, the value after that 9.99 Is a field price The default value.
);
CREATE TABLE products (
product_no SERIAL, --SERIAL A field of type indicates that the field is a self-increment field and is exactly the same Oracle In the Sequence .
name text,
price numeric DEFAULT 9.99
);
The output of is:
NOTICE: CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no"
4. Constraints:
The check constraint is the most common type of constraint in a table, which allows you to declare that a value in a field must satisfy a Boolean expression. Not only that, we can also declare table-level check constraints.
CREATE TABLE products (
product_no integer,
name text,
--price The value of the field must be greater than 0 Otherwise, when inserting or modifying the value of this field is, an illegal error will be raised. It is also important to note that the check constraint
-- Is an anonymous constraint that is not named when the table is defined PostgreSQL Depending on the current table name, field name, and constraint type,
-- Name the constraint automatically, such as: products_price_check .
price numeric CHECK (price > 0)
);
CREATE TABLE products (
product_no integer,
name text,
-- The check constraint for this field is shown and named as positive_price . This is best done because in the future, when the constraint is maintained, you can operate directly on the name.
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
The constraint below is a non-null constraint, meaning that the field of the constraint cannot be inserted with a null value, or that the existing data can be updated with a null value.
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
if there are more than one constraint in a field, you can define it without considering the order in which the constraints are declared.
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0)
);
uniqueness constraint that the specified field cannot be inserted with a duplicate value, or that the value of a record can be updated to an existing value in the current table.
CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric
);
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);
defines joint uniqueness for multiple fields in a table.
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
names the uniqueness constraint.
CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);
When inserts data, null values (NULL) are treated as unequal data, so null values can be inserted multiple times for a unique field. It is important to note, however, that this rule is not followed by all databases, so database migration can cause some problems.
5. Primary and foreign keys:
technically, the primary key constraint is just a combination of unique and non-null constraints.
DROP TABLE products;
like the uniqueness constraint, the primary key can act on multiple fields at the same time to form a joint primary key:
DROP TABLE products;
The foreign key constraint states that the value of a field (or group of fields) must match the value of some rows in another table. We call this behavior referential integrity between two related tables.
CREATE TABLE orders (
order_id integer PRIMARY KEY, -- The table can also have its own primary key.
-- The table product_no The field is above products Table primary key (product_no) The foreign key.
product_no integer REFERENCES products(product_no),
quantity integer
);
CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
-- The number of fields in the foreign key must be the same as the number of primary keys in the referenced table.
FOREIGN KEY (b, c) REFERENCES example (b, c)
);
If you want to delete a row record in the applied table (primary key table) when there is a reference constraint relationship between multiple tables with primary foreign keys, the delete operation will fail because the primary key field value of the row record may be associated with a record in its reference table (foreign key table). If you want to do this, the obvious way to do it is to delete the row associated with the record in the referenced table, and then delete the row in the referenced table. However, it should be noted that PostgreSQL provides us with a more convenient way to do this.
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, -- Limit the options
order_id integer REFERENCES orders ON DELETE CASCADE, -- Cascading delete options
quantity integer,
PRIMARY KEY (product_no, order_id)
);
restriction and cascading deletion are the two most common options. RESTRICT prohibits deleting referenced lines. NO ACTION means to throw an error if any reference lines still exist while checking the constraint. If you don't declare anything, it is the default behavior. (the real difference between these two choices is that NO ACTION allows constraint checking to be deferred until later in the transaction, while RESTRICT does not.) CASCADE declares that when a referenced row is deleted, the line that references it is also automatically deleted. There are also two options for actions on the outer key field: SET NULL and SET DEFAULT. This causes the fields referenced by the referenced rows to be set to null or default when they are deleted. Note that these options do not allow you to escape being observed and restrained. For example, if an action declares SET DEFAULT, but the default does not satisfy the foreign key, the action fails. Similar to ON DELETE, there is the ON UPDATE option, which is called when the referenced field is modified (updated). The available actions are the same.
system field:
Each data table in PostgreSQL contains several implicitly defined system fields. Therefore, these names cannot be used for user-defined field names. The function of these system fields is somewhat similar to rownum and rowid in Oracle.
oid: line object identifier (object ID). This field appears only if WITH OIDS is used when the table is created, or if the configuration parameter default_with_oids is set. The type of this field is oid(the same name as the field).
tableoid: OID containing the table of the row. This field is especially useful for queries that are selected from the inheritance hierarchy, because without it it is difficult to say which separate table a row comes from. tableoid can be concatenate with the oid field of pg_class to get the table name.
xmin: insert the identity of the transaction for the version of the row (transaction ID).
cmin: command id inside the insert transaction (starting from zero).
xmax: deletes the identity of the transaction (transaction ID), which is zero if it is not the deleted row version.
cmax: command identifier inside the delete transaction, or zero.
ctid: the physical location of a row version within the table in which it resides. Note that although ctid can be used to locate the row version very quickly, a row ctid is updated or moved each time after VACUUM FULL. Therefore, ctid cannot be used as a long-term line identifier.
OID is a 32-bit quantity assigned to a common counter in the same cluster. For a large or long-running database, this counter may overlap. Therefore, it is very wrong to assume that OID are unique, unless you have taken steps to ensure that they are. If you need to identify rows in a table, we strongly recommend using a sequence number generator.
3. Modification of the table:
1. Add field:
DROP TABLE products;
The newly added fields in will initially be populated with the default values given for existing rows in the table (if you do not declare an DEFAULT clause, the default is null).
When a field is added, can specify a constraint on that field at the same time.
DROP TABLE products;
2. Delete field:
DROP TABLE products;
If the table is a referenced table and the field is a referenced field, the delete operation above will fail. If you want to cascade all referenced fields while deleting the referenced fields, you can use the following syntax.
DROP TABLE products;
3. Add constraints:
DROP TABLE products;
4. Delete constraint:
DROP TABLE products;
can be directly deleted by its name for a constraint that shows a name, and for an implicitly autonamed constraint, the name of the constraint can be obtained by using psql's \d tablename. As with deleting fields, if you want to remove a constraint with a dependency, you need to use CASCADE. An example is a foreign key constraint that depends on a unique or primary key constraint on a referenced field. Such as:
CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99 --DEFAULT Is the keyword, the value after that 9.99 Is a field price The default value.
);
CREATE TABLE products (
product_no SERIAL, --SERIAL A field of type indicates that the field is a self-increment field and is exactly the same Oracle In the Sequence .
name text,
price numeric DEFAULT 9.99
);
unlike other constraints, the non-empty constraint does not have a name, so it can only be removed by:
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
5. Change the default value of the field:
When adds default values to existing fields, it does not affect any existing rows of data in the table; it simply changes the default values for future INSERT commands.
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
Delete the default value below :
ALTER TABLE products ALTER COLUMN price DROP DEFAULT
6. Modify the data type of the field:
will only succeed if each of the existing items in the field can be converted to a new type with an implicit type cast. For example, if the current data is all integer, and the target type of the conversion is numeric or varchar, the conversion will generally succeed. At the same time, PostgreSQL will also attempt to convert the default value (if any) of the field to a new type, as well as any constraints on the field. But these transformations may fail, or may produce strange results. Before you modify a field type, it is best to remove the constraints and then add the ones you have manually modified.
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
7. Modify the field name:
ALTER TABLE products RENAME COLUMN product_no TO product_number;
8. Change the table name:
ALTER TABLE products RENAME TO items;
4. Permission:
Only the owner of the table can modify or delete the permissions of the table. To give a permission, we use the GRANT command. To revoke a permission, we use the REVOKE command.
it should be noted that PUBLIC is a special "user" that can be used to grant privileges to every user in the system. Writing ALL where the permission is declared gives all permissions associated with the object type.
CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99 --DEFAULT Is the keyword, the value after that 9.99 Is a field price The default value.
);
CREATE TABLE products (
product_no SERIAL, --SERIAL A field of type indicates that the field is a self-increment field and is exactly the same Oracle In the Sequence .
name text,
price numeric DEFAULT 9.99
);
Initially, only the object owner (or superuser) can grant or revoke permissions to an object. However, we can give an "with grant option" permission, which gives the person who receives the permission permission to grant it to someone else. If the grant option is later revoked, all those users who have received permission from this recipient (either directly or through cascading authorization) will lose that permission.
it is important to note that most of the cases and paragraphs in this blog are taken from PostgreSQL Chinese documents. Please note the same source if you reprint this series of blogs.