Coisas que você NÃO deveria usar no PostgreSQL

Na Wiki do PostgreSQL existe uma lista de erros comuns, mencionando o motivo do porquê evitar usar algo e quando pode fazer sentido usar. Se você trabalha com PostgreSQL, você deveria conhecer isso.

A leitura é rápida e os pontos estão bem explicados. Mesmo que você não entenda tudo, vale a pena ler o documento completo na fonte, visto que vou destacar apenas alguns tópicos aqui.

Não use NOT IN

Dois motivos:

1. NOT IN se comporta de maneiras inesperadas se houver um nulo presente:

NOT IN (1, NULL) -- sempre retorna nenhuma linha
NOT IN (SELECT bar.x FROM bar) -- retorna nenhuma linha caso algum valor de "bar.x" seja nulo

2. Por causa do ponto 1 acima, NOT IN (SELECT ...) não otimiza muito bem. Em particular, o planejador não pode transformá-lo em um anti-join, e então ele se torna um Subplan com hash ou um Subplan simples. O subplan com hash é rápido, mas o planejador só permite esse plano para pequenos conjuntos de resultados; o subplan simples é bem lento — O(N²). Isso significa que o desempenho pode parecer bom em testes de pequena escala, mas depois diminuir em 5 ou mais ordens de magnitude quando um limite de tamanho é ultrapassado.

Na maioria dos casos, o comportamento NULL de NOT IN (SELECT ...) não é intencionalmente desejado, e a consulta pode ser reescrita usando NOT EXISTS (SELECT ...).

Quando usar?

É natural e até aconselhável usar NOT IN ao excluir valores constantes específicos de um resultado de consulta, já que você garante que não terá o NULL na lista.

Não use nomes de tabelas ou colunas em letras maiúsculas

O PostgreSQL coloca todos os nomes — de tabelas, colunas, funções e tudo mais — em letras minúsculas, a menos que estejam "entre aspas duplas". Então, create table Foo() criará uma tabela chamada foo, enquanto create table "Bar"() criará uma tabela chamada Bar.

Isso significa que se você usar caracteres maiúsculos nos nomes de suas tabelas ou colunas, você terá que sempre colocá-los entre aspas duplas ou nunca colocá-los entre aspas duplas.

Quando usar?

Se for importante que nomes "bonitos" sejam exibidos na saída do relatório, então você pode querer usá-los. Mas você também pode usar aliases de coluna para usar nomes em minúsculas em uma tabela e ainda obter nomes bonitos na saída de uma consulta: select character_name as "Character Name" from foo.

Não use BETWEEN (especialmente com timestamps)

BETWEEN usa uma comparação de intervalo fechado: os valores de ambas as extremidades do intervalo especificado são incluídos no resultado. Este é um problema específico com consultas do tipo

SELECT * FROM blah WHERE timestampcol BETWEEN '2018-06-01' AND '2018-06-08'

Isso incluirá resultados em que o timestamp é exatamente 2018-06-08 00:00:00.000000, mas não timestamps posteriores no mesmo dia. Portanto, a consulta pode parecer funcionar, mas assim que você obtiver uma entrada exatamente à meia-noite, você acabará contando-a duas vezes.

Em vez disso, faça:

SELECT * FROM blah WHERE timestampcol >= '2018-06-01' AND timestampcol < '2018-06-08'

Quando usar?

É seguro usar BETWEEN para quantidades discretas como inteiros ou datas, desde que você se lembre de que ambas as extremidades do intervalo estão incluídas no resultado. Apesar disso, estará adquirindo um mau hábito.

Não use timestamp (sem fuso horário)

Não use o tipo timestamp para armazenar registros de data e hora; em vez disso, use timestamptz (também conhecido como timestamp com fuso horário).

timestamptz registra um único momento no tempo. Você pode inserir valores em qualquer fuso horário e ele armazenará o ponto no tempo que o valor descreve. Por padrão, ele exibirá os horários no seu fuso horário atual, mas você pode usar at time zone para exibi-lo em outros fusos horários.

