Postgresql 删除数据表
Postgresql INSERT 语句 

Postgresql 修改数据表

在 PostgreSQL 中，修改数据表通常涉及到几种不同的操作，包括添加列、删除列、修改列的数据类型、重命名列或表等。

基本语法

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]    action [, ... ]
ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column_name TO new_column_nameALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    RENAME CONSTRAINT constraint_name TO new_constraint_nameALTER TABLE [ IF EXISTS ] name
    RENAME TO new_nameALTER TABLE [ IF EXISTS ] name
    SET SCHEMA new_schemaALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [ IF EXISTS ] name
    DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

上述语法结构中的 action 有如下选项：

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

选项说明

  • ADD COLUMN [ IF NOT EXISTS ] -- 使用与 CREATE TABLE 相同的语法向表中添加新列。如果指定了 IF NOT EXISTS 并且已存在具有此名称的列，则不会引发错误。

  • DROP COLUMN [ IF EXISTS ] -- 从表中删除一列。涉及该列的索引和表约束也将被自动删除。如果删除列将导致统计信息仅包含单个列的数据，则引用已删除列的多变量统计信息也将被删除。如果表外的任何内容（例如，外键引用或视图）取决于列，则需要说 CASCADE。如果指定了 IF EXISTS 并且该列不存在，则不会引发错误。在这种情况下，将发出通知。

  • SET DATA TYPE -- 更改表的列的类型。涉及该列的索引和简单表约束将通过重新解析最初提供的表达式自动转换为使用新的列类型。可选的 COLLATE 子句指定新列的排序规则;如果省略，则排序规则是新列类型的默认值。可选的 USING 子句指定如何从旧列值计算新列值;如果省略，则默认转换与从 old 数据类型转换为 new 的赋值相同。如果没有从 old 类型到 new 类型的隐式强制转换或赋值转换，则必须提供 USING 子句。

  • SET/DROP DEFAULT -- 设置或删除列的默认值（其中删除等效于将默认值设置为 NULL）。新的默认值将仅适用于后续的 INSERT 或 UPDATE 命令;它不会导致表中已有的行发生更改。

  • SET/DROP NOT NULL -- 更改是将列标记为允许 Null 值还是拒绝 Null 值。

  • DROP EXPRESSION [ IF EXISTS ] -- 将存储的生成列转换为普通的 base 列。列中的现有数据将保留，但将来的更改将不再应用生成表达式。

  • ADD table_constraint_using_index -- 基于现有唯一索引向表中添加新的 PRIMARY KEY 或 UNIQUE 约束。索引的所有列都将包含在 constraint 中。

  • DROP CONSTRAINT [ IF EXISTS ] -- 删除表上的指定约束，以及约束下的任何索引。如果指定了 IF EXISTS 并且约束不存在，则不会引发错误。在这种情况下，将发出通知。

  • RENAME -- RENAME 形式更改表的名称（或索引、序列、视图、具体化视图或外部表）、表中单个列的名称或表的约束的名称。重命名具有基础索引的约束时，也会重命名索引。对存储的数据没有影响。


使用示例

  • 要向表中添加 varchar 类型的列

ALTER TABLE user ADD COLUMN address varchar(50);

  • 要添加具有非 null 默认值的列

ALTER TABLE user
  ADD COLUMN addtime timestamp with time zone DEFAULT now();

  • 要添加列并使用与默认值不同的值填充该列以供以后使用

ALTER TABLE user
  ADD COLUMN status varchar(30) DEFAULT 'old',
  ALTER COLUMN status SET default 'current';

  • 要从表中删除列

ALTER TABLE user DROP COLUMN address RESTRICT;

  • 要在一次操作中更改两个现有列的类型

ALTER TABLE user
    ALTER COLUMN address TYPE varchar(50),
    ALTER COLUMN email TYPE varchar(30);

  • 重命名现有列

ALTER TABLE user RENAME COLUMN address TO addr;

  • 要重命名现有表

ALTER TABLE user RENAME TO users;

  • 要重命名现有约束

ALTER TABLE user RENAME CONSTRAINT id_index TO user_id_index;

  • 向列添加 not-null 约束

ALTER TABLE user ALTER COLUMN email SET NOT NULL;

  • 要从列中删除 not-null 约束

ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

  • 添加 check 约束

ALTER TABLE user ADD CONSTRAINT zipchk CHECK (char_length(zipcode) =6);

  • 删除 check 约束

ALTER TABLE ONLY user DROP CONSTRAINT zipchk;

  • 要将表移动到其他表空间

ALTER TABLE user SET TABLESPACE usertablespace;

  • 要向表添加自动命名的主键约束，请注意一个表只能有一个主键

ALTER TABLE user ADD PRIMARY KEY (user_id);

  • 要重新创建主键约束，并且在重建索引时不阻止更新

CREATE UNIQUE INDEX CONCURRENTLY user_id_idx ON user (user_id);
ALTER TABLE user DROP CONSTRAINT user_id_idx,
    ADD CONSTRAINT user_id_idx PRIMARY KEY USING INDEX user_id_temp_idx;