PostgreSQL常用表操作SQL脚本整理

注意：这是连接工具(navicat)下的操作脚本，关于Linux下的暂不整理

查看 PostgreSQL表字段信息包括 名称，数据类型，精度，注释等信息

	
-- 使用sql语句查看 PostgreSQL表字段信息包括 名称，数据类型，精度，注释等信息 
	 SELECT
-- 	 cols.table_catalog, --数据库的名称。
-- 	 cols.table_schema, --表所属的 schema 名称。  
--   cols.table_name, --表的名称。 
  cols.ordinal_position, -- 列在表中的顺序（从 1 开始）。
	cols.column_name,
  cols.data_type, --列的数据类型。
  cols.character_maximum_length, -- 对于字符类型的列，这是最大长度（以字符为单位）。
--   cols.character_octet_length, -- 对于字符类型的列，这是最大长度（以字节为单位）。
  cols.numeric_precision, --对于数值类型的列，这是精度（即数字的总位数）。
	cols.numeric_precision_radix, --对于数值类型的列，这是基数（通常是 2 或 10）。
--   cols.datetime_precision, --对于日期和时间类型的列，这是精度（即秒的小数位数）。
	cols.numeric_scale, --对于数值类型的列，这是标度（即小数点后的位数）。
--   cols.interval_type, -- 对于间隔类型的列，这是间隔的类型（如 YEAR、MONTH、DAY 等）。
  cols.column_default, --列的默认值，如果有的话。
  cols.is_nullable, --是否允许 NULL 值。YES 表示允许，NO 表示不允许。
  col_description(
    (cols.table_schema || '.' || cols.table_name)::regclass, 
    cols.ordinal_position::int
  ) AS comment
FROM
  information_schema.columns cols
WHERE
  cols.table_schema = 'public'
  AND cols.table_name = 'bc_deposit_conversion_records';
	
-- 其他不常用相关值含义	
-- interval_precision,对于间隔类型的列，这是间隔的精度。
-- character_set_catalog,对于字符集敏感的列，这是字符集的目录名称。
-- character_set_schema,对于字符集敏感的列，这是字符集的 schema 名称。
-- character_set_name,对于字符集敏感的列，这是字符集的名称。
-- collation_catalog,对于具有校对规则的列，这是校对规则的目录名称。
-- collation_schema,对于具有校对规则的列，这是校对规则的 schema 名称。
-- collation_name,对于具有校对规则的列，这是校对规则的名称。
-- domain_catalog,对于域的列，这是域的目录名称。
-- domain_schema,对于域的列，这是域的 schema 名称。
-- domain_name,对于域的列，这是域的名称。	

查看数据库中的 schemas

-- 你可以使用 SQL 命令来查看数据库中的 schemas，例如：
SELECT schema_name FROM information_schema.schemata;

查看特定 schema 中的所有表

-- 要查看特定 schema 中的所有表，你可以使用如下命令：
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

查看指定表所有列的信息：

-- 查看指定表所有列的信息：
SELECT  
*
--   COLUMN_NAME,
-- 	ordinal_position,
-- 	data_type,
-- 	udt_name ,
-- 	is_nullable,
-- 	column_default
FROM information_schema.COLUMNS 
WHERE table_schema = 'public' -- 替换为你的schema名称
AND TABLE_NAME = 'bc_deposit_conversion_records';-- 替换为你的表名称

	
-- 查看表的字段对应注释信息：
SELECT
attname,  col_description(attrelid, attnum)
FROM  pg_attribute
WHERE
  attrelid = 'public.bc_deposit_conversion_records'::regclass
  AND attnum > 0
  AND NOT attisdropped;

查看主键信息：

-- 查看主键信息：
SELECT kc.column_name, tc.table_name, kc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kc
  ON tc.constraint_name = kc.constraint_name
  AND tc.table_schema = kc.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
  AND tc.table_name = 'bc_deposit_conversion_records'
  AND tc.table_schema = 'public';

查看索引信息：

-- 查看索引信息：
SELECT
  i.relname AS index_name,
  idx.indisunique AS is_unique,
  idx.indisprimary AS is_primary,
  idx.indkey,
  pg_get_indexdef(idx.indexrelid) AS definition
FROM
  pg_class t,
  pg_class i,
  pg_index idx
WHERE
  t.oid = idx.indrelid
  AND i.oid = idx.indexrelid
  AND t.relkind = 'r'
  AND t.relname = 'bc_deposit_conversion_records'
  AND t.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

创建库表模板

DROP TABLE IF EXISTS  bc_common_new;

CREATE TABLE bc_common (
	-- id SERIAL PRIMARY KEY, -- 自增的整数 ID
	id BIGSERIAL PRIMARY KEY,
	user_name VARCHAR(10) NOT NULL,
	amount numeric(20,4),
	age INTEGER,
	sex CHAR(1) ,
	birthday DATE,
	content_detil TEXT,
	del_flag INTEGER DEFAULT 0,
	create_date DATE,
	modify_date DATE,
	datetime_field TIMESTAMP WITHOUT TIME ZONE ,-- 日期时间
	create_by VARCHAR(10) ,
	modify_by VARCHAR(10) 
);

-- 添加注释
COMMENT ON TABLE bc_common IS '建表模板信息表111'; -- 修改表注释

