PostgreSQL命令速查 – 数据操作

参考Postgres XL官方文档PostgreSQL官方文档

数据类型

【名称(别名)】 【大小】 【格式】 【描述】
smallint (int2) 2 bytes 数字:整数,取值范围-32768到+32767
integer (int,int4) 4 bytes 数字:整数,取值范围-2147483648到+2147483647
bigint (int8) 8 bytes 数字:整数,取值范围-9223372036854775808到+9223372036854775807
numeric (decimal) 变长 1.23 数字:浮点数,numeric(p,s) 例如numeric(5,2)表示精度xxx.xx
real (float4) 4 bytes 数字:浮点数,6位十进制数精度
double precision (float8) 8 bytes 数字:浮点数,15位十进制数精度
smallserial (serial2) 2 bytes 数字:自然数,取值范围1到32767
serial (serial4) 4 bytes 数字:自然数,取值范围1到2147483647
bigserial (serial8) 8 bytes 数字:自然数,取值范围1到9223372036854775807
money 8 bytes 数字:浮点数,2位十进制数精度,取值范围-92233720368547758.08到+92233720368547758.07
character (char) 定长 abc 字符:定长空白填充,例如char(4)
character varying (varchar) 变长 abc 字符:例如varchar(64)
text 变长 abc 字符:不限长度
bytea 变长 字节数组:变长二进制串
timestamp 8 bytes 日期时间:时间戳,不含时区
timestamp with time zone (timestamptz) 8 bytes 日期时间:时间戳,含时区
time 8 bytes 日期时间:仅一日内时间,不含时区
time with time zone (timetz) 12 bytes 日期时间:仅一日内时间,含时区
date 4 bytes 日期时间:仅日期
interval 16 bytes 日期时间:时间差
boolean (bool) 1 bytes TRUE 布尔:是否状态,TRUE/FALSE,’t’/’f’,’y’/’n’,’1’/’0’,’true’/’false’,’on’/’off’,’yes’/’on’
point 16 bytes (x,y) 几何:二维点
lseg 32 bytes ((x1,y1),(x2,y2)) 几何:线段
box 32 bytes ((x1,y1),(x2,y2)) 几何:矩形
circle 24 bytes <(x,y),r> 几何:圆
inet 7 or 19 bytes 192.168.100.128/25 IP地址:IPv4和IPv6
bit 定长 1001 位串:例如bit(6)
bit varying (varbit) 变长 10010 位串:例如varbit(64)
uuid 定长 a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 通用唯一标识符
xml 变长 bar XML
jsonb 变长 {“foo”: [true, “bar”], “tags”: {“a”: 1, “b”: null}} JSON
int4range 定长 [4,10] 区间:integer
int8range 定长 [4,10) 区间:bigint
numrange 定长 [-2.2,9.9] 区间:numeric
tsrange 定长 (2010-01-01 14:30, 2010-01-01 15:30] 区间:timestamp
tstzrange 定长 [2010-01-01 14:30, 2010-01-01 15:30) 区间:timestamp with time zone
daterange 定长 (2010-01-01,2010-01-12) 区间:date
xxx[] 变长 数组:例如integer[],text[][],bigint[4]
# 创建枚举类型
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

# 创建复合类型
CREATE TYPE game_item AS (
    id      integer,
    type    smallint,
    amount  integer
);

# 查询所有枚举类型值和标签
SELECT * FROM pg_enum;

# 删除类型
DROP TYPE mood;

样例表

