PostgreSQL常用表操作SQL脚本整理

🕗 发布于 2024-09-20 06:01 postgresql

标题

查看 PostgreSQL表字段信息包括名称，数据类型，精度，注释等信息
创建库表模板
修改表信息
修改表字段信息

注意：这是连接工具(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;

原文地址：https://blog.csdn.net/liu918458630/article/details/142339933

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

neo4j节点关联路径的表示、节点的增删改查
neo4j节点Nodes、关联Relationships、路径Paths的表示、节点的增删改查
阅读更多2024-09-20
生动好看的css卡片样式
【代码】生动好看的css卡片样式。
阅读更多2024-09-20
51单片机-AT24C02(IIC总线介绍及其时序编写步骤)-第一节（下一节实战）
我以前的文章也有对基本常用的通信协议讲解，如SPI UART IIC RS232 RS485 CAN的讲解，可前往主页查询，（2024.9.12,晚上20：53，将AT24C02存储芯片，掉电不丢失，
阅读更多2024-09-20
视频怎么提取音频？一键音频提取，视频内容轻松听！
视频怎么提取音频？一键解锁音频世界，让视频精彩不再静默！无论您是忙碌于日常工作的上班族，还是热衷于学习的求知者，亦或是享受闲暇时光的聆听者，一键提取音频'功能让视频内容瞬间转化为耳畔的温柔低语，让知识
阅读更多2024-09-20
观成科技：新版suo5隧道工具加密流量跟踪分析
suo5是一个高性能的http隧道代理工具，支持全双工半双工模式，具有很高的传输性能，现在越来越多的webshell管理工具与内存马生成工具都支持了生成suo5木马的功能。从v1.1.0版本开始，其T
阅读更多2024-09-20
23：SPI二：W25Q64存储器模块的使用
其中最主要的特点就是掉电不丢失。由上图所示：W25Qxx的地址是24位的，则代表地址总线是24根地址总线。一个地址则代表一个门牌号，一个门牌号的存储空间是一个字节，那么24位的地址一共有多少个字节？地
阅读更多2024-09-20
C++ 面试模拟02
C++ 面试模拟02
阅读更多2024-09-20
游戏如何应对云手机刷量问题
不同于市面上其他产品，FairGuard加固采用更底层的检测手段，精准区分游戏运行环境，可识别云手机、虚拟机、虚拟框架、越狱、ROOT等各类风险环境，并提供个性化闪退策略。云手机的实现原理是依托公有云
阅读更多2024-09-20
Jupyter的使用
在 Jupyter Notebook 中，代码提示和自动补全的快捷键是非常实用的。可以查看该函数或对象的文档字符串（docstring），提供参数说明和用法示例。在 Jupyter Notebook
阅读更多2024-09-20
Sui与3DOS合作推动3D打印网络的去中心化
3DOS与Sui 联手，通过按需、本地化的3D打印推动全球制造的去中心化。
阅读更多2024-09-20