# Postgres DDL Support

This page lists Postgres DDL statements and describes the level of support for each statement in CockroachDB. In some cases where features are not implemented, workarounds are suggested. Where relevant, CockroachDB extensions are listed.

Note:

The Postgres reference syntax used in this document was taken from the Postgres 10 docs. Any features specific to Postgres 11 are not covered here.

## Tablespace-level DDL

From the Postgres `CREATE TABLESPACE` documentation:

    CREATE TABLESPACE tablespace_name
        [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
        LOCATION 'directory'
        [ WITH ( tablespace_option = value [ ... ] ) ]

### Support in CockroachDB

This feature is not supported in CockroachDB.

Instead, CockroachDB nodes should be started with the `--store` parameter and zone configurations should be used to attach databases/tables to the appropriate stores.

## Database-level DDL

Note:

Postgres uses the terms "database" and "catalog" interchangeably.

### `CREATE DATABASE`

From the Postgres `CREATE DATABASE` documentation:

    CREATE DATABASE name
        [ [ WITH ] [ OWNER [=] user_name ]
               [ TEMPLATE [=] template ]
               [ ENCODING [=] encoding ]
               [ LC_COLLATE [=] lc_collate ]
               [ LC_CTYPE [=] lc_ctype ]
               [ TABLESPACE [=] tablespace_name ]
               [ ALLOW_CONNECTIONS [=] allowconn ]
               [ CONNECTION LIMIT [=] connlimit ]
               [ IS_TEMPLATE [=] istemplate ] ]

#### Support in CockroachDB

The basic form without options is supported in CockroachDB 2.1. For more information, see `CREATE DATABASE`.

Supported options:

`WITH` Option | Supported in 2.1? | Notes
--- | --- | ---
`OWNER` | No | See #30875.
`TEMPLATE` | Yes/Ignored | Fixed "template0", no template actually used. See #10151.
`IS_TEMPLATE` | No | See #10151.
`ENCODING` | Yes/Ignored | Fixed "UTF-8"/"UNICODE".
`LC_COLLATE` | Yes/Ignored | Fixed "C"/"C.UTF-8". See #16618.
`LC_CTYPE` | Yes/Ignored | Fixed "C"/"C.UTF-8".
`TABLESPACE` | No | See Tablespace-level DDL.
`ALLOW_CONNECTIONS` | No |
`CONNECTION LIMIT` | No |

### `ALTER DATABASE`

From the Postgres `ALTER DATABASE` documentation:

    ALTER DATABASE name [ [ WITH ] option [ ... ] ]

    where option can be:

        ALLOW_CONNECTIONS allowconn
        CONNECTION LIMIT connlimit
        IS_TEMPLATE istemplate

    ALTER DATABASE name RENAME TO new_name

    ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

    ALTER DATABASE name SET TABLESPACE new_tablespace

    ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
    ALTER DATABASE name SET configuration_parameter FROM CURRENT
    ALTER DATABASE name RESET configuration_parameter
    ALTER DATABASE name RESET ALL

#### Support in CockroachDB

Only `ALTER DATABASE ... RENAME TO ...` is supported. For more information, see `ALTER DATABASE`.

Owner support is conditional on the completion of #30875.

#### Extensions

- Use `ALTER DATABASE ... CONFIGURE ZONE ...` to configure replication zones.

### `DROP DATABASE`

From the Postgres `DROP DATABASE` documentation:

    DROP DATABASE [ IF EXISTS ] name

#### Support in CockroachDB

Supported in CockroachDB.

#### Extensions

    DROP DATABASE [ IF EXISTS ] name [ CASCADE | RESTRICT ]

For more information, see `DROP DATABASE`.

## Schema-level DDL

Note:

Currently, CockroachDB supports a single pre-defined schema called `public`.

### `CREATE SCHEMA`

From the Postgres `CREATE SCHEMA` documentation:

    CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
    CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
    CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
    CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

    where role_specification can be:

        user_name
      | CURRENT_USER
      | SESSION_USER

#### Support in CockroachDB

Not supported. For more information, see #26443.

### `ALTER SCHEMA`

From the Postgres `ALTER SCHEMA` documentation:

    ALTER SCHEMA name RENAME TO new_name
    ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

#### Support in CockroachDB

Not supported. For more information, see #26443 and 30875.

### `DROP SCHEMA`

From the Postgres `DROP SCHEMA` documentation:

    DROP SCHEMA [ IF EXISTS ] name [ ...] [ CASCADE | RESTRICT ]

#### Support in CockroachDB

Not supported. For more information, see #26443.

## Sequence DDL

### `CREATE SEQUENCE`

From the Postgres `CREATE SEQUENCE` documentation:

    CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
        [ AS data_type ]
        [ INCREMENT [ BY ] increment ]
        [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
        [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
        [ OWNED BY { table_name.column_name | NONE } ]

#### Support in CockroachDB

Most options are supported. For details, see the table below.

For more information, see `CREATE SEQUENCE`.

Note:

Sequences are not the preferred way to create row IDs in CockroachDB. For best performance, use UUIDs. For more information, see How do I generate unique, slowly increasing sequential numbers in CockroachDB?

Qualifier | Supported? | Notes
--- | --- | ---
`TEMPORARY` / `TEMP` | No | See #5807.
`AS <type>` | No | Sequences always have type `INT8`/`BIGINT`. For more information, see #25110.
`INCREMENT [ BY ] ...` | Yes |
`[NO] MINVALUE/MAXVALUE ...` | Yes |
`START ...` | Yes |
`CACHE 1` | Yes | (This is also the default.)
`CACHE N` | No |
`NO CYCLE` | Yes | (This is also the default.)
`CYCLE` | No | See #20961.
`OWNED BY` | No | See #26382.

#### Extensions

- Virtual sequences (experimental): Create a virtual sequence with values auto-generated by `unique_rowid()`. For more information, see the `SERIAL` data type documentation.

### `ALTER SEQUENCE`

From the Postgres `ALTER SEQUENCE` documentation:

    ALTER SEQUENCE [ IF EXISTS ] name
        [ AS data_type ]
        [ INCREMENT [ BY ] increment ]
        [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
        [ START [ WITH ] start ]
        [ RESTART [ [ WITH ] restart ] ]
        [ CACHE cache ] [ [ NO ] CYCLE ]
        [ OWNED BY { table_name.column_name | NONE } ]
    ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
    ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name
    ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

#### Support in CockroachDB

Some options are supported. For details, see the table below.

For more information, see `ALTER SEQUENCE`.

Qualifier | Supported? | Notes
--- | --- | ---
`AS <type>` | No | Sequences always have type `INT8`/`BIGINT`. For more information, see #25110.
`INCREMENT [BY] ...` | Yes |
`[NO] MINVALUE/MAXVALUE ...` | Yes |
`START ...` | Yes |
`RESTART ...` | No | See #20956.
`CACHE 1` | Yes | (This is also the default.)
`CACHE N` | No |
`NO CYCLE` | Yes | (This is also the default.)
`CYCLE` | No | See #20961.
`OWNED BY` | No | See #26382.
`ALTER ... OWNER TO` | No | See #30875.
`ALTER ... RENAME TO` | Yes | For more information, see `RENAME SEQUENCE`.
`ALTER ... SET SCHEMA` | No | See #26443.

### `DROP SEQUENCE`

From the Postgres `DROP SEQUENCE` documentation:

    DROP SEQUENCE [ IF EXISTS ] name [ ...] [ CASCADE | RESTRICT ]

#### Support in CockroachDB

Supported in CockroachDB. For more information, see `DROP SEQUENCE`.

Warning:

Note that because `OWNED BY` and sequence dependencies are not tracked properly in CockroachDB, the effects of `CASCADE`/`RESTRICT` are different (incomplete) in CockroachDB when compared to Postgres. For more information, see #20965.

## View DDL

### `CREATE VIEW`

From the Postgres `CREATE VIEW` documentation:

    CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [ ...] ) ]
        [ WITH ( view_option_name [= view_option_value] [ ... ] ) ]
        AS query
        [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

#### Support in CockroachDB

Only the basic form is supported, as described below. For more information, see `CREATE VIEW`.

    CREATE VIEW name [ ( column_name ... ) ] AS query

Features not supported yet:

- `OR REPLACE` not supported.
- `TEMP` / `TEMPORARY` not supported. For more information, see #5807.
- `RECURSIVE` views not supported (in general, recursive queries are not supported in CockroachDB).
- `WITH` option views not supported.

Additional restrictions:

- SQL `'*'` cannot be used in views. For more information, see #10028.
- View column dependencies are not tracked precisely. For more information, see #29021 and #24556.
- Cannot `INSERT`/`UPDATE`/`DELETE` data using a view. For more information, see #20948.

### `CREATE MATERIALIZED VIEW`

From the Postgres `CREATE MATERIALIZED VIEW` documentation:

    CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
        [ (column_name [ ...] ) ]
        [ WITH ( storage_parameter [= value] [ ... ] ) ]
        [ TABLESPACE tablespace_name ]
        AS query
        [ WITH [ NO ] DATA ]

#### Support in CockroachDB

Not supported. For more information, see #24747.

Tip:

Consider using computed columns instead, which are materialized in CockroachDB.

### `ALTER VIEW`

From the Postgres `ALTER VIEW` documentation:

    ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression
    ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT
    ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
    ALTER VIEW [ IF EXISTS ] name RENAME TO new_name
    ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema
    ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [ ... ] )
    ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [ ... ] )

#### Support in CockroachDB

Only view renames are supported:

    ALTER VIEW [ IF EXISTS ] name RENAME TO new_name

For more information, see `ALTER VIEW`.

Additional features being considered include:

- Owner support - #30875.
- Schema support - #26443.

### `DROP VIEW`

From the Postgres `DROP VIEW`:

    DROP VIEW [ IF EXISTS ] name [ ...] [ CASCADE | RESTRICT ]

#### Support in CockroachDB

Supported in CockroachDB. For more information, see `DROP VIEW`.

## Table-level DDL

### `CREATE TABLE`

From the Postgres `CREATE TABLE` documentation:

Simple tables:

    CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
      { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
        | table_constraint
        | LIKE source_table [ like_option ... ] }
        [ ... ]
    ] )
    [ INHERITS ( parent_table [ ... ] ) ]
    [ PARTITION BY ... ]
    [ WITH ( storage_parameter [= value] [ ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace_name ]
    and like_option is:

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

Typed tables:

    CREATE ... TABLE ... table_name OF type_name

#### Support in CockroachDB

The basic form is supported, with the restrictions described below. For more information, see `CREATE TABLE`.

Qualifier | Supported | Notes
--- | --- | ---
`GLOBAL` / `LOCAL` | No | See #5807.
`TEMPORARY` / `TEMP` | No | See #5807.
`UNLOGGED` | No |
`LIKE` | No | See #30840.
`INHERITS` | No |
`WITH (...)` | No | Use stores and zone configs.
`WITH` / `WITHOUT OIDS` | No | CockroachDB does not use OIDs for rows.
`ON COMMIT` | No | See #5807.
`TABLESPACE` | No | See the section on Tablespace-level DDL.
`OF ...` (typed tables) | No |

### `CREATE TABLE ... PARTITION OF ...`

Partitioned tables are available in CockroachDB but do not follow the Postgres model. For information, see Define Table Partitions.

The restrictions on `CREATE TABLE` described in the previous section apply here.

### Column data types

For each data type listed below:

- Aliases are listed.
- The level of support is described.
- Links to additional information are provided (when available).

The list of data types is taken from the Postgres documentation.

Name | Aliases | Supported | Notes
--- | --- | --- | ---
`bigint` | `int8` | Yes | See the `INT` documentation
`bigserial` | `serial8` | Yes | Not generated unless session option enabled.
`bit [(n)]` |  | No | Planned for support in 2.2.
`bit varying [(n)]` | `varbit [(n)]` | No | Planned for support in 2.2.
`boolean` | `bool` | Yes |
`box` |  | No | See #21286.
`bytea` | `bytes` | Yes | See `BYTES`.
`bytes` | `bytea` | EXTENSION | See `BYTES`.
`character [(n)]` | `char [(n)]` | Yes | See `STRING`.
`character varying [(n)]` | `varchar [(n)]` | Yes | See `STRING`.
`cidr` |  | No |
`circle` |  | No | See #21286.
`date` |  | Yes/Partial | Different range from Postgres; may convert incorrectly. See `TIME`.
`double precision` | `float8` | Yes | See `FLOAT`.
`inet` |  | Yes | See `INET`.
`integer` | `int`, `int4` | Yes/Partial | Handled as `INT8` not `INT4`. See #26925.
`interval` |  | Yes | Different range from PostgreSQL; may convert incorrectly. See `INTERVAL`.
`interval [ fields ] [ (p) ]` |  | No |
`json` |  | Yes/Partial | An alias to "jsonb". There is no text type "json" in CockroachDB. See `JSONB`.
`jsonb` |  | Yes/Partial | Some operators may be missing. See `JSONB`.
`line` |  | No | See #21286.
`lseg` |  | No | See #21286.
`macaddr` |  | No |
`macaddr8` |  | No |
`money` |  | No |
`numeric [ (p, s) ]` | `decimal [ (p, s) ]` | Yes | See `DECIMAL`.
`path` |  | No | See #21286.
`pg_lsn` |  | No |
`point` |  | No | See #21286.
`polygon` |  | No | See #21286.
`real` | `float4` | Yes/Partial | Separate type from `FLOAT8`, but handled the same internally. Results may differ from Postgres. See `FLOAT`.
`smallint` | `int2` | Yes | See `INT`.
`smallserial` | `serial2` | Yes | Sequence not automatically generated unless session option is opted in. See `SERIAL`.
`serial` | `serial4` | Yes/Partial | Integer currently handled as `int8` not `int4`. See #26925.
`text` |  | Yes | See `STRING`.
`time [ without time zone ]` |  | Yes | Different range from Postgres; may convert incorrectly. See `TIME`.
`time [ (p) ] with time zone` | `timetz` | No |
`time [ (p) ] [ without time zone ]` |  | No | Precision not supported.
`timestamp [ without time zone ]` |  | Yes | See `TIME`.
`timestamp with time zone` | `timestamptz` | Yes | See `TIME`.
`timestamp [ (p) ] [ without time zone ]` |  | No | Precision not supported. See #32098.
`timestamp [ (p) ] with time zone` | `timestamptz` | No | Precision not supported. See #32098.
`tsquery` |  | No | Related to "Full-text search". See #7821
`tsvector` |  | No | Related to "Full-text search".
`txid_snapshot` |  | No |
`uuid` |  | Yes | See `UUID`.
`xml` |  | No |

### Column constraints for `CREATE TABLE`

From the Postgres `CREATE TABLE` documentation:

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

#### Support in CockroachDB

Most options are supported, with the restrictions described below. For more information, see `CREATE TABLE`.

Qualifier | Supported | Notes
--- | --- | ---
`NULL` / `NOT NULL` | Yes | See `NOT NULL` constraint.
`CHECK` | Yes | See `CHECK` constraint.
`NO INHERIT on CHECK` | No |
`DEFAULT` | Yes | See `DEFAULT` value constraint.
`GENERATED ... AS IDENTITY` | No |
`UNIQUE` | Yes | See `UNIQUE` constraint.
`PRIMARY KEY` | Yes | See `PRIMARY KEY` constraint.
`REFERENCES ...` (basic form) | Yes (see note) | CockroachDB may create more foreign key indexes than Postgres for an equivalent `CREATE TABLE` statement.
`REFERENCES ... MATCH` | Partial | See #30026.
`REFERENCES ... ON DELETE/UPDATE` | Yes | See foreign key constraint.
`[NOT] DEFERRABLE` / `INITIALLY DEFERRED` / `IMMEDIATE` | No/Partial | Only `NOT DEFERRABLE INITIALLY IMMEDIATE` is supported. See #9897.

#### Extensions

- Computed Columns

### Table constraints for `CREATE TABLE`

From the Postgres `CREATE TABLE` documentation:

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

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

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

    exclude_element in an EXCLUDE constraint is:

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

#### Support in CockroachDB

Some options are supported, with the restrictions described below. For more information, see `CREATE TABLE`.

Qualifier | Supported? | Notes
--- | --- | ---
`CHECK` | Yes | See `CHECK` constraint.
`CHECK ... NO INHERIT` | No |
`UNIQUE` | Yes | See `UNIQUE` constraint.
`PRIMARY KEY` | Yes | See `PRIMARY KEY` constraint.
`EXCLUDE` | No |
`FOREIGN KEY` | Partial | See the notes about `REFERENCES` above.
Index parameter `WITH` | No | Use stores and zone configs.
Index parameter `USING INDEX TABLESPACE` | No | See the section on Tablespace-level DDL.

#### Extensions

- Column families

- Index definitions: CockroachDB supports creating indexes as part of the `CREATE TABLE` statement, for example:

    > CREATE TABLE t (
        X INT,
        Y INT,
        INDEX idx (X DESC, Y ASC)
      );

### `CREATE TABLE AS`

From the Postgres `CREATE TABLE AS` documentation:

    CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
        [ (column_name [ ...] ) ]
        [ WITH ( storage_parameter [= value] [ ... ] ) | WITH OIDS | WITHOUT OIDS ]
        [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
        [ TABLESPACE tablespace_name ]
        AS query
        [ WITH [ NO ] DATA ]

#### Support in CockroachDB

Only the basic form without options is supported, with the restrictions described below. For more information, see `CREATE TABLE AS`.

Qualifier | Supported | Notes
--- | --- | ---
`GLOBAL` / `LOCAL` | No | See #5807.
`TEMPORARY` / `TEMP` | No | See #5807.
`UNLOGGED` | No |
`WITH (...)` | No | Use stores and zone configs.
`WITH`/`WITHOUT OIDS` | No | CockroachDB does not use OIDs for rows.
`ON COMMIT` | No | See #5807.
`TABLESPACE` | No | See the section on Tablespace-level DDL.
`WITH [NO] DATA` | No | Table data is always copied.

### `ALTER TABLE` - Statement forms

From the Postgres `ALTER TABLE` documentation:

    ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
        action [ ... ]
    ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
        RENAME [ COLUMN ] column_name TO new_column_name
    ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
        RENAME CONSTRAINT constraint_name TO new_constraint_name
    ALTER TABLE [ IF EXISTS ] name
        RENAME TO new_name
    ALTER TABLE [ IF EXISTS ] name
        SET SCHEMA new_schema
    ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [ ... ] ]
        SET TABLESPACE new_tablespace [ NOWAIT ]
    ALTER TABLE [ IF EXISTS ] name
        ATTACH PARTITION partition_name FOR VALUES partition_bound_spec
    ALTER TABLE [ IF EXISTS ] name
        DETACH PARTITION partition_name

#### Support in CockroachDB

Some options are supported, with the restrictions described below. For more information, see `ALTER TABLE`.

Statement form | Supported? | Notes
--- | --- | ---
`ALTER TABLE name ...` | Yes | See `ALTER TABLE` - Table actions.
`ALTER TABLE ONLY name` | No | Table inheritance is not yet supported.
`ALTER TABLE name *` | No | Table inheritance is not yet supported.
`ALTER TABLE RENAME TO` | Yes | See `ALTER TABLE`.
`ALTER TABLE SET SCHEMA` | No | See #26443.
`ALTER TABLE SET TABLESPACE` | No | See the section on Tablespace-level DDL.
`ALTER TABLE ATTACH PARTITION` | No | See note below.
`ALTER TABLE DETACH PARTITION` | No | Partitioned tables are available in CockroachDB, but they do not follow the Postgres model. For more information, see Define Table Partitions.

### `ALTER TABLE` - Table actions

Tip:

The information in this section applies to `ALTER TABLE` statement forms that change the schema of a table.

From the Postgres `ALTER TABLE` documentation:

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

    and table_constraint_using_index is:

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

#### Support in CockroachDB

Some options are supported, with the restrictions described below. For more information, see `ALTER TABLE` and `ALTER COLUMN`.

Table action | Supported? | Notes
--- | --- | ---
`ADD COLUMN` | Yes | See `ADD COLUMN`.
`DROP COLUMN` | Yes | See `DROP COLUMN`.
`ALTER COLUMN [SET DATA] TYPE` | Partial | Only conversions that do not require column rewrites are supported, e.g., `INT2 -> INT4`, `CHAR(10) -> CHAR(100)`.
`ALTER COLUMN SET DEFAULT` | Yes | See `ALTER COLUMN`.
`ALTER COLUMN DROP DEFAULT` | Yes | See `ALTER COLUMN`.
`ALTER COLUMN DROP NOT NULL` | Yes | See row below, and `ALTER COLUMN`.
`ALTER COLUMN SET NOT NULL` | No | See #28751.
`ALTER COLUMN ADD GENERATED` | No | See notes about `GENERATED` for `CREATE TABLE`. As a workaround, use `ALTER COLUMN SET DEFAULT nextval...`.
`ALTER COLUMN {SET GENERATED/SET/RESTART}` | No | See notes about `GENERATED` for `CREATE TABLE`. As a workaround, use `ALTER SEQUENCE`.
`ALTER COLUMN DROP IDENTITY` | No | See notes about `GENERATED` for `CREATE TABLE`.
`ALTER COLUMN SET STATISTICS` | No |
`ALTER COLUMN SET/RESET (attributes)` | No |
`ALTER COLUMN SET STORAGE` | No |
`ADD (constraint)` | Yes/Partial | See `ADD CONSTRAINT`.
`ADD (constraint) NOT VALID` | Yes/Partial | See `ADD CONSTRAINT`.
`ALTER CONSTRAINT` | No |
`VALIDATE CONSTRAINT` | Yes | See `VALIDATE CONSTRAINT`.
`DROP CONSTRAINT` | Yes/Partial | See `DROP CONSTRAINT`.
`ENABLE/DISABLE ... TRIGGER` | No |
`ENABLE/DISABLE ... RULE` | No |
`[NO] DISABLE/ENABLE/FORCE ROW SECURITY` | No |
`CLUSTER ON` | No |
`SET WITHOUT CLUSTER` | No |
`SET WITH/WITHOUT OIDs` | No | CockroachDB does not use OIDs for rows.
`SET TABLESPACE` | No | See the section on Tablespace-level DDL.
`SET LOGGED/UNLOGGED` | No |
`SET/RESET (storage parameters)` | No | Use stores and zone configs.
`INHERIT` | No |
`NOT/OF ...` (typed tables) | No |
`OWNER TO` | No | See #30875.
`REPLICA IDENTITY` | No |

#### Extensions

- `ALTER` for computed columns.
- `ALTER` for table partitions.
- `ALTER ... EXPERIMENTAL AUDIT`.

### `DROP TABLE`

From the Postgres `DROP TABLE` documentation:

    DROP TABLE [ IF EXISTS ] name [ ...] [ CASCADE | RESTRICT ]

#### Support in CockroachDB

Fully supported in CockroachDB. For more information, see `DROP TABLE`.

## See Also

- Porting from Postgres
- Migrate from Postgres
- Migration Overview
- Configure Replication Zones
- Start a Node