CREATE SEQUENCE seq_item_id START 1;
CREATE TYPE message_type AS ENUM ('help', 'confirm', 'accept', 'be_friend', 'chat');
CREATE TYPE game_item AS (
    id      integer,
    type    smallint,
    amount  integer
);
CREATE TABLE user_info (
    id             serial8         PRIMARY KEY,
    name           varchar(64)     NOT NULL,
    sex            char(2)         DEFAULT 'F' CHECK (sex IN ('F', 'M')),
    age            int2            CONSTRAINT user_info_c_positive_age CHECK (age > 0 AND age < 100),
    height         numeric(3,2),
    weight         numeric(5,2),
    birthday       date,
    is_manager     bool            DEFAULT false,
    information    text,
    reg_position   point,
    reg_ip         inet,
    reg_time       timestamp       DEFAULT now(),
    class_ids      int[],
    power_range    int4range,
    bit_set        varbit(64),
    xml_data       xml,
    jsonb_data     jsonb           NOT NULL DEFAULT '{}'
);
CREATE TABLE user_message (
    id             uuid            PRIMARY KEY,
    to_user_id     serial8         REFERENCES user_info(id) ON DELETE CASCADE,
    from_user_id   serial8         REFERENCES user_info(id) ON DELETE CASCADE,
    msg_type       message_type    NOT NULL,
    msg_content    varchar(128),
    msg_item       game_item,
    cur_time       timestamp       DEFAULT now(),
    state          int2            DEFAULT 0
);
CREATE TABLE user_message_201701(
    CHECK ( cur_time >= DATE '2017-01-01' AND cur_time < DATE '2017-02-01')
) INHERITS (user_message);

数据操作

# 插入
INSERT INTO user_info (id, name, sex, age, height, weight, birthday, is_manager, information, reg_position, reg_ip, reg_time, class_ids, power_range, bit_set, xml_data, jsonb_data) 
    VALUES (1, '张三', 'M', 12, 1.78, 62.55, '2000-12-22', FALSE, 'some info', '(-194.0,53.0)', '52.52.180.99', '2017-01-01 10:10:23', '{2,3}', '(60,92]', '10010', '<a>xx</a>', '{"a":1}');

INSERT INTO user_info (id, name, age, height, weight, birthday, reg_ip, class_ids, power_range) 
    VALUES (2, '李四', 13, 1.72, 80.2, '2000-02-02 12:12:12', '192.168.0.1/24', ARRAY[2,3], '[60,92)')
    RETURNING reg_time;          --RETURNING 返回指定字段值

INSERT INTO user_message (id, to_user_id, from_user_id, msg_type, msg_content, msg_item, cur_time, state) 
    VALUES ('6bde7181-a651-40f3-913c-2d21d8d31a51', 1, 2, 'help', 'please help me', ROW(1,2,100), '2017-02-02 10:12:22', 0);

INSERT INTO user_message AS u VALUES ('c255ebf2-f4b5-46d4-b1c3-088edb37ad18', 2, 1, 'confirm', 'allright', NULL, '2017-02-02 12:11:26', 1)
    ON CONFLICT (id)         --字段需要有唯一校验
    DO UPDATE SET msg_content= EXCLUDED.msg_content || ' ' || u.msg_content 
    --WHERE u.to_user_id = 2 
    ;

INSERT INTO user_message AS u VALUES ('c255ebf2-f4b5-46d4-b1c3-088edb37ad18', 2, 1, 'confirm', 'allright', NULL, '2017-02-02 12:11:26', 1)
    ON CONFLICT ON CONSTRAINT user_message_pkey         --使用键名
    DO NOTHING;

INSERT INTO user_message_201701 VALUES 
    ('f4daf1ef-67a7-4887-b5e2-6e13a1d9197d', 2, 1, 'be_friend', NULL, '2017-01-15 16:11:26', 0),
    ('5f408df5-6b80-48aa-a6f6-e0bc7e30625a', 2, 1, 'be_friend', 'x', '2017-01-17 18:15:26', DEFAULT);

# 插入30条随机数据
INSERT INTO user_info (id, name, sex, age, height, weight) 
    SELECT generate_series(101, 130), 
           'ran_' || generate_series(101, 130), 
           CASE WHEN random() > 0.5 THEN 'F' ELSE 'M' END, 
           (random() * 30 + 10)::int2, 
           trunc((random() + 1)::numeric, 2), 
           trunc((random() * 30 + 50)::numeric, 2);

# 将查询结果插入另一个已经存在的表 insert ... select
INSERT INTO user_message SELECT * FROM user_message_cache WHERE cur_time < '2017-01-01';

# 将查询结果插入一个新建的表 select ... into ... from
SELECT * INTO user_message_tmp0 FROM user_message_cache WHERE cur_time < '2017-01-01';

# 用with包装子查询或返回结果集,使其可以通过别名被后面的insert语句引用
WITH um AS (
    UPDATE user_message SET msg_content = 'oo' WHERE to_user_id = (SELECT id FROM user_info WHERE name = '李四')
    RETURNING *
) INSERT INTO user_message_cache SELECT * FROM um;