Como ele armazena um ponto no tempo, ele fará a coisa certa com aritmética envolvendo timestamps inseridos em fusos horários diferentes — incluindo entre timestamps do mesmo local em lados diferentes de uma mudança de horário de verão.

timestamp (também conhecido como timestamp sem fuso horário) não faz nada disso, ele apenas armazena uma data e hora que você fornece. Você pode pensar nisso como uma imagem de um calendário e um relógio em vez de um ponto no tempo. Sem informações adicionais — o fuso horário — você não sabe que horas ele registra. Por isso, a aritmética entre timestamps de diferentes locais ou entre timestamps de verão e inverno pode dar a resposta errada.

Quando usar?

Se você estiver lidando com registros de data e hora de forma abstrata, ou apenas salvando e recuperando-os de um aplicativo, onde não será necessário fazer cálculos aritméticos com eles, o timestamp pode ser adequado.

Não use timetz

Você provavelmente quer o timestamptz em vez do timetz.

Até mesmo o manual diz que o timetz é implementado apenas para conformidade com SQL.

O tipo time with time zone é definido pelo padrão SQL, mas a definição exibe propriedades que levam a uma utilidade questionável. Na maioria dos casos, uma combinação de date, time, timestamp without time zone e timestamp with time zone deve fornecer uma gama completa de funcionalidades de data/hora exigidas por qualquer aplicativo.

Quando usar?

Nunca.

Não use CURRENT_TIME

Não use a função CURRENT_TIME. Use qualquer uma destas que for apropriada:

• CURRENT_TIMESTAMP ou now() se você quiser um timestamp com fuso horário,
• LOCALTIMESTAMP se você quiser um timestamp sem fuso horário,
• CURRENT_DATE se você quiser uma data,
• LOCALTIME se você quiser um horário

O CURRENT_TIME retorna um valor do tipo timetz (veja o tópico anterior).

Quando usar?

Nunca.

Não use timestamp(0) ou timestamptz(0)

Não use uma especificação de precisão, especialmente 0, para colunas de timestamp ou conversões para timestamp. Use date_trunc('second', blah) em vez disso.

A especificação de precisão arredonda a parte fracionária em vez de truncá-la como todos esperariam. Isso pode causar problemas inesperados; considere que quando você armazena now() em tal coluna, você pode estar armazenando um valor meio segundo no futuro.

Quando usar?

Nunca.

Não use char(n)

Você provavelmente quer text.

Qualquer string que você inserir em um campo char(n) será preenchida com espaços até a largura declarada. Provavelmente não é isso que você realmente quer.

O manual diz:

Valores do tipo character são preenchidos fisicamente com espaços até a largura especificada n, e são armazenados e exibidos dessa forma. No entanto, espaços finais são tratados como semanticamente insignificantes e desconsiderados ao comparar dois valores do tipo character. Em collations onde o espaço em branco é significativo, esse comportamento pode produzir resultados inesperados; por exemplo, SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) retorna true, mesmo que o locale C considere um espaço maior que uma nova linha. Espaços finais são removidos ao converter um valor de caractere para um dos outros tipos de string. Observe que espaços finais são semanticamente significativos em valores de variação de caracteres e texto, e ao usar correspondência de padrões, que são LIKE e expressões regulares.

O preenchimento de espaço desperdiça espaço, mas não torna as operações mais rápidas; na verdade, o inverso, graças à necessidade de remover espaços em muitos contextos.

É importante notar que, do ponto de vista do armazenamento, char(n) não é um tipo de largura fixa. O número real de bytes varia, pois os caracteres podem ocupar mais de um byte, e os valores armazenados são, portanto, tratados como de comprimento variável de qualquer maneira (mesmo que o preenchimento de espaço esteja incluído no armazenamento).

Quando usar?

Quando está portando um software muito, muito antigo, que usa campos de largura fixa. Ou quando você lê o trecho do manual acima e pensa "sim, isso faz todo o sentido e é uma boa combinação para meus requisitos" em vez de balbuciar e fugir.

