postgresql update 设置某列为某些条件生成的值

最新推荐文章于 2024-03-04 18:06:40 发布
最新推荐文章于 2024-03-04 18:06:40 发布
阅读量1.3k 收藏 1
点赞数
分类专栏： 数据库 文章标签： postgresql update coalesce lpad rpad
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/cainiaobulan/article/details/102688588
版权 
with tmp as( select row_number() over() as rownum,id idd  from bas_cm_verification_strategy)
update bas_cm_verification_strategy
set code = COALESCE(to_char(create_time, 'yyyyMMdd')) || lpad(CAST(tmp.rownum as VARCHAR), 3, '0')
from tmp where id = tmp.idd;

需求说明：

   接到一个需求：给表bas_cm_verification_strategy新增一个code字段，表中历史数据要根据创建时间+001递增来设置code的值。

注释：

其中 row_number() over() 获取到的是数据的行号，此处为了省事没有用序列。

lpad(string text, length int [, fill text])    通过填充字符 fill （缺省时为空白）， 把 string 填充为长度 length。 如果 string 已经比 length 长则将其截断（在右边）。类似的还有rpad，不过填充的位置相反。

COALESCE(to_char(create_time, 'yyyyMMdd')) 格式化时间

CAST(tmp.rownum as VARCHAR)    转换格式为字符串