# 把服务器上的文本文件中的数据装载到表 copy ... from  如果想复制客户端的本地文件需要使用\copy
COPY user_message FROM '/home/user/user_message.txt';

# 把数据复制到服务器上的文本文件 copy ... to  如果想复制客户端的本地文件需要使用\copy
COPY user_info (id, name, sex, age) TO PROGRAM 'gzip > /tmp/user_info.gz';
COPY (SELECT * FROM user_info WHERE sex = 'F') TO '/tmp/user_info.copy';

# 更新
UPDATE user_info 
    SET information = 'new info', sex = DEFAULT, age = age + 1, power_range = NULL 
    WHERE name = '张三' AND is_manager = FALSE;

UPDATE user_message SET msg_item = ROW(2,3,150) FROM user_info 
    WHERE user_info.id = user_message.to_user_id;

UPDATE user_message SET (to_user_id, from_user_id) = 
    (SELECT to_user_id, from_user_id FROM user_message_cache WHERE user_message.id = user_message_cache.id);


# 删除
DELETE FROM user_message WHERE from_user_id <> 1;
DELETE FROM user_message WHERE msg_type IN ('help', 'confirm', 'accept', 'be_friend') RETURNING *;
DELETE FROM user_message;


# 查询
SELECT DISTINCT ON (age) id, name, age FROM user_info WHERE sex = 'F' AND is_manager ORDER BY age;

SELECT * FROM ONLY user_message;         --ONLY 仅查询当前表的数据,不含继承表

SELECT sex, birthday, count(id) FROM user_info GROUP BY sex, birthday HAVING count(id) < 40;

SELECT weight / (height ^ 2.0) AS bmi, * FROM user_info ORDER BY sex DESC, bmi ASC;

SELECT id, name, age, 
    CASE WHEN sex = 'F' THEN '女'
         WHEN sex = 'M' THEN '男'
         ELSE '妖'
    END,                                               --条件选择
    COALESCE(height, weight, 0.0) AS coalesce_v,       --如果第一个参数为空则选第二个,如果第二个也为空则选第三个
    NULLIF(name, 'ran_121') AS nullif_v                --如果第一个参数和第二个参数相等则返回NULL
    FROM user_info WHERE CASE WHEN age > 20 THEN age % 2 = 1 ELSE false END;      --条件选择

SELECT * FROM user_info WHERE id IN (101, 102, 103, 104, 1, 2) AND (sex, age) NOT IN (('F', 36), ('M', '27')) OFFSET 2 LIMIT 1;  -- OFFSET 2表示跳过前两条结果

SELECT * FROM user_info WHERE EXISTS (SELECT to_user_id FROM user_message WHERE state = ANY (ARRAY[1, 0]));  -- EXISTS里的子查询如果存在返回行则代表条件为true

SELECT * FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow')) AS names (first, last);

SELECT * FROM user_message, LATERAL (SELECT * FROM user_info WHERE birthday IS NOT NULL) ui;

SELECT sex, age, count(id) FROM user_info 
    GROUP BY GROUPING SETS ((sex), (sex, age))       --显示sex分组结果和sex + age分组结果
    --GROUP BY CUBE (sex, age)                       --相当于GROUPING SETS ((sex, age), (sex), (age), ()) 排列组合
    --GROUP BY ROLLUP (sex, age)                     --相当于GROUPING SETS ((sex, age), (sex), ()) 依次去掉后面一个
    ;

WITH user_age_gt_30 AS (
        SELECT count(id) count_gt_30, sex FROM user_info WHERE age > 30 GROUP BY sex
    ),
     user_age_lt_avg AS (
        SELECT count(id) count_lt_avg, sex FROM user_info WHERE age < (SELECT avg(age) FROM user_info) GROUP BY sex
    )
    SELECT * FROM user_age_gt_30 INNER JOIN user_age_lt_avg USING (sex);    --用with包装子查询或返回结果集,使其可以通过别名被后面的语句引用

WITH RECURSIVE result (id, from_user_id, to_user_id, msg_content, depth) AS (
        SELECT id, from_user_id, to_user_id, msg_content, 1 
            FROM user_message WHERE from_user_id = 1
        UNION ALL
        SELECT um.id, um.from_user_id, um.to_user_id, um.msg_content, (result.depth + 1) 
            FROM user_message um, result WHERE um.from_user_id = result.to_user_id AND result.depth <= 5
    ) 
    SELECT * FROM result;                                 -- 使用RECURSIVE开启递归 将result放进子查询中使用 递归直到最底层子查询结果为空 用于遍历树 注意死循环

