Sintaxe no PostgreSQL: estrutura lexica

Este post inicia uma série de artigos sobre a linguagem SQL no PostgreSQL. Na documentação oficial, o capítulo 4 possui uma longa explicação sobre sintaxe SQL no PostgreSQL. Confesso que quando eu li sobre isso há mais de 15 anos, tive vontade de pular essa parte. Bastante burocrática e pouco interessante. Mas… o mal mora nos detalhes! Entre as minhas abas abertas do meu navegador (quase sempre com uma ou duas abas da documentação do Postgres), me vejo revisitando este capítulo mais e mais vezes. Gostaria de começar pelo começo… e de fato, temos muitos detalhes importantes que vão influenciar na forma como você deve escrever seus comandos SQL no PostgreSQL.

Dando nomes às partes

Trazendo diretamente da documentação temos:

“SQL input consists of a sequence of commands. A command is composed of a sequence of tokens, terminated by a semicolon (“;”).”

A tradução de alguns termos é meio estranha aos ouvidos, por isso trouxe a expressão original do inglês, mas vamos lá: “Uma entrada SQL consiste numa sequência de comandos. Cada comando é composto por uma sequência de tokens terminadas por um ponto e vírgula (“;”). ”

A tradução da palavra token é meio complicada, mas você pode chamar de símbolo, sinal, palavra ou outras possibilidades que o Google me sugeriu, mas isto iria confundir a definição a seguir:

“Um token pode ser uma palavra chave, um identificador, um identificador entre aspas*, uma constante, ou um símbolo de caractere especial. As fichas são normalmente separadas por espaços em branco(espaços, tabulações ou nova linha), mas isso não é obrigatório se não houver ambiguidade (que é geralmente o caso se um caractere especial estiver entre fichas de outros tipos)”

Uma definição relativamente simples, que já dá algumas dicas importantes, mas vamos por partes. Agora vamos ver isso em detalhes:

Palavras chave

São fichas que possuem um significado especial na linguagem SQL como UPDATE, SELECT, WHERE, FROM, etc. As palavras chave vão mudando com o passar do tempo como você pode ver na lista da documentação. Isso significa que conforme novas versões do padrão SQL vão surgindo, novas palavras chave vão sendo adicionadas. Da mesma forma, conforme novas versões do PostgreSQL vão sendo lançadas, novas implementações do padrão SQL e fora do padrão SQL vão sendo incorporadas a lista de palavras chave. Existem funcionalidades que o PostgreSQL lança antes de se tornarem padrão SQL e funcionalidades do padrão SQL que o PostgreSQL não implementa. Para complicar um pouco mais, existem palavras chaves reservadas e não reservadas. A diferença é que as palavras chave reservadas nunca podem ser utilizadas como identificadores e as palavras chaves não reservadas podem ser utilizadas em alguns tipos de identificadores. E tem mais uma questão importante… nada impede que no futuro, novas palavras chaves sejam criadas para novas funcionalidades que venham a ser implementadas no futuro.

Então o mais importantes sobre palavras chaves que você deve entender é: não use uma palavra chave como identificador. Seja esse identificador o nome de uma tabela, coluna de tabela ou nome de uma função. Não use e pronto. Independente se ela é reservada no padrão SQL:92, SQL:2011 ou SQL:2016 ou apenas no PostgreSQL. Evite ao máximo. Sua vida vai ficar bem mais fácil assim.

Uma excessão à regra é o uso de apelidos (alias em inglês) em nomes de colunas como em

SELECT resultado AS table FROM foo;

Palavras chave não fazem distinção de letras maiúsculas e minúsculas (ou em inglês case insensitive) no PostgreSQL. Isso significa que você pode escrever UPDATE, update, ou UPdate e o Postgres vai interpretar exatamente da mesma forma. Mas é um costume antigo e considerado desejável, que você escreva suas palavras reservadas sempre em letras maiúsculas. Algumas vezes as pessoas também tem o hábito de escrever nomes de funções em letras maiúsculas. No entanto, nomes de funções não são palavras reservadas, são identificadores, o que pode gerar alguns problemas em situações bastante específicas.  o que não é necessário, mas também não gera muitos

Identificadores

