AndreKR's questions

Para cada linha do lado direito em um JOIN, gostaria de criar um número sequencial, começando em 1 (ou 0) para cada linha do lado esquerdo.

Exemplo:

create table persons (person_id int, person_name text);
create table places (place_id int, person_id int, place_name text);
insert into persons values (10, 'Aulus Agerius'), (20, 'Numerius Negidius');
insert into places values (10, 10, 'Anytown'), (20, 10, 'Timbuktu'), (30, 20, 'Podunk');

select person_name, place_name
from persons join places using (person_id)
order by person_id, place_id;

violino

Resultado desejado:

Então, por exemplo, um 2 in place_seqsignificaria "este é o 2º lugar encontrado para esta pessoa".

Como criar a place_seqcoluna?

Aqui estão alguns dados para brincar:

CREATE TABLE a (
    a_id   int  NOT NULL,
    a_prop text NOT NULL
);

CREATE TABLE b (
    b_id   int  NOT NULL,
    b_prop text NOT NULL
);

INSERT INTO a VALUES (1, 'blah'), (2, 'blah'), (4, 'not this one');
INSERT INTO b VALUES (1, 'blah'), (3, 'blah'), (5, 'not this one');

Agora eu gostaria de escrever uma consulta que retorna:

Uma possibilidade é:

SELECT *
FROM a
FULL OUTER JOIN b ON a_id = b_id
WHERE (a_prop = 'blah' OR a_prop IS NULL)
AND (b_prop = 'blah' OR b_prop IS NULL);

Isso exige que eu escreva OR ... IS NULLpara todos os campos nos quais tenho uma condição. Isso se torna ainda mais detalhado se algumas condições forem intervalos de datas e similares.

Se esta fosse uma junção à esquerda:

SELECT *
FROM a
LEFT JOIN b ON a_id = b_id
WHERE a_prop = 'blah'
AND (b_prop = 'blah' OR b_prop IS NULL);

Eu poderia mover a condição para a ONcláusula para evitar isso:

SELECT *
FROM a
LEFT JOIN b ON a_id = b_id AND b_prop = 'blah'
WHERE a_prop = 'blah';

Existe uma maneira de fazer isso com a junção externa completa também?

violino

Suponha que eu tenha uma tabela com imagens que devem passar por várias etapas:

CREATE TABLE images (filename text, extracted bool, cropped bool, resized bool);
    
INSERT INTO images (filename, extracted, cropped, resized)
VALUES
    ('foo', false, false, false),
    ('bar', true, false, false),
    ('baz', true, true, false),
    ('qux', true, true, true);

Em algum momento, tenho uma consulta para encontrar todas as imagens cortadas, mas que ainda precisam ser redimensionadas:

SELECT count(*) FROM images WHERE cropped AND NOT resized;

Agora acredito que a melhor maneira de tornar essa consulta rápida é um índice parcial:

CREATE INDEX ON images (cropped, resized) WHERE (cropped AND NOT resized);

Eu o tornaria parcial porque cropped AND NOT resizedé um estado relativamente raro, embora possa haver milhões de imagens que já estão totalmente processadas e também milhões que ainda não foram cortadas.

Minha pergunta agora é, preciso de estatísticas além do índice?

Um desses?

CREATE STATISTICS stat1 (dependencies) ON cropped, resized FROM images;
CREATE STATISTICS stat2 (ndistinct) ON cropped, resized FROM images;
CREATE STATISTICS stat3 (mcv) ON cropped, resized FROM images;
ANALYZE images;

Encontrei o capítulo Como o planejador usa estatísticas que eu havia perdido anteriormente (ou melhor, misturado com Estatísticas usadas pelo planejador ), mas ele fala apenas sobre como as estatísticas são transformadas em estimativas de linha. O que não está claro para mim é como os índices são escolhidos, uma vez que aparentemente não há estatísticas sobre os índices.

