psql：
选项 说明
-c command 指定 psql 执行一条 SQL 命令 command(用双引号括起)，执行后退出。
-d dbname 待连接的数据库名称。
-E 回显由\d 和其他反斜杠命令生成的实际查询。
-f filename 使用 filename 文件中的数据作为命令输入源，而不是交互式读入查询。在处理完文件后，psql 结束并退出。
-h hostname 声明正在运行服务器的主机名
-l 列出所有可用的数据库，然后退出。
-L filename 除了正常的输出源之外，把所有查询记录输出到文件 filename。
-o filename 将所有查询重定向输出到文件 filename。
-p port 指定 PostgreSQL 服务器的监听端口。
-q --quiet 让 psql 安静地执行所处理的任务。缺省时 psql 将输出打印欢迎和许多其他信息。
-t --tuples-only 关闭打印列名称和结果行计数脚注等信息。
-U username 以用户 username 代替缺省用户与数据库建立连接。
psql内部命令
\l 显示所有数据库
\c database 连接数据库
\dn 显示所有scheme
\dt 查看所有表
\di 查看所有索引
\d tablename 查看表格详细内容
\df 查看所有存储过程
\sf function 查看某个存储过程
\z 列出表权限
\i file 执行文件 等价于psql -f file
\q 退出登录
\h 或 \? 列出所有可用的SQL命令
show search_path; 查看当前scheme
set search_path to cfgstat; 切换scheme为cfgstat
ALTER USER postgres WITH PASSWORD '1234'; 设置登陆数据库的默认用户密码
select * from pg_shadow; 显示密码
select * from pg_indexes where tablename='xxx'; 查看表的索引信息
select pg_proc.proname,prosrc from pg_proc where proname='zxommexecdmlproc';查看pg存储过程：
pg移植from sybase：
=========================================================================================================================
sybase pg
-------------------------------------------------------------------------------------------------------------------------
数值类型为null时输出0 isnull(@abc,0) COALESCE(v_abc, 0);
任意变量类型转换 convert(int, v_str) cast(v_str as int) cast(v_count as varchar(10));
获取当前时间戳 getdate() now();
时间戳加减 dateadd(ss, -3, getdate()) now() - (3 || 'second')::INTERVAL;
dateadd(day, 3, getdate()) now() + (3 || 'day')::INTERVAL;
dateadd(day, 3, @v_endtime) v_endtime::timestamp + (3 || 'day')::INTERVAL;
获取时间戳中的部分 datepart(hh,getdate()) date_part('hour',now()); extract(hour from now());
日期相减得到秒数 datediff(ss,'1994-1-1',getdate()) extract(epoch from(now() - '1994.01.01'))
datediff(ss,'1994-1-1',@v_endtime) extract(epoch from(v_endtime::timestamp - '1994.01.01'))
日期相减得到天数 datediff(day,'1994-1-1',@v_endtime) date v_endtime - date '1994-1-1'
日期相减得到分钟 datediff(mi,'1994-1-1',@v_endtime) (date '1994-1-2 03:35:10' - date '1994-1-1 00:00:00')*24*60+date_part('hour',timestamp '1994-1-2 03:35:10')*60 + date_part('minute',timestamp '1994-1-2 03:35:10');
datediff(mi,'1994-1-1',getdate()) (date (now()::text) - date '1994-1-1 00:00:00')*24*60+date_part('hour',now())*60 + date_part('minute',now());
时间戳转换为字符串 convert(varchar(10), @v_endtime, 102) to_char(v_endtime,'yyyy.mm.dd HH24:MI:SS');
左右拼接（left|right） right('00'+datepart(hh,getdate())) right('00'||date_part('hour',now()))
获取字符数 len('json') / char_length('json') length('abc');
获取字符串的字节数 datalength('你好abc') octet_length('你好abc');
指定子串的位置 charindex(@v_split, @v_temp1) position(substring in string)
错误码 if @@error <> 0 exception when others then raise
影响行数 @@rowcount get diagnostics v_count_var :=ROW_COUNT;
创建临时表 create table #temp() create temporary table temp();
退出循环 break exit;
时间戳变量定义类型 datetime timestamp
获取随机数 rand() random();
数值类型转换为字符 convert(varchar,rand()) cast(random() as varchar); random()::text
字符串替换 stuff('abcd',1,2,'s')或str_replace('abcd','ab','s') replace('abcd','ab','s')
查询前10行 select top 10 * from abc select * from abc litim 10; (offset 2)
向临时表插入数据 select id into #temp from abc; create temporary table temp as select id from abc;
数字转字符串 LTRIM(STR(123,3,0)) LTRIM(to_char(123,'999'))
-------------------------------------------------------------------------------------------------------------------------
pg动态sql语句中不能使用select into语句：
例如：v_sqlstr='select into v_count count(1) from abc'; execute v_sqlstr; (错误)
v_sqlstr='select count(1) from abc'; execute v_sqlstr into v_count; (正确)
获取昨天的时间日期：
select now() - '1 day'::interval; 2020-11-12 10:35:39.11988+08 (等价与dateadd(day,-1,getdate()))
select cast(now() - '1 day'::interval as varchar(10)); 2020-11-12
select substring(cast(now() - '1 day'::interval as varchar(10)),9,2); 12
时间和字符串转换：
select to_char(now(),'yyyy-mm-dd HH24:MI:SS'); 时间戳转换为字符串
select to_date('2020-11-13','yyyy-mm-dd'); 字符串转换为时间戳
select cast('20201113' as timestamp);
to_date('1994-1-1','yyyy-mm-dd') + (3600||'second')::interval 综合
拼接表中列为一行：
select string_agg(name) from abc;
select id,string_agg(name,',') from abc group by id;
获得结果状态：
（1）GET DIAGNOSTICS integer_var := item{ROW_COUNT,RESULT_OID,PG_CONTEXT};
ROW_COUNT bigint 最近的SQL命令处理的行数
RESULT_OID oid 最近的SQL命令插入的最后一行的 OID（只有在一条INSERT命令插入到一个具有 OID 的表后才有用）
PG_CONTEXT text 描述当前调用栈的文本行
（2）FOUND(boolean类型)特殊变量
如果一个SELECT INTO语句赋值了一行，它将把FOUND设置为真，如果没有返回行则将之设置为假。
如果一个PERFORM语句生成（并且抛弃）一行或多行，它将把FOUND设置为真，如果没有产生行则将之设置为假。
如果UPDATE、INSERT以及DELETE语句影响了至少一行，它们会把FOUND设置为真，如果没有影响行则将之设置为假。
如果一个FETCH语句返回了一行，它将把FOUND设置为真，如果没有返回行则将之设置为假。
如果一个MOVE语句成功地重定位了游标，它将会把FOUND设置为真，否则设置为假。
如果一个FOR或FOREACH语句迭代了一次或多次，它将会把FOUND设置为真，否则设置为假。当循环退出时，FOUND用这种方式设置；在循环执行中，尽管FOUND可能被循环体中的其他语句的执行所改变，但它不会被循环语句修改。
如果查询返回至少一行，RETURN QUERY和RETURN QUERY EXECUTE语句会把FOUND设为真， 如果没有返回行则设置为假。
其他的PL/pgSQL语句不会改变FOUND的状态。尤其需要注意的一点是：EXECUTE会修改GET DIAGNOSTICS的输出，但不会修改FOUND的输出。
FOUND是每个PL/pgSQL函数的局部变量；任何对它的修改只影响当前的函数。
游标的使用：
pg:
声明：declare user_cursor REFCURSOR;
打开：open user_cursor for select id from abc1;
使用：fetch user_cursor into v_id;
装取成功：while(FOUND = true) --FOUND内置布尔类型变量，sybse中判断条件为@@sqlstatus = 0 (0:装取成功，1失败，2读完)
loop
raise info '%',v_id;
fetch user_cursor into v_id;
end loop;
关闭：close user_cursor;
调用函数：
（1）位置记号法
在位置记号法中，参数可以按照从右往左被忽略并且因此而得到默认值。
（2）命名记号法
使用命名记号法的一个优点是参数可以用任何顺序指定。
SELECT concat_lower_or_upper(a => 'Hello', uppercase => true, b => 'World');
（3）混合记号法
更新表的统计信息：
sybase:
update index statistics table; 更新table的索引统计
pg:
analyze [verbose] [ table_name [ ( column_name [, ...] ) ] ] 更新表的统计信息
获取系统时间：
sybase
select getdate() 获取系统实时时钟的当前时间戳
验证：
begin
select getdate()
waitfor delay '00:00:05'
select getdate()
end
结果：
'2020-11-27 19:55:32.786'
'2020-11-27 19:55:37.75'
pg：
select now(); 当前事务开始的时间戳
select CURRENT_TIMESTAMP; 当前事务开始的时间戳
select clock_timestamp(); 实时时钟的当前时间戳
identity自增：
sqlserver中的identity：自增
create table abcd
(
id int identity,
name varchar
)with identity_gap=5
postgres中id自增的一种方法：
create table abcd
(
id int generated always as identity (cache 100000 START WITH 1 INCREMENT BY 1),
name varchar(32)
);
扫一扫
872
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
