user4150760's questions

Diga que eu tenho uma restrição

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

que precisa ser modificado para que o comprimento do CEP seja 6.

Eu tenho que largar a restrição zipchkprimeiro e depois recriá-la?

ALTER TABLE distributors DROP CONSTRAINT zipchk;
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 6);

ou existe um MODIFYcomando (like):

ALTER TABLE distributors MODIFY CONSTRAINT zipchk CHECK (char_length(zipcode) = 6);

Eu tenho uma tabela scoresonde cada entrada corresponde a um (student, subject)par específico.

CREATE TABLE score (
  id serial PRIMARY KEY,
  value integer NOT NULL,
  subject_id integer NOT NULL,
  student_id integer NOT NULL,
  CONSTRAINT s1_id FOREIGN KEY (subject_id) REFERENCES subject (id),
  CONSTRAINT s2_id FOREIGN KEY (student_id) REFERENCES student (id),
);

Quero escolher as 5 principais disciplinas com as pontuações gerais mais altas e, em seguida, calcular a pontuação média de cada aluno nessas 5 disciplinas. Alguns alunos podem não ter inscrições para algumas disciplinas. Esses valores receberiam uma pontuação padrão.

Aqui está o que eu tenho:

SELECT student_id, AVG(COALESCE(score.value, default_value)) FROM 
(
    SELECT score.subject_id, subject.name, SUM(score.value) AS score_sum
    FROM score
    JOIN subject on subject.id = score.subject_id
    WHERE subject.name != 'skip me'
    GROUP BY score.subject_id
    ORDER BY score_sum DESC
    LIMIT 5
) AS score_sort
LEFT JOIN score ON score_sort.subject_id = score.subject_id 
GROUP BY student_id

A consulta interna funciona corretamente para selecionar os 5 primeiros. Mas a LEFT JOINconsulta externa não seleciona as linhas em que um aluno não possui uma pontuação. O que eu estou fazendo errado aqui ?

Tenho uma tabela em um DB Postgres onde col1e col2são chaves estrangeiras referentes a uma mesma coluna. Eu quero apenas combinações únicas de (col1, col2), ou seja, se (1,2)for inserido, (2,1)deve ser rejeitado. Como posso conseguir isso?

Minha definição de tabela:

CREATE TABLE mytable (
    id serial primary key,
    col1 int NOT NULL,
    col2 int NOT NULL,
    unique (col1, col2)
)

Tenho uma tabela de alunos e uma tabela de notas em várias disciplinas. Quero somar a pontuação1 e a pontuação2 de cada aluno em todas as disciplinas e, em seguida, adicionar o bônus para cada aluno.

CREATE TABLE student (
   id serial PRIMARY KEY,
   name text NOT NULL,
   bonus integer NOT NULL,
);

CREATE TABLE score (
  id serial PRIMARY KEY,
  subject text NOT NULL,
  score1 integer NOT NULL,
  score2 integer NOT NULL,
  student_id integer NOT NULL,
  CONSTRAINT s_id FOREIGN KEY (student_id) REFERENCES student (id),
);

A consulta para unir score1 e score2 se parece com isto:

SELECT st.name, sum(sc.score1 + sc.score2) as total
FROM student st
LEFT JOIN score sc ON sc.student_id = st.id
group by st.name

Se eu adicionar bonusa esta consulta, ou seja sum(sc.score1 + sc.score2 + st.bonus), ela será repetida para cada aluno várias vezes (dependendo de quantas vezes o student_id ocorrer na tabela de pontuação).

Tenho que usar uma subconsulta, ou seja, calcular a soma de score1 e score2 primeiro e depois adicionar isso ao bônus (veja abaixo) ou existe uma maneira melhor?

SELECT sq.name, sum(sq.bonus+sq.total) FROM
( SELECT st.bonus, st.name, sum(sc.score1 + sc.score2) as total
  FROM student st
  LEFT JOIN score sc ON sc.student_id = st.id
  group by st.name
) AS sq

Esta é uma tabela para um catálogo de medicamentos. Alguns têm uma marca farmacêutica, outros são genéricos (ou seja, nunca terão informações de marca)

CREATE TABLE medicine (
   id serial PRIMARY KEY,
   name text NOT NULL,
   brand_id integer
   CONSTRAINT brand_fk FOREIGN KEY (brand_id) REFERENCES brand (id)       
);

CREATE TABLE brand (
  id serial PRIMARY KEY,
  name text NOT NULL
);

Para armazenar medicamentos genéricos, por exemplo abc& xyz, existem 2 opções:

1. Use NULL para a chave estrangeirabrand_id

   INSERT INTO medicine (name, brand_id) VALUES ('abc', NULL)
INSERT INTO medicine (name, brand_id) VALUES ('xyz', NULL)

2. Insira apenas 1 string vazia no nome da marca e use-a para todos os brand_id de medicamentos genéricos

   INSERT INTO brand (id, name) VALUES (1, '')
INSERT INTO medicine (name, brand_id) VALUES ('abc', 1)
INSERT INTO medicine (name, brand_id) VALUES ('xyz', 1)

Pelo que li no StackExchange, parece que 1é a maneira geral de fazer isso. No entanto, se eu quiser um índice exclusivo sobre o nome do medicamento e o brand_id, terei que usar índices parciais (ou seja, 2 índices, respectivamente, onde brand_id IS NULL& brand_id is NOT NULL).

Se eu optar pela segunda abordagem, posso alcançá-la com um único índice.

Existe alguma outra vantagem/desvantagem de qualquer uma dessas abordagens. Sinto que o segundo método não é convencional e pode haver alguns problemas que descobrirei mais tarde ao longo do caminho.

PS Usei o exemplo de medicamentos para ilustrar minha consulta, mas quero entender os méritos técnicos da solução puramente do ponto de vista do banco de dados, ou seja, sem entrar em uma discussão sobre qual abordagem seria melhor para o exemplo de medicamento.

Os documentos do Postgres dizem:

O uso de índices para impor restrições exclusivas pode ser considerado um detalhe de implementação que não deve ser acessado diretamente. Deve-se, no entanto, estar ciente de que não há necessidade de criar índices manualmente em colunas únicas; fazer isso apenas duplicaria o índice criado automaticamente.

Com base nisso, se eu quiser um índice de expressão em uma coluna e também quiser que essa coluna seja única, case 2abaixo seria melhor, pois pode realizar o acima com um único índice. Considerando case 1que teria um índice criado automaticamente por causa de uma restrição única e outro porque preciso de um índice em minúsculas?

Como @Colin'tHart apontou, esses 2 casos não são os mesmos. Eu deveria ter postado esta pergunta sem o uso de lower()expressão. Nesse caso, entendo que a CREATE UNIQUE INDEXseria melhor do que uma restrição única e um índice simples.

Com base nisso, se eu quiser um índice com uma classe de operador (por exemplo text_pattern_ops, ) em uma coluna e também quiser que essa coluna seja única, case 2abaixo seria melhor, pois pode realizar o acima com um único índice. Considerando case 1que teria um índice criado automaticamente por causa de uma restrição única e outro porque preciso de uma classe de operador diferente?

Caso 1:

CREATE TABLE book (
   id SERIAL PRIMARY KEY,
   name text NOT NULL,
   CONSTRAINT book_name_key UNIQUE (name)
);

CREATE INDEX book_name_like ON book (name text_pattern_ops);

Caso 2:

CREATE TABLE book (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE UNIQUE INDEX book_name_like ON book (name text_pattern_ops);

Estou com uma dúvida básica de como JOINfunciona em várias tabelas. Eu quero contar ocorrências de chave estrangeira em link1&link2

CREATE TABLE main (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE link1 (
   id SERIAL PRIMARY KEY,
   main_id integer NOT NULL,
   CONSTRAINT main_id_fk FOREIGN KEY (main_id) REFERENCES main (id)
);

-- link2 is similar to link1

SQL Fiddle

Por que a consulta abaixo fornece um produto de contagens (em vez de soma) quando a contagem é diferente de zero em ambas as colunas.

SELECT main.id, COUNT(link1.main_id) + COUNT(link2.main_id)
FROM main
LEFT JOIN link1 ON main.id=link1.main_id
LEFT JOIN link2 ON main.id=link2.main_id
GROUP BY main.id

É possível atualizar uma visão materializada de forma incremental no PostgreSQL, ou seja, apenas para os dados que são novos ou alterados?

Considere esta tabela e visualização materializada:

CREATE TABLE graph (
   xaxis integer NOT NULL,
   value integer NOT NULL,
);

CREATE MATERIALIZED VIEW graph_avg AS 
SELECT xaxis, AVG(value)
FROM graph
GROUP BY xaxis

Periodicamente, novos valores são adicionados graphou um valor existente é atualizado. Desejo atualizar a exibição a graph_avgcada duas horas apenas para os valores que foram atualizados. No entanto, no PostgreSQL 9.3, toda a tabela é atualizada. Isso é bastante demorado. A próxima versão 9.4 permite CONCURRENTatualização, mas ainda atualiza toda a visualização. Com centenas de milhões de linhas, isso leva alguns minutos.

Qual é uma boa maneira de acompanhar os valores atualizados e novos e apenas atualizar a exibição parcialmente?

Estou usando o PostgreSQL 9.3. Quero entender se tenho a opção de tornar uma restrição exclusiva em toda a tabela versus exclusiva em um subconjunto da tabela (ou seja, usando 2 colunas na restrição exclusiva, restrinjo a exclusividade), qual é o melhor para pesquisas ?

Considere esta tabela onde um código alfanumérico único é atribuído a cada aluno da turma.

CREATE TABLE sc_table (
   name text NOT NULL,
   code text NOT NULL,
   class_id integer NOT NULL,
   CONSTRAINT class_fk FOREIGN KEY (class_id) REFERENCES class (id),
   CONSTRAINT sc_uniq UNIQUE (code)
);

Atualmente, o codeé exclusivo em toda a tabela. No entanto, a especificação diz que é suficiente que o código seja único entre os classúnicos. Para meus requisitos de design, não há restrição de qualquer maneira.

No entanto, se eu alterar a restrição para ser exclusiva apenas para uma determinada classe, como isso afetaria a pesquisa por código?

Ou, em outras palavras, qual das seguintes combinações de restrição e pesquisa é a melhor em termos de velocidade:

-- 1. unique across entire table, lookup by value
CONSTRAINT sc_uniq UNIQUE (code)       
SELECT * FROM sc_table WHERE code='alpha-2-beta'

-- 2. unique across entire table, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code)       
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta' 

-- 3. unique per class, lookup by value
CONSTRAINT sc_uniq UNIQUE (code, class_id)       
SELECT * FROM sc_table WHERE code='alpha-2-beta'

-- 4. unique per class, lookup by value & class
CONSTRAINT sc_uniq UNIQUE (code, class_id)       
SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta'

Pergunta : Pelo que entendi, 2 é melhor que 1 e 4 é melhor que 3. Mas qual é melhor entre 1 contra 3 e 2 contra 4?

Atualização : Adicionando saída de explain analyze. 3é ruim porque não há índice para a pesquisa. 2parece ser o melhor, mas a tabela é muito pequena para concluir isso.

-- 1
"Index Scan using sc_uniq on sc_table  (cost=0.15..8.17 rows=1 width=72) (actual time=0.041..0.044 rows=1 loops=1)"
"  Index Cond: (code = 'code1'::text)"
"Total runtime: 0.096 ms"

-- 2
"Index Scan using sc_uniq on sc_table  (cost=0.15..8.17 rows=1 width=72) (actual time=0.024..0.026 rows=1 loops=1)"
"  Index Cond: (code = 'code1'::text)"
"  Filter: (class_id = 1)"
"Total runtime: 0.056 ms"

-- 3
"Bitmap Heap Scan on sc_table2  (cost=4.18..12.64 rows=4 width=72) (actual time=0.052..0.053 rows=1 loops=1)"
"  Recheck Cond: (code = 'code1'::text)"
"  ->  Bitmap Index Scan on sc_uniq2  (cost=0.00..4.18 rows=4 width=0) (actual time=0.039..0.039 rows=1 loops=1)"
"        Index Cond: (code = 'code1'::text)"
"Total runtime: 0.121 ms"

-- 4
"Index Scan using sc_uniq2 on sc_table2  (cost=0.15..8.17 rows=1 width=72) (actual time=0.036..0.039 rows=1 loops=1)"
"  Index Cond: ((code = 'code1'::text) AND (class_id = 1))"
"Total runtime: 0.093 ms"

Eu tenho uma tabela de artigos onde quero que o slug seja único.

CREATE TABLE article (
   title char(50) NOT NULL,
   slug  char(50) NOT NULL
);

Quando o usuário insere um título, por exemplo News on Apple, quero verificar o banco de dados para ver se existe um slug correspondente, por exemplo news-on-apple. Em caso afirmativo, colocarei um sufixo em um valor numérico até encontrar um único, por exemplo news-on-apple-1. Isso pode ser alcançado com uma consulta CTE recursiva em vez de fazer recursão em meu ORM. Existe um bom número aproximado onde devo parar de recorrer e errar. Posso imaginar pessoas usando o mesmo título 1.000 vezes, o que resultaria em 1.000 consultas apenas para criar 1 artigo.

É possível que meu entendimento de CTE recursivo esteja incorreto e não haja maneira melhor de encontrar um slug exclusivo. Por favor, sugira quaisquer alternativas.