数据库字符编码问题:
-- 查看PostgreSQL数据库服务器端编码:
show server_encoding;
-- 查看PostgreSQL客户端工具psql编码:
show client_encoding;
-- 指定Postgresql会话的客户端编码:
set client_encoding to 'utf8';
-- 获取当前系统时间(包括时区) 形如:2017-09-07 14:30:07.965671+08
select now();
select current_timestamp;
-- 获取当前系统时间(不包括时区) 形如: 2017-09-07 14:27:56
select now()::timestamp(0)without time zone;
-- 分页查询:
select * from city c LIMIT 100 OFFSET 0;
-- PostgreSQL获取数据库中所有view名 视图:
SELECT viewname FROM pg_views WHERE schemaname ='public' ;
-- PostgreSQL获取数据库中所有table名 表:
SELECT tablename FROM pg_tables
WHERE tablename NOT LIKE 'pg%'
AND tablename NOT LIKE 'sql_%'
ORDER BY tablename;
-- 查询 pahldata 用户的数据权限
select * from INFORMATION_SCHEMA.role_table_grants where grantee='pahldata';
-- PostgreSQL获取某个表 tablename 所有字段名称 , 类型,备注,是否为空 等
SELECT a.attname as name,col_description(a.attrelid,a.attnum) as comment,pg_type.typname as typename,a.attnotnull as notnull
FROM pg_class as c,pg_attribute as a inner join pg_type on pg_type.oid = a.atttypid
where c.relname = 'pahl_product_info' and a.attrelid = c.oid and a.attnum>0;