PostgreSQL Tutorial (i): Detailed data Sheet _postgresql

Definition of a table:

For any relational database, tables are the most core and basic object unit of data storage. Now let's start from here.
1. Create a table:

code as follows:

CREATE TABLE Products (
Product_no Integer,
Name text,
Price numeric
);

2. Delete table:

code as follows:

DROP TABLE Products;

3. Create a table with default values:

code as follows:

CREATE TABLE Products (
Product_no Integer,
Name text,
Price numeric default 9.99--default is the keyword, followed by a value of 9.99, which is the default value for field price.
);

CREATE TABLE Products (
Product_no Serial, the field of the--serial type represents the field from which it is added, exactly equivalent to the sequence in Oracle.
Name text,
Price Numeric DEFAULT 9.99
);

The output is:

 code as follows:

Notice:create TABLE would CREATE implicit sequence "Products_product_no_seq" for serial column "Products.product_no"

4. Constraints:
A check constraint is the most common constraint type in a table, and it 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.

 code as follows:

CREATE TABLE Products (
Product_no Integer,
Name text,
The value of the--price field must be greater than 0, or a violation error will be raised if the field value is inserted or modified. It also needs to be explained that the check constraint
--An anonymous constraint that does not display a named constraint at the table definition, so that PostgreSQL will be based on the current table name, field name, and constraint type.
--Automatically named for this constraint, 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 displayed named Positive_price. The advantage of this is that when you maintain the constraint in the future, you can do it directly based on that name.
Price numeric CONSTRAINT positive_price CHECK (Price > 0)
);

The following constraint is a non-empty constraint, that is, the field of the constraint cannot be inserted into a null value, or the existing data is updated to a null value. 

 code as follows:

CREATE TABLE Products (
Product_no Integer Not NULL,
Name text not NULL,
Price numeric
);

If you have more than one constraint in a field, you can define it without considering the declaration order of the constraint.

code 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 a duplicate value, or the value of a record is updated to an existing value in the current table.

code 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 union uniqueness for multiple fields in a table.

 code as follows:

CREATE TABLE Example (
A integer,
b Integer,
C Integer,
UNIQUE (A, c)
);

Name the uniqueness constraint.

 code as follows:

CREATE TABLE Products (
Product_no integer CONSTRAINT must_be_different UNIQUE,
Name text,
Price numeric
);

When inserting data, null values (NULL) are treated as unequal data, so you can insert a null value multiple times for a unique field. However, it is important to note that this rule is not adhered to by all databases, so it can be a problem when porting a database.

5. Primary KEY and foreign key:
Technically, a PRIMARY key constraint is simply a combination of a unique constraint and a non-empty constraint.

code 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
);

As with uniqueness constraints, a primary key can work on more than one field at a time, forming a federated primary key:

code as follows:

CREATE TABLE Example (
A integer,
b Integer,
C Integer,
PRIMARY KEY (b, c)
);

A FOREIGN key constraint declares that the value of a field (or a group of fields) must match the number of rows in another table. We call this behavior the referential integrity between the two related tables.

 code as follows:

CREATE TABLE Orders (
order_id integer PRIMARY Key--This table can also have its own primary key.
--The Product_no field for this table is the foreign key for 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 in the foreign key and the amount of primary key in the referenced table must be consistent.
FOREIGN KEY (b, c) REFERENCES example (b, C)
);

When a referential constraint relationship exists between multiple tables for a primary foreign key, the If you want to delete a row record in the applied table (primary key table), the delete operation will fail because the primary key field value of the row record may be being associated with a record in its referencing table (foreign key table). An obvious way to do this is to delete the reference table and the rows associated with the record before deleting the row record in the referenced table. It should be explained, however, that PostgreSQL provides us with a more convenient way to complete such operations.

 code 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 options
order_id integer REFERENCES orders on delete CASCADE--Cascading deletion options
Quantity Integer,
PRIMARY KEY (Product_no, order_id)
);

Restrictions and cascading deletes are two of the most common options. RESTRICT prohibit the deletion of referenced rows. No action means that if the constraint is checked, an error is thrown if there is any reference row, and if you do not 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 automatically deleted as well. The action on the foreign key field also has two options: Set NULL and set DEFAULT. This causes the fields that reference them to be set to NULL or default values when the referenced row is deleted. Please note that these options do not allow you to escape being observed and bound. For example, if an action declares SET default, but the default value does not satisfy the foreign key, then the action fails. Similar to on DELETE and the On Update option, which is invoked when the referenced field is modified (updated). The available actions are the same.

Second, the system field:

Each data table in the

    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 somewhat similar to those of RowNum and rowID in Oracle.
    OID: The object identifier (object ID) of the row. This field occurs only when the table is created using the with OIDs, or when the configuration parameter default_with_oids is set. The type of this field is the OID (with the same name as the field).
    tableoid: The OID of the table that contains the bank. This field is particularly useful for queries that are selected from the inheritance hierarchy, because without it, it is difficult to tell which separate row comes from. Tableoid can be connected to the OID field of Pg_class to get the table name.
    xmin: The identity (transaction ID) of the transaction that inserted the row version.
    Cmin: The identity of the command (zero-based) that is inside the insert transaction.
    Xmax: Deletes the identity (transaction ID) of the transaction, or zero if it is not the row version that was deleted.
    Cmax: The command identifier, or zero, within the delete transaction.
    Ctid: The physical location of a row version within the table in which it is located. Note that although Ctid can be used to locate row versions very quickly, the ctid of a row will be updated or moved every time vacuum full. Therefore, Ctid cannot be used as a long line identifier.    
    OID is a 32-bit amount that is assigned on a common counter in the same cluster. For a large or long time database, this counter is likely to overlap. Therefore, it is very wrong to assume that the OID is the only one, unless you have taken steps to ensure that they are unique. If you need to identify the rows in the table, we strongly recommend using the serial number generator.     &NBSP
   
Three, table modifications:

1. Add fields:

 code as follows:

ALTER TABLE Products ADD COLUMN description text;

The new field will initially populate the given default value for rows that already exist in the table (if you do not declare a default clause, the default is null).
When you add a new field, you can assign the constraint to the field at the same time.

 code as follows:

ALTER TABLE Products ADD COLUMN Description text CHECK (description <> ');

2. Delete fields:

 code as follows:

ALTER TABLE Products DROP COLUMN description;

If the table is a referenced table and the field is referenced, the deletion above will fail. If you want to delete all of its reference fields while deleting the referenced field, you can use the following syntax form.

code as follows:

ALTER TABLE Products DROP COLUMN description CASCADE;

3. Increase the constraint:

code as follows:

ALTER TABLE Products ADD CHECK (name <> '); --Adding a table-level constraint
ALTER TABLE Products Add CONSTRAINT some_name unique (product_no);--Increase the uniqueness of a named constraint.
ALTER TABLE Products ADD FOREIGN KEY (pdt_grp_id) REFERENCES Pdt_grps; --Increase the foreign key constraint.
Alter TABLE products ' alter COLUMN product_no SET not NULL; --Add a Non-empty constraint.

4. Delete constraint:

 code as follows:

ALTER TABLE Products DROP CONSTRAINT some_name;

For display-named constraints, you can delete them directly according to their names, and for implicit automatic naming constraints, you can get the name of the constraint by Psql's \d tablename. As with deleting fields, if you want to delete a dependency constraint, you need to use cascade. An example is a FOREIGN key constraint that relies on a unique constraint on the referenced field or a PRIMARY key constraint. Such as:

 code 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, a Non-empty constraint has no name and can only be deleted in the following way:

 code as follows:

Alter TABLE, products alter COLUMN product_no DROP not NULL;

5. Change the default value of the field:
When you add a default value to an existing field, it does not affect the rows of data that are already in any table, it simply changes the default value for the future insert command.

 code as follows:

Alter TABLE, Products alter COLUMN, price SET DEFAULT 7.77;

The following is the deletion of the default values:

 code as follows:

Alter TABLE products ALTER COLUMN price DROP DEFAULT

6. Modify the data type of the field:
It is possible to succeed only if every item existing in a field can be converted to a new type with an implied type conversion. For example, the current data is integral type, and the conversion of the target type is numeric or varchar, such conversion can generally be successful. At the same time, PostgreSQL will try to convert the default value of the field (if it exists) to the new type, as well as any constraints that involve the field. However, these conversions may fail or may produce strange results. Before you modify a field type, you'd better remove those constraints and then add them manually.

 code as follows:

Alter TABLE products, alter COLUMN price TYPE numeric (10,2);

7. Modify Field Name:

 code as follows:

ALTER TABLE Products RENAME COLUMN product_no to Product_number;

8. Modify the table name:

 code as follows:

ALTER TABLE products RENAME to items;

Four, the authority:

Only the owner of the table can modify or delete permissions for the table. To give a permission, we use the GRANT command to revoke a permission, using the REVOKE command.
It should be noted that public is a special "user" that can be used to give permissions to each user in the system. Write all at the location where the permission is declared assigns all the permissions associated with that object type.

 code as follows:

GRANT UPDATE on table_name to user; --assigns the table's update permissions to the specified user.
GRANT SELECT on TABLE_NAME to group group; --Assigns a table's SELECT permission to the specified group.
REVOKE all in table_name from public; --Revoke all permissions for a table from public.

Initially, only the object owner (or Superuser) can grant or revoke permissions on the object. However, we can give a "with GRANT option" permission to the person who accepts the permission to grant the permission to the other person. If the grant option is subsequently revoked, then all users who have received permission from this recipient (either directly or through a cascade authorization) will lose that permission.

In particular, it is necessary to note that most of the cases and paragraphs in the blog are taken from PostgreSQL Chinese documents, such as reprinted this series of blogs, please also indicate the source.