SELECT t1.*, t2.* FROM user_info AS t1 
    INNER JOIN user_message AS t2 ON (t1.id = t2.to_user_id) 
    --LEFT JOIN user_data AS t2 USING (id)                         --使用USING代替ON可以使连接字段列只显示一次,连接字段的名字必须相同
    --RIGHT JOIN user_message AS t2 ON t1.id = t2.to_user_id 
    --FULL JOIN user_message AS t2 ON t1.id = t2.to_user_id 
    --CROSS JOIN user_message AS t2 ON t1.id = t2.to_user_id 
    WHERE t1.age < 100;

SELECT id, name, age FROM user_info WHERE age > 20
    UNION                            -- 合并两个结果集,加ALL不去除两个结果集中重复的
    --INTERSECT                      -- 查询两个结果集的交集
    --EXCEPT                         -- 查询在前一个结果集中但是不在后面一个结果集中的记录
    SELECT id, name, age FROM user_info WHERE age > 30;

# 聚合
SELECT sex, max(age), min(age), avg(height), sum(weight), count(id),    -- 最大,最小,平均,合计,计数
    array_agg(age),                                  -- 聚合成数组
    string_agg(name, ';'),                           -- 聚合成分号分隔的字符串
    jsonb_agg(jsonb_data),                           -- jsonb聚合
    xmlagg(xml_data ORDER BY age DESC),              -- xml聚合,聚合顺序按age倒序
    jsonb_object_agg(name, age),                     -- 构造KV对,并聚合成jsonb
    bool_and(is_manager), bool_or(is_manager)        -- 布尔与和或
    FROM user_info GROUP BY sex;

SELECT mode() WITHIN GROUP (ORDER BY sex),           -- 取sex中出现频率最多的一个值
    rank(26) WITHIN GROUP (ORDER BY age),            -- 取26在排名中的位置
    dense_rank(26) WITHIN GROUP (ORDER BY age),      -- 取26在排名中的位置,名次连续,名次相同不跳级
    percent_rank(26) WITHIN GROUP (ORDER BY age)     -- 取26在排名中的百分比位置
    FROM user_info;

SELECT *, rank() OVER (ORDER BY age),                -- 显示按age的正序排名
    dense_rank() OVER (ORDER BY age),                -- 显示按age的正序排名,名次连续,名次相同不跳级
    percent_rank() OVER (PARTITION BY sex ORDER BY age DESC),               -- 按sex分区,显示按age的倒序百分比排名
    row_number() OVER (),                                                   -- 显示行号
    lag(age::integer, 1, -100) OVER (order by age),                         -- 显示按age正序排序的前第一行的age值,缺省为-100,同 lead(age, -1, -100) OVER (order by age),
    lag(age::integer, -2) OVER (order by age)                               -- 显示按age正序排序的后第二行的age值,由于函数第一个参数要求是integer所以这里转换了下
    FROM user_info;

SELECT * FROM (SELECT id, name, sex, age, rank() OVER (PARTITION BY sex ORDER BY age) AS pos FROM user_info) AS sub
    WHERE pos < 5;                                          -- 按sex分区后按age排序,显示排名小于5的数据

SELECT id, name, sex, age, 
    sum(age) OVER (PARTITION BY sex ORDER BY age DESC),     -- OVER的作用是将聚合结果按行展开,加ORDER BY则显示的是逐行计算值,否则是最终值
    avg(age) OVER (PARTITION BY sex) 
    FROM user_info;

SELECT sex, sum(age) OVER w, avg(age) OVER w
    FROM user_info
    WINDOW w AS (PARTITION BY sex ORDER BY age DESC);

