Dada esta tabela chamada prices:

Preciso somar os valores na my_valuecoluna com base na condição que my_separatorestá 1dentro desse grupo (excluindo assim os valores where my_separator = 0). Isso deve retornar os seguintes valores:

• para o período 2012-02-10-2012-02-13=> 100 + 120 + 120 + 110 = 450
• para o período 2012-02-16-2012-02-18=> 130 + 190 + 180 = 500
• para a data 2012-02-20=> 130

Tentei usar funções de janela, mas não consegui encontrar nada válido. Qualquer ajuda?

Eu tenho um dicionário construído desta forma:

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;

que deve incluir não apenas termos genéricos, mas palavras de parada.

Com base nisso, estou esperando algo como:

SELECT to_tsvector('open plan') @@ to_tsquery('off <-> plan');

retornaria falsecomo offnão está no texto. Mas por algum motivo está voltando true.

Se eu executar apenas:

# SELECT to_tsquery('off <-> plan');
 to_tsquery
------------
 'plan'
(1 row)

o que explica por que a expressão anterior está retornando true. Mas por que a consulta removeu o offtermo? Qualquer ideia?

Eu tenho uma função assim:

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;

Quando eu executo com select * from test();o que recebo é:

ERROR: column reference "my_date" is ambiguous
LINE 4: my_date,

A consulta, se não estiver dentro da função, funciona corretamente, mas não consigo fazê-la funcionar dentro dela. Qualquer ideia?

Eu tenho duas tabelas onde a tabela Atem uma coluna de geometria (4326) que contém superfícies (~ 20M). Uma segunda tabela Btem ~ 500K pontos (4326). O que eu quero fazer é "sinalizar" (adicionando uma coluna booleana) cada superfície Aque contém pelo menos um dos pontos em B.

Eu fiz isso:

CREATE TABLE foo_data AS
    SELECT
        A.*,
        (SELECT EXISTS (SELECT ST_Contains(A.geom, B.geom) FROM B)) as flagged
    FROM A;

Mas eu tenho todos os valores "sinalizados" como true(o que é impossível).

Com joins/lateral não fui muito longe.

Qualquer ideia?

Estou tentando despejar alguns dados como json com:

\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

o que estou esperando é um json válido por linha, mas o que recebo é:

"{""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]}"

Existem extras "como primeiro e último caractere e ""ao redor em vez de único ".

Como posso obter um json válido removendo aspas desnecessárias?

Estou executando a seguinte consulta no 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)
);

que falha com um enigmático:

ERROR:  GEOSContains: TopologyException: side location conflict at -2.0889721121124643 53.528652265475735

devido ao Postgis (e bibliotecas usadas relativas).

Existe uma maneira de pular casos excepcionais e manter a consulta em execução até terminar?

Tenho uma tabela assim:

 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

Como posso agrupar esses valores de forma que cada grupo tenha o valor mais próximo possível de 60.000?

Por exemplo (provavelmente não é o melhor ajuste):

Z + J + U + F + A + Y + O + K + I + R -- 59024
S -- 59613
B + H -- 58404
etc.

Tenho uma tabela assim:

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')
        );

Como você pode ver, alguns valores na foo_pricecoluna estão faltando.

O que eu preciso é que os valores ausentes sejam preenchidos com o valor disponível "anterior" desta maneira:

 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

Minha tentativa:

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;

Como você pode ver daqui:

https://www.db-fiddle.com/#&togetherjs=s6giIonUxT

Não preenche completamente a série '100'.

Alguma ideia de como posso obter o resultado desejado?

Dada uma tabela my_datacomo esta:

 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)
);

Para o mesmo id, os valores (se presentes) nas respectivas colunas são sempre os mesmos, então como posso "esmagá-los" para obter:

 id | name | surname | age
----+------+---------+------
 1  | john | smith   | 32

Minha tentativa

A cross join lateralpara cada coluna é minha única ideia até agora, mas duvido que seja boa:

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);

Tenho uma função que aceita como parâmetro um array como:

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;

A matriz de entrada é gerada por outra função foo. Então eu chamo tudo assim:

select my_func(array(select foo()));

onde a função fooé:

CREATE OR REPLACE FUNCTION foo() RETURNS SETOF custom_datum

O problema é que para uma grande quantidade de dados array(select foo())retorna:

ERRO: o tamanho do array excede o máximo permitido (1073741823)