Eu estava consultando assim:

SELECT count(*)
FROM orders
WHERE planned_shipping_date >= '2022-04-04'
AND planned_shipping_date < '2022-04-05'

Então me deparei com esta resposta e, como em consultas mais complicadas, tornou a consulta mais fácil de ler, reescrevi a consulta assim:

SELECT count(*)
FROM orders
WHERE planned_shipping_date <@ daterange('2022-04-04', '2022-04-05')

Acredito que sejam semanticamente idênticos, mas vejam os planos:

Aggregate  (cost=76.91..76.92 rows=1 width=8) (actual time=1.066..1.068 rows=1 loops=1)
  ->  Index Only Scan using orders_planned_shipping_date_idx on orders  (cost=0.29..69.73 rows=2872 width=0) (actual time=0.067..0.646 rows=2813 loops=1)
        Index Cond: ((planned_shipping_date >= '2022-04-04'::date) AND (planned_shipping_date < '2022-04-05'::date))
        Heap Fetches: 0

Aggregate  (cost=2753.57..2753.58 rows=1 width=8) (actual time=18.309..18.311 rows=1 loops=1)
  ->  Index Only Scan using orders_planned_shipping_date_idx on orders  (cost=0.29..2751.93 rows=655 width=0) (actual time=17.520..18.132 rows=2813 loops=1)
        Filter: (planned_shipping_date <@ '[2022-04-04,2022-04-05)'::daterange)
        Rows Removed by Filter: 128138
        Heap Fetches: 0

O uso do intervalo de datas parece impedir o uso de um índice.

Preciso de um índice diferente ou não devo usar intervalos de datas como este?

Se eu tiver uma coluna de texto namecom um agrupamento, por exemplo tr-TR, e executar uma consulta

SELECT * FROM t WHERE name LIKE 'a%'

então isso fará uma varredura seq.

Se eu agora criar um índice

CREATE INDEX ON t(name text_pattern_ops)

a consulta acima se tornará uma varredura de bitmap. Mas há outra técnica que alcançará o mesmo resultado:

CREATE INDEX ON t(name COLLATE "C")

Essas abordagens são completamente equivalentes ou existem diferenças?

Eu gostaria de adicionar (ou melhor, subtrair) um número de dias de um timestamp.

Por exemplo de 2020-04-13 00:00:00 Europe/Berlinquero subtrair 20 dias e espero que o resultado seja 2020-03-24 00:00:00 Europe/Berlin.

Se eu correr

select ('2020-04-13 00:00:00 Europe/Berlin'::timestamptz - INTERVAL '20 DAY') AT TIME ZONE 'Europe/Berlin'

eu consigo 2020-03-23 23:00:00.000000.

Isso parece ser porque o PostgreSQL subtrai 20 * 24 horas (uma mudança de horário de verão ocorre durante esse intervalo de datas), o que é diferente de subtrair 20 dias .

Eu gostaria de configurar uma restrição CHECK que garante que text[]os elementos de uma coluna contenham apenas determinados valores.

Configurando um exemplo:

CREATE TABLE foo(
  countries text[]
);

INSERT INTO foo VALUES ('{"Morocco", "Mali", "Indonesia"}');
INSERT INTO foo VALUES ('{"Sokovia", "Mali"}');

Agora apenas "Marrocos", "Mali" e "Indonésia" devem ser permitidos, então a segunda linha deve ser rejeitada pela restrição.

Eu tenho uma solução "funcionando":

CHECK (array_length(
  array_remove(
    array_remove(
      array_remove(
        countries,
        'Indonesia'
      ), 'Mali'
    ), 'Morocco'
  ), 1) IS NULL)

Mas isso não é muito legível.

Eu tentei isso também:

CHECK ((
  SELECT unnest(countries)
  EXCEPT
  SELECT unnest(array['Morocco', 'Mali', 'Indonesia'])
) IS NULL)

