PostgreSQL table operation table creation and summary of basic table syntax

PostgreSQL table type

PostgreSQL supports the following table types:

Regular Table: This is the most commonly used table type, used to store data.
Temporary Table: These tables only exist in the current session and are automatically deleted after the session ends. Temporary tables are usually used to store intermediate results or temporary data.
View: A view is a virtual table that is created by querying existing tables or other views. Views provide a way to simplify data access and can be used as a filter for data.
External Table: These tables do not actually store data, but establish connections with external data sources (such as files or remote databases) to read and query data.
Partitioned Table: A partitioned table divides data into multiple smaller subtables, each subtable is called a partition. Each partition can be queried and managed independently, thereby improving query performance.
Explicitly Locked Table: This type of table is locked when performing certain operations to prevent other sessions from reading or modifying them. Lock tables are used to handle concurrent access issues.
Replicated Table: A replicated table is a table that provides data redundancy and high availability by replicating data to multiple nodes.

These table types provide different functions and uses, and the appropriate table types can be selected according to specific needs to store and manage data.

Create Table

Order:

postgres=# \help create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT } ] [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and like_option is:

{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }

and partition_bound_spec is:

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

referential_action in a FOREIGN KEY/REFERENCES constraint is:

{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }

URL: /docs/16/

postgres=#

Alter Table

Order

postgres=# \help alter table
Command:     ALTER TABLE
Description: change the definition of a table
Syntax:
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_name
ALTER TABLE [ IF EXISTS ] name
    RENAME TO new_name
ALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