Identificadores são nomes que você dá para os objetos que você cria no seu banco de dados, bem como variáveis em linguagens de programação como o PL/pgSQL. Como dissemos antes, não utilize nunca palavras reservadas como identificadores. Identificadores são sensíveis a diferenças entre maiúsculas e minúsculas (case sensitive em inglês) de uma forma particular. Se o seu identificador não estiver entre aspas duplas, o Postgres vai considerar todas as letras como minúsculas independente de como você escrever. Desta forma: ID, id e Id para o PostgreSQL sempre serão equivalentes a id. Por outro lado, se você utilizar aspas duplas, como em “ID”, “id” e “Id”, cada um deles se referem a um objeto diferente. Isso pode causar uma bela confusão, maior do que você possa imaginar. Então, por uma questão de sanidade mental e maior chance de não ser colocado de castigo na chuva num domingo de carnaval amarrado no meio da jaula dos leões, é melhor você criar identificadores com letras minúsculas e apenas letras minúsculas. Você não vai precisar nunca das aspas e nunca vai se confundir. Para separar várias palavras num identificador, você utiliza o sublinhado ( _ ), como em nome_produto_nota. Simples e fácil de ler. Você pode criar identificadores com até 63 caracteres, o que é mais do que suficiente para você criar um monstro se necessário.

Então vamos às regras de ouro dos identificadores:

• Use letras minúsculas, sempre;
• Não utilize acentos ou melhor, utilize apenas letras do alfabeto latino sem acentos. Nada de cirílico, letras gregas, runas, ideogramas etc;
• Se seu identificador possui varias palavras, separe elas por um sublinhado;
• Todo identificador deve começar com uma letra;
• Você pode utilizar alem de letras (eu te avisei para só utilizar as minúsculas, né?), algarismos de 0 a 9 e sublinhado. Também é possível usar o $, mas como isso não é padrão SQL, isso não é uma boa ideia;

Por fim, uma recomendação importante: siga um padrão de nomenclatura para os seus identificadores. Não importa qual seja. A discussão sobre qual é o melhor padrão é interminável. Apenas escolha um que fizer mais sentido para a sua equipe siga ele até a morte. Se você mudar de padrão no meio de um projeto, vai instalar o caos. Mas adote um padrão. Qualquer um.

Constantes

Constantes são os dados atribuídos aos seus identificadores. Eles podem ter diversos tipos e cada um deles tem suas peculiaridades. O SQL é uma linguagem fortemente tipada. Isso significa que o tipo da constante faz muita diferença para ele e você deve se importar com isso. E muito. Você será uma pessoa muito mais feliz, se souber utilizar isso de forma correta. Pode não ficar milionário, mas as pessoas vão te respeitar mais, você terá mais sucesso no amor e terá menos doenças relacionadas ao stress.

Dependendo de como você escrever uma determinada constante, ela vai ser automaticamente enquadrada em um tipo específico. Isso ocorre particularmente com texto (sempre entre aspas simples) e números. Em algumas situações, como durante um INSERT, UPDATE ou numa cláusula WHERE, o PostgreSQL vai interpretar o tipo da constante segundo o campo relacionado.

Números

Números possuem uma sintaxe específica. Sempre que você escrevem algarismos sem aspas simples ou duplas, o PostgreSQL vai tentar interpretar como um número. O detalhe importante é como lidar com o separador de casas decimais e com a notação científica:

• • Sem separador decimal, o PostgreSQL interpreta sempre a constante como um INTEGER. Se o número for muito grande, ele vai utilizar o BIGINT. Note que a vírgula é utilizada para separar duas constantes inteiras distintas. Ex:

teste=# SELECT 42,8;
 ?column? | ?column?
----------+----------
       42 |        8

• Com o separador decimal (que é sempre um ponto, não uma vírgula), o PostgreSQL interpresa como um NUMERIC. Ex:

teste=# SELECT 4.2, .4, 4.;
 ?column? | ?column? | ?column?
----------+----------+----------
      4.2 |      0.4 |        4

• Podemos usar a notação científica também usando a letra ‘e’ ou ‘E’. Então quando queremos um número como 42×10³ que é a mesma coisa que 42000, usamos 42e3. Ex:

teste=# SELECT 42e5, 42e-5, 4.2e5, 4.2e-5;
 ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
  4200000 |  0.00042 |   420000 | 0.000042
(1 row)

teste=# SELECT 42E5, 42E-5, 4.2E5, 4.2E-5;
 ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
  4200000 |  0.00042 |   420000 | 0.000042
(1 row)

Cadeias de Caracteres

Caracteres devem vir sempre entre aspas simples. Não confundir com aspas duplas que são utilizados para identificadores, como vimos acima e não para constantes. Existem 3 tipos de constantes relacionadas com caracteres no PostgreSQL: CHARACTER ou CHAR,  CHARACTER VARYNG ou VARCHAR e TEXT. Se você não especificar nada, o PostgreSQL vai sempre assumir como TEXT, que é semelhante ao VARCHAR sem limitação de tamanho (até o limite do PostgreSQL de 1GB). Trabalhar com cadeias de caracteres (ou strings em inglês) tem suas peculiaridades.

Concatenação

Juntar duas strings é fácil:

teste=# SELECT 'foo','bar';
 ?column? | ?column?
----------+----------
 foo      | bar
(1 row)

teste=# SELECT 'foo' 'bar';
ERROR:  syntax error at or near "'bar'"
LINE 1: SELECT 'foo' 'bar';
                     ^