mas:

ERRO não pode usar a subconsulta na restrição de verificação

Estou usando to_regclass()para verificar se existe uma tabela (a partir daqui ). Isso funciona para nomes de tabelas em minúsculas, mas para nomes de tabelas com letras maiúsculas sempre retorna NULL, mesmo quando a tabela existe:

CREATE TABLE "asdf" (s text);
CREATE TABLE "FooBar" (s text);

SELECT to_regclass('asdf'); --     -> "asdf"
SELECT to_regclass('FooBar'); --   -> NULL
SELECT to_regclass('foobar'); --   -> NULL

violino

Aqui está parte de uma EXPLAIN ANALYZE do Postgres 9.6:

->  Bitmap Heap Scan on cities  (cost=90.05..806.49 rows=265 width=4) (actual time=4.733..45.772 rows=17 loops=1)
       Recheck Cond: (regexp_replace(regexp_replace(replace(replace(replace(replace(lower(name), 'ä'::text, 'ae'::text), 'ö'::text, 'oe'::text), 'ü'::text, 'ue'::text), 'ß'::text, 'ss'::text), 'strasse\M'::text, 'strasse'::text, 'g'::text), '\W'::text, ''::text, 'g'::text) % 'coerde'::text)
      Rows Removed by Index Recheck: 567
      Heap Blocks: exact=497
      ->  Bitmap Index Scan on city_lookup_index  (cost=0.00..89.98 rows=265 width=0) (actual time=4.229..4.229 rows=584 loops=1)
             Index Cond: (regexp_replace(regexp_replace(replace(replace(replace(replace(lower(name), 'ä'::text, 'ae'::text), 'ö'::text, 'oe'::text), 'ü'::text, 'ue'::text), 'ß'::text, 'ss'::text), 'strasse\M'::text, 'strasse'::text, 'g'::text), '\W'::text, ''::text, 'g'::text) % 'coerde'::text)

O que Rows Removed by Index Rechecksignifica?

Ou em outras palavras: o bitmap não apresenta perdas (apenas exactpáginas), então por que ele contém ponteiros de tupla que foram (presumivelmente) removidos pela nova verificação?

Existem muitas quase duplicatas para esta pergunta e todas que encontrei até agora estavam erradas e/ou incompletas, então sinto que precisamos de uma resposta canônica.

No PostgreSQL, como posso conceder todas as permissões em um banco de dados a um usuário, sem dar a ele permissões de SUPERUSER?

Isso inclui todas as tabelas , sequências , tipos , gatilhos , índices , procedimentos e quaisquer outros objetos que estejam atualmente no banco de dados e quaisquer objetos que sejam criados no banco de dados posteriormente, por este usuário ou outros. Claro, também inclui a capacidade de criar objetos no banco de dados.

O que eu entendo:

Além das referências válidas à tabela referenciada, uma coluna com uma chave estrangeira também pode conter um valor NULL. O padrão SQL define vários modos de correspondência para chaves estrangeiras, como MATCH SIMPLE e MATCH FULL. Para chaves estrangeiras de várias colunas, MATCH SIMPLE permite um valor errado (= sem referência) em qualquer uma das colunas, desde que pelo menos uma coluna da chave estrangeira contenha um valor NULL.

As chaves estrangeiras de várias colunas são raras (porque as chaves primárias de várias colunas são), mas posso imaginar casos de uso válidos. Por exemplo, uma tradução de uma postagem de blog pode ter uma chave primária (post_id, language_id). Agora, algo que faça referência a tal tradução (por exemplo, qual tradução um usuário está editando no momento) teria uma chave estrangeira de várias colunas.

O que não entendo:

Por que eu usaria MATCH SIMPLE para minha chave estrangeira?

No exemplo acima, não faz sentido ter uma entrada "atualmente editando" que apenas faz referência a post_ide não a language_id.

Qual seria um exemplo em que faz sentido?