数据库名、schema、用户名关系
Pg一个数据库对应多个schema,一个用户对应多个数据库;数据库名不是schema;用户是用户,与数据库名和schema没有关系
单引号、双引号、反单引号的区别
(1)不支持反单引号
(2)关键字,对象名,字段名,别名等数据库中存在值的用双引号修饰
(3)字符串用单引号修饰
(4)别名只能用双引号,不能用单引号
大小写问题
(如果不加相关引号,默认都是小写;加上引号区分大小写)
(1)如果表名、字段名、别名等数据库中存在值如果有大写字母,必须用双引号修饰,否则默认当做小写处理
(2)select语句中,如果字段名或表名等数据库中存在值有大写字母,必须加上双引号;如果不加双引号默认小写,查询语句报错
(3)create语句中,如果字段中有大写字母,必须加双引号,否则当小写处理
(4)别名只能用双引号,不能用单引号
例如:
select batch_num "batchNum" from blood_relation_import-
---别名就是batchNum
select batch_num batchNum from blood_relation_import
----别名是batchnum
schema与表的标准格式:“schema”.“table”
在pg数据库中空字符串与null是不同的
Pg数据库7.3以后
除了字符串类型外,其他的类型不支持和空字符串(’’)比较,插入空字符串(’’)操作
只有字符串类型才可以和空字符串(’’)比较、插入操作
分页
sql格式:SELECT * FROM "TB01" LIMIT [no of rows]
SELECT *FROM "TB01" LIMIT 3;-----返回前三条记录
LIMIT 子句与 OFFSET 子句一起使用
SELECT * FROM table_name
LIMIT [pageSize] OFFSET [pageNum]
-- pageSize:每页显示条数
-- pageNum:从第几条开始查询
--第一页 pageSize:3 pageNum:(1-1)*3=0
select * from class_name LIMIT 3 OFFSET 0;
进行正则时,列必须是字符串类型
常见SQL语句
判断名字叫做BXM的schema下的BXM_QUALITY_ERROR_DATA数据表是否存在
SELECT
CASE
WHEN COUNT
( * ) > 0 THEN
1 ELSE 0
END AS "result"
FROM
information_schema.tables
WHERE
table_schema = 'BXM'
AND table_type = 'BASE TABLE'
AND TABLE_NAME = 'BXM_QUALITY_ERROR_DATA'
select * from pg_tables where tablename='BXM_QUALITY_ERROR_DATA';(此语句不判断schema)
在名字叫做BXM的schema下创建BXM_QUALITY_ERROR_DATA数据表
CREATE TABLE "BXM"."BXM_QUALITY_ERROR_DATA" (
"RESULT_CODE" VARCHAR ( 200 ) COLLATE "pg_catalog"."default" NOT NULL,
"TASK_CODE" VARCHAR ( 200 ) COLLATE "pg_catalog"."default" NOT NULL,
"VER_CODE" VARCHAR ( 200 ) COLLATE "pg_catalog"."default" NOT NULL,
"DATA" TEXT COLLATE "pg_catalog"."default"
);
COMMENT ON TABLE "BXM"."BXM_QUALITY_ERROR_DATA" IS '异常数据表';
判断BXM数据库是否存在
select * from pg_database where datname='BXM';
函数
CAST函数
CAST("字段名" as varchar)
CAST("字段名" as numeric)
COALESCE函数(类似于IFNULL函数)
COALESCE函数返回它的第一个非null参数的值,检索数据时用缺省值替换null值
select name,COALESCE(adress,'c') as adress from null_test;
字符串拼接
方式一:函数:string || string
返回类型:字符串
描述:字符串连接
样例:‘Post’ || ‘greSQL’
结果:PostgreSQL
方式二:函数:string || non-string or non-string || string
返回类型:text
描述:连接空字符串
样例:‘Value: ’ || 42’
结果:Value: 42
方式三:函数:concat(str “any” [, str “any” [, …] ])
返回类型:字符串
描述:连接函数里所有的字符串参数,忽略空字符串
样例:concat(‘abcde’, 2, NULL, 22)
结果:abcde222
方式四:函数:concat_ws(sep text, str “any” [, str “any” [, …] ])
返回类型:字符串
描述:以第一个参数作为分隔符,连接其他的几个参数
样例:concat_ws(’,’, ‘abcde’, 2, NULL, 22)
结果:abcde,2,22
注意:在pg数据库中拼接null与拼接空字符串('')没有区别,结果相同
例如:concat('abcde', 2, NULL, 22)与concat('abcde', 2,'', 22)结果都是abcde222
内容替换函数replace
replace(字段名,‘替换前的内容’,‘替换后的内容’)
正则表达式(列必须是字符串类型)
在postgresql中使用正则表达式时需要使用关键字“”,以表示该关键字之前的内容需匹配之后的正则表达式,若匹配规则不需要区分大小写,可以使用组合关键字“*”;
相反,若需要查询不匹配这则表达式的记录,只需在该关键字前加否定关键字“!”即可。若正则表达式包含转义字符,则需在表达式前加关键字“E”。
日期相关函数
数据类型格式化函数
PostgreSQL格式化函数提供一套有效的工具用于把各种数据类型(日期/时间、integer、floating point和numeric)转换成格式化的字符串以及反过来从格式化的字符串转换成指定的数据类型。下面列出了这些函数,它们都遵循一个公共的调用习惯:第一个参数是待格式化的值,而第二个是定义输出或输出格式的模板。
函数 | 返回类型 | 描述 | 例子 |
---|---|---|---|
to_char(timestamp, text) | text | 把时间戳转换成字串 | to_char(current_timestamp, ‘HH12:MI:SS’) |
to_char(interval, text) | text | 把时间间隔转为字串 | to_char(interval ‘15h 2m 12s’, ‘HH24:MI:SS’) |
to_char(int, text) | text | 把整数转换成字串 | to_char(125, ‘999’) |
to_char(double precision, text) | text | 把实数/双精度数转换成字串 | to_char(125.8::real, ‘999D9’) |
to_char(numeric, text) | text | 把numeric转换成字串 | to_char(-125.8, ‘999D99S’) |
to_date(text, text) | date | 把字串转换成日期 | to_date(‘05 Dec 2000’, ‘DD Mon YYYY’) |
to_timestamp(text, text) | timestamp | 把字串转换成时间戳 | to_timestamp(‘05 Dec 2000’, ‘DD Mon YYYY’) |
to_timestamp(double) | timestamp | 把UNIX纪元转换成时间戳 | to_timestamp(200120400) |
to_number(text, text) | numeric | 把字串转换成numeric | to_number(‘12,454.8-’, ‘99G999D9S’) |
- 用于日期/时间格式化的模式:
模式 | 描述 |
---|---|
HH | 一天的小时数(01-12) |
HH12 | 一天的小时数(01-12) |
HH24 | 一天的小时数(00-23) |
MI | 分钟(00-59) |
SS | 秒(00-59) |
MS | 毫秒(000-999) |
US | 微秒(000000-999999) |
AM | 正午标识(大写) |
Y,YYY | 带逗号的年(4和更多位) |
YYYY | 年(4和更多位) |
YYY | 年的后三位 |
YY | 年的后两位 |
Y | 年的最后一位 |
MONTH | 全长大写月份名(空白填充为9字符) |
Month | 全长混合大小写月份名(空白填充为9字符) |
month | 全长小写月份名(空白填充为9字符) |
MON | 大写缩写月份名(3字符) |
Mon | 缩写混合大小写月份名(3字符) |
mon | 小写缩写月份名(3字符) |
MM | 月份号(01-12) |
DAY | 全长大写日期名(空白填充为9字符) |
Day | 全长混合大小写日期名(空白填充为9字符) |
day | 全长小写日期名(空白填充为9字符) |
DY | 缩写大写日期名(3字符) |
Dy | 缩写混合大小写日期名(3字符) |
dy | 缩写小写日期名(3字符) |
DDD | 一年里的日子(001-366) |
DD | 一个月里的日子(01-31) |
D | 一周里的日子(1-7;周日是1) |
W | 一个月里的周数(1-5)(第一周从该月第一天开始) |
WW | 一年里的周数(1-53)(第一周从该年的第一天开始) |
- 用于数值格式化的模板模式:
模式 | 描述 |
---|---|
9 | 带有指定数值位数的值 |
0 | 带前导零的值 |
.(句点) | 小数点 |
,(逗号) | 分组(千)分隔符 |
PR | 尖括号内负值 |
S | 带符号的数值 |
L | 货币符号 |
D | 小数点 |
G | 分组分隔符 |
MI | 在指明的位置的负号(如果数字 < 0) |
PL | 在指明的位置的正号(如果数字 > 0) |
SG | 在指明的位置的正/负号 |
时间/日期函数和操作符:
1. 下面是PostgreSQL中支持的时间/日期操作符的列表:
操作符 | 例子 | 结果 |
---|---|---|
+ | date ‘2001-09-28’ + integer ‘7’ | date ‘2001-10-05’ |
+ | date ‘2001-09-28’ + interval ‘1 hour’ | timestamp ‘2001-09-28 01:00’ |
+ | date ‘2001-09-28’ + time ‘03:00’ | timestamp ‘2001-09-28 03:00’ |
+ | interval ‘1 day’ + interval ‘1 hour’ | interval ‘1 day 01:00’ |
+ | timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’ | timestamp ‘2001-09-29 00:00’ |
+ | time ‘01:00’ + interval ‘3 hours’ | time ‘04:00’ |
- | - interval ‘23 hours’ | interval ‘-23:00’ |
- | date ‘2001-10-01’ - date ‘2001-09-28’ | integer ‘3’ |
- | date ‘2001-10-01’ - integer ‘7’ | date ‘2001-09-24’ |
- | date ‘2001-09-28’ - interval ‘1 hour’ | timestamp ‘2001-09-27 23:00’ |
- | time ‘05:00’ - time ‘03:00’ | interval ‘02:00’ |
- | time ‘05:00’ - interval ‘2 hours’ | time ‘03:00’ |
- | timestamp ‘2001-09-28 23:00’ - interval ‘23 hours’ | timestamp ‘2001-09-28 00:00’ |
- | interval ‘1 day’ - interval ‘1 hour’ | interval ‘23:00’ |
- | timestamp ‘2001-09-29 03:00’ - timestamp ‘2001-09-27 12:00’ | interval ‘1 day 15:00’ |
* | interval ‘1 hour’ * double precision ‘3.5’ | interval ‘03:30’ |
/ | interval ‘1 hour’ / double precision ‘1.5’ | interval ‘00:40’ |
日期/时间函数:
函数 | 返回类型 | 描述 | 例子 | 结果 |
---|---|---|---|---|
age(timestamp, timestamp) | interval | 减去参数,生成一个使用年、月的"符号化"的结果 | age(‘2001-04-10’, timestamp ‘1957-06-13’) | 43 years 9 mons 27 days |
age(timestamp) | interval | 从current_date减去得到的数值 | age(timestamp ‘1957-06-13’) | 43 years 8 mons 3 days |
current_date | date | 今天的日期 | ||
current_time | time | 现在的时间 | ||
current_timestamp | timestamp | 日期和时间 | ||
date_part(text, timestamp) | double | 获取子域(等效于extract) | date_part(‘hour’, timestamp ‘2001-02-16 20:38:40’) | 20 |
date_part(text, interval) | double | 获取子域(等效于extract) | date_part(‘month’, interval ‘2 years 3 months’) | 3 |
date_trunc(text, timestamp) | timestamp | 截断成指定的精度 | date_trunc(‘hour’, timestamp ‘2001-02-16 20:38:40’) | 2001-02-16 20:00:00+00 |
extract(field from timestamp) | double | 获取子域 | extract(hour from timestamp ‘2001-02-16 20:38:40’) | 20 |
extract(field from interval) | double | 获取子域 | extract(month from interval ‘2 years 3 months’) | 3 |
localtime | time | 今日的时间 | ||
localtimestamp | timestamp | 日期和时间 | ||
now() | timestamp | 当前的日期和时间(等效于 current_timestamp) | ||
timeofday() | text | 当前日期和时间 |
EXTRACT,date_part函数支持的field:
域 | 描述 | 例子 | 结果 |
---|---|---|---|
CENTURY | 世纪 | EXTRACT(CENTURY FROM TIMESTAMP ‘2000-12-16 12:21:13’); | 20 |
DAY | (月分)里的日期域(1-31) | EXTRACT(DAY from TIMESTAMP ‘2001-02-16 20:38:40’); | 16 |
DECADE | 年份域除以10 | EXTRACT(DECADE from TIMESTAMP ‘2001-02-16 20:38:40’); | 200 |
DOW | 每周的星期号(0-6;星期天是0) (仅用于timestamp) | EXTRACT(DOW FROM TIMESTAMP ‘2001-02-16 20:38:40’); | 5 |
DOY | 一年的第几天(1 -365/366) (仅用于 timestamp) | EXTRACT(DOY from TIMESTAMP ‘2001-02-16 20:38:40’); | 47 |
HOUR | 小时域(0-23) | EXTRACT(HOUR from TIMESTAMP ‘2001-02-16 20:38:40’); | 20 |
MICROSECONDS | 秒域,包括小数部分,乘以 1,000,000。 | EXTRACT(MICROSECONDS from TIME ‘17:12:28.5’); | 28500000 |
MILLENNIUM | 千年 | EXTRACT(MILLENNIUM from TIMESTAMP ‘2001-02-16 20:38:40’); | 3 |
MILLISECONDS | 秒域,包括小数部分,乘以 1000。 | EXTRACT(MILLISECONDS from TIME ‘17:12:28.5’); | 28500 |
MINUTE | 分钟域(0-59) | EXTRACT(MINUTE from TIMESTAMP ‘2001-02-16 20:38:40’); | 38 |
MONTH | 对于timestamp数值,它是一年里的月份数(1-12);对于interval数值,它是月的数目,然后对12取模(0-11) | EXTRACT(MONTH from TIMESTAMP ‘2001-02-16 20:38:40’); | 2 |
QUARTER | 该天所在的该年的季度(1-4)(仅用于 timestamp) | EXTRACT(QUARTER from TIMESTAMP ‘2001-02-16 20:38:40’); | 1 |
SECOND | 秒域,包括小数部分(0-59[1]) | EXTRACT(SECOND from TIMESTAMP ‘2001-02-16 20:38:40’); | 40 |
WEEK | 该天在所在的年份里是第几周。 | EXTRACT(WEEK from TIMESTAMP ‘2001-02-16 20:38:40’); | 7 |
YEAR | 年份域 | EXTRACT(YEAR from TIMESTAMP ‘2001-02-16 20:38:40’); | 2001 |
当前日期/时间:
我们可以使用下面的函数获取当前的日期和/或时间∶
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (precision)
CURRENT_TIMESTAMP (precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (precision)
LOCALTIMESTAMP (precision)
异常
duplicate key value violates unique constraint
报错信息:
duplicate key value violates unique constraint "parent_parentprofile_pkey"
DETAIL: Key (id)=(3) already exists.
原因分析:
postgres主键排序是不同步
主要是:serial key其实是由sequence实现的,当你手动给serial列赋值的时候,sequence是不会自增量变化的。
最好不要给serial手工赋值
解决方式:
可能是自增主键和序列不一致导致的,所以也可以不用重置序列起始值和主键,采取把序列号改成和当前最大主键一致的方式
SELECT setval('表名_id_seq', (SELECT MAX(id) FROM 表名)+1)