O que estou tentando contornar é a falta de possibilidade de passar funções diferentes, pois no momento, o array de entrada é gerado por diferentes funções:

select my_func(array(select foo()));
select my_func(array(select bar()));
select my_func(array(select ter()));
.... etc

Como posso contornar este problema?

Eu tenho algumas linhas assim:

 avg_income | date_from  | date_to
------------+------------+------------
     1256.9 | 2016-11-21 | 2017-07-10
     4383.2 | 2017-05-06 | 2017-06-01

e gostaria de projetar os avg_incomevalores para os respectivos meses, assim:

 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 

Até agora, acabei de sair com um horrível FOR/LOOP/generate_series(...), então estou querendo saber se há uma maneira mais elegante (e eficiente) de fazer isso?

my_datatable é assim (exemplo de uma linha):

 total_cost | total_exp | ind1 | ind2 | ind3 | flag
------------+-----------+------+------+------+------
 1000       | 2000      | A    | A1   | A2   | F

E eu tenho uma função assim:

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;

O resultado esperado é uma agregação de matriz com as agregações ind1,ind2,ind3renomeadas inde relativas.

O problema com essas funções é que eu tenho que mudar (e executar 3 vezes) select ind1 as ind....group by ind1,import_date.

Existe uma maneira de unificá-lo de uma só vez?

Eu tenho duas tabelas, ambas têm colunas lat/lon no mesmo país (Reino Unido). Os tamanhos das tabelas são aproximadamente 80M e 50M.

Junto com as colunas lat/lon, criei um geo-indexes para ambas as tabelas desta maneira:

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);

Para encontrar os pontos mais próximos da tabela com 80M em relação a 50M em 0,1 milhas, corro algo como:

   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;

A consulta é muito lenta para ser executada (praticamente é cartesiana 50M X 80M).

Existe uma maneira de acelerá-lo?

Além disso, é realmente útil usar a "indexação geográfica do postgis" para um problema como esse? Usar "teorema de Pitágoras" pode ser suficiente (como na resposta escolhida aqui https://stackoverflow.com/questions/1664799/calculating-distance-between-two-points-using-pythagorean-theorem ), pois estou esperando distâncias muito menor que o raio da terra ou pode levar a algum erro?

Dada uma tabela como:

 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);

Preciso inserir em outra tabela valores labelquando não forem NULL ou 0s. Assim o resultado final será:

label | coord | value |
------+-------+-------+
AAA   |   y   |  7    |
AAA   |   z   |  9    |
BBB   |   y   |  10   |
CCC   |   x   |  12   |

Eu tentei com diferente OVER/PARTITION, mas provavelmente está faltando alguma coisa. Qualquer ajuda?

Atualmente tenho esta consulta:

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;

Então se my_tabletem os valores:

property_type | price
--------------+-------
FLAT          | 5000
SEMIDETACHED  | 9000
FLAT          | 6000

a consulta retornará:

Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
11000                     | 9000

Como posso substituir o sumpara acumular os valores em arrays para obter?

Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
{5000, 6000}              | {9000}

Eu tenho uma concatenação de substituições de strings desta maneira:

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;

Atualmente conta com 10 operações diferentes e o número não para de crescer. Também tem o inconveniente de mudar de posição para transformações forçar a renomear variáveis.

Existe uma maneira melhor de gerenciar essa situação?

Eu tenho uma função assim no 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;

Quando carrego com PSQLrecebo o erro:

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

Eu tentei prefixar o p2etc com my_func., mas não funciona. Como posso me referir a um parâmetro de função nesse contexto?

Tenho uma tabela assim:

   |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 |

Como posso ordenar em ordem alfabética cada coluna de forma que os valores NULL sejam colocados na parte inferior, assim:

   |Name1 |Name2 | Name3 | Name4|
   |------+------+-------+------|
   |Julia |Ashley|Jane   |Eve   |
   |Zoe   |Belvet|Mark   |Jenny |
   |NULL  |Maria |NULL   |Ketty |

?

Por que esta função retorna uma tabela vazia (enquanto executa a consulta simples da linha de comando retorna valores diferentes):

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;

Eu até tentei usar executecom ||sem sorte.

Sou novo no Postgres e como estou prestes a começar a escrever cada vez mais código com ele (script com procedimentos, etc.) estou querendo saber qual é o "padrão de fato" (mais usado, maior comunidade, código aberto, etc) estrutura de teste de unidade para ele. Pesquisando encontrei algumas opções. O PgTap é a escolha certa?