# 条件判断
SELECT * FROM user_info WHERE sex = 'F'  
    AND id <= 10000 
    AND id > -1 
    AND class_ids[1] <> class_ids[2] 
    AND name IS NOT NULL
    AND information IS DISTINCT FROM ''     --DISTINCT 不同
    AND jsonb_data IS NOT DISTINCT FROM '{}'  --NOT DISTINCT 相同
    AND is_manager IS NOT TRUE                --unknown or false
    AND is_manager IS FALSE                   --false
    AND is_manager IS NOT UNKNOWN             --true or false
    AND age NOT BETWEEN 100 AND 200
    AND age BETWEEN SYMMETRIC 100 AND 1;       --排序xy值后between

# 数学
SELECT (1 + 2) * 3 / 4, 
    5 % 4 "取模",        --1
    2.0 ^ 3.0 "求幂",    --8.0000
    |/ 25.0 "平方根",    --5
    ||/ 27.0 "立方根",   --3
    (5 !) "阶乘",        --120
    @ -5.0 "绝对值",     --5.0
    91 & 15 "位AND",     --11
    32 | 3 "位OR",       --35
    17 # 5 "位XOR",      --20
    ~(-4) "位NOT",       --3
    1 << 4 "左位移",     --5
    8 >> 2 "右位移";     --5

SELECT div(9,4) "整除",                        -- 2
    abs(-17.4) "绝对值",                       -- 17.4
    mod(9,4) "取模,取余",                           -- 1
    sqrt(2.0) "平方根",                        -- 1.414213562373095
    cbrt(27.0) "立方根",                       -- 3
    ceil(-42.8) "向上舍入",                    -- -42
    ceiling(-42.8) "向上舍入",                 -- -42
    floor(-42.8) "向下舍入",                   -- -43
    round(42.4382, 2) "四舍五入",              -- 42.44
    power(9.0, 3.0) "9.0的3.0次幂",            -- 729.000000
    degrees(0.5) "弧度转成度",                 -- 28.6478897565412
    radians(45.0) "度转成弧度",                -- 0.785398163397448
    exp(1.0) "指数",                           -- 2.7182818284590452
    ln(2.0) "自然对数",                        -- 0.6931471805599453
    log(100.0) "10为底的对数",                 -- 2.000
    log(2.0, 64.0) "2为底64的对数",            -- 6.000
    pi() "圆周率",                             -- 3.14159265358979
    trunc(42.4382, 2) "精度转换",              -- 42.43
    sign(-8.4) "取数字符号(-1, 0, +1)",        -- -1
    random() "取随机数(0.0 <= x < 1.0)";       -- 0.745092083700001

SELECT sin(1), cos(1), tan(45), asin(-1), acos(0.3), atan(1);

SELECT generate_series(10, 20, 1.5);           -- 生成多行数字 开始于10 结束于20 步差1.5

# 字符串
SELECT 'Post'||'greSQL'||9.5 "字符连接", 
    concat('Post', 'greSQL', NULL, 9.5) "字符连接", 
    concat_ws(',', 'Post', 'greSQL', NULL, 9.5) "字符连接,逗号分隔";

SELECT bit_length('Bruce李') "字符串长度,位数", 
    char_length('Bruce李') "字符串长度,字符数", 
    octet_length('Bruce李') "字符串长度,字节数", 
    length('Bruce李') "字符串长度,字符数";

SELECT lower('TOM') "转小写", 
    upper('tom') "转大写", 
    initcap('hi THOMAS') "单次首字母大写,其他小写";

SELECT ltrim('zzzyLtrim', 'xyz') "修剪掉左边的任意xyz字符", 
    rtrim('Rtrimxxzx', 'xyz') "修剪掉右边的任意xyz字符", 
    btrim('xyxBtrimyyx', 'xyz') "修剪掉两边的任意xyz字符";  --第二个参数缺省表示空格

SELECT trim(leading 'xyz' from 'zzzyTrim') "修剪掉左边的任意xyz字符", 
    trim(trailing 'xyz' from 'Trimxxzx') "修剪掉右边的任意xyz字符", 
    trim(both 'xyz' from 'xyxTrimyyx') "修剪掉两边的任意xyz字符";

SELECT lpad('hi', 5, 'xy') "在左边填充xy字符,使最终串字符数量是5",   --xyxhi
    rpad('hi', 5, 'xy') "在右边填充xy字符,使最终串字符数量是5";  --第三个参数缺省表示空格