teste=# SELECT 'foo' || 'bar';
 ?column?
----------
 foobar
(1 row)

teste=# SELECT 'foo'
teste-# 'bar';
 ?column?
----------
 foobar
(1 row)

• No primeiro caso, a vírgula separa duas constantes distintas, assim como ocorreu com números.
• No segundo caso tivemos um erro de sintaxe.
• Para concatenar as duas cadeias de caracteres, nós usamos o operador ||, como mostrado no terceiro exemplo.
• O último exemplo é um pouco bizarro (mas é o que o padrão SQL define): se duas cadeias de caracteres são separadas por uma ou mais linhas em branco, elas são consideradas como se estivessem concatenadas. Vai por mim, evite esse tipo de coisa e use o operador || quando precisar.

Citação tipo dólar (dollar quoting)

Um dos problemas que temos ao lidar com texto é que eles são delimitados por aspas simples, mas em varias situações você pode querer incluir uma aspa simples no seu texto. A forma tradicional de lidar com isso é duplicar a aspa simples. Vejamos o caso clássico da Joana d’Arc:

teste=# SELECT 'Joana d''Arc';
  ?column?
-------------
 Joana d'Arc

O PostgreSQL possui uma alternativa para isso que não faz parte do padrão SQL mas que é bastante útil. Você pode ao invés de iniciar e terminar a sua cadeia de caracteres com uma aspa simples, você pode usar dois sinais de dólar, desta forma:

teste=# SELECT $$Joana d'Arc$$;
  ?column?
-------------
 Joana d'Arc

Na verdade você pode ainda utilizar um identificador qualquer entre os dois sinais de dólar (mas precisa ser o mesmo identificador no início e no final), se preferir:

teste=# SELECT $txt$Joana d'Arc$txt$;
  ?column?
-------------
 Joana d'Arc

Aqui, nós temos Joana d’Arc envolvidos por $txt$. De fato, isso ajuda muito em situações mais complexas. Quando você vai escrever uma função por exemplo, todo o código é considerado como texto. Para iniciar e terminar este texto, delimitamos ele com $function$ ou $BODY$.

Um detalhe curioso é que a maioria dos editores de texto que utilizam o realce de sintaxe (Syntax highlighting) se perdem quando utilizamos este tipo de notação. Aqui no texto eu deliguei o realce no segundo exemplo para ajudar um pouco, no primeiro, veja que ele considera o início da constante no lugar errado.

Escape no estilo C

O PostgreSQL foi todo escrito na linguagem C e como tal traz algumas práticas desta linguagem. Antigamente, qualquer cadeia de caractere poderia ser recheada de escapes no estilo C, utilizando as famosas barras invertidas ( \ ). Isso é muito útil para incluir caracteres não imprimíveis como uma tabulação ou uma quebra de linha. Antigamente o PostgreSQL aceitava isso nativamente. Existe uma tabela na documentação com todas as possibilidades, mas as mais comuns são:

• \n (quebra de linha)
• \t (tabulação)
• \r (equivalente ao “enter“)
• \\ (o caractere \)
• \’ (uma aspa simples)

A partir da versão 9.1 do PostgreSQL o parâmetro standard_conforming_strings passou a vir ligado por padrão. Isso significa que o comportamento histórico de aceitar escapes no estilo C dentro de uma cadeia de caracteres. Para contornar isso, o Postgres (e outros SGDBs também) criaram uma sintaxe alternativa para aceita-los. Basta preceder a aspa simples que abre a cadeia de caracteres por um E maiúsculo ou não:

teste=# SELECT E' foo \n bar';
 ?column?
----------
  foo    +
  bar
(1 row)

teste=# SELECT E'Joana d\'Arc';
  ?column?
-------------
 Joana d'Arc
(1 row)

No primeiro exemplo, utilizamos o \n para para quebrar a linha. No segundo utilizamos para escapar a aspa simples. Veja que o escape no estilo C é mantido dessa forma mais não é padrão SQL. Não estimulamos o seu uso. Se você realmente precisa inserir caracteres não imprimíveis como uma tabulação ou uma quebra de linha, você pode fazer isso usando o padrão SQL com uma função desta forma:

teste=# SELECT ' foo ' || CHR(10) || ' bar';
 ?column?
----------
  foo    +
  bar
(1 row)

Caracteres unicode

Aqui as coisas começam a complicar um pouco mais. Você pode querer inserir caracteres que não estão na tabela ASCII padrão, mas caracteres em outros alfabetos. Se você utiliza UTF8 como codificação de caracteres (você deveria fazer isso sempre, sério), então você pode utilizar esta notação para inserir caracteres específicos que seu teclado ABNT não possui. Você deve preceder a aspa simples com um sinal de U&:

teste=# SELECT   U&'\0441\043B\043E\043D';
 ?column?
----------
 слон
(1 row)

Existe um operador que utiliza o caractere & no PostgreSQL. Por este motivo, quando quiser trabalhar com caracteres UTF8 utilize o U&’ sem espaços, quando quiser utilizar o operador & utilize sempre com espaços antes de depois dele, de forma evitar qualquer ambiguidade.

Cadeias de caracteres binárias

Existem 2 tipos de cadeias de caracteres binárias no PostgreSQL: BIT (com tamanho fixo, assim como em CHAR) e BIT VARYING. Como se pode imaginar, eles só aceitam os caracteres 0 e 1. Este tipo é muito utilizado em mapas de bits, uma técnica muito comum em programação em C. Para utilizar cadeias binárias, você pode também utilizar caracteres hexadecimais, onde cada caractere hexadecimal equivale exatamente a 4 caracteres binários. Para isso você utiliza o B como prefixo quando for utilizar números binários e o X quando for utilizar números hexadecimais:

teste=# SELECT B'1010', X'A';
 ?column? | ?column?
----------+----------
 1010     | 1010
(1 row)

Coerção de tipos de dados

Todos os outros tipos de dados trabalham em geral com constantes entre aspas simples, em diferentes formatos. Se você estiver realizando uma operação de INSERT por exemplo, pode ser que o PostgreSQL consiga converter implicitamente para o tipo correto durante a operação. Mas em outras situações isso nem sempre acontece. Existem muitos detalhes dependendo de cada tipo de dado envolvido. Vamos deixar estes detalhes para outro artigo!

Existem 3 formas de definir com qual tipo de dado estamos trabalhando:

type ‘string’
‘string’::type
CAST ( ‘string’ AS type )

• O primeiro é estilo é o mais utilizado e deve ser preferido no dia-a-dia;
• O segundo estilo só existe no PostgreSQL e é mantido por razões históricas, portanto você deveria evitar o mesmo;
• O último utiliza a função CAST. Funciona bem e é padrão SQL, mas é um pouco mais verborrágico. Acredito que a primeira opção é mais enxuta.

Vejamos alguns exemplos utilizando os 3:

teste=# SELECT NUMERIC(4,2) '42';
 numeric
---------
   42.00
(1 row)

teste=# SELECT '42'::NUMERIC(4,2);
 numeric
---------
   42.00
(1 row)

teste=# SELECT CAST('42' AS NUMERIC(4,2));
 numeric
---------
   42.00

Note que quando utilizamos o 42 no primeiro exemplo, ele foi considerado um INTEGER e não um NUMERIC. Outro detalhe é que aqui utilizamos sim um número entre aspas simples. Esta regra vai valer para praticamente qualquer tipo de dado. Mas claro, sempre existem exceções…

Operadores

operadores são funções que utilizam símbolos especiais e tem significados específicos dependendo do tipo de dado envolvidos junto aos operadores. Em geral estamos acostumados com operadores que utilizam um caractere só, como +, -, * e /. Mas o PostgreSQL possui uma gama enorme de operadores que podem utilizar um ou mais dos seguintes caracteres:

+ – * / < > = ~ ! @ # % ^ & | ` ?

• Observação: Existem algumas regras para evitar confusão como comentários como — e /*

Os operadores são uma parte muito importante do PostgreSQL e um dos seus grandes trunfos. Isso porque estes operadores são indexáveis. Então você pode utilizar um tipo de dados geométrico e fazer uma consulta onde na cláusula WHERE você utiliza o operador ?-| para saber se outra figura geométrica é perpendicular ao seu polígono. E utilizar um índice para acelerar esta consulta!

Caracteres especiais

Existe por fim uma lista de caracteres especiais utilizados em situações específicas:

• $ –  O dólar é utilizado para numerar parâmetros de uma função de acordo com a ordem que aparece na chamada da função e na notação de cadeia de caracteres com o $$ como explicamos há pouco;
• () – Os parenteses são utilizados para agrupar parâmetros de uma função, agrupar expressões e para reforçar a precedência de operadores;
• [] – Os colchetes são utilizados em matrizes (arrays);
• , – As vírgulas servem para separar elementos sintáticos como variáveis, identificadores e constantes;
• ; – O ponto e vírgula fecha comandos SQL;
• : – Os dois pontos são utilizados em matrizes (arrays) para separar pedaços do mesmo. Também podem ser utilizados em substituição de variáveis como prefixo antes do nome da variável;
• * – O Asterisco pode substituir um conjunto de identificadores ou utilizado em funções de agregação;
• . – O ponto separa a parte inteira da decimal em números e também separa nome de esquemas de tabelas e colunas.

Próximos passos

Nos próximos artigos vamos brincar um pouco com vários tipos de dados e funções e ver como utilizar de maneira correta. Prepare-se!