标题
- 查看 PostgreSQL表字段信息包括 名称,数据类型,精度,注释等信息
- 查看数据库中的 schemas
- 查看特定 schema 中的所有表
- 查看指定表所有列的信息:
- 查看主键信息:
- 查看索引信息:
- 创建库表模板
- 修改表信息
- 修改表字段信息
- 新增字段
- 修改字段
- 新增、删除索引
- 设置主键约束、去掉主键和非空约束
- 删除字段
注意:这是连接工具(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
FROMinformation_schema.columns cols
WHEREcols.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
WHEREattrelid = 'public.bc_deposit_conversion_records'::regclassAND attnum > 0AND NOT attisdropped;
查看主键信息:
-- 查看主键信息:
SELECT kc.column_name, tc.table_name, kc.constraint_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcON tc.constraint_name = kc.constraint_nameAND 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';
查看索引信息:
-- 查看索引信息:
SELECTi.relname AS index_name,idx.indisunique AS is_unique,idx.indisprimary AS is_primary,idx.indkey,pg_get_indexdef(idx.indexrelid) AS definition
FROMpg_class t,pg_class i,pg_index idx
WHEREt.oid = idx.indrelidAND i.oid = idx.indexrelidAND 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, -- 自增的整数 IDid 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;