SELECT overlay('Txxxxas' placing 'hom' from 2 for 4) "子串替换",  --Thomas
    replace('Thomasmasmas', 'ma', 'XY') "子串替换",   --ThoXYsXYsXYs
    translate('Thomasmasmas', 'oa', '12') "字符替换",   --Th1m2sm2sm2s
    format('Hello %s, %1$s, %%', 'World') "格式替换",  --Hello World, World, %
    format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three'),  --Testing three, two, one
    format('UPDATE user_message SET %I = %L WHERE to_user_id = %L', 'msg_content', 'format abc', 10);   --%I表示SQL标识符 %L表示SQL文字 UPDATE user_message SET msg_content = 'format abc' WHERE to_user_id = '10'

SELECT position('om' in 'Thomas') "子串位置",   --3
    strpos('Thomas', 'om') "子串位置",   --3
    left('Thomas', 2) "从左边截取2个字符返回子串",   --Th
    right('Thomas', 2) "从右边截取2个字符返回子串",   --as
    substring('Thomas' from 2 for 3) "从第2个字符开始截取3个字符返回子串",   --hom
    substring('Thomas' from '...$') "取最后3个字符",               --mas
    substr('Thomas', 2, 3) "从第2个字符开始截取3个字符返回子串",   --hom
    reverse('ABCDE') "反转",
    repeat('Pg', 4) "重复",
    regexp_split_to_array('hello world', E'\\s+') "字符串正则分割成数组",        --{hello, world}
    split_part('abc~@~def~@~ghi', '~@~', 2) "字符串分割成数组取第二个元素",      --def
    regexp_split_to_table('hello,world', ',') "字符串正则分割成多行",
    regexp_replace('Thomas', '.[mN]a.', 'M') "正则匹配替换",                     --ThM
    regexp_matches('foobarbequebaz', '(bar)(beque)') "正则匹配取值",             --{bar, beque}
    regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
    
SELECT cast(2345 as char(30)) "将2345转换为char字符串",
    decode(encode('abcdefg','pwd'),'pwd') "解密由encode加密过的字符串",
    to_hex(2147483647),
    ascii('A'), chr(65),
    md5('abcdefg');

# 字符串模式匹配
SELECT 'abc' LIKE '_b_',                                    -- t
    'abc' NOT LIKE 'a%',                                    -- f
    'abc' SIMILAR TO '%(b|d)%',                             -- t
    'abc' NOT SIMILAR TO '(b|c)%';                          -- t

SELECT 'thomas' ~ '^th.*s$',                  -- ~   大小写敏感匹配        th开头,s结尾,中间0个或多个除换行符外的单字符
    'tho mas' ~* '\w+\s?\D{5}',               -- ~*  忽略大小写匹配        左侧1个或多个数字、字母、下划线,然后0个或1个空格,然后5个非数字字符
    'thomas_123:xyz' !~ '.{12,}',             -- !~  大小写敏感不匹配      至少12个除换行符外的单字符
    'thomas\\' !~* '.*vadim.*';               -- !~* 忽略大小写不匹配

--  ^ 开头 $ 结尾 * 0个或多个 ? 0个或1个 + 1个或多个 . 除换行符外的单字符 \d 数字 \s 空格 \W 非字母、下划线、数字 {n,m} n到m个

# 日期时间
SELECT date '2001-09-28' + integer '7' "加7天",                    -- date '2001-10-05'
    '2001-09-28'::date - 3 "减3天",                                -- date '2001-09-25'
    timestamp '2001-09-29 03:00' + interval '1 hour' "加1小时",    -- timestamp '2001-09-29 04:00:00'
    time '05:00' - interval '2 hours' "减2小时",                   -- time '03:00:00'
    date '2001-10-01' - date '2001-09-28' "求时间差",              -- integer 3
    interval '1 day' * 18.5 "18.5个1天",                           -- interval '18 days 12:00:00'
    interval '2 days' / double precision '1.5' "2天除以1.5";       -- interval '1 day 08:00:00'

