PostgreSQL Cookbook
- String Functions and Operators
- Lesser Known PostgreSQL Features
- 常用函数
quote_literal
- 避免注入format(formatstr text [, formatarg "any" [, ...] ])
%[position][flags][width]type
- type
- s 简单字符串, null 输出空字符串
- I 作为 SQL 的标识符可能会加双引号, 不允许 null, 等同于
quote_ident
- L 作为 SQL 中的文本对待进行加引号, null 输出 NULL, 等同于
quote_nullable
- type
- 格式化字符串
- 构建 SQL
-- 包含其他表的定义
CREATE TABLE test_new (LIKE test_old INCLUDING ALL);
json 数组去重
select distinct jsonb_array_elements(v)#>>'{}'
from (select '[
"1",
"2"
]'::jsonb union select '[
"1",
"4"
]'::jsonb) t(v);
数据转换
array to rows unset
字符串
select substring('abcdefgh' from n for 2) from generate_series(1, length( 'abcdefgh' ), 2) n;
统计
https://github.com/postgres-plr/plr
https://www.joeconway.com/presentations/oscon-pres-2003-1.pdf PostgreSQL-embedded Statistical Analysis with PL/R PL/R User’s Guide - R Procedural Language http://www.joeconway.com/plr/doc/plr-US.pdf
机器学习
http://madlib.apache.org/ https://wiki.postgresql.org/wiki/Ecosystem:Machine_learning
/usr/local/madlib/bin/madpack -s madlib -p postgres -c [user[/password]@][host][:port][/database] install
MADlib works with Python 2.6 and 2.7. Currently, Python 3.x is not supported.
UUID
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();
CREATE EXTENSION IF NOT EXISTS uuid-ossp;
SELECT uuid_generate_v4();
商业
https://www.cybertec-postgresql.com/
数据迁移
psql source_database -c 'COPY table TO stdout' | psql target_database -c 'COPY table FROM stdin'
CREATE DATABASE new_database TEMPLATE original_database;
INSERT INTO t(a, b, c)
SELECT a, b, c FROM dblink('host=xxx user=xxx password=xxx dbname=xxx', 'SELECT a, b, c FROM t') AS x(a integer, b integer, c integer)
图操作
-- 递归查找自己和所有子级
WITH RECURSIVE children(id) AS (
SELECT *
FROM items
WHERE id = 470569
UNION ALL
SELECT t.*
FROM children s, items t
WHERE t.parent = s.id
) SELECT *
FROM children;
-- 递归查找自己和所有子级并体现层级关系
WITH RECURSIVE children(id) AS (
SELECT
*,
id || '' AS path
FROM items
WHERE id = 470569
UNION ALL
SELECT
t.*,
s.path || '/' || t.id
FROM children s, items t
WHERE t.parent = s.id
) SELECT *
FROM children;
-- 查找所有父级
-- 映射 p 是必要的, 否则会有歧义
WITH RECURSIVE parents(p) AS (
SELECT parent as p,*
FROM items
WHERE id = 471118
UNION ALL
SELECT t.parent as p,t.*
FROM parents s, items t
WHERE t.id = s.p
) SELECT *
FROM parents;