PostgreSQL tutorial (1): Detailed description of data tables and detailed description of postgresql

PostgreSQL tutorial (1): Detailed description of data tables and detailed description of postgresql

I. Table definition:

For any relational database, tables are the core and basic object units of data storage. Now let's start from here.
 1. Create a table:
 Copy codeThe Code is as follows:
Create table products (
Product_no integer,
Name text,
Price numeric
);

2. delete a table:
 Copy codeThe Code is as follows:
Drop table products;
 

3. Create a table with default values:
 Copy codeThe Code is as follows:
Create table products (
Product_no integer,
Name text,
Price numeric DEFAULT 9.99 -- DEFAULT is the keyword, and the subsequent value 9.99 is the DEFAULT value of the field price.
);

Create table products (
Product_no SERIAL, -- the SERIAL type field indicates that this field is an auto-increment field, which is equivalent to Sequence in Oracle.
Name text,
Price numeric DEFAULT 9.99
);
 
Output:
 Copy codeThe Code is as follows:
NOTICE: create table will create implicit sequence "products_product_no_seq" for serial column "products. product_no"

4. constraints:
Check constraints are the most common constraints in a table. They allow you to declare that the values in a field must meet a Boolean expression. In addition, we can also declare table-Level Check constraints.
 Copy codeThe Code is as follows:
Create table products (
Product_no integer,
Name text,
-- The value of the price field must be greater than 0. Otherwise, a violation error occurs when the value of this field is inserted or modified. It should also be noted that this check Constraint
-- It is an anonymous constraint, that is, this constraint is not displayed when the table is defined, so that PostgreSQL will be based on the current table name, field name, and constraint type,
-- Automatically name the constraint, for example, products_price_check.
Price numeric CHECK (price> 0)
);

Create table products (
Product_no integer,
Name text,
-- The check constraint for this field is displayed and named positive_price. In this way, you can directly perform operations based on the name when maintaining the constraint in the future.
Price numeric CONSTRAINT positive_price CHECK (price> 0)
);
 
The following constraints are non-empty constraints, that is, the constraint fields cannot be inserted with null values, or existing data is updated to null values.
 Copy codeThe Code is as follows:
Create table products (
Product_no integer not null,
Name text not null,
Price numeric
);
 
If a field contains multiple constraints, you do not need to consider the declared order of constraints during definition.
 Copy codeThe Code is as follows:
Create table products (
Product_no integer not null,
Name text not null,
Price numeric not null check (price> 0)
);
 
Uniqueness constraint, that is, the specified field cannot insert duplicate values, or update the value of a record to an existing value in the current table.
 Copy codeThe Code is as follows:
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 the table.
 Copy codeThe Code is as follows:
Create table example (
A integer,
B integer,
C integer,
UNIQUE (a, c)
);
 
Name the uniqueness constraint.
 Copy codeThe Code is as follows:
Create table products (
Product_no integer CONSTRAINT must_be_different UNIQUE,
Name text,
Price numeric
);
 
When data is inserted, NULL values are considered to be unequal. Therefore, for a unique field, NULL values can be inserted multiple times. However, it should be noted that this rule is not followed by all databases, so it may cause some trouble during database transplantation.

5. Primary Key and foreign key:
Technically speaking, primary key constraints are only a combination of unique constraints and non-empty constraints.
 Copy codeThe Code is as follows:
Create table products (
Product_no integer primary key, -- the field product_no is defined as the unique primary key of the table.
Name text,
Price numeric
);
 
Like the uniqueness constraint, a primary key can act on multiple fields at the same time to form a joint primary key:
 Copy codeThe Code is as follows:
Create table example (
A integer,
B integer,
C integer,
Primary key (B, c)
);
 
The value of a field (or a set of fields) declared by the foreign key constraint must match the value of some rows in another table. We define this behavior as the reference integrity between two related tables.
 Copy codeThe Code is as follows:
Create table orders (
Order_id integer primary key, -- this table can also have its own primary key.
-- The product_no field of the table is the foreign key of the primary key (product_no) of the products table above.
Product_no integer REFERENCES products (product_no ),
Quantity integer
);

Create table t1 (
A integer primary key,
B integer,
C integer,
-- The number of fields for 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, because the primary key field value of the row record may be associated with a record in the referenced table (foreign key table), the deletion operation will fail. To complete this operation, you can delete the rows associated with the record in the referenced table before deleting the Row Records in the referenced table. However, it should be noted that PostgreSQL provides us with a more convenient way to complete such operations.
 Copy codeThe Code is as follows:
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, -- Restriction Option
Order_id integer REFERENCES orders on delete cascade, -- cascade delete option
Quantity integer,
Primary key (product_no, order_id)
);
 
