鉴于此表名为prices：
| 我的约会 | 我的值 | 我的分隔符 |
|---|---|---|
| 2012-02-10 | 100 | 1 |
| 2012-02-11 | 120 | 1 |
| 2012-02-12 | 120 | 1 |
| 2012-02-13 | 110 | 1 |
| 2012-02-14 | 100 | 0 |
| 2012-02-15 | 115 | 0 |
| 2012-02-16 | 130 | 1 |
| 2012-02-17 | 190 | 1 |
| 2012-02-18 | 180 | 1 |
| 2012-02-19 | 150 | 0 |
| 2012-02-20 | 130 | 1 |
我需要my_value根据该组内的条件对列my_separator中的值求和1（因此排除其中的值my_separator = 0）。这应该返回以下值：
2012-02-10-2012-02-13=> 100 + 120 + 120 + 110 = 4502012-02-16-2012-02-18=> 130 + 190 + 180 = 5002012-02-20=> 130我尝试使用窗口函数，但没有找到任何有效的方法。有什么帮助吗？
我有一本以这种方式建造的字典：
DROP TEXT SEARCH DICTIONARY IF EXISTS simple_english_with_stopwords CASCADE;
CREATE TEXT SEARCH DICTIONARY simple_english_with_stopwords (TEMPLATE = pg_catalog.simple);
CREATE TEXT SEARCH CONFIGURATION simple_english_with_stopwords (copy = english);
ALTER TEXT SEARCH CONFIGURATION simple_english_with_stopwords
ALTER MAPPING FOR asciihword, asciiword, hword, hword_asciipart, hword_part, word
WITH simple_english_with_stopwords;
其中不仅应包括通用术语，还应包括停用词。
基于此，我期待类似的东西：
SELECT to_tsvector('open plan') @@ to_tsquery('off <-> plan');
会false像off文本中没有的那样返回。但由于某种原因它又回来了true。
如果我只跑：
# SELECT to_tsquery('off <-> plan');
to_tsquery
------------
'plan'
(1 row)
这解释了为什么前面的表达式返回 true。但是为什么查询删除了这个off词呢？任何想法？
我有这样的功能：
CREATE OR REPLACE FUNCTION test() RETURNS
TABLE(my_date date, gates smallint, area_type character(1),
area_sizes float[], area_canvases float[]) AS $$
BEGIN
return query
select coalesce(s.my_date, r.my_date) as my_date, coalesce(s.gates, r.gates) as gates, coalesce(s.area_type, r.area_type) as area_type, s.area_sizes, r.area_canvases
from (select my_date, gates, area_type, area_sizes from table1 where area_type = 'X') s
full join (select my_date, gates, area_type, area_canvases from table2 where area_type = 'Y') r
on r.my_date = s.my_date;
END $$ language plpgsql;
当我运行它时select * from test();，我得到的是：
ERROR: column reference "my_date" is ambiguous
LINE 4: my_date,
如果未包含在函数中的查询可以正常工作，但我无法使其在其中工作。任何想法？
我有两个表，其中表A有一个包含曲面 (~20M) 的几何 (4326) 列。第二张表B有 ~ 500K 点 (4326)。我想要做的是“标记”（添加一个布尔列）A其中至少包含一个点的每个表面B。
我这样做了：
CREATE TABLE foo_data AS
SELECT
A.*,
(SELECT EXISTS (SELECT ST_Contains(A.geom, B.geom) FROM B)) as flagged
FROM A;
但是我得到了所有“标记”值true（这是不可能的）。
对于连接/横向，我并没有走得太远。
任何想法？
我正在尝试将一些数据转储为 json：
\copy (SELECT json_build_object('areaSqft',area_sqft, 'titleNos',title_nos, 'buildingIds',building_ids, 'geometry',geom_json) FROM my_data) to my_data.csv with delimiter ',' csv header
我期望的是每行有效的 json，但我得到的是：
"{""areaSqft"": 214.394254595041, ""geometry"": {""type"": ""MultiPolygon"", ""coordinates"": [[[[0.000015, 51.449107], [0.000154, 51.441108], [0.000238, 51.44111], [0.00024, 51.441052], [0.000137, 51.441051], [0.000041, 51.441049], [0.000015, 51.441107]]]]}, ""titleNos"": [""ZB78669""], ""buildingIds"": [7521141, 9530393, 7530394]}"
有额外"的第一个和最后一个字符以及""周围而不是 single "。
如何获得有效的 json 去除不必要的引号？
我在 Postgres 10.3/Postgis 2.4 上运行以下查询：
create table table_c as (
select A.*, B.area_name, B.id
from table_a A
left join table_b B
on ST_Contains(A.geom, B.geom)
);
以神秘的方式失败：
ERROR: GEOSContains: TopologyException: side location conflict at -2.0889721121124643 53.528652265475735
由于 Postgis（和相关使用的库）。
有没有办法跳过异常情况并保持查询运行直到完成？
我有一张这样的桌子：
v | c
---+-------
Z | 217
J | 620
U | 1891
F | 3751
A | 5673
Y | 5859
O | 7347
K | 9827
I | 11842
R | 11997
H | 14818
M | 18321
G | 18445
E | 19220
D | 22444
W | 22692
T | 24428
P | 26257
N | 35247
L | 41416
C | 42594
B | 43586
S | 59613
我如何以一种方式对这些值进行分组，使每个组都具有最接近 60000 的可能值？
例如（可能不是最合适的）：
Z + J + U + F + A + Y + O + K + I + R -- 59024
S -- 59613
B + H -- 58404
etc.
我有一张这样的桌子：
create table foo (foo_label text, foo_price int, foo_date date);
insert into foo (
values
('aaa', 100, '2017-01-01'),
('aaa', NULL, '2017-02-01'),
('aaa', NULL, '2017-03-01'),
('aaa', NULL, '2017-04-01'),
('aaa', 140, '2017-05-01'),
('aaa', NULL, '2017-06-01'),
('aaa', 180, '2017-07-01')
);
如您所见，该foo_price列上缺少一些值。
我需要的是缺失值以这种方式填充“以前的”可用值：
foo_label | fixed_foo_price | foo_date
-----------+-----------------+------------
aaa | 100 | 2017-01-01
aaa | 100 | 2017-02-01
aaa | 100 | 2017-03-01
aaa | 100 | 2017-04-01
aaa | 140 | 2017-05-01
aaa | 140 | 2017-06-01
aaa | 180 | 2017-07-01
我的尝试：
select
foo_label,
(case when foo_price is null then previous_foo_price else foo_price end) as fixed_foo_price,
foo_date
from (
select
foo_label,
lag(foo_price) OVER (PARTITION BY foo_label order by foo_date::date) as previous_foo_price,
foo_price,
foo_date
from foo
) T;
从这里可以看出：
https://www.db-fiddle.com/#&togetherjs=s6giIonUxT
它并没有完全填满“100”系列。
知道如何获得想要的结果吗？
给定一个这样的表my_data：
id | name | surname | age
----+------+---------+------
1 | john | smith | NULL
1 | NULL | smith | 32
1 | NULL | NULL | NULL
1 | john | smith | NULL
1 | john | NULL | 32
CREATE TABLE my_data(id,name,surname,age)
AS ( VALUES
(1::int, 'john', 'smith' ,NULL::int),
(1, NULL, 'smith' ,32),
(1, NULL, NULL ,NULL),
(1, 'john', 'smith' ,NULL),
(1, 'john', NULL ,32)
);
对于相同id的，各个列中的值（如果存在）始终相同，因此我如何“压缩”它们以获得：
id | name | surname | age
----+------+---------+------
1 | john | smith | 32
A cross join lateralfor each column 是我目前唯一的想法，但我怀疑它是否好：
select
distinct column1, c2.value, c3.value, c4.value
from my_data md
cross join lateral (select column2 from my_data where column1 = md.column1 and column2 is not null limit 1) as c2(value)
cross join lateral (select column3 from my_data where column1 = md.column1 and column3 is not null limit 1) as c3(value)
cross join lateral (select column4 from my_data where column1 = md.column1 and column4 is not null limit 1) as c4(value);
我有一个函数接受一个数组作为参数，例如：
CREATE OR REPLACE FUNCTION my_func(some_data custom_datum[]) RETURNS VOID AS $$
BEGIN
create table foo_table as (
select
coalesce(foo_ind, bar_ind, ter_ind) as foobarter,
import_date,
-- do some stuff here
from unnest (some_data) as T
group by grouping sets ((foo_ind, import_date), (bar_ind, import_date), (ter_ind, import_date))
);
END
$$ LANGUAGE plpgsql;
输入数组由另一个函数生成foo。所以我这样称呼一切：
select my_func(array(select foo()));
功能foo是：
CREATE OR REPLACE FUNCTION foo() RETURNS SETOF custom_datum
问题是对于大量数据array(select foo())返回：
错误：数组大小超过允许的最大值 (1073741823)
我要解决的问题是无法传递不同的函数，因为目前，输入数组是由不同的函数生成的：
select my_func(array(select foo()));
select my_func(array(select bar()));
select my_func(array(select ter()));
.... etc
我该如何解决这个问题？
我有一些这样的行：
avg_income | date_from | date_to
------------+------------+------------
1256.9 | 2016-11-21 | 2017-07-10
4383.2 | 2017-05-06 | 2017-06-01
我想将这些avg_income值投影到相应的月份，如下所示：
avg_income | month
------------+------------
1256.9 | 2016-11-01
1256.9 | 2016-12-01
1256.9 | 2017-01-01
1256.9 | 2017-02-01
1256.9 | 2017-03-01
1256.9 | 2017-04-01
1256.9 | 2017-05-01
1256.9 | 2017-06-01
1256.9 | 2017-07-01
4383.2 | 2017-05-01
4383.2 | 2017-06-01
到目前为止，我只是想出了一个糟糕的FOR/LOOP/generate_series(...)结果，所以我想知道是否有更优雅（和有效）的方法来做到这一点？
my_data表格是这样的（一行示例）：
total_cost | total_exp | ind1 | ind2 | ind3 | flag
------------+-----------+------+------+------+------
1000 | 2000 | A | A1 | A2 | F
我有这样的功能：
CREATE OR REPLACE FUNCTION create_my_aggregation() RETURNS VOID AS $$
DECLARE
import_date date;
BEGIN
FOR import_date IN
SELECT generate_series('2005-01-01'::date, '2015-04-01'::date, '1 month'::Interval)
LOOP
insert into my_timeseries
select
-- ind1 as ind,
-- ind2 as ind,
ind3 as ind,
import_date,
-- size
array_agg(total_cost) filter(where flag = 'F' and total_exp > 0) as f_cost_a,
array_agg(total_cost) filter(where flag = 'S' and total_exp > 50) as s_cost_a,
array_agg(total_cost) filter(where flag = 'D' and total_exp > 100) as d_cost_a,
array_agg(total_cost) filter(where flag = 'T' and total_exp > 20) as t_cost_a,
from my_data
where date_trunc('month', date_of_transfer) = date_trunc('month', import_date)
-- group by ind1, import_date;
-- group by ind2, import_date;
group by ind3, import_date;
END LOOP;
END
$$ LANGUAGE plpgsql;
ind1,ind2,ind3预期结果是具有重命名为ind和相对聚合的数组聚合。
该功能的问题是我必须切换（并运行 3 次）select ind1 as ind....group by ind1,import_date。
有没有办法一次性统一它？
我有两张桌子，在同一个国家（英国）都有纬度/经度列。表大小大约为 80M 和 50M。
除了纬度/经度列，我以这种方式为两个表创建了地理索引：
SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2);
UPDATE my_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);
为了在 0.1 英里内找到 80M 相对于 50M 的表中最近的点，我运行如下：
SELECT A.latitude, A.longitude, B.latitude, B.longitude,
FROM my_table_50 AS A, my_table_80 AS B
where ST_Distance(A.geom, B.geom) < 0.1609 -- 1 mile / 10
ORDER BY ST_Distance(A.geom, B.geom) ASC LIMIT 1;
查询运行速度很慢（几乎是笛卡尔 50M X 80M）。
有没有办法加快速度？
另外，对这样的问题使用“postgis 地理索引”真的有用吗？使用“毕达哥拉斯定理”就足够了（就像在这里选择的答案https://stackoverflow.com/questions/1664799/calculating-distance-between-two-points-using-pythagorean-theorem一样），因为我期待距离的方式比地球半径短得多，否则可能会导致一些错误？
给定一个表：
label | x | y | z
-------+-------+-------+------
AAA | NULL | 7 | 9
BBB | 0 | 10 | 0
CCC | 12 | NULL | 0
create table coords (label text, x integer, y integer, z integer);
insert into coords(label,x,y,z)
values
('AAA',NULL,7,9),
('BBB',0,10,0),
('CCC',12,NULL,0);
我需要label在它们不是 NULL 或 0 时插入另一个表值。所以最后的结果会是：
label | coord | value |
------+-------+-------+
AAA | y | 7 |
AAA | z | 9 |
BBB | y | 10 |
CCC | x | 12 |
我尝试了不同的方法OVER/PARTITION，但可能我遗漏了一些东西。有什么帮助吗？
目前我有这个查询：
select
sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price,
sum(case when my_table.property_type_mapped = 'SEMIDETACHED' then my_table.price else 0 end) as Semidetached_Current_Asking_Price
from
my_table;
所以如果my_table有值：
property_type | price
--------------+-------
FLAT | 5000
SEMIDETACHED | 9000
FLAT | 6000
查询将返回：
Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
11000 | 9000
如何替换sum以将值累积到数组中以获取？
Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
{5000, 6000} | {9000}
我以这种方式连接了字符串替换：
CREATE OR REPLACE FUNCTION norm(t text) RETURNS text AS $$
declare t1 text;
declare t2 text;
...
declare t10 text;
BEGIN
select replace (lower(t), ',', ' ') into t1;
select regexp_replace (t1, '[^0-9a-z\s-]', '', 'g') into t2;
...
select regexp_replace (t10, 'n?oise?.*', '', 'g') into t10;
RETURN trim(t10);
END;
$$ LANGUAGE plpgsql;
目前它有 10 个不同的操作，并且这个数字还在不断增长。此外，将位置更改为转换强制重命名变量也很不方便。
有没有更好的方法来处理这种情况？
我在 Postgres 9.5 中有这样的功能：
CREATE OR REPLACE FUNCTION my_func (p1 date, p2 integer, p3 integer, p4 integer) RETURNS
SETOF some_datum AS $func$
BEGIN
RETURN QUERY
with t as (
select p4, postcode, p1, rent * 12 * 100 / nullif(price, 0) as result
FROM crosstab (
$$
select postcode::text, indicator::int, value::float
from my_data
where (indicator = p2 or indicator = p3) and date = p1
order by 1,2
$$)
AS ct (
"postcode" text,
"price" float,
"rent" float)
)
select * from t where result is not null;
END
$func$ LANGUAGE plpgsql;
当我加载它时，PSQL我得到了错误：
ERROR: column "p2" does not exist
LINE 4: where (indicator = p2 ...
^
QUERY:
select postcode::text, indicator::int, value::float
from my_data
where (indicator = p2 or indicator = p3) and date = p1
order by 1,2
我试图在p2etc 前加上前缀，my_func.但它不起作用。我如何在该上下文中引用函数参数？
我有一张这样的桌子：
|Name1 |Name2 | Name3 | Name4|
|------+------+-------+------|
|Zoe |NULL |NULL |NULL |
|Julia |NULL |NULL |NULL |
|NULL |Ashley|NULL |NULL |
|NULL |Belvet|NULL |NULL |
|NULL |Maria |NULL |NULL |
|NULL |NULL |Mark |NULL |
|NULL |NULL |Jane |NULL |
|NULL |NULL |NULL |Eve |
|NULL |NULL |NULL |Jenny |
|NULL |NULL |NULL |Ketty |
我如何以将 NULL 值推到底部的方式按字母顺序排列每一列，如下所示：
|Name1 |Name2 | Name3 | Name4|
|------+------+-------+------|
|Julia |Ashley|Jane |Eve |
|Zoe |Belvet|Mark |Jenny |
|NULL |Maria |NULL |Ketty |
？
为什么这个函数返回一个空表（从命令行运行普通查询返回不同的值）：
CREATE OR REPLACE FUNCTION get_area_values (_tbl regclass, _column text, indicator_id integer) RETURNS
TABLE (indicator integer, postcode text, date date, value double precision) AS $$
BEGIN
execute format(
'WITH ad AS (select distinct area, district from postcode_lookup)'
|| ' select $1 as indicator, ad.area as postcode, t.date, sum(t.%I) as value'
|| ' from %I as t'
|| ' join ad on t.postcode = ad.district group by ad.area, t.date', _column, _tbl)
USING indicator_id;
END
$$ LANGUAGE plpgsql;
我什至尝试使用executewith ，||但没有任何运气。
我是 Postgres 的新手，因为我即将开始用它编写越来越多的代码（带有过程的脚本等），我想知道什么是“事实上的标准”（更多使用，更大的社区，开源等）它的单元测试框架。谷歌搜索我找到了一些选择。PgTap 是正确的选择吗？