SELECT age(timestamp '2001-04-10 01:20', timestamp '1957-06-13') "求时间间隔(缺省一个参数表示距当前日期0点)",    -- interval '43 years 9 mons 27 days 01:20:00'
    now() "当前日期时间(事务开始时间)",         -- 同 localtimestamp current_timestamp transaction_timestamp(),除localtimestamp外其他都含时区 2017-03-14 18:50:38.04767+08
    statement_timestamp() "当前日期时间(会话开始时间)",
    clock_timestamp() "当前日期时间(实时)",     -- timeofday()也是实时日期时间,但是返回的是text类型
    current_date "当前日期",                                                          -- 2017-03-14
    current_time "当前时间",                                                          -- 19:15:41.763299+08
    make_date(2013, 7, 15) "构造日期",                                                -- 2013-07-15
    make_timestamp(2013, 7, 15, 8, 15, 23.5) "构造日期时间",                          -- 2013-07-15 08:15:23.5
    to_timestamp(123456789) "转成日期时间(参数是距1970-01-01 00:00:00+00的秒数)",   -- 1973-11-30 05:33:09+08
    date_trunc('hour', timestamp '2001-02-16 20:38:40') "转换精度到小时",             -- 2001-02-16 20:00:00
    date_part('week', timestamp '2001-04-16 20:38:40') "求一年中的第几周";            -- 16 同 extract(week from timestamp '2001-04-16 20:38:40')
    -- date_part第一参数类型:year, quarter, month, week, day, hour, minute, second, milliseconds
       -- 一周中的第几天 dow (0~6) isodow (1~7)
       -- 一年中的第几天 doy

# 枚举
SELECT enum_first(null::message_type) "取枚举的第一个值",    -- 为了输入枚举类型而借用null来转换,'be_friend'::message_type也是可以的
    enum_last(null::message_type) "取枚举的最后一个值",
    enum_range('confirm'::message_type, 'be_friend'::message_type) "取两个枚举值区间的枚举值数组(缺省一个参数时表示取全部)";  -- {confirm, accept, be_friend}

# 序列
SELECT currval('seq_item_id') "返回当前位置值",      -- 同 lastval()
    nextval('seq_item_id') "移动序列到下一个位置,返回新值",
    setval('seq_item_id', 10) "设置当前值";

# 数组
SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] "强转换数组类型并判断数组是否相同",          -- t
    ARRAY[1,4,3] @> ARRAY[3,1] "数组是否包含另一个数组",                                     -- t
    ARRAY[2,7] <@ ARRAY[1,7,4,2,6] "数组是否包含于另一个数组",                               -- t
    ARRAY[1,4,3] && ARRAY[2,1] "两个数组是否含有相同元素",                                   -- t
    7 || ARRAY[1,2,3] || ARRAY[4,5,6] || 7 "数组合并或向数组追加元素",                       -- {7,1,2,3,4,5,6,7}
    array_append(ARRAY[1,2], 3) "向数组追加元素",                                            -- {1,2,3}
    array_prepend(1, ARRAY[2,3]) "向数组头部插入元素",                                       -- {1,2,3}
    array_cat(ARRAY[1,2,3], ARRAY[4,5]) "数组合并",                                          -- {1,2,3,4,5}
    array_length(array[1,2,3], 1) "数组1维的长度",                                           -- 3
    array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon') "求元素在数组中的位置",      -- 2
    array_positions(ARRAY['A','A','B','A'], 'A') "求元素在数组中的位置",                                 -- {1,2,4}
    array_remove(ARRAY[1,2,3,2], 2) "从数组中移除元素",                                                  -- {1,3}
    array_replace(ARRAY[1,2,5,4], 5, 3) "数组元素替换";                                                  -- {1,2,3,4}

SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*') "数组转字符串,逗号分隔元素,星号替换NULL(缺省第三个参数则忽略NULL元素)",
    string_to_array('xx~^~yy~^~zz', '~^~', 'yy') "字符串转数组",
    unnest(ARRAY[1,2]) "数组转多行";

SELECT * FROM unnest(ARRAY[1,2], ARRAY['foo','bar','baz'], ARRAY[2.0, 1.1]); -- 数组转多行,一数组占一列,NULL补位,只能SELECT * FROM方式

# JSON

# 预编译执行计划,仅存活于当前连接期间
PREPARE fooplan (int, text, bool, numeric) AS
    INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
SELECT * FROM pg_prepared_statements;
Creative Commons License

本文基于署名-非商业性使用-相同方式共享 4.0许可协议发布,欢迎转载、使用、重新发布,但请保留文章署名wanghengbin(包含链接:https://wanghengbin.com),不得用于商业目的,基于本文修改后的作品请以相同的许可发布。

发表评论