PostgreSQL迁库后序列号冲突
通过如下SQL
查询序列号,复制出需要drop
的行执行删除
SELECT 'DROP SEQUENCE "'||n.nspname||'"."'||c.relname||'";' FROM pg_class c
left join pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind ='S'
ORDER BY c.relname
通过如下SQL
查询序列号,复制出需要drop
的行执行删除
SELECT 'DROP SEQUENCE "'||n.nspname||'"."'||c.relname||'";' FROM pg_class c
left join pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind ='S'
ORDER BY c.relname
SELECT 'x'::varchar(10485761);
DO
$do$
begin
if not exists
(SELECT indexname from PG_INDEXES where "schemaname"='public' and "table_name"='my_table' and indexname='my_index') then
create index my_index on public.my_table("my_column");
end if;
END;
$do$
;
-- $do$可以换成一个单引号,内容部分的单引号改成2个单引号进行转义
-- 查询表或字段
select * from information_schema.columns where table_schema='public' and table_name ='my_table' and column_name='my_column'
uzip不支持超过4G的文件解压,需要安装7za,7za解压需要处理编码,否则乱码
yum install -y p7zip
# 解压命令,-r递归文件夹,-o指定解压目录,无空格
7za x xxx.zip -r -o/tmp/
# 解决乱码
yum install convmv
# LANG=C表示以US-ASCII这样的编码输出文件名
LANG=C 7za x xxx.zip
# 将解压后的文件进行递归转码
convmv -f GBK -t utf8 --notest -r ./xxx
-- 多个with
with t1(c1,c2) as (select c1, c2 from table),
t2(c1,c2) as (select c1, c2 from table)
select * from t1,t2;
-- 当做变量
with t1(c1, c2) as (values('a','b'),('a','b')),
select * from t1;