PostgreSQL alter column type 1 to type 2 using express or auto cast
postgresql to or type Using Column Express Auto
2023-09-14 08:57:17 时间
在使用数据库时,有些应用开发人员可能喜欢使用数值来表示布尔逻辑值,或者在最初定义一个字段的状态时使用的类型,将来不能表达所有的值。
未来则可能需要对字段进行转换,例如数值转换为布尔,或者布尔转换为数值。
还有的时候,一开始可能使用了大量的重复文本,在进行统计时,文本比整型的效率低,在进入仓库后可能需要字典化这些文本(例如APPNAME) , 也会涉及字段类型的转换。
例子:
postgres=# create table tbl(id int, stat numeric(1)); CREATE TABLE postgres=# insert into tbl select id,0 from generate_series(1,1000) t(id); INSERT 0 1000 postgres=# insert into tbl select id,1 from generate_series(1001,2000) t(id); INSERT 0 1000 postgres=# create or replace function n_to_b(numeric) returns boolean as select $1::int::boolean; language sql; CREATE FUNCTION postgres=# select n_to_b(1); n_to_b -------- (1 row) postgres=# select n_to_b(10); n_to_b -------- (1 row) postgres=# select n_to_b(0); n_to_b -------- (1 row) postgres=# select n_to_b(-1); n_to_b -------- (1 row) postgres=# alter table tbl alter column stat type boolean using stat::int::boolean; ALTER TABLE postgres=# select * from tbl limit 10; id | stat ----+------ 1 | f 2 | f 3 | f 4 | f ...
字典化
postgres=# create table test(id int, info text); CREATE TABLE postgres=# insert into test select id,string a from generate_series(1,100000) t(id); INSERT 0 100000 postgres=# insert into test select id,string b from generate_series(1,100000) t(id); INSERT 0 100000 postgres=# insert into test select id,string c from generate_series(1,100000) t(id); INSERT 0 100000 postgres=# create or replace function fun(text) returns int as declare begin case $1 when string a then return 0; when string b then return 1; when string c then return 2; else return 9999; end case; language plpgsql strict; CREATE FUNCTION postgres=# select fun(a); fun ------ 9999 (1 row) postgres=# select fun(string a); fun ----- (1 row) postgres=# alter table test alter column info type int using fun(info); ALTER TABLE postgres=# select * from test where id=1 limit 5; id | info ----+------ 1 | 0 1 | 1 1 | 2 (3 rows)
还有时,会涉及文本转数值,也可以使用类似的方法:
你可能需要用到to_number或者自定义函数(例如对于带有非数值的字符串,返回一个固定值)
postgres=# select to_number(123ab2,999) postgres-# ; to_number ----------- (1 row) postgres=# select to_number(123ab2,999); to_number ----------- (1 row) postgres=# select to_number(1a123ab2,999); to_number ----------- (1 row) postgres=# select to_number(1a123ab2,999999999999); to_number ----------- 11232 (1 row)
MySQL8.0 新特性:Partial Update of LOB Column MySQL8.0对json进行了比较完善的支持, 我们知道json具有比较特殊的存储格式,通常存在多个key value键值对,对于类似更新操作通常不会更新整个json列,而是某些键值。 对于某些复杂的应用,json列的数据可能会变的非常庞大,这时候一个突出的问题是:innodb并不识别json类型,对它而言这些存储统一都是LOB类型,而在之前的版本中Innodb处理LOB更新的方式是标记删除旧记录,并插入新记录,显然这会带来一些存储上的开销(尽管Purge线程会去后台清理),而写入的redo log和Binlog的量也会偏高,对于超大列,可能会严重影响到性能。
相关文章
- Postgresql数据库优化上该考虑些什么
- 使用PostgreSQL数据库建立用户画像系统
- MICROSOFT SQL SERVER TO POSTGRESQL MIGRATION USING PGLOADER
- [PostgreSQL] Use Foreign Keys to Ensure Data Integrity in Postgres
- 中文模糊查询性能优化 by PostgreSQL trgm
- PostgreSQL returning 插入,更新,删除反馈数据
- Hacking PostgreSQL
- PgSQL · 性能优化 · PostgreSQL TPC-C极限优化玩法
- PostgreSQL模板 pg_stat_statements的官方介绍/简单安装步骤
- postgresql pgsql登录及创建用户
- PostgreSQL 多维分析 CASE
- PAT 1033. To Fill or Not to Fill (贪婪)
- 常用Mysql或者PostGresql或者Greenplum的语句总结。
- PostgreSQL的学习心得和知识总结(八十六)|深入理解PostgreSQL数据库HOOK技术及开源hooks文档介绍
- PostgreSQL的学习心得和知识总结(五十七)|语法级自上而下完美实现PostgreSQL数据库 获取表结构一样的两张表的相同数据 的实现方案
- PostgreSQL的学习心得和知识总结(四十九)|深入理解PostgreSQL数据库行可见性判断机制基础