PostgreSQL learning Manual (data table)

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:
Create TableProducts (
Product_no integer,
Name text,
Price numeric
);

2. delete a table:
Drop tableProducts;

3. Create a table with default values:
Create TableProducts (
Product_no integer,
Name text,
Price numericDefault9.99-- Default is the keyword, and the value 9.99 is the default value of the field price.
);

Create TableProducts (
Product_noSerial,-- The serial field indicates that this field is an auto-incrementing field, which is equivalent to sequence in Oracle..
Name text,
Price numericDefault9.99
);
Output:
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.
Create TableProducts (
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 numericCheck(Price> 0)
);

Create TableProducts (
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 numericConstraintPositive_priceCheck(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.
Create TableProducts (
Product_no integerNot null,
Name textNot null,
Price numeric
);
If a field contains multiple constraints, you do not need to consider the declared order of constraints during definition.
Create TableProducts (
Product_no integerNot null,
Name textNot null,
Price numericNot 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.
Create TableProducts (
Product_no integerUnique,
Name text,
Price numeric
);

Create TableProducts (
Product_no integer,
Name text,
Price numeric,
Unique(Product_no)
);
Defines joint Uniqueness for multiple fields in the table.
Create TableExample (
A integer,
B integer,
C integer,
Unique(A, c)
);
Name the uniqueness constraint.
Create TableProducts (
Product_no integerConstraintMust_be_differentUnique,
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.
Create TableProducts (
Product_no integerPrimary 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:
Create TableExample (
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.
Create TableOrders (
Order_id integerPrimary Key,-- The 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 integerReferencesProducts (product_no ),
Quantity integer
);

Create TableT1 (
A integerPrimary 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)ReferencesExample (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.
Create TableProducts (
Product_no integerPrimary Key,
Name text,
Price numeric
);

Create TableOrders (
Order_id integerPrimary Key,
Shipping_address text
);

Create TableOrder_items (
Product_no integerReferencesProductsOn Delete restrict,-- Restriction options
Order_id integerReferencesOrdersOn 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: Object ID of a 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: Oid that contains the table of 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 is inserted to this row ).
Cmin: The command identifier (starting from scratch) in the insert transaction ).
Xmax: The identifier (transaction ID) of the delete transaction. 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:
ALTER TABLEProductsAdd ColumnDescription 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.
ALTER TABLEProductsAdd ColumnDescription textCheck(Description <> '');

2. delete a field:
ALTER TABLEProductsDrop ColumnDescription;
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.
ALTER TABLEProductsDrop ColumnDescriptionCascade;

3. Add constraints:
ALTER TABLEProductsAdd check(Name <> '');-- Adds a table-level constraint.
ALTER TABLEProductsAdd ConstraintSome_nameUnique(Product_no );-- Adds the naming uniqueness constraint.
ALTER TABLEProductsAdd foreign key(Pdt_grp_id)ReferencesPdt_grps;-- Adds a foreign key constraint.
ALTER TABLEProductsAlter ColumnProduct_noSet not null;-- Adds a non-empty constraint.

4. Delete constraints:
ALTER TABLEProductsDrop ConstraintSome_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:
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:
ALTER TABLEProductsAlter ColumnProduct_noDrop 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.
ALTER TABLEProductsAlter ColumnPriceSet Default7.77;
The default values for deletion are as follows:
ALTER TABLEProducts Alter Column PriceDrop 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.
ALTER TABLEProductsAlter ColumnPriceTypeNumeric (10, 2 );

7. Modify the field name:
ALTER TABLEProductsRename ColumnProduct_noToProduct_number;

8. Modify the table name:
ALTER TABLEProductsRenameItems;

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.
Grant update onTable_nameToUser;-- Grant the table update permission to the specified user.
Grant select onTable_nameTo GroupGroup;-- Grant the select permission of the table to the specified group.
Revoke all onTable_nameFromPublic;-- 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 "With grant option" In this way, the person who accepts the permission is granted 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.

note that most cases and paragraphs in this blog are taken from the PostgreSQL Chinese document, if you reprint this series of blogs, please also indicate the source.