tag: SQL基础
概述使用实例1、数组下标平移2、记录的变换3、十六进制和十进制的转换4、空间索引中的间隔（时间、地址）查询5、行变量插入到表中的简短形式6、ALTER TABLE ALTER COLUMN的使用7、schema.name使用quota_ident8、较长前缀的快速搜索9、统计子串的个数10、不同时区下的timestamp计算，连带索引11、使用不可变函数作为优化器的提示12、PostgreSQL中的自治事务13、数组中varchar变量重复赋值的优化14、Use IS DISTINCT FROM without COALESCE (这现在还是问题吗？)15、移植到Oracle, to_date函数的问题16、PostgreSQL dump错误字符的识别和修正17、注意带有NULL的NOT IN18、快速指定某些表字段的第一行19、薪酬最高的雇员20、其它会话变量21、XPATH函数索引22、psql带less23、数组的交集24、SQL语言中的void函数25、下一行的快速提取参考
概述
整理PostgreSQL中一些常见的SQL小技巧，绝大部分来源于网上。有些技巧可能随着PG版本的不断升级和优化，已经不再实用了，所以要查阅的时候，需要自己审慎的判断以后再做决定，最好亲手试一下，看看是不是达到预期的效果。
使用实例
1、数组下标平移
CREATE OR REPLACE FUNCTION shift_idx(anyarray)
RETURNS anyarray AS $$
DECLARE
mi int := array_upper($1,1);
offset int := array_lower($1,1);
BEGIN
RETURN '[0:' || mi - offset || '] = ' || $1::varchar;
END
$$ LANGUAGE plpgsql IMMUTABLE STRICT;
CREATE FUNCTION
postgres=# SELECT shift_idx(array[1,2,3]);
shift_idx
---------------
[0:2]={1,2,3}
(1 row)
2、记录的变换
使用函数而不是继承表来返回记录：
CREATE FUNCTION foo(OUT a int, OUT b int, IN c int)
RETURNS record AS $$
BEGIN
a := c + 1; b := c + 2;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE t(c int);
INSERT INTO t VALUES(10),(20);
postgres=# SELECT c, foo(c) FROM t;
c | foo
----+---------
10 | (11,12)
20 | (21,22)
(2 rows)
postgres=# SELECT c, (foo(c)).* FROM t;
c | a | b
----+----+----
10 | 11 | 12
20 | 21 | 22
(2 rows)
3、十六进制和十进制的转换
(有点老哈，to_hex现在已经是正式函数了)
=# select to_hex(11);
to_hex
--------
b
(1 row)
postgres=# create or replace function to_dec(text)
returns integer as $$
declare r int;
begin
execute E'select x\''||$1|| E'\'::integer' into r;
return r;
end
$$ language plpgsql;
CREATE FUNCTION
postgres=# select to_dec('ff');
to_dec
--------
255
(1 row)
更快速的版本：
CREATE FUNCTION hex2dec(in_hex TEXT)
RETURNS INT
IMMUTABLE STRICT LANGUAGE sql AS $body$
SELECT CAST(CAST(('x' || CAST($1 AS text)) AS bit(8)) AS INT);
$body$;
任意长度的版本：
CREATE OR REPLACE FUNCTION hex2dec (varchar)
RETURNS varchar as
$$
DECLARE
_x numeric;
_count int;
_digit int;
BEGIN
_x := 0;
for _count in 1..length($1) loop
EXECUTE E'SELECT x\''||substring($1 from _count for 1)|| E'\'::integer' INTO _digit;
_x := _x * 16 + _digit ;
end loop;
return _x::varchar;
end
;
$$ language plpgsql immutable
;
4、空间索引中的间隔（时间、地址）查询
注意:搜索间隔通常很慢，因为优化器不使用索引。原因在于开始列和结束列之间的依赖关系。一种解决方案是基于空间索引:它允许处理两个相关的值，就好像它们是单个值一样:
postgres=# EXPLAIN ANALYZE SELECT * FROM testip WHERE 19999999 BETWEEN startip AND endip;
QUERY PLAN
----------------------------------------------------------------
Seq Scan on testip (cost=0.00..19902.00 rows=200814 width=12) (actual time=3.457..434.218 rows=1 loops=1)
Filter: ((19999999 >= startip) AND (19999999 <= endip))
Total runtime: 434.299 ms
(3 rows)
Time: 435,865 ms
postgres=# CREATE INDEX ggg ON testip USING gist ((box(point(startip,startip),point(endip,endip))) box_ops);
CREATE INDEX
Time: 75530,079 ms
postgres=# EXPLAIN ANALYZE
SELECT *
FROM testip
WHERE box(point(startip,startip),point(endip,endip)) @> box(point (19999999,19999999), point(19999999,19999999));
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on testip (cost=60.50..2550.14 rows=1000 width=12) (actual time=0.169..0.172 rows=1 loops=1)
Recheck Cond: (box(point((startip)::double precision, (startip)::double precision), point((endip)::double precision, (endip)::double precision)) @> '(19999999,19999999),(19999999,19999999)'::box)
-> Bitmap Index Scan on ggg (cost=0.00..60.25 rows=1000 width=0) (actual time=0.152..0.152 rows=1 loops=1)
Index Cond: (box(point((startip)::double precision, (startip)::double precision), point((endip)::double precision, (endip)::double precision)) @> '(19999999,19999999),(19999999,19999999)'::box)
Total runtime: 0.285 ms
(5 rows)
Time: 2,805 ms
5、行变量插入到表中的简短形式
postgres=# CREATE TABLE foo(a integer, b integer);
CREATE TABLE
postgres=# CREATE OR REPLACE FUNCTION fx()
RETURNS void as $$
DECLARE r foo;
BEGIN
SELECT INTO r * FROM foo;
INSERT INTO foo VALUES(r.*);
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# SELECT fx();
fx
----
(1 row)
6、ALTER TABLE ALTER COLUMN的使用
我不知道ALTER TABLE ALTER COLUMN中的USING子句。我认为旧的类型必须是可以浇铸到新的类型。但事实并非如此。捷克布尔字面值不能直接转换为PostgreSQL的bool类型，但是，使用USING子句，我可以执行ALTER COLUMN type从varchar转换为bool:
postgres=# CREATE TABLE foo(a varchar);
CREATE TABLE
postgres=# INSERT INTO foo VALUES ('ano'); -- czech yes
INSERT 0 1
postgres=# ALTER TABLE foo ALTER COLUMN a TYPE boolean ;
ERROR: column "a" cannot be cast to type "pg_catalog.bool"
postgres=# ALTER TABLE foo
ALTER COLUMN a TYPE boolean
USING CASE a
WHEN 'ano' THEN true
ELSE false END;
ALTER TABLE
postgres=# SELECT * FROM foo;
a
---
t
(1 row)
7、schema.name使用quota_ident
使用单引号是防止SQL注入的一种保护。quote_ident函数检查它的参数，当参数包含有问题的字符时，它返回用单引号括起来的参数。这是非常简单实用的。问题是schema.name对，因为'是一个被监视的字符:
postgres=# select quote_ident('public.foo');
quote_ident
--------------
"public.foo"
(1 row)
postgres=# select * from "public.foo";
ERROR: relation "public.foo" does not exist
postgres=# select * from public."foo";
a
---
(0 rows)
postgres=# select * from "public"."foo";
a
---
(0 rows)
我们可以造一个函数来处理这种问题：
CREATE OR REPLACE FUNCTION quote_array(text[])
RETURNS text AS $$
SELECT array_to_string(array(SELECT quote_ident($1[i])
FROM generate_series(1, array_upper($1,1)) g(i)),
'.')
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION quote_schema_ident(text)
RETURNS text AS $$
SELECT quote_array(string_to_array($1,'.'))
$$ LANGUAGE SQL IMMUTABLE;
postgres=# select quote_schema_ident('public.foo tab');
quote_schema_ident
--------------------
public."foo tab"
(1 row)
8、较长前缀的快速搜索
搜索最长前缀是一项常见的任务——主要在通信中。我们从前缀集合中搜索某个数字的最长前缀。几年前，我用存储过程和改进的间隔减半方法解决了这个问题。也许稍微慢一点但是Hubert Lubaciewski描述了一个简单健壮的方法
--table contains about 5000 prefixes
postgres=# select * from prefixesx limit 5;
costcode_name | costcode
--------------------+----------
Afghanistan | 93
Afghanistan Mobile | 9370
Afghanistan Mobile | 9379
Afghanistan Mobile | 9380
Alaska (USA) | 1907
(5 rows)
使用like来实现，速度慢点儿：
postgres=# SELECT *
FROM prefixesx
WHERE '420724181000' LIKE costcode || '%'
ORDER BY length(costcode) DESC
LIMIT 1;
costcode_name | costcode
---------------------------------+----------
Czech Republic Mobile - EuroTel | 42072
快速解决方案是从number生成所有可能的前缀并搜索这些前缀:
CREATE OR REPLACE FUNCTION prefixes(varchar)
RETURNS varchar[] AS $$
SELECT ARRAY(SELECT substring($1 FROM 1 FOR i)
FROM generate_series(1, length($1)) g(i))::varchar[];
$$ LANGUAGE sql IMMUTABLE;
postgres=# SELECT *
FROM prefixesx
WHERE costcode = ANY (prefixes('420724191000'))
ORDER BY length(costcode) DESC
LIMIT 1;
costcode_name | costcode
---------------------------------+----------
Czech Republic Mobile - EuroTel | 42072
在5000行时，这个查询比LIKE快4倍。稍微慢一点的查询与LIKE和LIKE firstchar || %
SELECT *
FROM prefixesx
WHERE '420724181000' LIKE costcode || '%'
AND costcode LIKE substring('420724191000' FROM 1 FOR 1) || '%'
ORDER BY length(costcode) DESC
LIMIT 1;
9、统计子串的个数
这个函数是基于流行的技巧-使用替换和长度函数没有循环，这是典型的解释器，内置函数比迭代更快:
CREATE OR REPLACE FUNCTION CountInString(text,text)
RETURNS integer AS $$
SELECT(Length($1) - Length(REPLACE($1, $2, ''))) / Length($2) ;
$$ LANGUAGE SQL IMMUTABLE;
略加改造后的实现 ：
CREATE OR REPLACE FUNCTION CountInString(text, text)
RETURNS integer AS $$
SELECT Array_upper(String_to_array($1,$2),1) - 1;
$$ LANGUAGE SQL IMMUTABLE;
10、不同时区下的timestamp计算，连带索引
在某个时区，最简单的时间规范是使用at time ZONE操作符。我们不能用timezone索引时间戳，因为我们可以更改当前时区，所以我们只能为某个指定时区的时间戳创建索引。来源:pgsql-general, Tom Lane
CREATE INDEX created_tz_paris ON data((created_on AT TIME ZONE 'Europe/Paris'));
11、使用不可变函数作为优化器的提示
对于高度特定的数据分布，我们可能会在预测和生成次优执行计划方面遇到问题。我们可以通过将一个SQL查询分解为更多的依赖查询并逐步求值来解决这些问题。以下查询使用实际结果(而不是估计)先前的查询，并且查询以最佳方式执行。Tom Lane的建议是基于使用在生成执行计划之前进行评估的不可变函数:
CREATE OR REPLACE FUNCTION getTypesLieuFromTheme(codeTheme text)
RETURNS text[] AS $f$
SELECT ARRAY(SELECT codetylieu::text FROM rubtylieu WHERE codeth = $1);
$f$ LANGUAGE SQL IMMUTABLE;
in query
SELECT ...
WHERE ... AND gl.codetylieu = ANY(getTypesLieuFromTheme('RES'))
look execution plan doesn't contain function call and query to table rubtylieu too
-> Bitmap Heap Scan on genrelieu gl (cost=2.23..6.22 rows=88 width=4) (actual time=0.022..0.075 rows=88 loops=1)
Recheck Cond: ((codetylieu)::text = ANY ('{RES}'::text[]))
-> Bitmap Index Scan on ind_genrelieu2 (cost=0.00..2.21 rows=88 width=0) (actual time=0.016..0.016 rows=88 loops=1)
Index Cond: ((codetylieu)::text = ANY ('{RES}'::text[]))
使用这个技巧作为最后一步。测试，请事先增加统计数字。
12、PostgreSQL中的自治事务
postgresql自治事务是一个有用的特性，主要用于日志记录。PostgreSQL还不支持这个特性。对于不可信的语言，我们可以模仿它。你可以看到Jon Roberts的解决方案。
CREATE OR REPLACE FUNCTION fn_log_error(p_function varchar, p_location int, p_error varchar)
RETURNS void AS $$
DECLARE
v_sql varchar;
v_return varchar;
v_error varchar;
BEGIN
PERFORM dblink_connect('connection_name', 'dbname=...');
v_sql := 'INSERT INTO error_log (function_name, location, error_message, error_time) '
|| 'VALUES (''' || p_function_name || ''', '
|| p_location || ', ''' || p_error || ''', clock_timestamp())';
SELECT INTO v_return *
FROM dblink_exec('connection_name', v_sql, false);
--get the error message
SELECT INTO v_error *
FROM dblink_error_message('connection_name');
IF position('ERROR' in v_error) > 0 OR position('WARNING' in v_error) > 0 THEN
RAISE EXCEPTION '%', v_error;
END IF;
PERFORM dblink_disconnect('connection_name');
EXCEPTION
WHEN others THEN
PERFORM dblink_disconnect('connection_name');
RAISE EXCEPTION '(%)', SQLERRM;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
13、数组中varchar变量重复赋值的优化
plpgsql对于密集的非SQL操作来说不是一种好的语言。它最适合作为SQL语句或原型的粘合剂。但有时plperl或plpython可以快得多。Plpgsql不喜欢对varchar或数组变量进行累积迭代。当我们不能使用Perl时，…我们可以使用SQL:
CREATE OR REPLACE FUNCTION SlowList(int) -- slow function, usable for N <= 100
RETURNS varchar AS $$
DECLARE s varchar = '';
BEGIN
FOR i IN 1..$1 LOOP
s := '- '
|| i || ''; -- slow is s := s || ..
END LOOP;
RETURN s;
END; $$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTON FastList(int) -- fast function
RETURNS varchar AS $$
BEGIN
RETURN array_to_string(ARRAY(SELECT '- '
|| i || ''
FROM generate_series(1, $1) g(i)),
'');
END; $$ LANGUAGE plpgsql IMMUTABLE;
对于N < 100，执行时间小于4ms(所以没有任何理由使用肮脏的伎俩)。对于较大的N，执行时间不同(SlowList(10000) - 4000ms, FastList(10000) - 52ms)。作者—— Pavel Stěhule。
14、Use IS DISTINCT FROM without COALESCE (这现在还是问题吗？)
Sim Zacks报告迁移到8.2.4后查询速度很慢。这个版本在预测表达式COALESCE(column, false) = false的结果时存在问题，然后执行计划是次优的。修正后PostgreSQL选择最优执行计划。
SELECT ...
FROM some_tab
LEFT JOIN
some_tab2
ON sometab2.col IS DISTINCT FROM true; --> join false or NULL
所以不要在WHERE子句中使用COALESCE, 下周有人报告了类似的问题:
-- execution plan is suboptimal
((COALESCE((at_type)::integer, 1) = 1) AND (COALESCE(at_language, 0::numeric) = 0::numeric))
-- execution plan is optimal
(at_type = 1 or at_type is null) AND (at_language = 0 or at_language is null)
15、移植到Oracle, to_date函数的问题
Peter Eisentraut报告了PostreSQL和Oracle之间to_date函数的不兼容性。
SQL> select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss') from
dual;
TO_DATE('
---------
31-DEC-07
On PostgreSQL:
select to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss');
to_date
--------------
200700-12-31
Oracle忽略格式字符串中的空格，但PostgreSQL需要精确的格式。这种行为可以在删除空格的自定义函数中更改:
CREATE OR REPLACE FUNCTION fn_to_date(p_date varchar, p_format varchar)
RETURNS timestamp AS $$
SELECT to_timestamp(replace($1, ' ', ''), replace($2, ' ', ''));
$$ LANGUAGE SQL STRICT IMMUTABLE;
# select fn_to_date('31-DEC-200700:00:00', 'dd-mon-yyyy hh24:mi:ss');
fn_to_date
---------------------
2007-12-31 00:00:00
(1 row)
16、PostgreSQL dump错误字符的识别和修正
对于转储中的正确字符，8.2比8.1更严格。原因是SQL注入安全修复(通过不正确的UTF8字符)。这种行为的改变可能会导致迁移问题。解决办法很简单。我们必须找到错误的字符并修复它。
$ iconv -f utf-8 -t utf-8 dump.sql > /dev/null
iconv: illegal input sequence at position 1000
$ head -c 1010 dump.sql | tail -c 20 | od -c
0000000 h . B u t i s n 222 t i t
0000020 h i s ?
0000024
详见： http://lca2007.linux.org.au/att_data/Miniconfs(2f)PostgreSQL/attachments/professional.pdf
评论：这还是个问题吗？
17、注意带有NULL的NOT IN
一些SQL开发人员不喜欢NULL。其他类似的。我也喜欢。NULL是必要的，可以节省很多工作。结果表达式中的NULL明确表示输入数据中存在问题。不喜欢NULL的人不理解它，并使用以下论点，引用“异常”:
postgres=# SELECT *
FROM (VALUES(10),(20),(30)) v(a)
WHERE a IN (10, 20, NULL); --> all is ok
a
----
10
20
(2 rows)
postgres=# SELECT *
FROM (VALUES(10),(20),(30)) v(a)
WHERE a NOT IN (10, 20, NULL);
a
---
(0 rows) --> anomaly, expects 30
但这并非特例。NOT IN操作符检查列表中的每一项，因为左边的值必须不同于所有项。由于与NULL的比较永远不会产生NULL以外的结果，因此没有任何值可以满足这个谓词。
18、快速指定某些表字段的第一行
我的任务很明确。我有地址簿与100,000项，我必须得到姓氏的所有第一个字符。这个任务是几个任务中的一个，其中相关子查询比其他任何任务都要快。因为seq扫描和agg函数需要扫描所有100000条记录，而相关子查询只需要32次访问函数索引:
SELECT *
FROM (
SELECT substring('abcdefghijklmnopqrstuvwxyzěščřžýáíé'
FROM i For 1)
FROM generate_series(1,32) g(i)
) p(onechar)
WHERE EXISTS(
SELECT 1
FROM address_book
WHERE substring(surname from 1 for 1) = p.onechar
and active
);
另一个版本：
SELECT *
FROM
(
SELECT substring('abcdefghijklmnopqrstuvwxyzěščřžýáíéúůňľó' FROM i For 1) FROM generate_series(1,40) g(i) UNION SELECT 'ch'
) p(onechar)
WHERE
EXISTS( SELECT 1 FROM a_osoba WHERE lower(substring(prijmeni from 1 for char_length(p.onechar))) = p.onechar );
19、薪酬最高的雇员
薪酬最高的员工这是典型的学校查询:在某个组中选择薪酬最高的员工。该查询可以通过派生表或相关子查询来解决。我们可以在PostgreSQL中使用非标准子句DISTINCT ON:
postgres=# SELECT * FROM employees ;
name | surname | department | payment
-----------+-----------+------------+-------
Pavel | Stehule | 1 | 10000
Zdenek | Stehule | 1 | 9000
Vladimira | Stehulova | 2 | 9000
(3 rows)
postgres=# SELECT DISTINCT ON department *
FROM employees
ORDER BY department, payment DESC;
name | surname | department | payment
-----------+-----------+------------+-------
Pavel | Stehule | 1 | 10000
Vladimira | Stehulova | 2 | 9000
(2 rows)
注意，DISTINCT ON不可移值。这种解决方案的一大缺点是，它只显示了来自每个部门的一个人，即使有更多的员工获得相同的最高薪酬，也会产生不完整的结果。
20、其它会话变量
PostgreSQL不支持服务器会话变量。这意味着，我们必须更频繁地编写存储过程，因为有变量。一些替换是模块变量。这些变量必须在配置中被允许。Andreas Kretschmer发送:
- define in your postgresql.conf:
custom_variable_classes = 'myvar'
- use within psql:
test=# set myvar.benutzer = 'foo';
SET
test=*# select * from foo;
id | name
----+------
1 | foo
2 | bar
(2 rows)
test=*# select * from foo where name=current_setting('myvar.benutzer');
id | name
----+------
1 | foo
(1 row)
test=*# set myvar.benutzer = 'none';
SET
test=*# select * from foo where name=current_setting('myvar.benutzer');
id | name
----+------
(0 rows)
21、XPATH函数索引
8.3集成了xpath功能。在XML支持方面存在差距，因为GIST或GIN索引不支持XML类型。xpath函数返回xml值数组。但是我们可以将自定义类型转换写为int array:
CREATE OR REPLACE FUNCTION xml_list_to_int(xml[])
RETURNS int[] AS $$
SELECT ARRAY(SELECT to_number(($1[i])::text,'999999.99')::int
FROM generate_series(1, array_upper($1,1)) g(i))
$$ LANGUAGE SQL IMMUTABLE;
CREATE CAST (xml[] AS int[]) WITH FUNCTION xml_list_to_int(xml[]);
-- array of integers are supported with GIST
CREATE INDEX fx ON foo USING
GIN((xpath('//id/text()',order_in_xml)::int[]));
22、psql带less
PSQL支持分页，但它无法与less提供的选项相比。下面是关于如何设置psql以使用less作为分页器的示例。作者:梅林·蒙居尔
#to profile
export PAGER=less
export LESS="-iMSx4 -FX"
#to .psqlrc
\timing
\pset pager always
23、数组的交集
CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT $1[i] AS "the_intersection"
FROM generate_series(
array_lower($1,1),
array_upper($1,1)
) AS i
INTERSECT
SELECT $2[j] AS "the_intersection"
FROM generate_series(
array_lower($2,1),
array_upper($2,1)
) AS j
);
$$;
另一个更快速的版本：
CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(SELECT UNNEST($1) INTERSECT SELECT UNNEST($2))
$$;
24、SQL语言中的void函数
SQL函数只能包含SQL语句。最后一条语句的结果就是函数的结果。结果的类型必须与函数的类型兼容。因为VOID是有效类型，所以任何语句都不能只返回一个异常。我们可以将NULL转换为VOID。
注意:不允许通过二进制协议使用void返回函数
CREATE OR REPLACE FUNCTION none()
RETURNS VOID AS $$ SELECT NULL::VOID; $$
LANGUAGE SQL;
25、下一行的快速提取
对于旧的应用程序(ISAM应用程序在一系列行上工作)，我们已经能够用一些值的组合(a1, b1, c1)搜索指定的下一行。传统的解决方案:
SELECT *
FROM data
WHERE (a > a1)
OR (a = a1 AND b > b1)
OR (a = a1 AND b = b1 AND c > c1)
ORDER BY a, b, c
LIMIT 1;
Merlin Moncure 注意到简化形式使用行方程。这个表单更具可读性，并且允许使用多列索引:
SELECT *
FROM data
WHERE (a, b, c) > (a1, b1, c1)
ORDER BY a, b, c
LIMIT 1;
ISAM访问降低SQL服务器性能。为了获得有效的SQL，我们必须对集合进行操作，但是当我们不能重写应用程序时，我们别无选择。
参考
[1] https://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II
:https://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II