{
    "content": [
        {
            "type": "text",
            "text": "# CREATE_TABLE(7) (man)\n\n**Summary:** CREATETABLE - define a new table\n\n**Synopsis:** CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename ( [\n{ columnname datatype [ COMPRESSION compressionmethod ] [ COLLATE collation ] [ columnconstraint [ ... ] ]\n| tableconstraint\n| LIKE sourcetable [ likeoption ... ] }\n[, ... ]\n] )\n[ INHERITS ( parenttable [, ... ] ) ]\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\nOF typename [ (\n{ columnname [ WITH OPTIONS ] [ columnconstraint [ ... ] ]\n| tableconstraint }\n[, ... ]\n) ]\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\nPARTITION OF parenttable [ (\n{ columnname [ WITH OPTIONS ] [ columnconstraint [ ... ] ]\n| tableconstraint }\n[, ... ]\n) ] { FOR VALUES partitionboundspec | DEFAULT }\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\nwhere columnconstraint is:\n[ CONSTRAINT constraintname ]\n{ NOT NULL |\nNULL |\nCHECK ( expression ) [ NO INHERIT ] |\nDEFAULT defaultexpr |\nGENERATED ALWAYS AS ( generationexpr ) STORED |\nGENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequenceoptions ) ] |\nUNIQUE indexparameters |\nPRIMARY KEY indexparameters |\nREFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]\n[ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\nand tableconstraint is:\n[ CONSTRAINT constraintname ]\n{ CHECK ( expression ) [ NO INHERIT ] |\nUNIQUE ( columnname [, ... ] ) indexparameters |\nPRIMARY KEY ( columnname [, ... ] ) indexparameters |\nEXCLUDE [ USING indexmethod ] ( excludeelement WITH operator [, ... ] ) indexparameters [ WHERE ( predicate ) ] |\nFOREIGN KEY ( columnname [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]\n[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\nand likeoption is:\n{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }\nand partitionboundspec is:\nIN ( partitionboundexpr [, ...] ) |\nFROM ( { partitionboundexpr | MINVALUE | MAXVALUE } [, ...] )\nTO ( { partitionboundexpr | MINVALUE | MAXVALUE } [, ...] ) |\nWITH ( MODULUS numericliteral, REMAINDER numericliteral )\nindexparameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:\n[ INCLUDE ( columnname [, ... ] ) ]\n[ WITH ( storageparameter [= value] [, ... ] ) ]\n[ USING INDEX TABLESPACE tablespacename ]\nexcludeelement in an EXCLUDE constraint is:\n{ columnname | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclassparameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]\n\n## Examples\n\n- `Create table films and table distributors:`\n- `CREATE TABLE films (`\n- `code        char(5) CONSTRAINT firstkey PRIMARY KEY,`\n- `title       varchar(40) NOT NULL,`\n- `did         integer NOT NULL,`\n- `dateprod   date,`\n- `kind        varchar(10),`\n- `len         interval hour to minute`\n- `);`\n- `CREATE TABLE distributors (`\n- `did    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,`\n- `name   varchar(40) NOT NULL CHECK (name <> '')`\n- `);`\n- `Create a table with a 2-dimensional array:`\n- `CREATE TABLE arrayint (`\n- `vector  int[][]`\n- `);`\n- `Define a unique table constraint for the table films. Unique table constraints can be defined`\n- `on one or more columns of the table:`\n- `CREATE TABLE films (`\n- `code        char(5),`\n- `title       varchar(40),`\n- `did         integer,`\n- `dateprod   date,`\n- `kind        varchar(10),`\n- `len         interval hour to minute,`\n- `CONSTRAINT production UNIQUE(dateprod)`\n- `);`\n- `Define a check column constraint:`\n- `CREATE TABLE distributors (`\n- `did     integer CHECK (did > 100),`\n- `name    varchar(40)`\n- `);`\n- `Define a check table constraint:`\n- `CREATE TABLE distributors (`\n- `did     integer,`\n- `name    varchar(40),`\n- `CONSTRAINT con1 CHECK (did > 100 AND name <> '')`\n- `);`\n- `Define a primary key table constraint for the table films:`\n- `CREATE TABLE films (`\n- `code        char(5),`\n- `title       varchar(40),`\n- `did         integer,`\n- `dateprod   date,`\n- `kind        varchar(10),`\n- `len         interval hour to minute,`\n- `CONSTRAINT codetitle PRIMARY KEY(code,title)`\n- `);`\n- `Define a primary key constraint for table distributors. The following two examples are`\n- `equivalent, the first using the table constraint syntax, the second the column constraint`\n- `syntax:`\n- `CREATE TABLE distributors (`\n- `did     integer,`\n- `name    varchar(40),`\n- `PRIMARY KEY(did)`\n- `);`\n- `CREATE TABLE distributors (`\n- `did     integer PRIMARY KEY,`\n- `name    varchar(40)`\n- `);`\n- `Assign a literal constant default value for the column name, arrange for the default value of`\n- `column did to be generated by selecting the next value of a sequence object, and make the`\n- `default value of modtime be the time at which the row is inserted:`\n- `CREATE TABLE distributors (`\n- `name      varchar(40) DEFAULT 'Luso Films',`\n- `did       integer DEFAULT nextval('distributorsserial'),`\n- `modtime   timestamp DEFAULT currenttimestamp`\n- `);`\n- `Define two NOT NULL column constraints on the table distributors, one of which is explicitly`\n- `given a name:`\n- `CREATE TABLE distributors (`\n- `did     integer CONSTRAINT nonull NOT NULL,`\n- `name    varchar(40) NOT NULL`\n- `);`\n- `Define a unique constraint for the name column:`\n- `CREATE TABLE distributors (`\n- `did     integer,`\n- `name    varchar(40) UNIQUE`\n- `);`\n- `The same, specified as a table constraint:`\n- `CREATE TABLE distributors (`\n- `did     integer,`\n- `name    varchar(40),`\n- `UNIQUE(name)`\n- `);`\n- `Create the same table, specifying 70% fill factor for both the table and its unique index:`\n- `CREATE TABLE distributors (`\n- `did     integer,`\n- `name    varchar(40),`\n- `UNIQUE(name) WITH (fillfactor=70)`\n- `WITH (fillfactor=70);`\n- `Create table circles with an exclusion constraint that prevents any two circles from`\n- `overlapping:`\n- `CREATE TABLE circles (`\n- `c circle,`\n- `EXCLUDE USING gist (c WITH &&)`\n- `);`\n- `Create table cinemas in tablespace diskvol1:`\n- `CREATE TABLE cinemas (`\n- `id serial,`\n- `name text,`\n- `location text`\n- `) TABLESPACE diskvol1;`\n- `Create a composite type and a typed table:`\n- `CREATE TYPE employeetype AS (name text, salary numeric);`\n- `CREATE TABLE employees OF employeetype (`\n- `PRIMARY KEY (name),`\n- `salary WITH OPTIONS DEFAULT 1000`\n- `);`\n- `Create a range partitioned table:`\n- `CREATE TABLE measurement (`\n- `logdate         date not null,`\n- `peaktemp        int,`\n- `unitsales       int`\n- `) PARTITION BY RANGE (logdate);`\n- `Create a range partitioned table with multiple columns in the partition key:`\n- `CREATE TABLE measurementyearmonth (`\n- `logdate         date not null,`\n- `peaktemp        int,`\n- `unitsales       int`\n- `) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));`\n- `Create a list partitioned table:`\n- `CREATE TABLE cities (`\n- `cityid      bigserial not null,`\n- `name         text not null,`\n- `population   bigint`\n- `) PARTITION BY LIST (left(lower(name), 1));`\n- `Create a hash partitioned table:`\n- `CREATE TABLE orders (`\n- `orderid     bigint not null,`\n- `custid      bigint not null,`\n- `status       text`\n- `) PARTITION BY HASH (orderid);`\n- `Create partition of a range partitioned table:`\n- `CREATE TABLE measurementy2016m07`\n- `PARTITION OF measurement (`\n- `unitsales DEFAULT 0`\n- `) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');`\n- `Create a few partitions of a range partitioned table with multiple columns in the partition`\n- `key:`\n- `CREATE TABLE measurementymolder`\n- `PARTITION OF measurementyearmonth`\n- `FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);`\n- `CREATE TABLE measurementymy2016m11`\n- `PARTITION OF measurementyearmonth`\n- `FOR VALUES FROM (2016, 11) TO (2016, 12);`\n- `CREATE TABLE measurementymy2016m12`\n- `PARTITION OF measurementyearmonth`\n- `FOR VALUES FROM (2016, 12) TO (2017, 01);`\n- `CREATE TABLE measurementymy2017m01`\n- `PARTITION OF measurementyearmonth`\n- `FOR VALUES FROM (2017, 01) TO (2017, 02);`\n- `Create partition of a list partitioned table:`\n- `CREATE TABLE citiesab`\n- `PARTITION OF cities (`\n- `CONSTRAINT cityidnonzero CHECK (cityid != 0)`\n- `) FOR VALUES IN ('a', 'b');`\n- `Create partition of a list partitioned table that is itself further partitioned and then add`\n- `a partition to it:`\n- `CREATE TABLE citiesab`\n- `PARTITION OF cities (`\n- `CONSTRAINT cityidnonzero CHECK (cityid != 0)`\n- `) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);`\n- `CREATE TABLE citiesab10000to100000`\n- `PARTITION OF citiesab FOR VALUES FROM (10000) TO (100000);`\n- `Create partitions of a hash partitioned table:`\n- `CREATE TABLE ordersp1 PARTITION OF orders`\n- `FOR VALUES WITH (MODULUS 4, REMAINDER 0);`\n- `CREATE TABLE ordersp2 PARTITION OF orders`\n- `FOR VALUES WITH (MODULUS 4, REMAINDER 1);`\n- `CREATE TABLE ordersp3 PARTITION OF orders`\n- `FOR VALUES WITH (MODULUS 4, REMAINDER 2);`\n- `CREATE TABLE ordersp4 PARTITION OF orders`\n- `FOR VALUES WITH (MODULUS 4, REMAINDER 3);`\n- `Create a default partition:`\n- `CREATE TABLE citiespartdef`\n- `PARTITION OF cities DEFAULT;`\n\n## See Also\n\n- ALTERTABLE(7)\n- DROPTABLE(7)\n- CREATETABLEAS(7)\n- CREATETABLESPACE(7)\n- CREATETYPE(7)\n- TABLE(7)\n\n## Section Outline\n\n- **NAME** (2 lines)\n- **SYNOPSIS** (84 lines)\n- **DESCRIPTION** (26 lines)\n- **PARAMETERS** (581 lines) — 1 subsections\n  - Storage Parameters (123 lines)\n- **NOTES** (10 lines)\n- **EXAMPLES** (236 lines)\n- **COMPATIBILITY** (2 lines) — 16 subsections\n  - Temporary Tables (26 lines)\n  - Non-Deferred Uniqueness Constraints (7 lines)\n  - Column Check Constraints (4 lines)\n  - EXCLUDE Constraint (2 lines)\n  - Foreign Key Constraints (9 lines)\n  - Constraint Naming (10 lines)\n  - Inheritance (4 lines)\n  - Zero-Column Tables (6 lines)\n  - Multiple Identity Columns (6 lines)\n  - Generated Columns (3 lines)\n  - LIKE Clause (4 lines)\n  - WITH Clause (2 lines)\n  - Tablespaces (3 lines)\n  - Typed Tables (5 lines)\n  - PARTITION BY Clause (2 lines)\n  - PARTITION OF Clause (2 lines)\n- **SEE ALSO** (6 lines)\n\n## Full Content\n\n### NAME\n\nCREATETABLE - define a new table\n\n### SYNOPSIS\n\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename ( [\n{ columnname datatype [ COMPRESSION compressionmethod ] [ COLLATE collation ] [ columnconstraint [ ... ] ]\n| tableconstraint\n| LIKE sourcetable [ likeoption ... ] }\n[, ... ]\n] )\n[ INHERITS ( parenttable [, ... ] ) ]\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\n\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\nOF typename [ (\n{ columnname [ WITH OPTIONS ] [ columnconstraint [ ... ] ]\n| tableconstraint }\n[, ... ]\n) ]\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\n\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\nPARTITION OF parenttable [ (\n{ columnname [ WITH OPTIONS ] [ columnconstraint [ ... ] ]\n| tableconstraint }\n[, ... ]\n) ] { FOR VALUES partitionboundspec | DEFAULT }\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\n\nwhere columnconstraint is:\n\n[ CONSTRAINT constraintname ]\n{ NOT NULL |\nNULL |\nCHECK ( expression ) [ NO INHERIT ] |\nDEFAULT defaultexpr |\nGENERATED ALWAYS AS ( generationexpr ) STORED |\nGENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequenceoptions ) ] |\nUNIQUE indexparameters |\nPRIMARY KEY indexparameters |\nREFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]\n[ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\n\nand tableconstraint is:\n\n[ CONSTRAINT constraintname ]\n{ CHECK ( expression ) [ NO INHERIT ] |\nUNIQUE ( columnname [, ... ] ) indexparameters |\nPRIMARY KEY ( columnname [, ... ] ) indexparameters |\nEXCLUDE [ USING indexmethod ] ( excludeelement WITH operator [, ... ] ) indexparameters [ WHERE ( predicate ) ] |\nFOREIGN KEY ( columnname [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]\n[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\n\nand likeoption is:\n\n{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }\n\nand partitionboundspec is:\n\nIN ( partitionboundexpr [, ...] ) |\nFROM ( { partitionboundexpr | MINVALUE | MAXVALUE } [, ...] )\nTO ( { partitionboundexpr | MINVALUE | MAXVALUE } [, ...] ) |\nWITH ( MODULUS numericliteral, REMAINDER numericliteral )\n\nindexparameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:\n\n[ INCLUDE ( columnname [, ... ] ) ]\n[ WITH ( storageparameter [= value] [, ... ] ) ]\n[ USING INDEX TABLESPACE tablespacename ]\n\nexcludeelement in an EXCLUDE constraint is:\n\n{ columnname | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclassparameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]\n\n### DESCRIPTION\n\nCREATE TABLE will create a new, initially empty table in the current database. The table will\nbe owned by the user issuing the command.\n\nIf a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is\ncreated in the specified schema. Otherwise it is created in the current schema. Temporary\ntables exist in a special schema, so a schema name cannot be given when creating a temporary\ntable. The name of the table must be distinct from the name of any other table, sequence,\nindex, view, or foreign table in the same schema.\n\nCREATE TABLE also automatically creates a data type that represents the composite type\ncorresponding to one row of the table. Therefore, tables cannot have the same name as any\nexisting data type in the same schema.\n\nThe optional constraint clauses specify constraints (tests) that new or updated rows must\nsatisfy for an insert or update operation to succeed. A constraint is an SQL object that\nhelps define the set of valid values in the table in various ways.\n\nThere are two ways to define constraints: table constraints and column constraints. A column\nconstraint is defined as part of a column definition. A table constraint definition is not\ntied to a particular column, and it can encompass more than one column. Every column\nconstraint can also be written as a table constraint; a column constraint is only a\nnotational convenience for use when the constraint only affects one column.\n\nTo be able to create a table, you must have USAGE privilege on all column types or the type\nin the OF clause, respectively.\n\n### PARAMETERS\n\nTEMPORARY or TEMP\nIf specified, the table is created as a temporary table. Temporary tables are\nautomatically dropped at the end of a session, or optionally at the end of the current\ntransaction (see ON COMMIT below). The default searchpath includes the temporary schema\nfirst and so identically named existing permanent tables are not chosen for new plans\nwhile the temporary table exists, unless they are referenced with schema-qualified names.\nAny indexes created on a temporary table are automatically temporary as well.\n\nThe autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary\ntables. For this reason, appropriate vacuum and analyze operations should be performed\nvia session SQL commands. For example, if a temporary table is going to be used in\ncomplex queries, it is wise to run ANALYZE on the temporary table after it is populated.\n\nOptionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes\nno difference in PostgreSQL and is deprecated; see Compatibility below.\n\nUNLOGGED\nIf specified, the table is created as an unlogged table. Data written to unlogged tables\nis not written to the write-ahead log (see Chapter 30), which makes them considerably\nfaster than ordinary tables. However, they are not crash-safe: an unlogged table is\nautomatically truncated after a crash or unclean shutdown. The contents of an unlogged\ntable are also not replicated to standby servers. Any indexes created on an unlogged\ntable are automatically unlogged as well.\n\nIF NOT EXISTS\nDo not throw an error if a relation with the same name already exists. A notice is issued\nin this case. Note that there is no guarantee that the existing relation is anything like\nthe one that would have been created.\n\ntablename\nThe name (optionally schema-qualified) of the table to be created.\n\nOF typename\nCreates a typed table, which takes its structure from the specified composite type (name\noptionally schema-qualified). A typed table is tied to its type; for example the table\nwill be dropped if the type is dropped (with DROP TYPE ... CASCADE).\n\nWhen a typed table is created, then the data types of the columns are determined by the\nunderlying composite type and are not specified by the CREATE TABLE command. But the\nCREATE TABLE command can add defaults and constraints to the table and can specify\nstorage parameters.\n\ncolumnname\nThe name of a column to be created in the new table.\n\ndatatype\nThe data type of the column. This can include array specifiers. For more information on\nthe data types supported by PostgreSQL, refer to Chapter 8.\n\nCOLLATE collation\nThe COLLATE clause assigns a collation to the column (which must be of a collatable data\ntype). If not specified, the column data type's default collation is used.\n\nCOMPRESSION compressionmethod\nThe COMPRESSION clause sets the compression method for the column. Compression is\nsupported only for variable-width data types, and is used only when the column's storage\nmode is main or extended. (See ALTER TABLE (ALTERTABLE(7)) for information on column\nstorage modes.) Setting this property for a partitioned table has no direct effect,\nbecause such tables have no storage of their own, but the configured value will be\ninherited by newly-created partitions. The supported compression methods are pglz and\nlz4. (lz4 is available only if --with-lz4 was used when building PostgreSQL.) In\naddition, compressionmethod can be default to explicitly specify the default behavior,\nwhich is to consult the defaulttoastcompression setting at the time of data insertion\nto determine the method to use.\n\nINHERITS ( parenttable [, ... ] )\nThe optional INHERITS clause specifies a list of tables from which the new table\nautomatically inherits all columns. Parent tables can be plain tables or foreign tables.\n\nUse of INHERITS creates a persistent relationship between the new child table and its\nparent table(s). Schema modifications to the parent(s) normally propagate to children as\nwell, and by default the data of the child table is included in scans of the parent(s).\n\nIf the same column name exists in more than one parent table, an error is reported unless\nthe data types of the columns match in each of the parent tables. If there is no\nconflict, then the duplicate columns are merged to form a single column in the new table.\nIf the column name list of the new table contains a column name that is also inherited,\nthe data type must likewise match the inherited column(s), and the column definitions are\nmerged into one. If the new table explicitly specifies a default value for the column,\nthis default overrides any defaults from inherited declarations of the column. Otherwise,\nany parents that specify default values for the column must all specify the same default,\nor an error will be reported.\n\nCHECK constraints are merged in essentially the same way as columns: if multiple parent\ntables and/or the new table definition contain identically-named CHECK constraints, these\nconstraints must all have the same check expression, or an error will be reported.\nConstraints having the same name and expression will be merged into one copy. A\nconstraint marked NO INHERIT in a parent will not be considered. Notice that an unnamed\nCHECK constraint in the new table will never be merged, since a unique name will always\nbe chosen for it.\n\nColumn STORAGE settings are also copied from parent tables.\n\nIf a column in the parent table is an identity column, that property is not inherited. A\ncolumn in the child table can be declared identity column if desired.\n\nPARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ opclass ] [, ...] )\nThe optional PARTITION BY clause specifies a strategy of partitioning the table. The\ntable thus created is called a partitioned table. The parenthesized list of columns or\nexpressions forms the partition key for the table. When using range or hash partitioning,\nthe partition key can include multiple columns or expressions (up to 32, but this limit\ncan be altered when building PostgreSQL), but for list partitioning, the partition key\nmust consist of a single column or expression.\n\nRange and list partitioning require a btree operator class, while hash partitioning\nrequires a hash operator class. If no operator class is specified explicitly, the default\noperator class of the appropriate type will be used; if no default operator class exists,\nan error will be raised. When hash partitioning is used, the operator class used must\nimplement support function 2 (see Section 38.16.3 for details).\n\nA partitioned table is divided into sub-tables (called partitions), which are created\nusing separate CREATE TABLE commands. The partitioned table is itself empty. A data row\ninserted into the table is routed to a partition based on the value of columns or\nexpressions in the partition key. If no existing partition matches the values in the new\nrow, an error will be reported.\n\nPartitioned tables do not support EXCLUDE constraints; however, you can define these\nconstraints on individual partitions.\n\nSee Section 5.11 for more discussion on table partitioning.\n\nPARTITION OF parenttable { FOR VALUES partitionboundspec | DEFAULT }\nCreates the table as a partition of the specified parent table. The table can be created\neither as a partition for specific values using FOR VALUES or as a default partition\nusing DEFAULT. Any indexes, constraints and user-defined row-level triggers that exist in\nthe parent table are cloned on the new partition.\n\nThe partitionboundspec must correspond to the partitioning method and partition key of\nthe parent table, and must not overlap with any existing partition of that parent. The\nform with IN is used for list partitioning, the form with FROM and TO is used for range\npartitioning, and the form with WITH is used for hash partitioning.\n\npartitionboundexpr is any variable-free expression (subqueries, window functions,\naggregate functions, and set-returning functions are not allowed). Its data type must\nmatch the data type of the corresponding partition key column. The expression is\nevaluated once at table creation time, so it can even contain volatile expressions such\nas CURRENTTIMESTAMP.\n\nWhen creating a list partition, NULL can be specified to signify that the partition\nallows the partition key column to be null. However, there cannot be more than one such\nlist partition for a given parent table.  NULL cannot be specified for range partitions.\n\nWhen creating a range partition, the lower bound specified with FROM is an inclusive\nbound, whereas the upper bound specified with TO is an exclusive bound. That is, the\nvalues specified in the FROM list are valid values of the corresponding partition key\ncolumns for this partition, whereas those in the TO list are not. Note that this\nstatement must be understood according to the rules of row-wise comparison\n(Section 9.24.5). For example, given PARTITION BY RANGE (x,y), a partition bound FROM (1,\n2) TO (3, 4) allows x=1 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.\n\nThe special values MINVALUE and MAXVALUE may be used when creating a range partition to\nindicate that there is no lower or upper bound on the column's value. For example, a\npartition defined using FROM (MINVALUE) TO (10) allows any values less than 10, and a\npartition defined using FROM (10) TO (MAXVALUE) allows any values greater than or equal\nto 10.\n\nWhen creating a range partition involving more than one column, it can also make sense to\nuse MAXVALUE as part of the lower bound, and MINVALUE as part of the upper bound. For\nexample, a partition defined using FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any rows\nwhere the first partition key column is greater than 0 and less than or equal to 10.\nSimilarly, a partition defined using FROM ('a', MINVALUE) TO ('b', MINVALUE) allows any\nrows where the first partition key column starts with \"a\".\n\nNote that if MINVALUE or MAXVALUE is used for one column of a partitioning bound, the\nsame value must be used for all subsequent columns. For example, (10, MINVALUE, 0) is not\na valid bound; you should write (10, MINVALUE, MINVALUE).\n\nAlso note that some element types, such as timestamp, have a notion of \"infinity\", which\nis just another value that can be stored. This is different from MINVALUE and MAXVALUE,\nwhich are not real values that can be stored, but rather they are ways of saying that the\nvalue is unbounded.  MAXVALUE can be thought of as being greater than any other value,\nincluding \"infinity\" and MINVALUE as being less than any other value, including \"minus\ninfinity\". Thus the range FROM ('infinity') TO (MAXVALUE) is not an empty range; it\nallows precisely one value to be stored — \"infinity\".\n\nIf DEFAULT is specified, the table will be created as the default partition of the parent\ntable. This option is not available for hash-partitioned tables. A partition key value\nnot fitting into any other partition of the given parent will be routed to the default\npartition.\n\nWhen a table has an existing DEFAULT partition and a new partition is added to it, the\ndefault partition must be scanned to verify that it does not contain any rows which\nproperly belong in the new partition. If the default partition contains a large number of\nrows, this may be slow. The scan will be skipped if the default partition is a foreign\ntable or if it has a constraint which proves that it cannot contain rows which should be\nplaced in the new partition.\n\nWhen creating a hash partition, a modulus and remainder must be specified. The modulus\nmust be a positive integer, and the remainder must be a non-negative integer less than\nthe modulus. Typically, when initially setting up a hash-partitioned table, you should\nchoose a modulus equal to the number of partitions and assign every table the same\nmodulus and a different remainder (see examples, below). However, it is not required that\nevery partition have the same modulus, only that every modulus which occurs among the\npartitions of a hash-partitioned table is a factor of the next larger modulus. This\nallows the number of partitions to be increased incrementally without needing to move all\nthe data at once. For example, suppose you have a hash-partitioned table with 8\npartitions, each of which has modulus 8, but find it necessary to increase the number of\npartitions to 16. You can detach one of the modulus-8 partitions, create two new\nmodulus-16 partitions covering the same portion of the key space (one with a remainder\nequal to the remainder of the detached partition, and the other with a remainder equal to\nthat value plus 8), and repopulate them with data. You can then repeat this -- perhaps at\na later time -- for each modulus-8 partition until none remain. While this may still\ninvolve a large amount of data movement at each step, it is still better than having to\ncreate a whole new table and move all the data at once.\n\nA partition must have the same column names and types as the partitioned table to which\nit belongs. Modifications to the column names or types of a partitioned table will\nautomatically propagate to all partitions.  CHECK constraints will be inherited\nautomatically by every partition, but an individual partition may specify additional\nCHECK constraints; additional constraints with the same name and condition as in the\nparent will be merged with the parent constraint. Defaults may be specified separately\nfor each partition. But note that a partition's default value is not applied when\ninserting a tuple through a partitioned table.\n\nRows inserted into a partitioned table will be automatically routed to the correct\npartition. If no suitable partition exists, an error will occur.\n\nOperations such as TRUNCATE which normally affect a table and all of its inheritance\nchildren will cascade to all partitions, but may also be performed on an individual\npartition.\n\nNote that creating a partition using PARTITION OF requires taking an ACCESS EXCLUSIVE\nlock on the parent partitioned table. Likewise, dropping a partition with DROP TABLE\nrequires taking an ACCESS EXCLUSIVE lock on the parent table. It is possible to use ALTER\nTABLE ATTACH/DETACH PARTITION to perform these operations with a weaker lock, thus\nreducing interference with concurrent operations on the partitioned table.\n\nLIKE sourcetable [ likeoption ... ]\nThe LIKE clause specifies a table from which the new table automatically copies all\ncolumn names, their data types, and their not-null constraints.\n\nUnlike INHERITS, the new table and original table are completely decoupled after creation\nis complete. Changes to the original table will not be applied to the new table, and it\nis not possible to include data of the new table in scans of the original table.\n\nAlso unlike INHERITS, columns and constraints copied by LIKE are not merged with\nsimilarly named columns and constraints. If the same name is specified explicitly or in\nanother LIKE clause, an error is signaled.\n\nThe optional likeoption clauses specify which additional properties of the original\ntable to copy. Specifying INCLUDING copies the property, specifying EXCLUDING omits the\nproperty.  EXCLUDING is the default. If multiple specifications are made for the same\nkind of object, the last one is used. The available options are:\n\nINCLUDING COMMENTS\nComments for the copied columns, check constraints, indexes, and extended statistics\nwill be copied. The default behavior is to exclude comments, resulting in the\ncorresponding objects in the new table having no comments.\n\nINCLUDING COMPRESSION\nCompression method of the columns will be copied. The default behavior is to exclude\ncompression methods, resulting in columns having the default compression method.\n\nINCLUDING CONSTRAINTS\nCHECK constraints will be copied. No distinction is made between column constraints\nand table constraints. Not-null constraints are always copied to the new table.\n\nINCLUDING DEFAULTS\nDefault expressions for the copied column definitions will be copied. Otherwise,\ndefault expressions are not copied, resulting in the copied columns in the new table\nhaving null defaults. Note that copying defaults that call database-modification\nfunctions, such as nextval, may create a functional linkage between the original and\nnew tables.\n\nINCLUDING GENERATED\nAny generation expressions of copied column definitions will be copied. By default,\nnew columns will be regular base columns.\n\nINCLUDING IDENTITY\nAny identity specifications of copied column definitions will be copied. A new\nsequence is created for each identity column of the new table, separate from the\nsequences associated with the old table.\n\nINCLUDING INDEXES\nIndexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table will be\ncreated on the new table. Names for the new indexes and constraints are chosen\naccording to the default rules, regardless of how the originals were named. (This\nbehavior avoids possible duplicate-name failures for the new indexes.)\n\nINCLUDING STATISTICS\nExtended statistics are copied to the new table.\n\nINCLUDING STORAGE\nSTORAGE settings for the copied column definitions will be copied. The default\nbehavior is to exclude STORAGE settings, resulting in the copied columns in the new\ntable having type-specific default settings. For more on STORAGE settings, see\nSection 70.2.\n\nINCLUDING ALL\nINCLUDING ALL is an abbreviated form selecting all the available individual options.\n(It could be useful to write individual EXCLUDING clauses after INCLUDING ALL to\nselect all but some specific options.)\n\nThe LIKE clause can also be used to copy column definitions from views, foreign tables,\nor composite types. Inapplicable options (e.g., INCLUDING INDEXES from a view) are\nignored.\n\nCONSTRAINT constraintname\nAn optional name for a column or table constraint. If the constraint is violated, the\nconstraint name is present in error messages, so constraint names like col must be\npositive can be used to communicate helpful constraint information to client\napplications. (Double-quotes are needed to specify constraint names that contain spaces.)\nIf a constraint name is not specified, the system generates a name.\n\nNOT NULL\nThe column is not allowed to contain null values.\n\nNULL\nThe column is allowed to contain null values. This is the default.\n\nThis clause is only provided for compatibility with non-standard SQL databases. Its use\nis discouraged in new applications.\n\nCHECK ( expression ) [ NO INHERIT ]\nThe CHECK clause specifies an expression producing a Boolean result which new or updated\nrows must satisfy for an insert or update operation to succeed. Expressions evaluating to\nTRUE or UNKNOWN succeed. Should any row of an insert or update operation produce a FALSE\nresult, an error exception is raised and the insert or update does not alter the\ndatabase. A check constraint specified as a column constraint should reference that\ncolumn's value only, while an expression appearing in a table constraint can reference\nmultiple columns.\n\nCurrently, CHECK expressions cannot contain subqueries nor refer to variables other than\ncolumns of the current row (see Section 5.4.1). The system column tableoid may be\nreferenced, but not any other system column.\n\nA constraint marked with NO INHERIT will not propagate to child tables.\n\nWhen a table has multiple CHECK constraints, they will be tested for each row in\nalphabetical order by name, after checking NOT NULL constraints. (PostgreSQL versions\nbefore 9.5 did not honor any particular firing order for CHECK constraints.)\n\nDEFAULT defaultexpr\nThe DEFAULT clause assigns a default data value for the column whose column definition it\nappears within. The value is any variable-free expression (in particular,\ncross-references to other columns in the current table are not allowed). Subqueries are\nnot allowed either. The data type of the default expression must match the data type of\nthe column.\n\nThe default expression will be used in any insert operation that does not specify a value\nfor the column. If there is no default for a column, then the default is null.\n\nGENERATED ALWAYS AS ( generationexpr ) STORED\nThis clause creates the column as a generated column. The column cannot be written to,\nand when read the result of the specified expression will be returned.\n\nThe keyword STORED is required to signify that the column will be computed on write and\nwill be stored on disk.\n\nThe generation expression can refer to other columns in the table, but not other\ngenerated columns. Any functions and operators used must be immutable. References to\nother tables are not allowed.\n\nGENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequenceoptions ) ]\nThis clause creates the column as an identity column. It will have an implicit sequence\nattached to it and the column in new rows will automatically have values from the\nsequence assigned to it. Such a column is implicitly NOT NULL.\n\nThe clauses ALWAYS and BY DEFAULT determine how explicitly user-specified values are\nhandled in INSERT and UPDATE commands.\n\nIn an INSERT command, if ALWAYS is selected, a user-specified value is only accepted if\nthe INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is selected, then\nthe user-specified value takes precedence. See INSERT(7) for details. (In the COPY\ncommand, user-specified values are always used regardless of this setting.)\n\nIn an UPDATE command, if ALWAYS is selected, any update of the column to any value other\nthan DEFAULT will be rejected. If BY DEFAULT is selected, the column can be updated\nnormally. (There is no OVERRIDING clause for the UPDATE command.)\n\nThe optional sequenceoptions clause can be used to override the options of the sequence.\nSee CREATE SEQUENCE (CREATESEQUENCE(7)) for details.\n\nUNIQUE (column constraint)\nUNIQUE ( columnname [, ... ] ) [ INCLUDE ( columnname [, ...]) ] (table constraint)\nThe UNIQUE constraint specifies that a group of one or more columns of a table can\ncontain only unique values. The behavior of a unique table constraint is the same as that\nof a unique column constraint, with the additional capability to span multiple columns.\nThe constraint therefore enforces that any two rows must differ in at least one of these\ncolumns.\n\nFor the purpose of a unique constraint, null values are not considered equal.\n\nEach unique constraint should name a set of columns that is different from the set of\ncolumns named by any other unique or primary key constraint defined for the table.\n(Otherwise, redundant unique constraints will be discarded.)\n\nWhen establishing a unique constraint for a multi-level partition hierarchy, all the\ncolumns in the partition key of the target partitioned table, as well as those of all its\ndescendant partitioned tables, must be included in the constraint definition.\n\nAdding a unique constraint will automatically create a unique btree index on the column\nor group of columns used in the constraint.\n\nThe optional INCLUDE clause adds to that index one or more columns that are simply\n“payload”: uniqueness is not enforced on them, and the index cannot be searched on the\nbasis of those columns. However they can be retrieved by an index-only scan. Note that\nalthough the constraint is not enforced on included columns, it still depends on them.\nConsequently, some operations on such columns (e.g., DROP COLUMN) can cause cascaded\nconstraint and index deletion.\n\nPRIMARY KEY (column constraint)\nPRIMARY KEY ( columnname [, ... ] ) [ INCLUDE ( columnname [, ...]) ] (table constraint)\nThe PRIMARY KEY constraint specifies that a column or columns of a table can contain only\nunique (non-duplicate), nonnull values. Only one primary key can be specified for a\ntable, whether as a column constraint or a table constraint.\n\nThe primary key constraint should name a set of columns that is different from the set of\ncolumns named by any unique constraint defined for the same table. (Otherwise, the unique\nconstraint is redundant and will be discarded.)\n\nPRIMARY KEY enforces the same data constraints as a combination of UNIQUE and NOT NULL.\nHowever, identifying a set of columns as the primary key also provides metadata about the\ndesign of the schema, since a primary key implies that other tables can rely on this set\nof columns as a unique identifier for rows.\n\nWhen placed on a partitioned table, PRIMARY KEY constraints share the restrictions\npreviously described for UNIQUE constraints.\n\nAdding a PRIMARY KEY constraint will automatically create a unique btree index on the\ncolumn or group of columns used in the constraint.\n\nThe optional INCLUDE clause adds to that index one or more columns that are simply\n“payload”: uniqueness is not enforced on them, and the index cannot be searched on the\nbasis of those columns. However they can be retrieved by an index-only scan. Note that\nalthough the constraint is not enforced on included columns, it still depends on them.\nConsequently, some operations on such columns (e.g., DROP COLUMN) can cause cascaded\nconstraint and index deletion.\n\nEXCLUDE [ USING indexmethod ] ( excludeelement WITH operator [, ... ] ) indexparameters [\nWHERE ( predicate ) ]\nThe EXCLUDE clause defines an exclusion constraint, which guarantees that if any two rows\nare compared on the specified column(s) or expression(s) using the specified operator(s),\nnot all of these comparisons will return TRUE. If all of the specified operators test for\nequality, this is equivalent to a UNIQUE constraint, although an ordinary unique\nconstraint will be faster. However, exclusion constraints can specify constraints that\nare more general than simple equality. For example, you can specify a constraint that no\ntwo rows in the table contain overlapping circles (see Section 8.8) by using the &&\noperator. The operator(s) are required to be commutative.\n\nExclusion constraints are implemented using an index, so each specified operator must be\nassociated with an appropriate operator class (see Section 11.10) for the index access\nmethod indexmethod. Each excludeelement defines a column of the index, so it can\noptionally specify a collation, an operator class, operator class parameters, and/or\nordering options; these are described fully under CREATE INDEX (CREATEINDEX(7)).\n\nThe access method must support amgettuple (see Chapter 62); at present this means GIN\ncannot be used. Although it's allowed, there is little point in using B-tree or hash\nindexes with an exclusion constraint, because this does nothing that an ordinary unique\nconstraint doesn't do better. So in practice the access method will always be GiST or\nSP-GiST.\n\nThe predicate allows you to specify an exclusion constraint on a subset of the table;\ninternally this creates a partial index. Note that parentheses are required around the\npredicate.\n\nREFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE referentialaction ] [\nON UPDATE referentialaction ] (column constraint)\nFOREIGN KEY ( columnname [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH\nmatchtype ] [ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] (table\nconstraint)\nThese clauses specify a foreign key constraint, which requires that a group of one or\nmore columns of the new table must only contain values that match values in the\nreferenced column(s) of some row of the referenced table. If the refcolumn list is\nomitted, the primary key of the reftable is used. Otherwise, the refcolumn list must\nrefer to the columns of a non-deferrable unique or primary key constraint or be the\ncolumns of a non-partial unique index. The user must have REFERENCES permission on the\nreferenced table (either the whole table, or the specific referenced columns). The\naddition of a foreign key constraint requires a SHARE ROW EXCLUSIVE lock on the\nreferenced table. Note that foreign key constraints cannot be defined between temporary\ntables and permanent tables.\n\nA value inserted into the referencing column(s) is matched against the values of the\nreferenced table and referenced columns using the given match type. There are three match\ntypes: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default).  MATCH FULL\nwill not allow one column of a multicolumn foreign key to be null unless all foreign key\ncolumns are null; if they are all null, the row is not required to have a match in the\nreferenced table.  MATCH SIMPLE allows any of the foreign key columns to be null; if any\nof them are null, the row is not required to have a match in the referenced table.  MATCH\nPARTIAL is not yet implemented. (Of course, NOT NULL constraints can be applied to the\nreferencing column(s) to prevent these cases from arising.)\n\nIn addition, when the data in the referenced columns is changed, certain actions are\nperformed on the data in this table's columns. The ON DELETE clause specifies the action\nto perform when a referenced row in the referenced table is being deleted. Likewise, the\nON UPDATE clause specifies the action to perform when a referenced column in the\nreferenced table is being updated to a new value. If the row is updated, but the\nreferenced column is not actually changed, no action is done. Referential actions other\nthan the NO ACTION check cannot be deferred, even if the constraint is declared\ndeferrable. There are the following possible actions for each clause:\n\nNO ACTION\nProduce an error indicating that the deletion or update would create a foreign key\nconstraint violation. If the constraint is deferred, this error will be produced at\nconstraint check time if there still exist any referencing rows. This is the default\naction.\n\nRESTRICT\nProduce an error indicating that the deletion or update would create a foreign key\nconstraint violation. This is the same as NO ACTION except that the check is not\ndeferrable.\n\nCASCADE\nDelete any rows referencing the deleted row, or update the values of the referencing\ncolumn(s) to the new values of the referenced columns, respectively.\n\nSET NULL\nSet the referencing column(s) to null.\n\nSET DEFAULT\nSet the referencing column(s) to their default values. (There must be a row in the\nreferenced table matching the default values, if they are not null, or the operation\nwill fail.)\n\nIf the referenced column(s) are changed frequently, it might be wise to add an index to\nthe referencing column(s) so that referential actions associated with the foreign key\nconstraint can be performed more efficiently.\n\nDEFERRABLE\nNOT DEFERRABLE\nThis controls whether the constraint can be deferred. A constraint that is not deferrable\nwill be checked immediately after every command. Checking of constraints that are\ndeferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS\ncommand).  NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE,\nand REFERENCES (foreign key) constraints accept this clause.  NOT NULL and CHECK\nconstraints are not deferrable. Note that deferrable constraints cannot be used as\nconflict arbiters in an INSERT statement that includes an ON CONFLICT clause.\n\nINITIALLY IMMEDIATE\nINITIALLY DEFERRED\nIf a constraint is deferrable, this clause specifies the default time to check the\nconstraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement.\nThis is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the\nend of the transaction. The constraint check time can be altered with the SET CONSTRAINTS\ncommand.\n\nUSING method\nThis optional clause specifies the table access method to use to store the contents for\nthe new table; the method needs be an access method of type TABLE. See Chapter 61 for\nmore information. If this option is not specified, the default table access method is\nchosen for the new table. See defaulttableaccessmethod for more information.\n\nWITH ( storageparameter [= value] [, ... ] )\nThis clause specifies optional storage parameters for a table or index; see Storage\nParameters below for more information. For backward-compatibility the WITH clause for a\ntable can also include OIDS=FALSE to specify that rows of the new table should not\ncontain OIDs (object identifiers), OIDS=TRUE is not supported anymore.\n\nWITHOUT OIDS\nThis is backward-compatible syntax for declaring a table WITHOUT OIDS, creating a table\nWITH OIDS is not supported anymore.\n\nON COMMIT\nThe behavior of temporary tables at the end of a transaction block can be controlled\nusing ON COMMIT. The three options are:\n\nPRESERVE ROWS\nNo special action is taken at the ends of transactions. This is the default behavior.\n\nDELETE ROWS\nAll rows in the temporary table will be deleted at the end of each transaction block.\nEssentially, an automatic TRUNCATE is done at each commit. When used on a partitioned\ntable, this is not cascaded to its partitions.\n\nDROP\nThe temporary table will be dropped at the end of the current transaction block. When\nused on a partitioned table, this action drops its partitions and when used on tables\nwith inheritance children, it drops the dependent children.\n\nTABLESPACE tablespacename\nThe tablespacename is the name of the tablespace in which the new table is to be\ncreated. If not specified, defaulttablespace is consulted, or temptablespaces if the\ntable is temporary. For partitioned tables, since no storage is required for the table\nitself, the tablespace specified overrides defaulttablespace as the default tablespace\nto use for any newly created partitions when no other tablespace is explicitly specified.\n\nUSING INDEX TABLESPACE tablespacename\nThis clause allows selection of the tablespace in which the index associated with a\nUNIQUE, PRIMARY KEY, or EXCLUDE constraint will be created. If not specified,\ndefaulttablespace is consulted, or temptablespaces if the table is temporary.\n\n#### Storage Parameters\n\nThe WITH clause can specify storage parameters for tables, and for indexes associated with a\nUNIQUE, PRIMARY KEY, or EXCLUDE constraint. Storage parameters for indexes are documented in\nCREATE INDEX (CREATEINDEX(7)). The storage parameters currently available for tables are\nlisted below. For many of these parameters, as shown, there is an additional parameter with\nthe same name prefixed with toast., which controls the behavior of the table's secondary\nTOAST table, if any (see Section 70.2 for more information about TOAST). If a table parameter\nvalue is set and the equivalent toast.  parameter is not, the TOAST table will use the\ntable's parameter value. Specifying these parameters for partitioned tables is not supported,\nbut you may specify them for individual leaf partitions.\n\nfillfactor (integer)\nThe fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is\nthe default. When a smaller fillfactor is specified, INSERT operations pack table pages\nonly to the indicated percentage; the remaining space on each page is reserved for\nupdating rows on that page. This gives UPDATE a chance to place the updated copy of a row\non the same page as the original, which is more efficient than placing it on a different\npage, and makes heap-only tuple updates more likely. For a table whose entries are never\nupdated, complete packing is the best choice, but in heavily updated tables smaller\nfillfactors are appropriate. This parameter cannot be set for TOAST tables.\n\ntoasttupletarget (integer)\nThe toasttupletarget specifies the minimum tuple length required before we try to\ncompress and/or move long column values into TOAST tables, and is also the target length\nwe try to reduce the length below once toasting begins. This affects columns marked as\nExternal (for move), Main (for compression), or Extended (for both) and applies only to\nnew tuples. There is no effect on existing rows. By default this parameter is set to\nallow at least 4 tuples per block, which with the default block size will be 2040 bytes.\nValid values are between 128 bytes and the (block size - header), by default 8160 bytes.\nChanging this value may not be useful for very short or very long rows. Note that the\ndefault setting is often close to optimal, and it is possible that setting this parameter\ncould have negative effects in some cases. This parameter cannot be set for TOAST tables.\n\nparallelworkers (integer)\nThis sets the number of workers that should be used to assist a parallel scan of this\ntable. If not set, the system will determine a value based on the relation size. The\nactual number of workers chosen by the planner or by utility statements that use parallel\nscans may be less, for example due to the setting of maxworkerprocesses.\n\nautovacuumenabled, toast.autovacuumenabled (boolean)\nEnables or disables the autovacuum daemon for a particular table. If true, the autovacuum\ndaemon will perform automatic VACUUM and/or ANALYZE operations on this table following\nthe rules discussed in Section 25.1.6. If false, this table will not be autovacuumed,\nexcept to prevent transaction ID wraparound. See Section 25.1.5 for more about wraparound\nprevention. Note that the autovacuum daemon does not run at all (except to prevent\ntransaction ID wraparound) if the autovacuum parameter is false; setting individual\ntables' storage parameters does not override that. Therefore there is seldom much point\nin explicitly setting this storage parameter to true, only to false.\n\nvacuumindexcleanup, toast.vacuumindexcleanup (enum)\nForces or disables index cleanup when VACUUM is run on this table. The default value is\nAUTO. With OFF, index cleanup is disabled, with ON it is enabled, and with AUTO a\ndecision is made dynamically, each time VACUUM runs. The dynamic behavior allows VACUUM\nto avoid needlessly scanning indexes to remove very few dead tuples. Forcibly disabling\nall index cleanup can speed up VACUUM very significantly, but may also lead to severely\nbloated indexes if table modifications are frequent. The INDEXCLEANUP parameter of\nVACUUM, if specified, overrides the value of this option.\n\nvacuumtruncate, toast.vacuumtruncate (boolean)\nEnables or disables vacuum to try to truncate off any empty pages at the end of this\ntable. The default value is true. If true, VACUUM and autovacuum do the truncation and\nthe disk space for the truncated pages is returned to the operating system. Note that the\ntruncation requires ACCESS EXCLUSIVE lock on the table. The TRUNCATE parameter of VACUUM,\nif specified, overrides the value of this option.\n\nautovacuumvacuumthreshold, toast.autovacuumvacuumthreshold (integer)\nPer-table value for autovacuumvacuumthreshold parameter.\n\nautovacuumvacuumscalefactor, toast.autovacuumvacuumscalefactor (floating point)\nPer-table value for autovacuumvacuumscalefactor parameter.\n\nautovacuumvacuuminsertthreshold, toast.autovacuumvacuuminsertthreshold (integer)\nPer-table value for autovacuumvacuuminsertthreshold parameter. The special value of -1\nmay be used to disable insert vacuums on the table.\n\nautovacuumvacuuminsertscalefactor, toast.autovacuumvacuuminsertscalefactor (floating\npoint)\nPer-table value for autovacuumvacuuminsertscalefactor parameter.\n\nautovacuumanalyzethreshold (integer)\nPer-table value for autovacuumanalyzethreshold parameter.\n\nautovacuumanalyzescalefactor (floating point)\nPer-table value for autovacuumanalyzescalefactor parameter.\n\nautovacuumvacuumcostdelay, toast.autovacuumvacuumcostdelay (floating point)\nPer-table value for autovacuumvacuumcostdelay parameter.\n\nautovacuumvacuumcostlimit, toast.autovacuumvacuumcostlimit (integer)\nPer-table value for autovacuumvacuumcostlimit parameter.\n\nautovacuumfreezeminage, toast.autovacuumfreezeminage (integer)\nPer-table value for vacuumfreezeminage parameter. Note that autovacuum will ignore\nper-table autovacuumfreezeminage parameters that are larger than half the system-wide\nautovacuumfreezemaxage setting.\n\nautovacuumfreezemaxage, toast.autovacuumfreezemaxage (integer)\nPer-table value for autovacuumfreezemaxage parameter. Note that autovacuum will ignore\nper-table autovacuumfreezemaxage parameters that are larger than the system-wide\nsetting (it can only be set smaller).\n\nautovacuumfreezetableage, toast.autovacuumfreezetableage (integer)\nPer-table value for vacuumfreezetableage parameter.\n\nautovacuummultixactfreezeminage, toast.autovacuummultixactfreezeminage (integer)\nPer-table value for vacuummultixactfreezeminage parameter. Note that autovacuum will\nignore per-table autovacuummultixactfreezeminage parameters that are larger than half\nthe system-wide autovacuummultixactfreezemaxage setting.\n\nautovacuummultixactfreezemaxage, toast.autovacuummultixactfreezemaxage (integer)\nPer-table value for autovacuummultixactfreezemaxage parameter. Note that autovacuum\nwill ignore per-table autovacuummultixactfreezemaxage parameters that are larger than\nthe system-wide setting (it can only be set smaller).\n\nautovacuummultixactfreezetableage, toast.autovacuummultixactfreezetableage (integer)\nPer-table value for vacuummultixactfreezetableage parameter.\n\nlogautovacuumminduration, toast.logautovacuumminduration (integer)\nPer-table value for logautovacuumminduration parameter.\n\nusercatalogtable (boolean)\nDeclare the table as an additional catalog table for purposes of logical replication. See\nSection 49.6.2 for details. This parameter cannot be set for TOAST tables.\n\n### NOTES\n\nPostgreSQL automatically creates an index for each unique constraint and primary key\nconstraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for\nprimary key columns. (See CREATE INDEX (CREATEINDEX(7)) for more information.)\n\nUnique constraints and primary keys are not inherited in the current implementation. This\nmakes the combination of inheritance and unique constraints rather dysfunctional.\n\nA table cannot have more than 1600 columns. (In practice, the effective limit is usually\nlower because of tuple-length constraints.)\n\n### EXAMPLES\n\nCreate table films and table distributors:\n\nCREATE TABLE films (\ncode        char(5) CONSTRAINT firstkey PRIMARY KEY,\ntitle       varchar(40) NOT NULL,\ndid         integer NOT NULL,\ndateprod   date,\nkind        varchar(10),\nlen         interval hour to minute\n);\n\nCREATE TABLE distributors (\ndid    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,\nname   varchar(40) NOT NULL CHECK (name <> '')\n);\n\nCreate a table with a 2-dimensional array:\n\nCREATE TABLE arrayint (\nvector  int[][]\n);\n\nDefine a unique table constraint for the table films. Unique table constraints can be defined\non one or more columns of the table:\n\nCREATE TABLE films (\ncode        char(5),\ntitle       varchar(40),\ndid         integer,\ndateprod   date,\nkind        varchar(10),\nlen         interval hour to minute,\nCONSTRAINT production UNIQUE(dateprod)\n);\n\nDefine a check column constraint:\n\nCREATE TABLE distributors (\ndid     integer CHECK (did > 100),\nname    varchar(40)\n);\n\nDefine a check table constraint:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40),\nCONSTRAINT con1 CHECK (did > 100 AND name <> '')\n);\n\nDefine a primary key table constraint for the table films:\n\nCREATE TABLE films (\ncode        char(5),\ntitle       varchar(40),\ndid         integer,\ndateprod   date,\nkind        varchar(10),\nlen         interval hour to minute,\nCONSTRAINT codetitle PRIMARY KEY(code,title)\n);\n\nDefine a primary key constraint for table distributors. The following two examples are\nequivalent, the first using the table constraint syntax, the second the column constraint\nsyntax:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40),\nPRIMARY KEY(did)\n);\n\nCREATE TABLE distributors (\ndid     integer PRIMARY KEY,\nname    varchar(40)\n);\n\nAssign a literal constant default value for the column name, arrange for the default value of\ncolumn did to be generated by selecting the next value of a sequence object, and make the\ndefault value of modtime be the time at which the row is inserted:\n\nCREATE TABLE distributors (\nname      varchar(40) DEFAULT 'Luso Films',\ndid       integer DEFAULT nextval('distributorsserial'),\nmodtime   timestamp DEFAULT currenttimestamp\n);\n\nDefine two NOT NULL column constraints on the table distributors, one of which is explicitly\ngiven a name:\n\nCREATE TABLE distributors (\ndid     integer CONSTRAINT nonull NOT NULL,\nname    varchar(40) NOT NULL\n);\n\nDefine a unique constraint for the name column:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40) UNIQUE\n);\n\nThe same, specified as a table constraint:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40),\nUNIQUE(name)\n);\n\nCreate the same table, specifying 70% fill factor for both the table and its unique index:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40),\nUNIQUE(name) WITH (fillfactor=70)\n)\nWITH (fillfactor=70);\n\nCreate table circles with an exclusion constraint that prevents any two circles from\noverlapping:\n\nCREATE TABLE circles (\nc circle,\nEXCLUDE USING gist (c WITH &&)\n);\n\nCreate table cinemas in tablespace diskvol1:\n\nCREATE TABLE cinemas (\nid serial,\nname text,\nlocation text\n) TABLESPACE diskvol1;\n\nCreate a composite type and a typed table:\n\nCREATE TYPE employeetype AS (name text, salary numeric);\n\nCREATE TABLE employees OF employeetype (\nPRIMARY KEY (name),\nsalary WITH OPTIONS DEFAULT 1000\n);\n\nCreate a range partitioned table:\n\nCREATE TABLE measurement (\nlogdate         date not null,\npeaktemp        int,\nunitsales       int\n) PARTITION BY RANGE (logdate);\n\nCreate a range partitioned table with multiple columns in the partition key:\n\nCREATE TABLE measurementyearmonth (\nlogdate         date not null,\npeaktemp        int,\nunitsales       int\n) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));\n\nCreate a list partitioned table:\n\nCREATE TABLE cities (\ncityid      bigserial not null,\nname         text not null,\npopulation   bigint\n) PARTITION BY LIST (left(lower(name), 1));\n\nCreate a hash partitioned table:\n\nCREATE TABLE orders (\norderid     bigint not null,\ncustid      bigint not null,\nstatus       text\n) PARTITION BY HASH (orderid);\n\nCreate partition of a range partitioned table:\n\nCREATE TABLE measurementy2016m07\nPARTITION OF measurement (\nunitsales DEFAULT 0\n) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');\n\nCreate a few partitions of a range partitioned table with multiple columns in the partition\nkey:\n\nCREATE TABLE measurementymolder\nPARTITION OF measurementyearmonth\nFOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);\n\nCREATE TABLE measurementymy2016m11\nPARTITION OF measurementyearmonth\nFOR VALUES FROM (2016, 11) TO (2016, 12);\n\nCREATE TABLE measurementymy2016m12\nPARTITION OF measurementyearmonth\nFOR VALUES FROM (2016, 12) TO (2017, 01);\n\nCREATE TABLE measurementymy2017m01\nPARTITION OF measurementyearmonth\nFOR VALUES FROM (2017, 01) TO (2017, 02);\n\nCreate partition of a list partitioned table:\n\nCREATE TABLE citiesab\nPARTITION OF cities (\nCONSTRAINT cityidnonzero CHECK (cityid != 0)\n) FOR VALUES IN ('a', 'b');\n\nCreate partition of a list partitioned table that is itself further partitioned and then add\na partition to it:\n\nCREATE TABLE citiesab\nPARTITION OF cities (\nCONSTRAINT cityidnonzero CHECK (cityid != 0)\n) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);\n\nCREATE TABLE citiesab10000to100000\nPARTITION OF citiesab FOR VALUES FROM (10000) TO (100000);\n\nCreate partitions of a hash partitioned table:\n\nCREATE TABLE ordersp1 PARTITION OF orders\nFOR VALUES WITH (MODULUS 4, REMAINDER 0);\nCREATE TABLE ordersp2 PARTITION OF orders\nFOR VALUES WITH (MODULUS 4, REMAINDER 1);\nCREATE TABLE ordersp3 PARTITION OF orders\nFOR VALUES WITH (MODULUS 4, REMAINDER 2);\nCREATE TABLE ordersp4 PARTITION OF orders\nFOR VALUES WITH (MODULUS 4, REMAINDER 3);\n\nCreate a default partition:\n\nCREATE TABLE citiespartdef\nPARTITION OF cities DEFAULT;\n\n### COMPATIBILITY\n\nThe CREATE TABLE command conforms to the SQL standard, with exceptions listed below.\n\n#### Temporary Tables\n\nAlthough the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect\nis not the same. In the standard, temporary tables are defined just once and automatically\nexist (starting with empty contents) in every session that needs them.  PostgreSQL instead\nrequires each session to issue its own CREATE TEMPORARY TABLE command for each temporary\ntable to be used. This allows different sessions to use the same temporary table name for\ndifferent purposes, whereas the standard's approach constrains all instances of a given\ntemporary table name to have the same table structure.\n\nThe standard's definition of the behavior of temporary tables is widely ignored.\nPostgreSQL's behavior on this point is similar to that of several other SQL databases.\n\nThe SQL standard also distinguishes between global and local temporary tables, where a local\ntemporary table has a separate set of contents for each SQL module within each session,\nthough its definition is still shared across sessions. Since PostgreSQL does not support SQL\nmodules, this distinction is not relevant in PostgreSQL.\n\nFor compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary\ntable declaration, but they currently have no effect. Use of these keywords is discouraged,\nsince future versions of PostgreSQL might adopt a more standard-compliant interpretation of\ntheir meaning.\n\nThe ON COMMIT clause for temporary tables also resembles the SQL standard, but has some\ndifferences. If the ON COMMIT clause is omitted, SQL specifies that the default behavior is\nON COMMIT DELETE ROWS. However, the default behavior in PostgreSQL is ON COMMIT PRESERVE\nROWS. The ON COMMIT DROP option does not exist in SQL.\n\n#### Non-Deferred Uniqueness Constraints\n\nWhen a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness\nimmediately whenever a row is inserted or modified. The SQL standard says that uniqueness\nshould be enforced only at the end of the statement; this makes a difference when, for\nexample, a single command updates multiple key values. To obtain standard-compliant behavior,\ndeclare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware\nthat this can be significantly slower than immediate uniqueness checking.\n\n#### Column Check Constraints\n\nThe SQL standard says that CHECK column constraints can only refer to the column they apply\nto; only CHECK table constraints can refer to multiple columns.  PostgreSQL does not enforce\nthis restriction; it treats column and table check constraints alike.\n\n#### EXCLUDE Constraint\n\nThe EXCLUDE constraint type is a PostgreSQL extension.\n\n#### Foreign Key Constraints\n\nIt is a PostgreSQL extension that a foreign key constraint may reference columns of a unique\nindex instead of columns of a primary key or unique constraint.\n\nNULL ““Constraint””\nThe NULL “constraint” (actually a non-constraint) is a PostgreSQL extension to the SQL\nstandard that is included for compatibility with some other database systems (and for\nsymmetry with the NOT NULL constraint). Since it is the default for any column, its presence\nis simply noise.\n\n#### Constraint Naming\n\nThe SQL standard says that table and domain constraints must have names that are unique\nacross the schema containing the table or domain.  PostgreSQL is laxer: it only requires\nconstraint names to be unique across the constraints attached to a particular table or\ndomain. However, this extra freedom does not exist for index-based constraints (UNIQUE,\nPRIMARY KEY, and EXCLUDE constraints), because the associated index is named the same as the\nconstraint, and index names must be unique across all relations within the same schema.\n\nCurrently, PostgreSQL does not record names for NOT NULL constraints at all, so they are not\nsubject to the uniqueness restriction. This might change in a future release.\n\n#### Inheritance\n\nMultiple inheritance via the INHERITS clause is a PostgreSQL language extension. SQL:1999 and\nlater define single inheritance using a different syntax and different semantics.\nSQL:1999-style inheritance is not yet supported by PostgreSQL.\n\n#### Zero-Column Tables\n\nPostgreSQL allows a table of no columns to be created (for example, CREATE TABLE foo();).\nThis is an extension from the SQL standard, which does not allow zero-column tables.\nZero-column tables are not in themselves very useful, but disallowing them creates odd\nspecial cases for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec\nrestriction.\n\n#### Multiple Identity Columns\n\nPostgreSQL allows a table to have more than one identity column. The standard specifies that\na table can have at most one identity column. This is relaxed mainly to give more flexibility\nfor doing schema changes or migrations. Note that the INSERT command supports only one\noverride clause that applies to the entire statement, so having multiple identity columns\nwith different behaviors is not well supported.\n\n#### Generated Columns\n\nThe option STORED is not standard but is also used by other SQL implementations. The SQL\nstandard does not specify the storage of generated columns.\n\n#### LIKE Clause\n\nWhile a LIKE clause exists in the SQL standard, many of the options that PostgreSQL accepts\nfor it are not in the standard, and some of the standard's options are not implemented by\nPostgreSQL.\n\n#### WITH Clause\n\nThe WITH clause is a PostgreSQL extension; storage parameters are not in the standard.\n\n#### Tablespaces\n\nThe PostgreSQL concept of tablespaces is not part of the standard. Hence, the clauses\nTABLESPACE and USING INDEX TABLESPACE are extensions.\n\n#### Typed Tables\n\nTyped tables implement a subset of the SQL standard. According to the standard, a typed table\nhas columns corresponding to the underlying composite type as well as one other column that\nis the “self-referencing column”.  PostgreSQL does not support self-referencing columns\nexplicitly.\n\n#### PARTITION BY Clause\n\nThe PARTITION BY clause is a PostgreSQL extension.\n\n#### PARTITION OF Clause\n\nThe PARTITION OF clause is a PostgreSQL extension.\n\n### SEE ALSO\n\nALTER TABLE (ALTERTABLE(7)), DROP TABLE (DROPTABLE(7)), CREATE TABLE AS\n(CREATETABLEAS(7)), CREATE TABLESPACE (CREATETABLESPACE(7)), CREATE TYPE (CREATETYPE(7))\n\n\n\nPostgreSQL 14.23                                2026                                 CREATE TABLE(7)\n\n"
        }
    ],
    "structuredContent": {
        "command": "CREATE_TABLE",
        "section": "7",
        "mode": "man",
        "summary": "CREATETABLE - define a new table",
        "synopsis": "CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename ( [\n{ columnname datatype [ COMPRESSION compressionmethod ] [ COLLATE collation ] [ columnconstraint [ ... ] ]\n| tableconstraint\n| LIKE sourcetable [ likeoption ... ] }\n[, ... ]\n] )\n[ INHERITS ( parenttable [, ... ] ) ]\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\nOF typename [ (\n{ columnname [ WITH OPTIONS ] [ columnconstraint [ ... ] ]\n| tableconstraint }\n[, ... ]\n) ]\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\nPARTITION OF parenttable [ (\n{ columnname [ WITH OPTIONS ] [ columnconstraint [ ... ] ]\n| tableconstraint }\n[, ... ]\n) ] { FOR VALUES partitionboundspec | DEFAULT }\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\nwhere columnconstraint is:\n[ CONSTRAINT constraintname ]\n{ NOT NULL |\nNULL |\nCHECK ( expression ) [ NO INHERIT ] |\nDEFAULT defaultexpr |\nGENERATED ALWAYS AS ( generationexpr ) STORED |\nGENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequenceoptions ) ] |\nUNIQUE indexparameters |\nPRIMARY KEY indexparameters |\nREFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]\n[ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\nand tableconstraint is:\n[ CONSTRAINT constraintname ]\n{ CHECK ( expression ) [ NO INHERIT ] |\nUNIQUE ( columnname [, ... ] ) indexparameters |\nPRIMARY KEY ( columnname [, ... ] ) indexparameters |\nEXCLUDE [ USING indexmethod ] ( excludeelement WITH operator [, ... ] ) indexparameters [ WHERE ( predicate ) ] |\nFOREIGN KEY ( columnname [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]\n[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\nand likeoption is:\n{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }\nand partitionboundspec is:\nIN ( partitionboundexpr [, ...] ) |\nFROM ( { partitionboundexpr | MINVALUE | MAXVALUE } [, ...] )\nTO ( { partitionboundexpr | MINVALUE | MAXVALUE } [, ...] ) |\nWITH ( MODULUS numericliteral, REMAINDER numericliteral )\nindexparameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:\n[ INCLUDE ( columnname [, ... ] ) ]\n[ WITH ( storageparameter [= value] [, ... ] ) ]\n[ USING INDEX TABLESPACE tablespacename ]\nexcludeelement in an EXCLUDE constraint is:\n{ columnname | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclassparameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]",
        "tldr_summary": null,
        "tldr_examples": [],
        "tldr_source": null,
        "flags": [],
        "examples": [
            "Create table films and table distributors:",
            "CREATE TABLE films (",
            "code        char(5) CONSTRAINT firstkey PRIMARY KEY,",
            "title       varchar(40) NOT NULL,",
            "did         integer NOT NULL,",
            "dateprod   date,",
            "kind        varchar(10),",
            "len         interval hour to minute",
            ");",
            "CREATE TABLE distributors (",
            "did    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,",
            "name   varchar(40) NOT NULL CHECK (name <> '')",
            ");",
            "Create a table with a 2-dimensional array:",
            "CREATE TABLE arrayint (",
            "vector  int[][]",
            ");",
            "Define a unique table constraint for the table films. Unique table constraints can be defined",
            "on one or more columns of the table:",
            "CREATE TABLE films (",
            "code        char(5),",
            "title       varchar(40),",
            "did         integer,",
            "dateprod   date,",
            "kind        varchar(10),",
            "len         interval hour to minute,",
            "CONSTRAINT production UNIQUE(dateprod)",
            ");",
            "Define a check column constraint:",
            "CREATE TABLE distributors (",
            "did     integer CHECK (did > 100),",
            "name    varchar(40)",
            ");",
            "Define a check table constraint:",
            "CREATE TABLE distributors (",
            "did     integer,",
            "name    varchar(40),",
            "CONSTRAINT con1 CHECK (did > 100 AND name <> '')",
            ");",
            "Define a primary key table constraint for the table films:",
            "CREATE TABLE films (",
            "code        char(5),",
            "title       varchar(40),",
            "did         integer,",
            "dateprod   date,",
            "kind        varchar(10),",
            "len         interval hour to minute,",
            "CONSTRAINT codetitle PRIMARY KEY(code,title)",
            ");",
            "Define a primary key constraint for table distributors. The following two examples are",
            "equivalent, the first using the table constraint syntax, the second the column constraint",
            "syntax:",
            "CREATE TABLE distributors (",
            "did     integer,",
            "name    varchar(40),",
            "PRIMARY KEY(did)",
            ");",
            "CREATE TABLE distributors (",
            "did     integer PRIMARY KEY,",
            "name    varchar(40)",
            ");",
            "Assign a literal constant default value for the column name, arrange for the default value of",
            "column did to be generated by selecting the next value of a sequence object, and make the",
            "default value of modtime be the time at which the row is inserted:",
            "CREATE TABLE distributors (",
            "name      varchar(40) DEFAULT 'Luso Films',",
            "did       integer DEFAULT nextval('distributorsserial'),",
            "modtime   timestamp DEFAULT currenttimestamp",
            ");",
            "Define two NOT NULL column constraints on the table distributors, one of which is explicitly",
            "given a name:",
            "CREATE TABLE distributors (",
            "did     integer CONSTRAINT nonull NOT NULL,",
            "name    varchar(40) NOT NULL",
            ");",
            "Define a unique constraint for the name column:",
            "CREATE TABLE distributors (",
            "did     integer,",
            "name    varchar(40) UNIQUE",
            ");",
            "The same, specified as a table constraint:",
            "CREATE TABLE distributors (",
            "did     integer,",
            "name    varchar(40),",
            "UNIQUE(name)",
            ");",
            "Create the same table, specifying 70% fill factor for both the table and its unique index:",
            "CREATE TABLE distributors (",
            "did     integer,",
            "name    varchar(40),",
            "UNIQUE(name) WITH (fillfactor=70)",
            "WITH (fillfactor=70);",
            "Create table circles with an exclusion constraint that prevents any two circles from",
            "overlapping:",
            "CREATE TABLE circles (",
            "c circle,",
            "EXCLUDE USING gist (c WITH &&)",
            ");",
            "Create table cinemas in tablespace diskvol1:",
            "CREATE TABLE cinemas (",
            "id serial,",
            "name text,",
            "location text",
            ") TABLESPACE diskvol1;",
            "Create a composite type and a typed table:",
            "CREATE TYPE employeetype AS (name text, salary numeric);",
            "CREATE TABLE employees OF employeetype (",
            "PRIMARY KEY (name),",
            "salary WITH OPTIONS DEFAULT 1000",
            ");",
            "Create a range partitioned table:",
            "CREATE TABLE measurement (",
            "logdate         date not null,",
            "peaktemp        int,",
            "unitsales       int",
            ") PARTITION BY RANGE (logdate);",
            "Create a range partitioned table with multiple columns in the partition key:",
            "CREATE TABLE measurementyearmonth (",
            "logdate         date not null,",
            "peaktemp        int,",
            "unitsales       int",
            ") PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));",
            "Create a list partitioned table:",
            "CREATE TABLE cities (",
            "cityid      bigserial not null,",
            "name         text not null,",
            "population   bigint",
            ") PARTITION BY LIST (left(lower(name), 1));",
            "Create a hash partitioned table:",
            "CREATE TABLE orders (",
            "orderid     bigint not null,",
            "custid      bigint not null,",
            "status       text",
            ") PARTITION BY HASH (orderid);",
            "Create partition of a range partitioned table:",
            "CREATE TABLE measurementy2016m07",
            "PARTITION OF measurement (",
            "unitsales DEFAULT 0",
            ") FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');",
            "Create a few partitions of a range partitioned table with multiple columns in the partition",
            "key:",
            "CREATE TABLE measurementymolder",
            "PARTITION OF measurementyearmonth",
            "FOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);",
            "CREATE TABLE measurementymy2016m11",
            "PARTITION OF measurementyearmonth",
            "FOR VALUES FROM (2016, 11) TO (2016, 12);",
            "CREATE TABLE measurementymy2016m12",
            "PARTITION OF measurementyearmonth",
            "FOR VALUES FROM (2016, 12) TO (2017, 01);",
            "CREATE TABLE measurementymy2017m01",
            "PARTITION OF measurementyearmonth",
            "FOR VALUES FROM (2017, 01) TO (2017, 02);",
            "Create partition of a list partitioned table:",
            "CREATE TABLE citiesab",
            "PARTITION OF cities (",
            "CONSTRAINT cityidnonzero CHECK (cityid != 0)",
            ") FOR VALUES IN ('a', 'b');",
            "Create partition of a list partitioned table that is itself further partitioned and then add",
            "a partition to it:",
            "CREATE TABLE citiesab",
            "PARTITION OF cities (",
            "CONSTRAINT cityidnonzero CHECK (cityid != 0)",
            ") FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);",
            "CREATE TABLE citiesab10000to100000",
            "PARTITION OF citiesab FOR VALUES FROM (10000) TO (100000);",
            "Create partitions of a hash partitioned table:",
            "CREATE TABLE ordersp1 PARTITION OF orders",
            "FOR VALUES WITH (MODULUS 4, REMAINDER 0);",
            "CREATE TABLE ordersp2 PARTITION OF orders",
            "FOR VALUES WITH (MODULUS 4, REMAINDER 1);",
            "CREATE TABLE ordersp3 PARTITION OF orders",
            "FOR VALUES WITH (MODULUS 4, REMAINDER 2);",
            "CREATE TABLE ordersp4 PARTITION OF orders",
            "FOR VALUES WITH (MODULUS 4, REMAINDER 3);",
            "Create a default partition:",
            "CREATE TABLE citiespartdef",
            "PARTITION OF cities DEFAULT;"
        ],
        "see_also": [
            {
                "name": "ALTERTABLE",
                "section": "7",
                "url": "https://www.chedong.com/phpMan.php/man/ALTERTABLE/7/json"
            },
            {
                "name": "DROPTABLE",
                "section": "7",
                "url": "https://www.chedong.com/phpMan.php/man/DROPTABLE/7/json"
            },
            {
                "name": "CREATETABLEAS",
                "section": "7",
                "url": "https://www.chedong.com/phpMan.php/man/CREATETABLEAS/7/json"
            },
            {
                "name": "CREATETABLESPACE",
                "section": "7",
                "url": "https://www.chedong.com/phpMan.php/man/CREATETABLESPACE/7/json"
            },
            {
                "name": "CREATETYPE",
                "section": "7",
                "url": "https://www.chedong.com/phpMan.php/man/CREATETYPE/7/json"
            },
            {
                "name": "TABLE",
                "section": "7",
                "url": "https://www.chedong.com/phpMan.php/man/TABLE/7/json"
            }
        ],
        "section_outline": [
            {
                "name": "NAME",
                "lines": 2,
                "subsections": []
            },
            {
                "name": "SYNOPSIS",
                "lines": 84,
                "subsections": []
            },
            {
                "name": "DESCRIPTION",
                "lines": 26,
                "subsections": []
            },
            {
                "name": "PARAMETERS",
                "lines": 581,
                "subsections": [
                    {
                        "name": "Storage Parameters",
                        "lines": 123
                    }
                ]
            },
            {
                "name": "NOTES",
                "lines": 10,
                "subsections": []
            },
            {
                "name": "EXAMPLES",
                "lines": 236,
                "subsections": []
            },
            {
                "name": "COMPATIBILITY",
                "lines": 2,
                "subsections": [
                    {
                        "name": "Temporary Tables",
                        "lines": 26
                    },
                    {
                        "name": "Non-Deferred Uniqueness Constraints",
                        "lines": 7
                    },
                    {
                        "name": "Column Check Constraints",
                        "lines": 4
                    },
                    {
                        "name": "EXCLUDE Constraint",
                        "lines": 2
                    },
                    {
                        "name": "Foreign Key Constraints",
                        "lines": 9
                    },
                    {
                        "name": "Constraint Naming",
                        "lines": 10
                    },
                    {
                        "name": "Inheritance",
                        "lines": 4
                    },
                    {
                        "name": "Zero-Column Tables",
                        "lines": 6
                    },
                    {
                        "name": "Multiple Identity Columns",
                        "lines": 6
                    },
                    {
                        "name": "Generated Columns",
                        "lines": 3
                    },
                    {
                        "name": "LIKE Clause",
                        "lines": 4
                    },
                    {
                        "name": "WITH Clause",
                        "lines": 2
                    },
                    {
                        "name": "Tablespaces",
                        "lines": 3
                    },
                    {
                        "name": "Typed Tables",
                        "lines": 5
                    },
                    {
                        "name": "PARTITION BY Clause",
                        "lines": 2
                    },
                    {
                        "name": "PARTITION OF Clause",
                        "lines": 2
                    }
                ]
            },
            {
                "name": "SEE ALSO",
                "lines": 6,
                "subsections": []
            }
        ],
        "sections": {
            "NAME": {
                "content": "CREATETABLE - define a new table\n",
                "subsections": []
            },
            "SYNOPSIS": {
                "content": "CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename ( [\n{ columnname datatype [ COMPRESSION compressionmethod ] [ COLLATE collation ] [ columnconstraint [ ... ] ]\n| tableconstraint\n| LIKE sourcetable [ likeoption ... ] }\n[, ... ]\n] )\n[ INHERITS ( parenttable [, ... ] ) ]\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\n\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\nOF typename [ (\n{ columnname [ WITH OPTIONS ] [ columnconstraint [ ... ] ]\n| tableconstraint }\n[, ... ]\n) ]\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\n\nCREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] tablename\nPARTITION OF parenttable [ (\n{ columnname [ WITH OPTIONS ] [ columnconstraint [ ... ] ]\n| tableconstraint }\n[, ... ]\n) ] { FOR VALUES partitionboundspec | DEFAULT }\n[ PARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]\n[ USING method ]\n[ WITH ( storageparameter [= value] [, ... ] ) | WITHOUT OIDS ]\n[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]\n[ TABLESPACE tablespacename ]\n\nwhere columnconstraint is:\n\n[ CONSTRAINT constraintname ]\n{ NOT NULL |\nNULL |\nCHECK ( expression ) [ NO INHERIT ] |\nDEFAULT defaultexpr |\nGENERATED ALWAYS AS ( generationexpr ) STORED |\nGENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequenceoptions ) ] |\nUNIQUE indexparameters |\nPRIMARY KEY indexparameters |\nREFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]\n[ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\n\nand tableconstraint is:\n\n[ CONSTRAINT constraintname ]\n{ CHECK ( expression ) [ NO INHERIT ] |\nUNIQUE ( columnname [, ... ] ) indexparameters |\nPRIMARY KEY ( columnname [, ... ] ) indexparameters |\nEXCLUDE [ USING indexmethod ] ( excludeelement WITH operator [, ... ] ) indexparameters [ WHERE ( predicate ) ] |\nFOREIGN KEY ( columnname [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]\n[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] }\n[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]\n\nand likeoption is:\n\n{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }\n\nand partitionboundspec is:\n\nIN ( partitionboundexpr [, ...] ) |\nFROM ( { partitionboundexpr | MINVALUE | MAXVALUE } [, ...] )\nTO ( { partitionboundexpr | MINVALUE | MAXVALUE } [, ...] ) |\nWITH ( MODULUS numericliteral, REMAINDER numericliteral )\n\nindexparameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:\n\n[ INCLUDE ( columnname [, ... ] ) ]\n[ WITH ( storageparameter [= value] [, ... ] ) ]\n[ USING INDEX TABLESPACE tablespacename ]\n\nexcludeelement in an EXCLUDE constraint is:\n\n{ columnname | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclassparameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]\n",
                "subsections": []
            },
            "DESCRIPTION": {
                "content": "CREATE TABLE will create a new, initially empty table in the current database. The table will\nbe owned by the user issuing the command.\n\nIf a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is\ncreated in the specified schema. Otherwise it is created in the current schema. Temporary\ntables exist in a special schema, so a schema name cannot be given when creating a temporary\ntable. The name of the table must be distinct from the name of any other table, sequence,\nindex, view, or foreign table in the same schema.\n\nCREATE TABLE also automatically creates a data type that represents the composite type\ncorresponding to one row of the table. Therefore, tables cannot have the same name as any\nexisting data type in the same schema.\n\nThe optional constraint clauses specify constraints (tests) that new or updated rows must\nsatisfy for an insert or update operation to succeed. A constraint is an SQL object that\nhelps define the set of valid values in the table in various ways.\n\nThere are two ways to define constraints: table constraints and column constraints. A column\nconstraint is defined as part of a column definition. A table constraint definition is not\ntied to a particular column, and it can encompass more than one column. Every column\nconstraint can also be written as a table constraint; a column constraint is only a\nnotational convenience for use when the constraint only affects one column.\n\nTo be able to create a table, you must have USAGE privilege on all column types or the type\nin the OF clause, respectively.\n",
                "subsections": []
            },
            "PARAMETERS": {
                "content": "TEMPORARY or TEMP\nIf specified, the table is created as a temporary table. Temporary tables are\nautomatically dropped at the end of a session, or optionally at the end of the current\ntransaction (see ON COMMIT below). The default searchpath includes the temporary schema\nfirst and so identically named existing permanent tables are not chosen for new plans\nwhile the temporary table exists, unless they are referenced with schema-qualified names.\nAny indexes created on a temporary table are automatically temporary as well.\n\nThe autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary\ntables. For this reason, appropriate vacuum and analyze operations should be performed\nvia session SQL commands. For example, if a temporary table is going to be used in\ncomplex queries, it is wise to run ANALYZE on the temporary table after it is populated.\n\nOptionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes\nno difference in PostgreSQL and is deprecated; see Compatibility below.\n\nUNLOGGED\nIf specified, the table is created as an unlogged table. Data written to unlogged tables\nis not written to the write-ahead log (see Chapter 30), which makes them considerably\nfaster than ordinary tables. However, they are not crash-safe: an unlogged table is\nautomatically truncated after a crash or unclean shutdown. The contents of an unlogged\ntable are also not replicated to standby servers. Any indexes created on an unlogged\ntable are automatically unlogged as well.\n\nIF NOT EXISTS\nDo not throw an error if a relation with the same name already exists. A notice is issued\nin this case. Note that there is no guarantee that the existing relation is anything like\nthe one that would have been created.\n\ntablename\nThe name (optionally schema-qualified) of the table to be created.\n\nOF typename\nCreates a typed table, which takes its structure from the specified composite type (name\noptionally schema-qualified). A typed table is tied to its type; for example the table\nwill be dropped if the type is dropped (with DROP TYPE ... CASCADE).\n\nWhen a typed table is created, then the data types of the columns are determined by the\nunderlying composite type and are not specified by the CREATE TABLE command. But the\nCREATE TABLE command can add defaults and constraints to the table and can specify\nstorage parameters.\n\ncolumnname\nThe name of a column to be created in the new table.\n\ndatatype\nThe data type of the column. This can include array specifiers. For more information on\nthe data types supported by PostgreSQL, refer to Chapter 8.\n\nCOLLATE collation\nThe COLLATE clause assigns a collation to the column (which must be of a collatable data\ntype). If not specified, the column data type's default collation is used.\n\nCOMPRESSION compressionmethod\nThe COMPRESSION clause sets the compression method for the column. Compression is\nsupported only for variable-width data types, and is used only when the column's storage\nmode is main or extended. (See ALTER TABLE (ALTERTABLE(7)) for information on column\nstorage modes.) Setting this property for a partitioned table has no direct effect,\nbecause such tables have no storage of their own, but the configured value will be\ninherited by newly-created partitions. The supported compression methods are pglz and\nlz4. (lz4 is available only if --with-lz4 was used when building PostgreSQL.) In\naddition, compressionmethod can be default to explicitly specify the default behavior,\nwhich is to consult the defaulttoastcompression setting at the time of data insertion\nto determine the method to use.\n\nINHERITS ( parenttable [, ... ] )\nThe optional INHERITS clause specifies a list of tables from which the new table\nautomatically inherits all columns. Parent tables can be plain tables or foreign tables.\n\nUse of INHERITS creates a persistent relationship between the new child table and its\nparent table(s). Schema modifications to the parent(s) normally propagate to children as\nwell, and by default the data of the child table is included in scans of the parent(s).\n\nIf the same column name exists in more than one parent table, an error is reported unless\nthe data types of the columns match in each of the parent tables. If there is no\nconflict, then the duplicate columns are merged to form a single column in the new table.\nIf the column name list of the new table contains a column name that is also inherited,\nthe data type must likewise match the inherited column(s), and the column definitions are\nmerged into one. If the new table explicitly specifies a default value for the column,\nthis default overrides any defaults from inherited declarations of the column. Otherwise,\nany parents that specify default values for the column must all specify the same default,\nor an error will be reported.\n\nCHECK constraints are merged in essentially the same way as columns: if multiple parent\ntables and/or the new table definition contain identically-named CHECK constraints, these\nconstraints must all have the same check expression, or an error will be reported.\nConstraints having the same name and expression will be merged into one copy. A\nconstraint marked NO INHERIT in a parent will not be considered. Notice that an unnamed\nCHECK constraint in the new table will never be merged, since a unique name will always\nbe chosen for it.\n\nColumn STORAGE settings are also copied from parent tables.\n\nIf a column in the parent table is an identity column, that property is not inherited. A\ncolumn in the child table can be declared identity column if desired.\n\nPARTITION BY { RANGE | LIST | HASH } ( { columnname | ( expression ) } [ opclass ] [, ...] )\nThe optional PARTITION BY clause specifies a strategy of partitioning the table. The\ntable thus created is called a partitioned table. The parenthesized list of columns or\nexpressions forms the partition key for the table. When using range or hash partitioning,\nthe partition key can include multiple columns or expressions (up to 32, but this limit\ncan be altered when building PostgreSQL), but for list partitioning, the partition key\nmust consist of a single column or expression.\n\nRange and list partitioning require a btree operator class, while hash partitioning\nrequires a hash operator class. If no operator class is specified explicitly, the default\noperator class of the appropriate type will be used; if no default operator class exists,\nan error will be raised. When hash partitioning is used, the operator class used must\nimplement support function 2 (see Section 38.16.3 for details).\n\nA partitioned table is divided into sub-tables (called partitions), which are created\nusing separate CREATE TABLE commands. The partitioned table is itself empty. A data row\ninserted into the table is routed to a partition based on the value of columns or\nexpressions in the partition key. If no existing partition matches the values in the new\nrow, an error will be reported.\n\nPartitioned tables do not support EXCLUDE constraints; however, you can define these\nconstraints on individual partitions.\n\nSee Section 5.11 for more discussion on table partitioning.\n\nPARTITION OF parenttable { FOR VALUES partitionboundspec | DEFAULT }\nCreates the table as a partition of the specified parent table. The table can be created\neither as a partition for specific values using FOR VALUES or as a default partition\nusing DEFAULT. Any indexes, constraints and user-defined row-level triggers that exist in\nthe parent table are cloned on the new partition.\n\nThe partitionboundspec must correspond to the partitioning method and partition key of\nthe parent table, and must not overlap with any existing partition of that parent. The\nform with IN is used for list partitioning, the form with FROM and TO is used for range\npartitioning, and the form with WITH is used for hash partitioning.\n\npartitionboundexpr is any variable-free expression (subqueries, window functions,\naggregate functions, and set-returning functions are not allowed). Its data type must\nmatch the data type of the corresponding partition key column. The expression is\nevaluated once at table creation time, so it can even contain volatile expressions such\nas CURRENTTIMESTAMP.\n\nWhen creating a list partition, NULL can be specified to signify that the partition\nallows the partition key column to be null. However, there cannot be more than one such\nlist partition for a given parent table.  NULL cannot be specified for range partitions.\n\nWhen creating a range partition, the lower bound specified with FROM is an inclusive\nbound, whereas the upper bound specified with TO is an exclusive bound. That is, the\nvalues specified in the FROM list are valid values of the corresponding partition key\ncolumns for this partition, whereas those in the TO list are not. Note that this\nstatement must be understood according to the rules of row-wise comparison\n(Section 9.24.5). For example, given PARTITION BY RANGE (x,y), a partition bound FROM (1,\n2) TO (3, 4) allows x=1 with any y>=2, x=2 with any non-null y, and x=3 with any y<4.\n\nThe special values MINVALUE and MAXVALUE may be used when creating a range partition to\nindicate that there is no lower or upper bound on the column's value. For example, a\npartition defined using FROM (MINVALUE) TO (10) allows any values less than 10, and a\npartition defined using FROM (10) TO (MAXVALUE) allows any values greater than or equal\nto 10.\n\nWhen creating a range partition involving more than one column, it can also make sense to\nuse MAXVALUE as part of the lower bound, and MINVALUE as part of the upper bound. For\nexample, a partition defined using FROM (0, MAXVALUE) TO (10, MAXVALUE) allows any rows\nwhere the first partition key column is greater than 0 and less than or equal to 10.\nSimilarly, a partition defined using FROM ('a', MINVALUE) TO ('b', MINVALUE) allows any\nrows where the first partition key column starts with \"a\".\n\nNote that if MINVALUE or MAXVALUE is used for one column of a partitioning bound, the\nsame value must be used for all subsequent columns. For example, (10, MINVALUE, 0) is not\na valid bound; you should write (10, MINVALUE, MINVALUE).\n\nAlso note that some element types, such as timestamp, have a notion of \"infinity\", which\nis just another value that can be stored. This is different from MINVALUE and MAXVALUE,\nwhich are not real values that can be stored, but rather they are ways of saying that the\nvalue is unbounded.  MAXVALUE can be thought of as being greater than any other value,\nincluding \"infinity\" and MINVALUE as being less than any other value, including \"minus\ninfinity\". Thus the range FROM ('infinity') TO (MAXVALUE) is not an empty range; it\nallows precisely one value to be stored — \"infinity\".\n\nIf DEFAULT is specified, the table will be created as the default partition of the parent\ntable. This option is not available for hash-partitioned tables. A partition key value\nnot fitting into any other partition of the given parent will be routed to the default\npartition.\n\nWhen a table has an existing DEFAULT partition and a new partition is added to it, the\ndefault partition must be scanned to verify that it does not contain any rows which\nproperly belong in the new partition. If the default partition contains a large number of\nrows, this may be slow. The scan will be skipped if the default partition is a foreign\ntable or if it has a constraint which proves that it cannot contain rows which should be\nplaced in the new partition.\n\nWhen creating a hash partition, a modulus and remainder must be specified. The modulus\nmust be a positive integer, and the remainder must be a non-negative integer less than\nthe modulus. Typically, when initially setting up a hash-partitioned table, you should\nchoose a modulus equal to the number of partitions and assign every table the same\nmodulus and a different remainder (see examples, below). However, it is not required that\nevery partition have the same modulus, only that every modulus which occurs among the\npartitions of a hash-partitioned table is a factor of the next larger modulus. This\nallows the number of partitions to be increased incrementally without needing to move all\nthe data at once. For example, suppose you have a hash-partitioned table with 8\npartitions, each of which has modulus 8, but find it necessary to increase the number of\npartitions to 16. You can detach one of the modulus-8 partitions, create two new\nmodulus-16 partitions covering the same portion of the key space (one with a remainder\nequal to the remainder of the detached partition, and the other with a remainder equal to\nthat value plus 8), and repopulate them with data. You can then repeat this -- perhaps at\na later time -- for each modulus-8 partition until none remain. While this may still\ninvolve a large amount of data movement at each step, it is still better than having to\ncreate a whole new table and move all the data at once.\n\nA partition must have the same column names and types as the partitioned table to which\nit belongs. Modifications to the column names or types of a partitioned table will\nautomatically propagate to all partitions.  CHECK constraints will be inherited\nautomatically by every partition, but an individual partition may specify additional\nCHECK constraints; additional constraints with the same name and condition as in the\nparent will be merged with the parent constraint. Defaults may be specified separately\nfor each partition. But note that a partition's default value is not applied when\ninserting a tuple through a partitioned table.\n\nRows inserted into a partitioned table will be automatically routed to the correct\npartition. If no suitable partition exists, an error will occur.\n\nOperations such as TRUNCATE which normally affect a table and all of its inheritance\nchildren will cascade to all partitions, but may also be performed on an individual\npartition.\n\nNote that creating a partition using PARTITION OF requires taking an ACCESS EXCLUSIVE\nlock on the parent partitioned table. Likewise, dropping a partition with DROP TABLE\nrequires taking an ACCESS EXCLUSIVE lock on the parent table. It is possible to use ALTER\nTABLE ATTACH/DETACH PARTITION to perform these operations with a weaker lock, thus\nreducing interference with concurrent operations on the partitioned table.\n\nLIKE sourcetable [ likeoption ... ]\nThe LIKE clause specifies a table from which the new table automatically copies all\ncolumn names, their data types, and their not-null constraints.\n\nUnlike INHERITS, the new table and original table are completely decoupled after creation\nis complete. Changes to the original table will not be applied to the new table, and it\nis not possible to include data of the new table in scans of the original table.\n\nAlso unlike INHERITS, columns and constraints copied by LIKE are not merged with\nsimilarly named columns and constraints. If the same name is specified explicitly or in\nanother LIKE clause, an error is signaled.\n\nThe optional likeoption clauses specify which additional properties of the original\ntable to copy. Specifying INCLUDING copies the property, specifying EXCLUDING omits the\nproperty.  EXCLUDING is the default. If multiple specifications are made for the same\nkind of object, the last one is used. The available options are:\n\nINCLUDING COMMENTS\nComments for the copied columns, check constraints, indexes, and extended statistics\nwill be copied. The default behavior is to exclude comments, resulting in the\ncorresponding objects in the new table having no comments.\n\nINCLUDING COMPRESSION\nCompression method of the columns will be copied. The default behavior is to exclude\ncompression methods, resulting in columns having the default compression method.\n\nINCLUDING CONSTRAINTS\nCHECK constraints will be copied. No distinction is made between column constraints\nand table constraints. Not-null constraints are always copied to the new table.\n\nINCLUDING DEFAULTS\nDefault expressions for the copied column definitions will be copied. Otherwise,\ndefault expressions are not copied, resulting in the copied columns in the new table\nhaving null defaults. Note that copying defaults that call database-modification\nfunctions, such as nextval, may create a functional linkage between the original and\nnew tables.\n\nINCLUDING GENERATED\nAny generation expressions of copied column definitions will be copied. By default,\nnew columns will be regular base columns.\n\nINCLUDING IDENTITY\nAny identity specifications of copied column definitions will be copied. A new\nsequence is created for each identity column of the new table, separate from the\nsequences associated with the old table.\n\nINCLUDING INDEXES\nIndexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original table will be\ncreated on the new table. Names for the new indexes and constraints are chosen\naccording to the default rules, regardless of how the originals were named. (This\nbehavior avoids possible duplicate-name failures for the new indexes.)\n\nINCLUDING STATISTICS\nExtended statistics are copied to the new table.\n\nINCLUDING STORAGE\nSTORAGE settings for the copied column definitions will be copied. The default\nbehavior is to exclude STORAGE settings, resulting in the copied columns in the new\ntable having type-specific default settings. For more on STORAGE settings, see\nSection 70.2.\n\nINCLUDING ALL\nINCLUDING ALL is an abbreviated form selecting all the available individual options.\n(It could be useful to write individual EXCLUDING clauses after INCLUDING ALL to\nselect all but some specific options.)\n\nThe LIKE clause can also be used to copy column definitions from views, foreign tables,\nor composite types. Inapplicable options (e.g., INCLUDING INDEXES from a view) are\nignored.\n\nCONSTRAINT constraintname\nAn optional name for a column or table constraint. If the constraint is violated, the\nconstraint name is present in error messages, so constraint names like col must be\npositive can be used to communicate helpful constraint information to client\napplications. (Double-quotes are needed to specify constraint names that contain spaces.)\nIf a constraint name is not specified, the system generates a name.\n\nNOT NULL\nThe column is not allowed to contain null values.\n\nNULL\nThe column is allowed to contain null values. This is the default.\n\nThis clause is only provided for compatibility with non-standard SQL databases. Its use\nis discouraged in new applications.\n\nCHECK ( expression ) [ NO INHERIT ]\nThe CHECK clause specifies an expression producing a Boolean result which new or updated\nrows must satisfy for an insert or update operation to succeed. Expressions evaluating to\nTRUE or UNKNOWN succeed. Should any row of an insert or update operation produce a FALSE\nresult, an error exception is raised and the insert or update does not alter the\ndatabase. A check constraint specified as a column constraint should reference that\ncolumn's value only, while an expression appearing in a table constraint can reference\nmultiple columns.\n\nCurrently, CHECK expressions cannot contain subqueries nor refer to variables other than\ncolumns of the current row (see Section 5.4.1). The system column tableoid may be\nreferenced, but not any other system column.\n\nA constraint marked with NO INHERIT will not propagate to child tables.\n\nWhen a table has multiple CHECK constraints, they will be tested for each row in\nalphabetical order by name, after checking NOT NULL constraints. (PostgreSQL versions\nbefore 9.5 did not honor any particular firing order for CHECK constraints.)\n\nDEFAULT defaultexpr\nThe DEFAULT clause assigns a default data value for the column whose column definition it\nappears within. The value is any variable-free expression (in particular,\ncross-references to other columns in the current table are not allowed). Subqueries are\nnot allowed either. The data type of the default expression must match the data type of\nthe column.\n\nThe default expression will be used in any insert operation that does not specify a value\nfor the column. If there is no default for a column, then the default is null.\n\nGENERATED ALWAYS AS ( generationexpr ) STORED\nThis clause creates the column as a generated column. The column cannot be written to,\nand when read the result of the specified expression will be returned.\n\nThe keyword STORED is required to signify that the column will be computed on write and\nwill be stored on disk.\n\nThe generation expression can refer to other columns in the table, but not other\ngenerated columns. Any functions and operators used must be immutable. References to\nother tables are not allowed.\n\nGENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequenceoptions ) ]\nThis clause creates the column as an identity column. It will have an implicit sequence\nattached to it and the column in new rows will automatically have values from the\nsequence assigned to it. Such a column is implicitly NOT NULL.\n\nThe clauses ALWAYS and BY DEFAULT determine how explicitly user-specified values are\nhandled in INSERT and UPDATE commands.\n\nIn an INSERT command, if ALWAYS is selected, a user-specified value is only accepted if\nthe INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is selected, then\nthe user-specified value takes precedence. See INSERT(7) for details. (In the COPY\ncommand, user-specified values are always used regardless of this setting.)\n\nIn an UPDATE command, if ALWAYS is selected, any update of the column to any value other\nthan DEFAULT will be rejected. If BY DEFAULT is selected, the column can be updated\nnormally. (There is no OVERRIDING clause for the UPDATE command.)\n\nThe optional sequenceoptions clause can be used to override the options of the sequence.\nSee CREATE SEQUENCE (CREATESEQUENCE(7)) for details.\n\nUNIQUE (column constraint)\nUNIQUE ( columnname [, ... ] ) [ INCLUDE ( columnname [, ...]) ] (table constraint)\nThe UNIQUE constraint specifies that a group of one or more columns of a table can\ncontain only unique values. The behavior of a unique table constraint is the same as that\nof a unique column constraint, with the additional capability to span multiple columns.\nThe constraint therefore enforces that any two rows must differ in at least one of these\ncolumns.\n\nFor the purpose of a unique constraint, null values are not considered equal.\n\nEach unique constraint should name a set of columns that is different from the set of\ncolumns named by any other unique or primary key constraint defined for the table.\n(Otherwise, redundant unique constraints will be discarded.)\n\nWhen establishing a unique constraint for a multi-level partition hierarchy, all the\ncolumns in the partition key of the target partitioned table, as well as those of all its\ndescendant partitioned tables, must be included in the constraint definition.\n\nAdding a unique constraint will automatically create a unique btree index on the column\nor group of columns used in the constraint.\n\nThe optional INCLUDE clause adds to that index one or more columns that are simply\n“payload”: uniqueness is not enforced on them, and the index cannot be searched on the\nbasis of those columns. However they can be retrieved by an index-only scan. Note that\nalthough the constraint is not enforced on included columns, it still depends on them.\nConsequently, some operations on such columns (e.g., DROP COLUMN) can cause cascaded\nconstraint and index deletion.\n\nPRIMARY KEY (column constraint)\nPRIMARY KEY ( columnname [, ... ] ) [ INCLUDE ( columnname [, ...]) ] (table constraint)\nThe PRIMARY KEY constraint specifies that a column or columns of a table can contain only\nunique (non-duplicate), nonnull values. Only one primary key can be specified for a\ntable, whether as a column constraint or a table constraint.\n\nThe primary key constraint should name a set of columns that is different from the set of\ncolumns named by any unique constraint defined for the same table. (Otherwise, the unique\nconstraint is redundant and will be discarded.)\n\nPRIMARY KEY enforces the same data constraints as a combination of UNIQUE and NOT NULL.\nHowever, identifying a set of columns as the primary key also provides metadata about the\ndesign of the schema, since a primary key implies that other tables can rely on this set\nof columns as a unique identifier for rows.\n\nWhen placed on a partitioned table, PRIMARY KEY constraints share the restrictions\npreviously described for UNIQUE constraints.\n\nAdding a PRIMARY KEY constraint will automatically create a unique btree index on the\ncolumn or group of columns used in the constraint.\n\nThe optional INCLUDE clause adds to that index one or more columns that are simply\n“payload”: uniqueness is not enforced on them, and the index cannot be searched on the\nbasis of those columns. However they can be retrieved by an index-only scan. Note that\nalthough the constraint is not enforced on included columns, it still depends on them.\nConsequently, some operations on such columns (e.g., DROP COLUMN) can cause cascaded\nconstraint and index deletion.\n\nEXCLUDE [ USING indexmethod ] ( excludeelement WITH operator [, ... ] ) indexparameters [\nWHERE ( predicate ) ]\nThe EXCLUDE clause defines an exclusion constraint, which guarantees that if any two rows\nare compared on the specified column(s) or expression(s) using the specified operator(s),\nnot all of these comparisons will return TRUE. If all of the specified operators test for\nequality, this is equivalent to a UNIQUE constraint, although an ordinary unique\nconstraint will be faster. However, exclusion constraints can specify constraints that\nare more general than simple equality. For example, you can specify a constraint that no\ntwo rows in the table contain overlapping circles (see Section 8.8) by using the &&\noperator. The operator(s) are required to be commutative.\n\nExclusion constraints are implemented using an index, so each specified operator must be\nassociated with an appropriate operator class (see Section 11.10) for the index access\nmethod indexmethod. Each excludeelement defines a column of the index, so it can\noptionally specify a collation, an operator class, operator class parameters, and/or\nordering options; these are described fully under CREATE INDEX (CREATEINDEX(7)).\n\nThe access method must support amgettuple (see Chapter 62); at present this means GIN\ncannot be used. Although it's allowed, there is little point in using B-tree or hash\nindexes with an exclusion constraint, because this does nothing that an ordinary unique\nconstraint doesn't do better. So in practice the access method will always be GiST or\nSP-GiST.\n\nThe predicate allows you to specify an exclusion constraint on a subset of the table;\ninternally this creates a partial index. Note that parentheses are required around the\npredicate.\n\nREFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE referentialaction ] [\nON UPDATE referentialaction ] (column constraint)\nFOREIGN KEY ( columnname [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH\nmatchtype ] [ ON DELETE referentialaction ] [ ON UPDATE referentialaction ] (table\nconstraint)\nThese clauses specify a foreign key constraint, which requires that a group of one or\nmore columns of the new table must only contain values that match values in the\nreferenced column(s) of some row of the referenced table. If the refcolumn list is\nomitted, the primary key of the reftable is used. Otherwise, the refcolumn list must\nrefer to the columns of a non-deferrable unique or primary key constraint or be the\ncolumns of a non-partial unique index. The user must have REFERENCES permission on the\nreferenced table (either the whole table, or the specific referenced columns). The\naddition of a foreign key constraint requires a SHARE ROW EXCLUSIVE lock on the\nreferenced table. Note that foreign key constraints cannot be defined between temporary\ntables and permanent tables.\n\nA value inserted into the referencing column(s) is matched against the values of the\nreferenced table and referenced columns using the given match type. There are three match\ntypes: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default).  MATCH FULL\nwill not allow one column of a multicolumn foreign key to be null unless all foreign key\ncolumns are null; if they are all null, the row is not required to have a match in the\nreferenced table.  MATCH SIMPLE allows any of the foreign key columns to be null; if any\nof them are null, the row is not required to have a match in the referenced table.  MATCH\nPARTIAL is not yet implemented. (Of course, NOT NULL constraints can be applied to the\nreferencing column(s) to prevent these cases from arising.)\n\nIn addition, when the data in the referenced columns is changed, certain actions are\nperformed on the data in this table's columns. The ON DELETE clause specifies the action\nto perform when a referenced row in the referenced table is being deleted. Likewise, the\nON UPDATE clause specifies the action to perform when a referenced column in the\nreferenced table is being updated to a new value. If the row is updated, but the\nreferenced column is not actually changed, no action is done. Referential actions other\nthan the NO ACTION check cannot be deferred, even if the constraint is declared\ndeferrable. There are the following possible actions for each clause:\n\nNO ACTION\nProduce an error indicating that the deletion or update would create a foreign key\nconstraint violation. If the constraint is deferred, this error will be produced at\nconstraint check time if there still exist any referencing rows. This is the default\naction.\n\nRESTRICT\nProduce an error indicating that the deletion or update would create a foreign key\nconstraint violation. This is the same as NO ACTION except that the check is not\ndeferrable.\n\nCASCADE\nDelete any rows referencing the deleted row, or update the values of the referencing\ncolumn(s) to the new values of the referenced columns, respectively.\n\nSET NULL\nSet the referencing column(s) to null.\n\nSET DEFAULT\nSet the referencing column(s) to their default values. (There must be a row in the\nreferenced table matching the default values, if they are not null, or the operation\nwill fail.)\n\nIf the referenced column(s) are changed frequently, it might be wise to add an index to\nthe referencing column(s) so that referential actions associated with the foreign key\nconstraint can be performed more efficiently.\n\nDEFERRABLE\nNOT DEFERRABLE\nThis controls whether the constraint can be deferred. A constraint that is not deferrable\nwill be checked immediately after every command. Checking of constraints that are\ndeferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS\ncommand).  NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE,\nand REFERENCES (foreign key) constraints accept this clause.  NOT NULL and CHECK\nconstraints are not deferrable. Note that deferrable constraints cannot be used as\nconflict arbiters in an INSERT statement that includes an ON CONFLICT clause.\n\nINITIALLY IMMEDIATE\nINITIALLY DEFERRED\nIf a constraint is deferrable, this clause specifies the default time to check the\nconstraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement.\nThis is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the\nend of the transaction. The constraint check time can be altered with the SET CONSTRAINTS\ncommand.\n\nUSING method\nThis optional clause specifies the table access method to use to store the contents for\nthe new table; the method needs be an access method of type TABLE. See Chapter 61 for\nmore information. If this option is not specified, the default table access method is\nchosen for the new table. See defaulttableaccessmethod for more information.\n\nWITH ( storageparameter [= value] [, ... ] )\nThis clause specifies optional storage parameters for a table or index; see Storage\nParameters below for more information. For backward-compatibility the WITH clause for a\ntable can also include OIDS=FALSE to specify that rows of the new table should not\ncontain OIDs (object identifiers), OIDS=TRUE is not supported anymore.\n\nWITHOUT OIDS\nThis is backward-compatible syntax for declaring a table WITHOUT OIDS, creating a table\nWITH OIDS is not supported anymore.\n\nON COMMIT\nThe behavior of temporary tables at the end of a transaction block can be controlled\nusing ON COMMIT. The three options are:\n\nPRESERVE ROWS\nNo special action is taken at the ends of transactions. This is the default behavior.\n\nDELETE ROWS\nAll rows in the temporary table will be deleted at the end of each transaction block.\nEssentially, an automatic TRUNCATE is done at each commit. When used on a partitioned\ntable, this is not cascaded to its partitions.\n\nDROP\nThe temporary table will be dropped at the end of the current transaction block. When\nused on a partitioned table, this action drops its partitions and when used on tables\nwith inheritance children, it drops the dependent children.\n\nTABLESPACE tablespacename\nThe tablespacename is the name of the tablespace in which the new table is to be\ncreated. If not specified, defaulttablespace is consulted, or temptablespaces if the\ntable is temporary. For partitioned tables, since no storage is required for the table\nitself, the tablespace specified overrides defaulttablespace as the default tablespace\nto use for any newly created partitions when no other tablespace is explicitly specified.\n\nUSING INDEX TABLESPACE tablespacename\nThis clause allows selection of the tablespace in which the index associated with a\nUNIQUE, PRIMARY KEY, or EXCLUDE constraint will be created. If not specified,\ndefaulttablespace is consulted, or temptablespaces if the table is temporary.\n",
                "subsections": [
                    {
                        "name": "Storage Parameters",
                        "content": "The WITH clause can specify storage parameters for tables, and for indexes associated with a\nUNIQUE, PRIMARY KEY, or EXCLUDE constraint. Storage parameters for indexes are documented in\nCREATE INDEX (CREATEINDEX(7)). The storage parameters currently available for tables are\nlisted below. For many of these parameters, as shown, there is an additional parameter with\nthe same name prefixed with toast., which controls the behavior of the table's secondary\nTOAST table, if any (see Section 70.2 for more information about TOAST). If a table parameter\nvalue is set and the equivalent toast.  parameter is not, the TOAST table will use the\ntable's parameter value. Specifying these parameters for partitioned tables is not supported,\nbut you may specify them for individual leaf partitions.\n\nfillfactor (integer)\nThe fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is\nthe default. When a smaller fillfactor is specified, INSERT operations pack table pages\nonly to the indicated percentage; the remaining space on each page is reserved for\nupdating rows on that page. This gives UPDATE a chance to place the updated copy of a row\non the same page as the original, which is more efficient than placing it on a different\npage, and makes heap-only tuple updates more likely. For a table whose entries are never\nupdated, complete packing is the best choice, but in heavily updated tables smaller\nfillfactors are appropriate. This parameter cannot be set for TOAST tables.\n\ntoasttupletarget (integer)\nThe toasttupletarget specifies the minimum tuple length required before we try to\ncompress and/or move long column values into TOAST tables, and is also the target length\nwe try to reduce the length below once toasting begins. This affects columns marked as\nExternal (for move), Main (for compression), or Extended (for both) and applies only to\nnew tuples. There is no effect on existing rows. By default this parameter is set to\nallow at least 4 tuples per block, which with the default block size will be 2040 bytes.\nValid values are between 128 bytes and the (block size - header), by default 8160 bytes.\nChanging this value may not be useful for very short or very long rows. Note that the\ndefault setting is often close to optimal, and it is possible that setting this parameter\ncould have negative effects in some cases. This parameter cannot be set for TOAST tables.\n\nparallelworkers (integer)\nThis sets the number of workers that should be used to assist a parallel scan of this\ntable. If not set, the system will determine a value based on the relation size. The\nactual number of workers chosen by the planner or by utility statements that use parallel\nscans may be less, for example due to the setting of maxworkerprocesses.\n\nautovacuumenabled, toast.autovacuumenabled (boolean)\nEnables or disables the autovacuum daemon for a particular table. If true, the autovacuum\ndaemon will perform automatic VACUUM and/or ANALYZE operations on this table following\nthe rules discussed in Section 25.1.6. If false, this table will not be autovacuumed,\nexcept to prevent transaction ID wraparound. See Section 25.1.5 for more about wraparound\nprevention. Note that the autovacuum daemon does not run at all (except to prevent\ntransaction ID wraparound) if the autovacuum parameter is false; setting individual\ntables' storage parameters does not override that. Therefore there is seldom much point\nin explicitly setting this storage parameter to true, only to false.\n\nvacuumindexcleanup, toast.vacuumindexcleanup (enum)\nForces or disables index cleanup when VACUUM is run on this table. The default value is\nAUTO. With OFF, index cleanup is disabled, with ON it is enabled, and with AUTO a\ndecision is made dynamically, each time VACUUM runs. The dynamic behavior allows VACUUM\nto avoid needlessly scanning indexes to remove very few dead tuples. Forcibly disabling\nall index cleanup can speed up VACUUM very significantly, but may also lead to severely\nbloated indexes if table modifications are frequent. The INDEXCLEANUP parameter of\nVACUUM, if specified, overrides the value of this option.\n\nvacuumtruncate, toast.vacuumtruncate (boolean)\nEnables or disables vacuum to try to truncate off any empty pages at the end of this\ntable. The default value is true. If true, VACUUM and autovacuum do the truncation and\nthe disk space for the truncated pages is returned to the operating system. Note that the\ntruncation requires ACCESS EXCLUSIVE lock on the table. The TRUNCATE parameter of VACUUM,\nif specified, overrides the value of this option.\n\nautovacuumvacuumthreshold, toast.autovacuumvacuumthreshold (integer)\nPer-table value for autovacuumvacuumthreshold parameter.\n\nautovacuumvacuumscalefactor, toast.autovacuumvacuumscalefactor (floating point)\nPer-table value for autovacuumvacuumscalefactor parameter.\n\nautovacuumvacuuminsertthreshold, toast.autovacuumvacuuminsertthreshold (integer)\nPer-table value for autovacuumvacuuminsertthreshold parameter. The special value of -1\nmay be used to disable insert vacuums on the table.\n\nautovacuumvacuuminsertscalefactor, toast.autovacuumvacuuminsertscalefactor (floating\npoint)\nPer-table value for autovacuumvacuuminsertscalefactor parameter.\n\nautovacuumanalyzethreshold (integer)\nPer-table value for autovacuumanalyzethreshold parameter.\n\nautovacuumanalyzescalefactor (floating point)\nPer-table value for autovacuumanalyzescalefactor parameter.\n\nautovacuumvacuumcostdelay, toast.autovacuumvacuumcostdelay (floating point)\nPer-table value for autovacuumvacuumcostdelay parameter.\n\nautovacuumvacuumcostlimit, toast.autovacuumvacuumcostlimit (integer)\nPer-table value for autovacuumvacuumcostlimit parameter.\n\nautovacuumfreezeminage, toast.autovacuumfreezeminage (integer)\nPer-table value for vacuumfreezeminage parameter. Note that autovacuum will ignore\nper-table autovacuumfreezeminage parameters that are larger than half the system-wide\nautovacuumfreezemaxage setting.\n\nautovacuumfreezemaxage, toast.autovacuumfreezemaxage (integer)\nPer-table value for autovacuumfreezemaxage parameter. Note that autovacuum will ignore\nper-table autovacuumfreezemaxage parameters that are larger than the system-wide\nsetting (it can only be set smaller).\n\nautovacuumfreezetableage, toast.autovacuumfreezetableage (integer)\nPer-table value for vacuumfreezetableage parameter.\n\nautovacuummultixactfreezeminage, toast.autovacuummultixactfreezeminage (integer)\nPer-table value for vacuummultixactfreezeminage parameter. Note that autovacuum will\nignore per-table autovacuummultixactfreezeminage parameters that are larger than half\nthe system-wide autovacuummultixactfreezemaxage setting.\n\nautovacuummultixactfreezemaxage, toast.autovacuummultixactfreezemaxage (integer)\nPer-table value for autovacuummultixactfreezemaxage parameter. Note that autovacuum\nwill ignore per-table autovacuummultixactfreezemaxage parameters that are larger than\nthe system-wide setting (it can only be set smaller).\n\nautovacuummultixactfreezetableage, toast.autovacuummultixactfreezetableage (integer)\nPer-table value for vacuummultixactfreezetableage parameter.\n\nlogautovacuumminduration, toast.logautovacuumminduration (integer)\nPer-table value for logautovacuumminduration parameter.\n\nusercatalogtable (boolean)\nDeclare the table as an additional catalog table for purposes of logical replication. See\nSection 49.6.2 for details. This parameter cannot be set for TOAST tables.\n"
                    }
                ]
            },
            "NOTES": {
                "content": "PostgreSQL automatically creates an index for each unique constraint and primary key\nconstraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for\nprimary key columns. (See CREATE INDEX (CREATEINDEX(7)) for more information.)\n\nUnique constraints and primary keys are not inherited in the current implementation. This\nmakes the combination of inheritance and unique constraints rather dysfunctional.\n\nA table cannot have more than 1600 columns. (In practice, the effective limit is usually\nlower because of tuple-length constraints.)\n",
                "subsections": []
            },
            "EXAMPLES": {
                "content": "Create table films and table distributors:\n\nCREATE TABLE films (\ncode        char(5) CONSTRAINT firstkey PRIMARY KEY,\ntitle       varchar(40) NOT NULL,\ndid         integer NOT NULL,\ndateprod   date,\nkind        varchar(10),\nlen         interval hour to minute\n);\n\nCREATE TABLE distributors (\ndid    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,\nname   varchar(40) NOT NULL CHECK (name <> '')\n);\n\nCreate a table with a 2-dimensional array:\n\nCREATE TABLE arrayint (\nvector  int[][]\n);\n\nDefine a unique table constraint for the table films. Unique table constraints can be defined\non one or more columns of the table:\n\nCREATE TABLE films (\ncode        char(5),\ntitle       varchar(40),\ndid         integer,\ndateprod   date,\nkind        varchar(10),\nlen         interval hour to minute,\nCONSTRAINT production UNIQUE(dateprod)\n);\n\nDefine a check column constraint:\n\nCREATE TABLE distributors (\ndid     integer CHECK (did > 100),\nname    varchar(40)\n);\n\nDefine a check table constraint:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40),\nCONSTRAINT con1 CHECK (did > 100 AND name <> '')\n);\n\nDefine a primary key table constraint for the table films:\n\nCREATE TABLE films (\ncode        char(5),\ntitle       varchar(40),\ndid         integer,\ndateprod   date,\nkind        varchar(10),\nlen         interval hour to minute,\nCONSTRAINT codetitle PRIMARY KEY(code,title)\n);\n\nDefine a primary key constraint for table distributors. The following two examples are\nequivalent, the first using the table constraint syntax, the second the column constraint\nsyntax:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40),\nPRIMARY KEY(did)\n);\n\nCREATE TABLE distributors (\ndid     integer PRIMARY KEY,\nname    varchar(40)\n);\n\nAssign a literal constant default value for the column name, arrange for the default value of\ncolumn did to be generated by selecting the next value of a sequence object, and make the\ndefault value of modtime be the time at which the row is inserted:\n\nCREATE TABLE distributors (\nname      varchar(40) DEFAULT 'Luso Films',\ndid       integer DEFAULT nextval('distributorsserial'),\nmodtime   timestamp DEFAULT currenttimestamp\n);\n\nDefine two NOT NULL column constraints on the table distributors, one of which is explicitly\ngiven a name:\n\nCREATE TABLE distributors (\ndid     integer CONSTRAINT nonull NOT NULL,\nname    varchar(40) NOT NULL\n);\n\nDefine a unique constraint for the name column:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40) UNIQUE\n);\n\nThe same, specified as a table constraint:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40),\nUNIQUE(name)\n);\n\nCreate the same table, specifying 70% fill factor for both the table and its unique index:\n\nCREATE TABLE distributors (\ndid     integer,\nname    varchar(40),\nUNIQUE(name) WITH (fillfactor=70)\n)\nWITH (fillfactor=70);\n\nCreate table circles with an exclusion constraint that prevents any two circles from\noverlapping:\n\nCREATE TABLE circles (\nc circle,\nEXCLUDE USING gist (c WITH &&)\n);\n\nCreate table cinemas in tablespace diskvol1:\n\nCREATE TABLE cinemas (\nid serial,\nname text,\nlocation text\n) TABLESPACE diskvol1;\n\nCreate a composite type and a typed table:\n\nCREATE TYPE employeetype AS (name text, salary numeric);\n\nCREATE TABLE employees OF employeetype (\nPRIMARY KEY (name),\nsalary WITH OPTIONS DEFAULT 1000\n);\n\nCreate a range partitioned table:\n\nCREATE TABLE measurement (\nlogdate         date not null,\npeaktemp        int,\nunitsales       int\n) PARTITION BY RANGE (logdate);\n\nCreate a range partitioned table with multiple columns in the partition key:\n\nCREATE TABLE measurementyearmonth (\nlogdate         date not null,\npeaktemp        int,\nunitsales       int\n) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));\n\nCreate a list partitioned table:\n\nCREATE TABLE cities (\ncityid      bigserial not null,\nname         text not null,\npopulation   bigint\n) PARTITION BY LIST (left(lower(name), 1));\n\nCreate a hash partitioned table:\n\nCREATE TABLE orders (\norderid     bigint not null,\ncustid      bigint not null,\nstatus       text\n) PARTITION BY HASH (orderid);\n\nCreate partition of a range partitioned table:\n\nCREATE TABLE measurementy2016m07\nPARTITION OF measurement (\nunitsales DEFAULT 0\n) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');\n\nCreate a few partitions of a range partitioned table with multiple columns in the partition\nkey:\n\nCREATE TABLE measurementymolder\nPARTITION OF measurementyearmonth\nFOR VALUES FROM (MINVALUE, MINVALUE) TO (2016, 11);\n\nCREATE TABLE measurementymy2016m11\nPARTITION OF measurementyearmonth\nFOR VALUES FROM (2016, 11) TO (2016, 12);\n\nCREATE TABLE measurementymy2016m12\nPARTITION OF measurementyearmonth\nFOR VALUES FROM (2016, 12) TO (2017, 01);\n\nCREATE TABLE measurementymy2017m01\nPARTITION OF measurementyearmonth\nFOR VALUES FROM (2017, 01) TO (2017, 02);\n\nCreate partition of a list partitioned table:\n\nCREATE TABLE citiesab\nPARTITION OF cities (\nCONSTRAINT cityidnonzero CHECK (cityid != 0)\n) FOR VALUES IN ('a', 'b');\n\nCreate partition of a list partitioned table that is itself further partitioned and then add\na partition to it:\n\nCREATE TABLE citiesab\nPARTITION OF cities (\nCONSTRAINT cityidnonzero CHECK (cityid != 0)\n) FOR VALUES IN ('a', 'b') PARTITION BY RANGE (population);\n\nCREATE TABLE citiesab10000to100000\nPARTITION OF citiesab FOR VALUES FROM (10000) TO (100000);\n\nCreate partitions of a hash partitioned table:\n\nCREATE TABLE ordersp1 PARTITION OF orders\nFOR VALUES WITH (MODULUS 4, REMAINDER 0);\nCREATE TABLE ordersp2 PARTITION OF orders\nFOR VALUES WITH (MODULUS 4, REMAINDER 1);\nCREATE TABLE ordersp3 PARTITION OF orders\nFOR VALUES WITH (MODULUS 4, REMAINDER 2);\nCREATE TABLE ordersp4 PARTITION OF orders\nFOR VALUES WITH (MODULUS 4, REMAINDER 3);\n\nCreate a default partition:\n\nCREATE TABLE citiespartdef\nPARTITION OF cities DEFAULT;\n",
                "subsections": []
            },
            "COMPATIBILITY": {
                "content": "The CREATE TABLE command conforms to the SQL standard, with exceptions listed below.\n",
                "subsections": [
                    {
                        "name": "Temporary Tables",
                        "content": "Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect\nis not the same. In the standard, temporary tables are defined just once and automatically\nexist (starting with empty contents) in every session that needs them.  PostgreSQL instead\nrequires each session to issue its own CREATE TEMPORARY TABLE command for each temporary\ntable to be used. This allows different sessions to use the same temporary table name for\ndifferent purposes, whereas the standard's approach constrains all instances of a given\ntemporary table name to have the same table structure.\n\nThe standard's definition of the behavior of temporary tables is widely ignored.\nPostgreSQL's behavior on this point is similar to that of several other SQL databases.\n\nThe SQL standard also distinguishes between global and local temporary tables, where a local\ntemporary table has a separate set of contents for each SQL module within each session,\nthough its definition is still shared across sessions. Since PostgreSQL does not support SQL\nmodules, this distinction is not relevant in PostgreSQL.\n\nFor compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary\ntable declaration, but they currently have no effect. Use of these keywords is discouraged,\nsince future versions of PostgreSQL might adopt a more standard-compliant interpretation of\ntheir meaning.\n\nThe ON COMMIT clause for temporary tables also resembles the SQL standard, but has some\ndifferences. If the ON COMMIT clause is omitted, SQL specifies that the default behavior is\nON COMMIT DELETE ROWS. However, the default behavior in PostgreSQL is ON COMMIT PRESERVE\nROWS. The ON COMMIT DROP option does not exist in SQL.\n"
                    },
                    {
                        "name": "Non-Deferred Uniqueness Constraints",
                        "content": "When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness\nimmediately whenever a row is inserted or modified. The SQL standard says that uniqueness\nshould be enforced only at the end of the statement; this makes a difference when, for\nexample, a single command updates multiple key values. To obtain standard-compliant behavior,\ndeclare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware\nthat this can be significantly slower than immediate uniqueness checking.\n"
                    },
                    {
                        "name": "Column Check Constraints",
                        "content": "The SQL standard says that CHECK column constraints can only refer to the column they apply\nto; only CHECK table constraints can refer to multiple columns.  PostgreSQL does not enforce\nthis restriction; it treats column and table check constraints alike.\n"
                    },
                    {
                        "name": "EXCLUDE Constraint",
                        "content": "The EXCLUDE constraint type is a PostgreSQL extension.\n"
                    },
                    {
                        "name": "Foreign Key Constraints",
                        "content": "It is a PostgreSQL extension that a foreign key constraint may reference columns of a unique\nindex instead of columns of a primary key or unique constraint.\n\nNULL ““Constraint””\nThe NULL “constraint” (actually a non-constraint) is a PostgreSQL extension to the SQL\nstandard that is included for compatibility with some other database systems (and for\nsymmetry with the NOT NULL constraint). Since it is the default for any column, its presence\nis simply noise.\n"
                    },
                    {
                        "name": "Constraint Naming",
                        "content": "The SQL standard says that table and domain constraints must have names that are unique\nacross the schema containing the table or domain.  PostgreSQL is laxer: it only requires\nconstraint names to be unique across the constraints attached to a particular table or\ndomain. However, this extra freedom does not exist for index-based constraints (UNIQUE,\nPRIMARY KEY, and EXCLUDE constraints), because the associated index is named the same as the\nconstraint, and index names must be unique across all relations within the same schema.\n\nCurrently, PostgreSQL does not record names for NOT NULL constraints at all, so they are not\nsubject to the uniqueness restriction. This might change in a future release.\n"
                    },
                    {
                        "name": "Inheritance",
                        "content": "Multiple inheritance via the INHERITS clause is a PostgreSQL language extension. SQL:1999 and\nlater define single inheritance using a different syntax and different semantics.\nSQL:1999-style inheritance is not yet supported by PostgreSQL.\n"
                    },
                    {
                        "name": "Zero-Column Tables",
                        "content": "PostgreSQL allows a table of no columns to be created (for example, CREATE TABLE foo();).\nThis is an extension from the SQL standard, which does not allow zero-column tables.\nZero-column tables are not in themselves very useful, but disallowing them creates odd\nspecial cases for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec\nrestriction.\n"
                    },
                    {
                        "name": "Multiple Identity Columns",
                        "content": "PostgreSQL allows a table to have more than one identity column. The standard specifies that\na table can have at most one identity column. This is relaxed mainly to give more flexibility\nfor doing schema changes or migrations. Note that the INSERT command supports only one\noverride clause that applies to the entire statement, so having multiple identity columns\nwith different behaviors is not well supported.\n"
                    },
                    {
                        "name": "Generated Columns",
                        "content": "The option STORED is not standard but is also used by other SQL implementations. The SQL\nstandard does not specify the storage of generated columns.\n"
                    },
                    {
                        "name": "LIKE Clause",
                        "content": "While a LIKE clause exists in the SQL standard, many of the options that PostgreSQL accepts\nfor it are not in the standard, and some of the standard's options are not implemented by\nPostgreSQL.\n"
                    },
                    {
                        "name": "WITH Clause",
                        "content": "The WITH clause is a PostgreSQL extension; storage parameters are not in the standard.\n"
                    },
                    {
                        "name": "Tablespaces",
                        "content": "The PostgreSQL concept of tablespaces is not part of the standard. Hence, the clauses\nTABLESPACE and USING INDEX TABLESPACE are extensions.\n"
                    },
                    {
                        "name": "Typed Tables",
                        "content": "Typed tables implement a subset of the SQL standard. According to the standard, a typed table\nhas columns corresponding to the underlying composite type as well as one other column that\nis the “self-referencing column”.  PostgreSQL does not support self-referencing columns\nexplicitly.\n"
                    },
                    {
                        "name": "PARTITION BY Clause",
                        "content": "The PARTITION BY clause is a PostgreSQL extension.\n"
                    },
                    {
                        "name": "PARTITION OF Clause",
                        "content": "The PARTITION OF clause is a PostgreSQL extension.\n"
                    }
                ]
            },
            "SEE ALSO": {
                "content": "ALTER TABLE (ALTERTABLE(7)), DROP TABLE (DROPTABLE(7)), CREATE TABLE AS\n(CREATETABLEAS(7)), CREATE TABLESPACE (CREATETABLESPACE(7)), CREATE TYPE (CREATETYPE(7))\n\n\n\nPostgreSQL 14.23                                2026                                 CREATE TABLE(7)",
                "subsections": []
            }
        }
    }
}