COMMENT ON COLUMN bc_common.id IS '自增主键';
COMMENT ON COLUMN bc_common.user_name IS '用户姓名';
COMMENT ON COLUMN bc_common.amount IS '用户金额';
COMMENT ON COLUMN bc_common.age IS '用户年龄';
COMMENT ON COLUMN bc_common.sex IS '用户性别0男1女3未知';
COMMENT ON COLUMN bc_common.birthday IS '出生日期';
COMMENT ON COLUMN bc_common.content_detil IS '语句模板明细';
COMMENT ON COLUMN bc_common.del_flag IS '逻辑删除标志，默认0有效，1为无效';
COMMENT ON COLUMN bc_common.create_date IS '创建时间';
COMMENT ON COLUMN bc_common.modify_date IS '修改时间';
COMMENT ON COLUMN bc_common.datetime_field IS '包含日期时间';
COMMENT ON COLUMN bc_common.create_by IS '创建人';
COMMENT ON COLUMN bc_common.modify_by IS '修改人';

修改表信息

修改表注释

COMMENT ON TABLE bc_common IS '建表模板信息表111'; 

修改表名称

ALTER TABLE old_table_name RENAME TO new_table_name;

修改表字段信息

在执行这些操作之前，请确保你有足够的权限来修改表结构，并且建议在生产环境中先在测试数据库上进行测试。如果你的表中已经包含数据，添加新字段可能会影响表的性能，尤其是对于大型表。

新增字段

在 PostgreSQL 中，添加新字段到现有表通常不涉及指定特定位置，因为 SQL 标准并不保证字段的物理顺序。字段在表中的顺序通常只影响 SELECT 语句中字段的默认顺序，而不会影响数据的存储或检索。

ALTER TABLE bc_common
ADD COLUMN add_new_column CHAR(10);
-- 加上新增字段的注释信息
COMMENT ON COLUMN bc_common.add_new_column IS '新增字段注释';

--连续新增n个字段，你可以通过逗号分隔每个 ADD COLUMN 子句来添加多个字段。
ALTER TABLE bc_common
ADD COLUMN new_column1 CHAR(1),
ADD COLUMN new_column2 VARCHAR(10),
ADD COLUMN new_column3 DATE;


-- 如果你想为新添加的字段设置默认值，可以使用 DEFAULT 关键字：
ALTER TABLE employees
ADD COLUMN email VARCHAR(255) DEFAULT 'default@email.com';
-- 如果你想让新添加的字段能够存储 NULL 值，可以省略 NOT NULL 约束（这是默认行为）。
--如果你希望字段不允许 NULL 值，可以添加 NOT NULL 约束：
ALTER TABLE employees
ADD COLUMN email VARCHAR(255) NOT NULL;

修改字段

修改字段名

ALTER TABLE bc_common
RENAME COLUMN old_column_name TO new_column_name;

修改字段类型
这块需要区分修改的类型情况
①原类型上的修改如 VARCHAR(10)===》VARCHAR(50)
②原类型修改为另一种类型如 VARCHAR(50)===》numeric(8,4)

# 针对情况①
ALTER TABLE bc_common
ALTER COLUMN add_new_column TYPE numeric(18,4);

# 针对①、②都适用
ALTER TABLE bc_common
ALTER COLUMN add_new_column TYPE numeric(8,4) USING add_new_column::numeric(8,4);

修改字段注释
COMMENT ON COLUMN bc_common.add_new_column IS '修改字段注释';

新增、修改、删除默认值

-- 新增或修改默认值
ALTER TABLE bc_common
ALTER COLUMN add_new_column_newName SET DEFAULT '0.5';

-- 删除默认值
ALTER TABLE bc_common
ALTER COLUMN add_new_column_newName DROP DEFAULT;

新增、删除索引

-- CREATE INDEX index_name ON your_table_name (column_name); 
-- 这将创建一个标准的 B-tree 索引，适用于大多数基于等值和范围的查询。
CREATE INDEX index_name2 ON bc_common (new_column2);

-- 如果你需要创建特定类型的索引，比如 GIN（Generalized Inverted Index）
-- 或 GiST（Generalized Search Tree）索引，你需要在 USING 子句中指定索引类型。例如，创建一个 GIN 索引：
CREATE INDEX index_name ON your_table_name USING gin (column_name);


-- 要删除索引，你可以使用 DROP INDEX 语句。例如，如果你想删除名为 index_name2 的索引
DROP INDEX index_name2;
-- 删除索引时，使用 IF EXISTS 可以避免因索引不存在而导致的错误。
DROP INDEX  IF EXISTS index_name2;

-- 如果你想在删除索引时避免锁定表，可以使用 CONCURRENTLY 选项，这将异步删除索引，
-- 但有一些限制，比如不能与 CASCADE 选项一起使用，并且一次只能删除一个索引：
DROP INDEX CONCURRENTLY index_name2;

例如 现在你想添加一个唯一索引

-- CREATE UNIQUE INDEX index_name ON table_name (name);
 CREATE UNIQUE INDEX index_name1 ON bc_common (new_column1);
-- 如果表中已经存在重复的 new_column1值，创建唯一索引时会失败，并返回错误。
-- 唯一索引会阻止你插入或更新任何会导致 name 字段出现重复值的记录。
-- 唯一索引可以和普通索引一样，使用 DROP INDEX 命令删除。

设置主键约束、去掉主键和非空约束

-- alter table [tbl_name] add constraint pkey_name primary key(id);
ALTER TABLE bc_common ADD CONSTRAINT bc_common_pkey666 PRIMARY KEY (id);

-- alter table [tbl_name] drop constraint pkey_name;
ALTER TABLE bc_common DROP CONSTRAINT bc_common_pkey666;

-- alter table [tbl_name] alter [col_name] drop not null;
ALTER TABLE bc_common ALTER COLUMN user_name DROP NOT NULL;

删除字段

ALTER TABLE bc_common DROP COLUMN new_column3;