Restriction and cascading deletion are two of the most common options. RESTRICT prohibits the deletion of referenced rows. No action indicates that an error is thrown if any reference row exists while checking the constraints. If you do not declare anything, it is the default ACTION. (The actual difference between the two options is that no action allows the constraints to be checked to be postponed until the transaction is later, but RESTRICT does not work .) CASCADE declares that when a referenced row is deleted, the row that references it will also be deleted automatically. The action on the foreign key field has two options: set null and set default. In this way, when the referenced rows are deleted, the fields that reference them are set to null or the default values. Note that these options do not allow you to escape observation and constraints. For example, if an action declares set default, but the DEFAULT value does not meet the foreign key, the action fails. Similar to on delete and the on update option, it is called when the referenced field is modified (updated. The available actions are the same.

Ii. System fields:

Each data table in PostgreSQL contains several implicitly defined system fields. Therefore, these names cannot be used for user-defined field names. The functions of these system fields are similar to rownum and rowid in Oracle.
Oid: the Object ID of the row ). This field only appears when the table is created using with oids, or when the configuration parameter default_with_oids is set. This field is of the oid type (with the same name as the field ).
Tableoid: The OID of the table containing the row. This field is particularly useful for queries selected from the inheritance level, because without it, it is difficult to describe which independent table a row comes from. Tableoid can be connected to the oid field of pg_class to obtain the table name.
Xmin: The identifier (transaction ID) of the transaction that inserts this row ).
Cmin: The command identifier (starting from scratch) in the insert transaction ).
Xmax: ID of the transaction to be deleted. If it is not the row version to be deleted, it is zero.
Cmax: The command identifier inside the transaction to be deleted, or zero.
Ctid: physical location of a row version in its table. Note that although ctid can be used to quickly locate the row version, the ctid of a row is updated or moved every time VACUUM is FULL. Therefore, ctid cannot be a long-term row identifier.
OID is a 32-bit volume, which is assigned a value on a universal counter in the same cluster. For a large or long-time database, this counter may overlap. Therefore, it is very wrong to assume that the OID is unique unless you take measures to ensure that they are unique. If you need to identify rows in the table, we strongly recommend using the serial number generator.

Iii. Table modification:

1. Add a field:
 Copy codeThe Code is as follows:
Alter table products add column description text;
 
The new field will first fill in the given DEFAULT value for existing rows in the table (if you do not declare the DEFAULT clause, the DEFAULT value is null ).
When adding a field, you can specify constraints for the field.
 Copy codeThe Code is as follows:
Alter table products add column description text CHECK (description <> '');

2. delete a field:
 Copy codeThe Code is as follows:
Alter table products drop column description;
 
If the table is a referenced table and the field is a referenced field, the above deletion operation will fail. If you want to cascade the deletion of all referenced fields while deleting the referenced fields, you can use the following syntax.
 Copy codeThe Code is as follows:
Alter table products drop column description CASCADE;

3. Add constraints:
 Copy codeThe Code is as follows:
Alter table products add check (name <> ''); -- adds a TABLE-level constraint.
Alter table products add constraint some_name UNIQUE (product_no); -- adds the naming uniqueness CONSTRAINT.
Alter table products add foreign key (pdt_grp_id) REFERENCES pdt_grps; -- adds a foreign key constraint.
Alter table products alter column product_no set not null; -- adds a non-empty constraint.

4. Delete constraints:
 Copy codeThe Code is as follows:
Alter table products drop constraint some_name;
 
For display naming constraints, you can directly delete them based on their names. For implicit naming constraints, you can use psql's \ d tablename to obtain the name of the constraint. Like deleting a field, if you want to delete a field that is subject to dependency constraints, you need to use CASCADE. In one example, a foreign key constraint depends on the unique constraint or primary key constraint on the referenced field. For example:
 Copy codeThe Code is as follows:
MyTest = # \ d products
Table "public. products"
Column | Type | Modifiers
------------ + --------- + -----------
Product_no | integer |
Name | text |
Price | numeric |
Check constraints:
"Positive_price" CHECK (price> 0: numeric)

Unlike other constraints, non-empty constraints have no names, so they can only be deleted as follows:
 Copy codeThe Code is as follows:
Alter table products alter column product_no drop not null;

5. Change the default value of the field:
Adding a default value for an existing field does not affect the existing data rows in any table. It only changes the default value for the INSERT command in the future.
 Copy codeThe Code is as follows:
Alter table products alter column price set default 7.77;
 
The default values for deletion are as follows:
 Copy codeThe Code is as follows:
Alter table products alter column price DROP DEFAULT
 

6. Modify the Data Type of a field:
Only when each existing item in a field can be converted to a new type with an implicit type can it be successful. For example, if the current data type is integer and the target type of the conversion is numeric or varchar, the conversion is generally successful. At the same time, PostgreSQL will try to convert the default value (if any) of the field to a new type, and there are any constraints related to this field. However, these conversions may fail or may generate strange results. Before modifying a field type, you 'd better delete those constraints and add them manually modified.
 Copy codeThe Code is as follows:
Alter table products alter column price TYPE numeric (10, 2 );

7. Modify the field name:
 Copy codeThe Code is as follows:
Alter table products rename column product_no TO product_number;

8. Modify the table name:
 Copy codeThe Code is as follows:
Alter table products rename to items;

Iv. Permissions:

Only the table owner can modify or delete the table. To GRANT a permission, we use the GRANT command to REVOKE the permission and use the REVOKE command.
PUBLIC is a special "user" that can be used to grant permissions to every user in the system. Write ALL at the declared permission location to grant ALL permissions related to the object type.
 Copy codeThe Code is as follows:
Grant update on table_name TO user; -- GRANT the table UPDATE permission TO the specified user.
Grant select on table_name to group group; -- GRANT the select permission of the table TO the specified GROUP.
Revoke all on table_name from public; -- revoke all table permissions FROM Public.
 
Initially, only the object owner (or Super User) can grant or revoke the object's permissions. However, we can assign a "with grant option" permission to the person who accepts the permission to grant the permission to others. If the granted option is subsequently revoked, all users who have received the permission from the recipient (directly or through the level-connected authorization) will lose this permission.

It is worth noting that most of the cases and paragraphs in this blog are taken from the PostgreSQL Chinese document. If you repost this series of blogs, please also indicate the source.