【PostgreSQL教程】PostgreSQL 高级篇之索引
No8g攻城狮的博客
08-24 781
索引是加速搜索引擎检索数据的一种特殊表查询。简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。 拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据更新或插入。索引可以创建或删除，但不会影响数据。
【postgresql 基础入门】表的约束(二) 唯一unique、非空not null、check、exclusion约束，原理机制以及多列组合约束
一个追逐梦想的码农
03-25 3513
数据库的约束是一种规则，用于限制或规范数据库中的数据，确保数据的完整性和一致性。这些约束可以定义在表级别或列级别，处理机制是一致的。约束不占用任何数据库空间，而是存在于数据字典中，并在执行SQL期间使用。用户可以指明约束是启用的还是禁用的，当约束启用时，它增强了数据的完整性。
根据这些字段生成postgresql的建表语句
weixin_42612804的博客
12-20 213
好的，首先你需要提供这些字段的信息，例如字段名称、数据类型、是否非空等。 假设你有以下字段信息： id：数字类型，主键，自增长 name：字符串类型，非空 age：数字类型，非空 gender：字符串类型，可以为空 address：字符串类型，可以为空 则可以使用以下 SQL 语句来创建名为 person 的表： CREATE TABLE person ( id serial PRIMAR...
sql 更新某列的值为 row_number 查出的值
66666
11-27 2261
SQL Server Update字段值为ROW_NUMBER() SQL Server 2005之后，增加了ROW_NUMBER这个函数，可用于select语句中标示返回数据行的行号；如何用update语句更新数据表中的某一个字段为ROW_NUMBER的值呢？比如，想要更新table1中field1为ROW_NUMBER的值，可以通过以下两种方法的SQL实现： 1. update tem
postgre函数001—row_number()
05-11 1958
row_number(): 组内排序，不间断,如1，2，3，4，5，6 操作表 DROP TABLE IF EXISTS "test"."empsalary"; CREATE TABLE "test"."empsalary" ( "depname" varchar(255) COLLATE "pg_catalog"."default", "empno" int4, "salary" int4, "enroll_date" date ) ; INSERT INTO "test"."emps
SQL Server Update字段值为ROW_NUMBER()
04-20 9196
1、全表自动排序。OrderNum为要更新的排序字段。Id为按照什么顺序重置排序数据。 update 表名 set OrderNum = rownum from(         select OrderNum, ROW_NUMBER() over(order by Id) rownum from 表名     ) 表名 2、带条件的重置排序。把RoleId=2的所有数据按照i
2.00003《Postgresql内幕探索》走读 之 查询优化
四库全书的酷
03-04 602
在PostgreSQL中，尽管9.6版本中实现的并行查询使用了多个后台工作进程，但一个后端进程基本上处理连接的客户端发出的所有查询。 解析器 Parser解析器从明文的SQL语句生成解析树。 分析仪/分析仪 Analyzer分析器/分析器对解析树进行语义分析，生成查询树。 重写器 Rewriter重写器使用存储在规则体系rules 计划员 Planner计划员从查询树中生成最有效执行的计划树。 执行者 Executor。
PostgreSQL 欺骗优化器之扩展统计
lyu1026的博客
05-23 588
一、什么是扩展统计 扩展统计对象， 追踪指定表、外部表或物化视图的数据。 目前支持的种类： 启用n-distinct统计的 ndistinct。 启用功能依赖性统计的dependencies。 启用最常见的值列表的mcv。 本文仅讨论n-distinct统计信息，在优化器中的作用。手工修改统计信息，使得执行计划发生改变。 二、数据准备 建立一个大表，这个表模拟商业交易明细记录。这个表，不仅...
【数据库学习】Postgres（PG）概览与PostgreSQL基本语法
热门推荐
午后阳光
01-27 2万+
-数组可以定义长度、也可以不给。也可以定义多维数组。但定义长度和多维数组在实际使用中是无效的。如下面两种定义是等价的。类似于C的结构体，
pgsql jsonb字段设置某个属性值
qq_40551516的博客
03-30 382
set jsonb字段= jsonb_set(setting-'属性', '{属性}', '值'::jsonb, true)
Postgresql中plpgsql数组的赋值与取值分析
InternalsOf
10-11 3244
**数组赋值** - 如果是默认值数组 - 编译时只组装expr出来记录ARRAY[1,2,3,4,5] - 在执行赋默认值时走执行器把expr变成value记录到datum中 - 执行时语义解析阶段就会把只拼出来：transformArrayExpr将ARRAY[1,2,3,4,5]转换为ArrayExpr - 执行时ExecEvalExpr阶段只当做Const把Value赋值给Datum即可。 - 如果是传入数组，直接当做常量赋值即可。
oracle中update补空格lpad,Oracle lpad 函数
weixin_36142226的博客
04-08 765
Oracle lpad 函数Oracle lpad 函数：版权声明：转载时请以超链接形式标明文章原始出处和作者信息及本声明http://soysky.blogbus.com/logs/43623642.htmlOracle lpad函数将左边的字符串填充一些特定的字符，其语法格式如下：lpad( string1, padded_length [,pad_string ] )其中：string1是...
postgresql查询、更新语句
yGIS
10-27 1244
postgresql简单查询语句
PostgreSQL pg中 lpad函数使用
qq_41982570的博客
11-03 2800
PostgreSQL pg中 lpad函数使用
PostgreSQL pg中的截取补齐lpad函数怎么用？
凯哥大数据——刘凯的博客
12-18 7306
PostgreSQL pg中的截取补齐lpad函数怎么用？ 1 左边填充，右边截取 PostgreSQL中的lpad()函数有两个功能： 如果长度不够指定的长度，就在左边填充字符串 如果长度超出了指定的长度，就把右边截掉。 The PostgreSQL lpad() function is used to fill up a string of specific length by a substring. If the length of the substring is equal to the
PostgreSQL -- 高级操作基础使用
S_numb的博客
10-08 3073
PostgreSQL 进阶知识1. 约束1.1 NOT NULL 约束1.2 UNIQUE 约束1.3 PRIMARY KEY 约束1.4 FOREIGN KEY 约束1.5 CHECK 约束1.6 EXCLUSION 约束1.7 删除约束2. 连接(JOIN)2.1 创建表2.2 交叉连接（CROSS JOIN）2.3 内连接（INNER JOIN）2.4 左外连接（ LEFT OUTER JOIN ）2.5 右外连接（ RIGHT OUTER JOIN ）2.6 外连接（FULL OUTER JOIN）
psql update高级语法
LanJie的博客
01-16 989
update test set age=age-1 where name='zhangsan'  
这是postgresql
最新发布
04-28
<think>嗯，用户的问题是关于如何在PostgreSQL中修改数据库结构的。首先，我需要回顾一下用户之前的对话历史，看看有没有相关的上下文。之前用户提到了数据库结构优化，特别是自增主键的设置，还有关于PostgreSQL作为矢量数据库、创建数据库副本以及容量规划的内容。现在用户直接询问如何在PostgreSQL中修改数据库结构，可能需要详细的操作步骤和最佳实践。 用户给出的引用内容里提到了PostgreSQL的扩展功能、使用pg_dump和createdb复制数据库，以及容量规划的资源推荐。不过用户现在的问题更偏向于结构修改的具体方法，而不是复制或容量规划。所以需要聚焦在如何实际进行结构更改，比如添加表、修改列、创建索引等。 首先，我应该确认PostgreSQL中修改数据库结构的常见方法，比如使用ALTER TABLE语句，或者通过扩展来添加功能。同时，用户之前的问题中提到了ORM层和数据库层的同步，可能需要注意在修改结构时如何与应用程序的ORM保持兼容，避免出现不一致的问题。 另外，用户之前提到了自增主键的设置，可能还需要涉及序列（SEQUENCE）和SERIAL类型的说明，因为PostgreSQL中自增通常是通过SERIAL或IDENTITY实现的。在修改表结构时，比如添加或删除列，调整约束，这些操作都需要具体的SQL命令。 还要考虑数据迁移和版本控制的问题，用户之前提到使用Alembic进行迁移，可能在PostgreSQL中也有类似的工具，比如pgAdmin或者Flyway。需要建议用户使用迁移工具来管理结构变更，避免手动执行SQL带来的风险。 用户可能还需要知道如何安全地进行结构修改，比如在生产环境中如何避免锁表，或者如何测试结构变更。比如，使用事务性DDL（在PostgreSQL中，DDL是可以事务控制的），这样可以在一个事务中执行多个结构变更，如果出错可以回滚。 此外，用户引用的内容里提到了扩展，可能需要说明如何通过扩展来增强数据库功能，比如添加新的数据类型或索引方式，比如使用PostGIS扩展进行地理空间数据处理。这可能也是修改数据库结构的一种方式，但用户的问题更偏向于表结构的修改。 还需要注意权限问题，修改数据库结构通常需要较高的权限，用户需要确保执行命令的用户有足够的权限，比如超级用户或拥有相应表的ALTER权限。 可能还需要提到备份，在修改结构之前进行备份，使用pg_dump或者连续归档，以防止操作失误导致数据丢失。用户之前的问题中提到了创建数据库副本，所以备份策略可能也是需要考虑的一部分。 总结下来，回答应该包括以下几个部分： 1. 常见结构修改操作（如添加/删除列，修改类型，添加约束等）的具体SQL示例。 2. 使用事务来确保结构变更的安全性。 3. 扩展的使用，如何通过扩展来修改结构或添加功能。 4. 迁移工具和版本控制的最佳实践。 5. 备份和恢复策略，确保操作安全。 6. 权限管理和测试环境的建议。 需要确保回答结构清晰，步骤详细，同时结合用户之前的关注点，比如自增主键、ORM同步等，给出相应的注意事项。此外，用户可能需要知道在修改结构时如何不影响现有的应用程序运行，比如逐步迁移、使用双写策略等，但可能这属于高级优化，需要根据用户的问题复杂度来决定是否包含。 最后，生成相关问题时要围绕数据库结构修改的扩展内容，比如如何回滚结构变更、如何优化表结构设计，或者如何处理大表的结构修改等。</think>--- ### 一、PostgreSQL 数据库结构修改的核心方法 #### 1. **基础表结构操作** - **添加/删除列** ```sql -- 添加新列 ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT ''; -- 删除列（需先删除依赖约束） ALTER TABLE users DROP COLUMN email CASCADE; ``` - **注意**：`CASCADE` 自动删除依赖该列的索引或约束[^1] - **修改列类型与约束** ```sql -- 修改列类型（需兼容现有数据） ALTER TABLE users ALTER COLUMN age TYPE SMALLINT; -- 添加唯一约束 ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); ``` - **主键与自增机制** ```sql -- 使用 IDENTITY 列（PostgreSQL 10+） CREATE TABLE orders ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, product_name TEXT ); -- 修改现有列为自增 CREATE SEQUENCE user_id_seq; ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('user_id_seq'), ALTER COLUMN id SET NOT NULL; ``` #### 2. **索引优化** ```sql -- 创建 B-tree 索引 CREATE INDEX idx_users_email ON users(email); -- 并发创建索引（不阻塞写入） CREATE INDEX CONCURRENTLY idx_users_name ON users(name); ``` #### 3. **扩展功能集成** ```sql -- 启用扩展（如 PostGIS） CREATE EXTENSION postgis; -- 使用扩展功能创建空间索引 CREATE INDEX idx_geom ON locations USING GIST (geom); ``` --- ### 二、安全操作实践 #### 1. **事务性 DDL** ```sql BEGIN; ALTER TABLE users ADD COLUMN phone VARCHAR(20); ALTER TABLE users ADD CONSTRAINT valid_phone CHECK (phone ~ '^\d{11}$'); COMMIT; -- 若失败可 ROLLBACK ``` - **优势**：确保多步结构变更的原子性 #### 2. **备份与恢复** ```bash # 全量备份 pg_dump -U postgres -d exampledb -Fc > exampledb.dump # 恢复备份 pg_restore -U postgres -d exampledb_clone exampledb.dump ``` #### 3. **版本控制工具** - **使用 Flyway 管理迁移脚本**： ```sql -- V1__create_users_table.sql CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); ``` - 通过工具顺序执行迁移文件，记录版本号[^2] --- ### 三、性能敏感场景处理 #### 1. **大表结构变更** ```sql -- 使用 pg_repack 避免表锁 pg_repack --table users exampledb ``` - **原理**：创建新表后替换原表，减少业务中断[^3] #### 2. **分区表优化** ```sql -- 创建范围分区表 CREATE TABLE sales ( id SERIAL, sale_date DATE, amount NUMERIC ) PARTITION BY RANGE (sale_date); -- 添加子分区 CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'); ``` --- ### 四、验证与监控 #### 1. **结构变更验证** ```sql -- 查看表定义 \d+ users -- 检查索引状态 SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users'; ``` #### 2. **性能监控** ```sql -- 查询长事务（避免阻塞） SELECT pid, query, now() - xact_start AS duration FROM pg_stat_activity WHERE state = 'active' AND query LIKE '%ALTER%'; ``` --- ### 五、典型问题解决方案 #### **场景 1：重命名字段后 ORM 报错** - **操作**： ```sql ALTER TABLE users RENAME COLUMN old_name TO new_name; ``` - **同步 ORM**：更新模型字段映射，例如 SQLAlchemy： ```python class UserModel(db.Model): new_name = db.Column('new_name', db.String(50)) ``` #### **场景 2：添加 NOT NULL 列失败** - **分步操作**： ```sql ALTER TABLE users ADD COLUMN address TEXT; UPDATE users SET address = '未知' WHERE address IS NULL; ALTER TABLE users ALTER COLUMN address SET NOT NULL; ``` --- ### 六、扩展功能应用示例 #### **使用 pgcrypto 加密字段** ```sql CREATE EXTENSION pgcrypto; -- 存储加密数据 INSERT INTO secrets (encrypted_data) VALUES (pgp_sym_encrypt('敏感信息', '密钥')); -- 解密查询 SELECT pgp_sym_decrypt(encrypted_data::bytea, '密钥') FROM secrets; ``` ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值