Não use varchar(n) por padrão

Considere varchar (sem o limite de comprimento) ou text em vez disso.

varchar(n) é um campo de texto de largura variável que lançará um erro se você tentar inserir uma string maior que n caracteres (não bytes) nele.

varchar (sem o (n)) ou text são semelhantes, mas sem o limite de comprimento. Se você inserir a mesma string nos três tipos de campo, eles ocuparão exatamente a mesma quantidade de espaço, e você não conseguirá medir nenhuma diferença no desempenho.

Se o que você realmente precisa é de um campo de texto com um limite de comprimento, então varchar(n) é ótimo, mas se você escolher um comprimento arbitrário e escolher varchar(20) para um campo de sobrenome, estará arriscando erros de produção no futuro quando Hubert Blaine Wolfeschlegelsteinhausenbergerdorff se inscrever para seu serviço.

Alguns bancos de dados não têm um tipo que pode conter texto longo arbitrário, ou se eles têm, não é tão conveniente ou eficiente ou bem suportado quanto varchar(n). Usuários desses bancos de dados geralmente usam algo como varchar(255) quando o que eles realmente querem é texto.

Se você precisa restringir o valor em um campo, você provavelmente precisa de algo mais específico do que um comprimento máximo — talvez um comprimento mínimo também, ou um conjunto limitado de caracteres — e uma check constraint pode fazer todas essas coisas, bem como um comprimento máximo de string.

Quando usar?

Você pode usar varchar(n) quando você quiser, realmente. Se o que você quer é um campo de texto que lançará um erro se você inserir uma string muito longa nele, e você não quer usar uma restrição de verificação explícita, então varchar(n) é um tipo perfeitamente bom. Só não o use automaticamente sem pensar sobre isso.

Além disso, o tipo varchar está no padrão SQL, diferente do tipo text, então pode ser a melhor escolha para escrever aplicativos superportáteis.

Não use money

O tipo de dados money não é muito bom para armazenar valores monetários. Numérico ou (raramente) inteiro pode ser melhor.

Por que não? Por muitos motivos.

É um tipo de ponto fixo, implementado como um int de máquina, então a aritmética com ele é rápida. Mas ele não lida com frações de um centavo (ou equivalentes em outras moedas), seu comportamento de arredondamento provavelmente não é o que você quer.

Ele não armazena uma moeda com o valor, em vez disso, assume que todas as colunas money contêm a moeda especificada pela configuração de lc_monetary do banco de dados. Se você alterar a configuração lc_monetary por qualquer motivo, todas as colunas money conterão o valor errado. Isso significa que se você inserir '$10.00' enquanto lc_monetary estiver definido como 'en_US.UTF-8', o valor que você recuperar pode ser '10,00 Lei' ou '¥1,000' se lc_monetary for alterado.

Armazenar um valor como numérico, possivelmente com a moeda sendo usada em uma coluna adjacente, pode ser melhor.

Quando usar?

Se você estiver trabalhando apenas com uma única moeda, não estiver lidando com centavos fracionários e estiver apenas fazendo adição e subtração, então money pode ser a coisa certa.

Não use serial

Para novos aplicativos, colunas identity devem ser usadas.

Os tipos seriais têm alguns comportamentos estranhos que tornam o esquema, a dependência e o gerenciamento de permissões desnecessariamente trabalhosos.

Quando usar?

• Se você precisa de suporte para PostgreSQL mais antigo que a versão 10, pode usar serial.
• Em certas combinações com herança de tabela.
• Se você de alguma forma usar a mesma sequência para várias tabelas, embora nesses casos uma declaração explícita possa ser preferível aos tipos seriais.

Não acabou

A lista acima cita quando é um erro tomar determinadas escolhas, mas boa parte delas ainda pode ser "correta" em algumas situações. Como eu disse no começo, existem mais alguns erros comuns mencionados na fonte. Espero que, assim como eu, você tenha aprendido mais sobre o funcionamento do PostgreSQL nessa leitura.