where action is one of:

    ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
    ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
    ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
    ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
    ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
    ALTER [ COLUMN ] column_name SET STATISTICS integer
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }
    ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    DISABLE ROW LEVEL SECURITY
    ENABLE ROW LEVEL SECURITY
    FORCE ROW LEVEL SECURITY
    NO FORCE ROW LEVEL SECURITY
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    SET ACCESS METHOD new_access_method
    SET TABLESPACE new_tablespace
    SET { LOGGED | UNLOGGED }
    SET ( storage_parameter [= value] [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OF type_name
    NOT OF
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
    REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

and partition_bound_spec is:

IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
  TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

and column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED |
  GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint_using_index is:

    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

referential_action in a FOREIGN KEY/REFERENCES constraint is:

{ NO ACTION | RESTRICT | CASCADE | SET NULL [ ( column_name [, ... ] ) ] | SET DEFAULT [ ( column_name [, ... ] ) ] }

URL: /docs/16/

postgres=#

Delete table command

Order

postgres=# postgres=# \help drop table
Command:     DROP TABLE
Description: remove a table
Syntax:
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

URL: /docs/16/

postgres=#

View table list command

ci_database_test01=# \dtS
                    List of relations
   Schema   |           Name           | Type  |  Owner   
------------+--------------------------+-------+----------
 pg_catalog | pg_aggregate             | table | postgres
 pg_catalog | pg_am                    | table | postgres
 pg_catalog | pg_amop                  | table | postgres
 pg_catalog | pg_amproc                | table | postgres
 pg_catalog | pg_attrdef               | table | postgres
 pg_catalog | pg_attribute             | table | postgres
 pg_catalog | pg_auth_members          | table | postgres

ci_database_test01=# select * from pg_tables;
ci_database_test01=# select * from pg_tables where schemaname= '<schema name>';

Search table

Order

\dt pg_range

ci_database_test01=# select * from pg_tables where tablename ='<table name>' ;

View table details

Order

ci_database_test01=# \d pg_range
               Table "pg_catalog.pg_range"
    Column     |  Type   | Collation | Nullable | Default 
---------------+---------+-----------+----------+---------
 rngtypid      | oid     |           | not null | 
 rngsubtype    | oid     |           | not null | 
 rngmultitypid | oid     |           | not null | 
 rngcollation  | oid     |           | not null | 
 rngsubopc     | oid     |           | not null | 
 rngcanonical  | regproc |           | not null | 
 rngsubdiff    | regproc |           | not null | 
Indexes:
    "pg_range_rngtypid_index" PRIMARY KEY, btree (rngtypid)
    "pg_range_rngmultitypid_index" UNIQUE CONSTRAINT, btree (rngmultitypid)

ci_database_test01=#

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns 
WHERE table_name = '<table name>';

ci_database_test01=# SELECT column_name, data_type, is_nullable, column_default
ci_database_test01-# FROM information_schema.columns 
ci_database_test01-# WHERE table_name = 'pg_range';
  column_name  | data_type | is_nullable | column_default 
---------------+-----------+-------------+----------------
 rngtypid      | oid       | NO          | 
 rngsubtype    | oid       | NO          | 
 rngmultitypid | oid       | NO          | 
 rngcollation  | oid       | NO          | 
 rngsubopc     | oid       | NO          | 
 rngcanonical  | regproc   | NO          | 
 rngsubdiff    | regproc   | NO          | 
(7 rows)

ci_database_test01=#

parameter

parameter	Subparameters	illustrate
TEMPORARY or TEMP		If specified, the table is created as a temporary table. Temporary tables are automatically deleted at the end of the session, or automatically deleted at the end of the current transaction (see below). The default search_path includes temporary schemas first, so when temporary tables exist, existing permanent tables with the same name are not selected for the new plan unless they are referenced with a schema-qualified name. Any index created on a temporary table will also automatically become a temporary index. ON COMMIT The autovacuum daemon is not accessible, so temporary tables cannot be cleared or analyzed. Therefore, appropriate vacuum and analysis operations should be performed through session SQL commands. For example, if you want to use a temporary table in a complex query, it is best to run the temporary table on it after it is filled. ANALYZE Optional, or can be written before or. This is currently not different in PostgreSQL and has been deprecated; see Compatibility below. GLOBAL \ LOCAL TEMP OR ARYTEMP
UNLOGGED		If specified, the table is created as an unrecorded table. Write data to unlogged tables does not write to write-pre-logs, which makes them much faster than normal tables. However, they are not crash-safe: unrecorded tables are automatically truncated after a crash or unclean shutdown. The contents of the unlogged table are not copied to the standby server either. Any indexes created on unrecorded tables will also be automatically cancelled. If you specify this, any sequences created with the unrecorded table (for an identity or sequence column) are also created as unrecorded.
IF NOT EXISTS		If a relationship with the same name already exists, do not throw an error. In this case, a notification will be issued. Note that there is no guarantee that an existing relationship is similar to the one to be created.
table_name		The name of the table to be created (you can specify the schema schema).
OF type_name		Creates a typed table that gets its structure from the specified compound type (optionally referred to as schema qualification). A typed table is associated with its type; for example, if type is deleted (with). DROP TYPE … CASCADE When creating a typed table, the column's data type is determined by the underlying compound type, rather than specified by the command. However, this command can add default values and constraints to the table and can specify storage parameters. CREATE TABLE
column_name		The name of the column to be created in the new table.
data_type		The data type of the column. This can include array specifiers.
COLLATE collation		This clause assigns a sorting rule to a column (the column must be a mergeable data type). If not specified, the default sorting rules for the column data type are used.
STORAGE { PLAIN / EXTERNAL / EXTENDED / MAIN / DEFAULT }		This form sets the column storage mode. This controls whether this column is saved inline or in the secondary TOAST table and whether the data should be compressed. Must be used for fixed length values. Such as inline, uncompressed. is for inline, compressible. Used for external uncompressed data, and also for externally compressed data. Write sets the storage mode to the default mode for column data types. is the default value for most data types that support non-storage. Using , will make the substring operation very large and the value runs faster, but at the cost of increasing storage space.
COMPRESSION compression_method		This clause sets the compression method of the column. Compression supports only variable-width data types and is used only if the column's storage mode is or.
INHERITS ( parent_table [, … ] )		The optional clause specifies a list of tables from which a new table automatically inherits all columns. The parent table can be a normal table or an external table. Using of creates a persistent relationship between a new child table and its parent table. Schematic modifications to the parent table are usually propagated to the child table, and by default, the data of the child table is contained in the scan of the parent table. If the same column name exists in multiple parent tables, an error is reported unless the data type of the columns in each parent table matches. If there is no conflict, duplicate columns are merged to form a single column in the new table. If the column name list of the new table contains column names that are also inherited, the data type must also match the inherited column and the column definitions will be merged into one column. If the new table explicitly specifies the default value for a column, this default value overrides any default value inherited from the column's inheritance declaration. Otherwise, any parent that specifies a default value for a column must all specify the same default value, otherwise an error will be reported. The CHECK constraints are merged in a way that is basically the same as the columns: if multiple parent tables and/or new table definitions contain constraints with the same name, these constraints must have the same check expression, otherwise an error will be reported. Constraints with the same name and expression are merged into a copy. Constraints marked in the parent are not considered. Note that unnamed constraints in new tables are never merged, because a unique name will always be selected for it, CHECKNO INHERITCHECK. Column settings are also copied from the parent table (STORAGE). If the column in the parent table is an identification column, the attribute is not inherited. If necessary, you can declare the columns in the subtable as identification columns.
PARTITION BY { RANGE / LIST / HASH } ( { column_name / ( expression ) } [ opclass ] [, …] )		Optional clause specifies the policy for partitioning the table. The table created in this way is called a partition table. A list of brackets or expressions constitutes the partition key of the table. When using a range or hash partition, the partition key can contain multiple columns or expressions (up to 32, but this limit can be changed when building PostgreSQL), but for list partitioning, the partition key must contain a single column or expression. Range and list partitions require the btree operator class, while hash partitions require the hash operator class. If the operator class is not explicitly specified, the default operator class of the corresponding type will be used; if the default operator class does not exist, an error will be raised. When using hash partitions, the operator class used must implement the support function. Partition tables are divided into subtables (called partitions) that are created using separate commands. The partition table itself is empty. The rows of data inserted into the table are routed to the partition according to the values of the columns or expressions in the partition key. If no existing partition matches the value in the new row, an error is reported. CREATE TABLE Partition tables do not support constraints; however, you can define these constraints on a single partition.
PARTITION OF parent_table { FOR VALUES partition_bound_spec / DEFAULT }		Creates a table as a partition for the specified parent table. You can use : to create a table as a partition with a specific value, or to create it as a default partition. Any indexes, constraints, and user-defined row-level triggers present in the parent table will be cloned on the new partition. partition_bound_spec must correspond to the partition method and partition key of the parent table and must not overlap with any existing partitions of the parent table. Form with is used for list partitions, form with is used for range partitions, and form with is used for hash partitions. partition_bound_expr is any variable-free expression (subquery, window functions, aggregate functions, and collection return functions are not allowed). Its data type must match the data type of the corresponding partition key column. The expression is evaluated once when the table is created, so it can even contain mutable expressions such as .CURRENT_TIMESTAMP When creating a list partition, you can specify the partition key column to indicate that the partition allows the partition key column to be null. However, for a given parent table, there cannot be multiple such list partitions. Cannot be specified for range partitions. When creating a range partition, the specified lower limit is a non-exclusive boundary, while the specified upper limit is an exclusive boundary. That is, the value specified in the list is the valid value of the corresponding partition key column for this partition, while the value in the list is not. Note that this statement must be understood according to the line-by-line comparison rules (Section 9.24.5). For example, given , partition binding allows any , any non-null and any .FROMTOFROMTOPARTITION BY RANGE (x,y)FROM (1, 2) TO (3, 4)x=1y>=2x=2yx=3y<4 When creating a range partition, you can use special values and , to indicate that the column's values have no lower or upper limit. For example, using a defined partition allows any value less than 10, while using a defined partition allows any value greater than or equal to 10. MINVALUEMAXVALUEFROM (MINVALUE) TO (10)FROM (10) TO (MAXVALUE) When creating a range partition involving multiple columns, it also makes sense to use it as part of the lower limit and part of the upper limit. For example, using a defined partition allows any row with the first partition key column greater than 0 and less than or equal to 10. Likewise, using a defined partition allows any row that starts with "a" in the first partition key column. MAXVALUEMINVALUEFROM (0, MAXVALUE) TO (10, MAXVALUE) FROM (‘a’, MINVALUE) TO (‘b’, MINVALUE) Note that if or is used for a column on the partition boundary, the same value must be used for all subsequent columns. For example, not a valid boundary; you should write .MINVALUEMAXVALUE(10, MINVALUE, 0)(10, MINVALUE, MINVALUE) Also note that some element types (e.g. ) have the concept of "infinity", which is just another value that can be stored. This is different from and , which are not true values that can be stored, but rather indicate that the values are infinite. It can be considered greater than any other value, including "infinity", and less than any other value, including "minus infinity". Therefore, this range is not an empty range; it only allows storing one value - "infinity". br>timestampMINVALUEMAXVALUEMAXVALUEMINVALUEFROM (‘infinity’) TO (MAXVALUE) If specified, the table is created as the default partition of the parent table. This option does not apply to hash partition tables. Partition key values that are not suitable for any other partitions of a given parent will be routed to the default partition. When a table has an existing partition and adds a new partition to it, the default partition must be scanned to verify that it does not contain any rows that correctly belong to the new partition. This can be slow if the default partition contains a large number of rows. If the default partition is an outer surface, or it has a constraint that proves that it cannot contain rows that should be placed in the new partition, the scan will be skipped. When creating a hash partition, you must specify the modulus and remainder. The modulus must be a positive integer, and the remainder must be a non-negative integer smaller than the modulus. Generally, when you initially set up a hash partition table, you should select a modulus equal to the number of partitions and assign the same modulus and a different remainder to each table (see the example below). However, it is not required that each partition has the same modulus, but that each modulus that appears between partitions of the hash partition table is the factor of the next larger modulus. This allows increasing the number of partitions in incremental fashion without moving all data at once. For example, suppose you have a hash partition table with 8 partitions with a modulus of 8 for each partition, but find it necessary to increase the number of partitions to 16. You can separate one of the modulo 8 partitions, create two new modulo 16 partitions, cover the same part of the key space (one partition's remainder equals the remainder of the separate partition, and the other partition's remainder equals the value plus 8), and then refill them with the data. You can then repeat this for each modulo 8 partition (perhaps later) until there are no partitions left. While this may still involve a lot of data movement at each step, it's still better than having to create a brand new table and move all the data at once. A partition must have the same column name and type as the partition table to which it belongs. Modifications to the column name or type of the partition table will be automatically propagated to all partitions. Each partition automatically inherits the constraints, but a single partition can specify other constraints; other constraints with the same name and conditions as the parent constraint will be merged with the parent constraint. Default values can be specified individually for each partition. But please note that when inserting tuples into the partition table, the default value of the partition is not applied. Rows inserted into the partition table will be automatically routed to the correct partition. If no suitable partition exists, an error occurs. Usually operations that affect the table and all its inherited children will be cascading to all partitions, but can also be performed on a single partition. Note that using create partitions requires locking the parent partition table. Similarly, deleting a partition requires locking the parent table. These operations can be performed under weak locks using ALTER TABLE ATTACH/DETACH PARTITION to reduce interference with concurrent operations on partition tables. PARTITION OFACCESS EXCLUSIVEDROP TABLEACCESS EXCLUSIVE
LIKE source_table [ like_option … ]		This clause specifies a table from which all column names, their data types, and their non-null constraints are automatically copied from. Unlike this, the new and original tables are completely decoupled after creation is completed. Changes to the original table are not applied to the new table, and data for the new table cannot be included in the scan of the original table. Additionally, copied columns and constraints are not merged with columns and constraints with similar names. An error signal is emitted if the same name is explicitly specified or the same name is specified in another clause. The optional like_option clause specifies which additional properties of the original table to be copied. Specifies that the attribute will be copied, and Specifies that the attribute will be omitted. is the default value. If multiple specifications are made for objects of the same type, the last specification is used.
	INCLUDING COMMENTS	Comments for the copied columns, constraints, and indexes will be copied. The default behavior is to exclude comments, which causes the copied columns and constraints in the new table to be uncommented.
	INCLUDING COMPRESSION	The compression method that will copy the column. The default behavior is to exclude compression methods, resulting in columns having default compression methods.
	INCLUDING CONSTRAINTS	The CHECK constraint will be copied. There is no difference between column constraints and table constraints. Non-empty constraints are always copied to the new table.
	INCLUDING DEFAULTS	The default expressions defined by the copied column will be copied. Otherwise, the default expression is not copied, resulting in the default value of the copied columns in the new table being empty. Note that copying the default value of the database modification function, for example, may create a feature link between the original table and the new table.
	INCLUDING GENERATED	Any generated expressions defined by the copied column will be copied. By default, the new column will be a regular base column.
	INCLUDING IDENTITY	Any identification specifications defined by the copied column will be copied. Creates a new sequence for each identity column of the new table, separate from the sequence associated with the old table.
	INCLUDING INDEXES	The indexes,, and constraints on the original table will be created on the new table. Select the name of the new index and constraint based on the default rules regardless of how the original index and constraint are named. (This behavior avoids possible duplicate name failures for new indexes.) Main KEY UNIQUE EXCLUDE
	INCLUDING STATISTICS	Extended statistics are copied to the new table.
	INCLUDING STORAGE	The storage settings defined by the copied column will be copied. The default behavior is to exclude settings, resulting in the columns copied in the new table having a type-specific default setting.
	INCLUDING ALL	Include all abbreviations for selecting all available individual options. (It may be useful to write separate clauses after selecting all options except certain options.) EXCLUDING INCLUDING All This clause can also be used to copy column definitions from view, appearance, or composite type. Options that are not applicable (for example, from view) are ignored. LIKE INCLUDING Index
CONSTRAINT constraint_name		Optional name for a column or table constraint. If a constraint is violated, the constraint name is displayed in the error message, so you can use the constraint name to pass useful constraint information to the client application. (Double quotes are required to specify the constraint name containing spaces.) If the constraint name is not specified, the system will generate a name. Cold must be positive
NOT NULL		Columns are not allowed to contain null values.
NULL		Columns are allowed to contain null values. This is the default value.
CHECK ( expression ) [ NO INHERIT ]		The clause specifies an expression that produces a Boolean result that a new or updated row must satisfy the result, and the insert or update operation must succeed. The expression evaluated to TRUE or UNKNOWN is successful. If any row of the insert or update operation produces a FALSE result, an error exception is thrown and insert or update does not change the database. A check constraint specified as a column constraint should refer to only the value of that column, while an expression that appears in a table constraint can refer to multiple columns. When a table has multiple constraints, after checking the constraints, each row will be tested alphabetically by the name.
DEFAULT default_expr		This clause assigns a default data value to the column in which the column definition occurs. This value is any variable-independent expression (in particular, cross-references to other columns in the current table are not allowed). Subqueries are not allowed. The data type of the default expression must match the data type of the column. The default expression will be used for any insertion operations that do not specify a value for the column. If the column does not have a default value, the default value is empty.
GENERATED ALWAYS AS ( generation_expr ) STORED		This clause creates the column as the generated column. This column cannot be written, and the result of the specified expression will be returned when read. This keyword is required to indicate that the column will be calculated at write time and will be stored on disk. Generate expressions can refer to other columns in the table, but cannot refer to other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.
GENERATED { ALWAYS / BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]		This clause creates the column as an identification column. It will have an implicit sequence attached to it, and the columns of the new row will automatically have the values in the sequence assigned to it. Such columns are implicit. Clauses and determine how to explicitly process user-specified values in and commands. In the command, if if is selected, the user-specified value is only accepted if specified by the statement. If selected, the user-specified value is preferred. See INSERT for more information. (In this command, the user-specified value will be used no matter how it is set.) By default, the total path is inserted when overwriting the system value. In the command, if this option is selected, the column is rejected to update any value other than that value. After selecting, the column can be updated normally. (The command has no clauses.) The optional sequence_options clause can be used to override the sequence options.
UNIQUE [ NULLS [ NOT ] DISTINCT ] (column constraint) UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, … ] ) [ INCLUDE ( column_name [, …]) ] (table constraint) #		Constraints a group composed of one or more columns in a specified table can only contain unique values. The behavior of a unique table constraint is the same as that of a unique column constraint and has the additional functionality to span multiple columns. Therefore, the constraint forces any two rows to be different in at least one of these columns. For the purpose of a unique constraint, null values are not considered equal unless specified. null is indistinguishable Each unique constraint should name a set of columns that are different from any other unique key constraint or primary key constraint named for the table. (Otherwise, the extra unique constraint will be discarded.) When establishing a unique constraint for a multilevel partition hierarchy, all columns in the partition key of the target partition table and columns in the partition key of all its descendant partition tables must be included in the constraint definition. Adding a unique constraint will automatically create a unique b-tree index on the columns or column groups used in the constraint. An optional clause adds one or more columns to the index, which are simply "payloads": they are not mandatory for uniqueness, and the index cannot be searched for based on these columns. However, they can be retrieved by index-only scans. Note that although there are no constraints enforced on included columns, it still depends on them. Therefore, some operations on these columns (for example) may result in cascading constraints and index deletion.
PRIMARY KEY (column constraint) PRIMARY KEY ( column_name [, … ] ) [ INCLUDE ( column_name [, …]) ] (table constraint)		Constraints one or more columns of a specified table can only contain unique (non-duplicate) and non-null values. A table can only specify one primary key, whether as a column or a table constraint. Primary key constraints should name a set of columns that are different from columns named for any unique constraints defined for the same table. (Otherwise, the only constraint is redundant and will be discarded.) PRIMARY KEY forces the same data constraints as the combination. However, identifying a set of columns as primary keys also provides metadata about schema design, because the primary key means that other tables can rely on this set of columns as unique identifiers for rows. UNIQUE NOT 0 When placing a constraint in a partitioned table, the constraints share the constraints described earlier. Adding a constraint will automatically create a unique b-tree index on the columns or column groups used in the constraint. An optional clause adds one or more columns to the index, which are simply "payloads": they are not mandatory for uniqueness, and the index cannot be searched for based on these columns. However, they can be retrieved by index-only scans. Note that although there are no constraints enforced on included columns, it still depends on them. Therefore, some operations on these columns (for example) may result in cascading constraints and index deletion
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, … ] ) index_parameters [ WHERE ( predicate ) ]		The clause defines an exclusion constraint that ensures that not all of these comparisons will be returned if the specified operator is used to compare any two rows on the specified column or expression. If all specified operators test for equality, this is equivalent to a constraint, although ordinary unique constraints will be faster. However, excluding constraints can specify more general constraints than simple equality. Exclusion constraints are implemented using indexes, so each specified operator must be associated with the appropriate operator class for the index access method index_method. Operators must be interchangeable. Each exclude_element can specify operator class and/or sorting options; Currently this means that GIN cannot be used. Although this is allowed, it doesn't make much sense to use a b-tree or hash index under exclusion constraints, as this does not do anything better with a normal unique constraint. The predicate allows you to specify exclusion constraints on a subset of the table; this creates a partial index internally. Note that brackets are required around the predicate.
REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (column constraint)FOREIGN KEY ( column_name [, … ] ) REFERENCES reftable [ ( refcolumn [, … ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (table constraint)		These clauses specify a foreign key constraint that requires that one or more columns of a new table must contain only values that match the values in the referenced columns of some rows of the referenced table. If the refcolumn list is omitted, the primary key of the reftable is used. The referenced column must be a column that is not delayed by a unique key constraint or primary key constraint in the referenced table. The user must have permissions to the referenced table (the entire table or a specific reference column). Adding a foreign key constraint requires adding a lock to the reference table. Note that foreign key constraints cannot be defined between temporary and permanent tables. The value inserted into the reference column will match the values of the reference table and reference column using the given matching type. There are three matching types: and (this is the default). One column in multiple columns of foreign keys is not allowed to be empty unless all foreign key columns are empty; if they are all empty, the rows are not required to have matches in the reference table. Allow any foreign key columns to be empty; if any of them are empty, there is no need to have a match in the reference table. Not yet implemented. (Of course, constraints can be applied to reference columns to prevent these situations.) Match Full Match Part Match Simplematch Full Match Simplematch Part is not empty Additionally, when the data in the reference column changes, some operations are performed on the data in the table column. The clause specifies the action to be performed when the referenced rows in the referenced table are deleted. Likewise, this clause specifies the action to be performed when the referenced column in the referenced table is updated to a new value. If the row is updated but the referenced column has not actually changed, nothing is done. Reference operations other than checking cannot be delayed, even if the constraint is declared as delayable. Each clause has the following possible operations: ON DELETEON UPDATENO ACTION
	NO ACTION	An error is generated that states that deletion or update will create an error that violates foreign key constraints. If the constraint is delayed, if any reference rows still exist, this error will be generated during constraint checking. This is the default action.
	RESTRICT	An error is generated that states that deletion or update will create an error that violates foreign key constraints. This is the same as except that it is not deferred. No action
	CASCADE	Delete all rows that reference deleted rows, or update the value of the referenced column to the new value of the referenced column, respectively.
	SET NULL [ ( column_name [, … ] ) ]	Sets all reference columns or specified subsets of reference columns to empty. A subset of a column can only be specified for operations. Delete
	SET DEFAULT [ ( column_name [, … ] ) ]	Sets all reference columns or specified subsets of reference columns to their default values. A subset of a column can only be specified for operations. (If the default value is not empty, there must be a row in the reference table that matches the default value, otherwise the operation will fail.)
DEFERRABLE NOT DEFERRABLE		This controls whether the constraint can be delayed. Non-delayed constraints are checked immediately after each command. Checking for delayable constraints can be delayed until the end of the transaction (using the SET constraints command). is the default value. Currently, only , , and (foreign key) constraints accept this clause. Constraints cannot be delayed. Note that delayable constraints cannot be used as conflict arbitrators in statements containing clauses.
INITIALLY IMMEDIATE INITIALLY DEFERRED		If the constraint is delayable, this clause specifies the default time to check the constraint. If the constraint is, check it after each statement. This is the default value. If the constraint is, it is checked only at the end of the transaction. The constraint check time can be changed using the SET CONSTRAINTS command. The default is immediate check or delay check
USING method		This optional clause specifies a table access method used to store the contents of a new table; this method needs to be a type of access method.
WITH ( storage_parameter [= value] [, … ] )		This clause specifies optional storage parameters for a table or index; for backward compatibility, the table clause can also include clauses that specify that the rows of the new table should not contain oid (object identifier) and are no longer supported. WITHOIDS = FALSEOIDS = TRUE
WITHOUT OIDS		This is backward compatible declaration table syntax and no longer supports table creation. without oid, with oid
ON COMMIT		The behavior of temporary tables at the end of a transaction block can be used. ON COMMIT has three options
	PRESERVE ROWS	No special action is taken at the end of the transaction. This is the default behavior.
	DELETE ROWS	All rows in the temporary table will be deleted at the end of each transaction block. In fact, TRUNCATE is automatically executed every time you submit. When used on a partition table, it is not cascading to its partition.
	DROP	The temporary table will be deleted at the end of the current transaction block. When you use this action on a partitioned table, its partition is deleted; when you use this action on an inherited child table, the dependent child table is deleted.
TABLESPACE tablespace_name	tablespace_name is the name of the tablespace to create the new table. If not specified, query default_tablespace, if it is a temporary table, query temp_tablespaces. For partitioned tables, since the table itself does not require storage, when no other tablespace is explicitly specified, the specified tablespace will overwrite any newly created partition as the default tablespace.
USING INDEX TABLESPACE	This clause allows the selection of the tablespace to create an index associated with, or constraints. If not specified, query default_tablespace, if it is a temporary table, query temp_tablespaces.

Summarize

This is the article about the creation of tables and basic table syntax for PostgreSQL table operations. For more related PostgreSQL table creation and basic syntax content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!