PostgreSQL Tutorial

O PostgreSQL, normalmente chamado de Postgres, é um sistema de gerenciamento de banco de dados do tipo objeto-relacional (ORDBMS) com ênfase em extensibilidade e em padrões de conformidade. Como um servidor de banco de dados, sua principal função é armazenar dados de forma segura, apoiando as melhores práticas, permitindo a recuperação dos dados a pedido de outras aplicações de software. Ele pode lidar com cargas de trabalho que vão desde pequenas aplicações single-machine a aplicações de grande porte voltadas para a Internet, onde será utilizada de forma simultânea por vários usuários.

Guia do artigo:

• Sobre o PostgreSQL
• CREATE USER
• CREATE DATABASE
• pgAdmin III
• Psql
• Tablespace
• Schema
• Tabela de testes
• Tipos de dados

Tendo isso em vista, neste artigo conheceremos algumas das funcionalidades presentes no Postgres, além de termos uma noção geral de sua arquitetura. Criaremos uma base de dados simples para trabalharmos com Tablespaces e schemas. Veremos alguns dos tipos de dados utilizados, instruções de inserção e seleção de dados.

Quer se especializar ainda mais no PostgreSQL? Confira esse excelente curso de postgreSQL que a DevMedia preparou para seus assinantes.

Sobre o PostgreSQL

O PostgreSQL é um sistema de gerenciamento de banco de dados objeto-relacional (ORDBMS) baseado no POSTGRES versão 4.21, que foi desenvolvido na Universidade da Califórnia em Berkeley Computer Science Department. Foi pioneiro em muitos dos conceitos que só se tornaram disponíveis em alguns sistemas de banco de dados comerciais mais tarde. O PostgreSQL é um descendente de código aberto do código original desenvolvido em Berkeley e suporta uma grande parte do padrão SQL e oferece muitas características modernas, como é o caso de chaves estrangeiras, functions, triggers, Views, integridades transacionais, data types, funções agregadas, operadores, dentre muitos outros.

Saiba mais: A história do PostgreSQL

Ele consiste em um processo de servidor que lê e grava os arquivos de banco de dados reais, e um conjunto de programas cliente que se comunicam com o servidor. O mais comumente utilizado é o comando psql, que permite ao usuário executar consultas SQL e visualizar os seus resultados. Nenhum dos clientes acessa os arquivos do banco de dados diretamente, o que é deixado inteiramente para o servidor. Todos os arquivos de banco de dados são armazenados em um diretório como, por exemplo, /var/lib/pgsql ou /usr/local/pgsql.

Antes de começarmos a trabalhar com o PostgreSQL precisamos realizar o download do mesmo e em seguida instalá-lo. No momento da instalação precisamos nos ater ao detalhe que um outro programa é solicitado no momento da instalação, que é o Microsoft Visual ++, o qual muitas vezes é instalado automaticamente. Caso não seja realizada esta instalação por algum motivo, pode-se realizar o download do programa (vide seção Links) e em seguida realizar a instalação como Administrador. Até a criação desse artigo a versão oficial do PostgreSQL é a 9.4, sendo esta a mais estável. Utilizaremos uma máquina com o Windows 8 instalado para a realização dos nossos testes e para prosseguirmos, baixaremos o PostgreSQL, através de seu site oficial (vide seção Links). Siga o processo next...next...finish, pois usaremos as configurações padrão: apenas atribuiremos uma senha para acessar o banco.

Como é típico de aplicações cliente/servidor, ambos podem estar em diferentes hosts. Nesse caso, eles se comunicam através de uma conexão de rede TCP/IP, e devido a isso, precisamos estar cientes de que os arquivos que podem ser acessados numa máquina cliente podem não ser acessíveis na máquina do servidor de banco de dados, ou vice-versa. O PostgreSQL pode tratar múltiplas conexões concorrentes dos clientes. Os processos de um servidor realizam a comunicação sem a intervenção do processo original do postgres. Dessa forma, temos que o processo do servidor principal estará sempre correndo, esperando por conexões de clientes. Após a conclusão da nossa instalação teremos disponíveis algumas ferramentas para a nossa utilização, como é o caso da ferramenta gráfica pgAdmin III e o pgsql.exe.

CREATE DATABASE

Cria um novo banco de dados PostgreSQL.

Para criar um banco de dados, você deve ser um superusuário ou ter o privilégio especial CREATEDB. Veja o comando CREATE USER.

Por padrão, o novo banco de dados será criado clonando o modelo de banco de dados de sistema template1. Um modelo diferente pode ser especificado escrevendo o nome do TEMPLATE. Em particular, ao escrever o TEMPLATE0 template0, você pode criar um banco de dados virgem contendo apenas os objetos padrão predefinidos pela sua versão do PostgreSQL. Isso é útil se você desejar evitar a cópia de qualquer objeto local de instalação que possa ter sido incluído no template1.

    CREATE DATABASE name
        [ [ WITH ] [ OWNER [=] user_name ]
               [ TEMPLATE [=] template ]
               [ ENCODING [=] encoding ]
               [ LC_COLLATE [=] lc_collate ]
               [ LC_CTYPE [=] lc_ctype ]
               [ TABLESPACE [=] tablespace ]
               [ CONNECTION LIMIT [=] connlimit ] ]
    

Parâmetros

• name: O nome de um banco de dados para criar.
• user_name: O nome da função do usuário que possuirá o novo banco de dados, ou DEFAULT para usar o padrão (ou seja, o usuário que está executando o comando). Para criar um banco de dados de propriedade de outra função, você deve ser um membro direto ou indireto dessa função ou ser um superusuário.
• template: O nome do template a partir do qual criar o novo banco de dados, ou DEFAULT para usar o template padrão (template1).
• encoding:Codificação do conjunto de caracteres para usar no novo banco de dados. Especifique uma constante de cadeia (por exemplo, 'SQL_ASCII'), ou um número de codificação de inteiro, ou DEFAULT para usar a codificação padrão (ou seja, a codificação do banco de dados de modelo).
• lc_collate: Ordem de agrupamento (LC_COLLATE) para usar no novo banco de dados. Isso afeta a ordem de classificação aplicada às strings, por exemplo, em consultas com ORDER BY, bem como a ordem usada em índices em colunas de texto. O padrão é usar a ordem de agrupamento do banco de dados de modelos.
• lc_ctype: Classificação de caracteres (LC_CTYPE) para usar no novo banco de dados. Isso afeta a categorização de caracteres, por ex. inferior, superior e dígito. O padrão é usar a classificação de caracteres do banco de dados de modelos. Veja abaixo as restrições adicionais.
• tablespace: O nome do espaço de tabela que será associado ao novo banco de dados, ou DEFAULT para usar o espaço de tabela do banco de dados de modelo. Esse espaço de tabela será o espaço de tabela padrão usado para objetos criados neste banco de dados. Veja CREATE TABLESPACE para mais informações.
• connlimit: Quantas conexões simultâneas podem ser feitas neste banco de dados. -1 (o padrão) significa sem limite.

Parâmetros opcionais podem ser escritos em qualquer ordem, não apenas na ordem ilustrada acima.

CREATE USER

Define uma nova conta de usuário do banco de dados.

O CREATE USER adiciona um novo usuário a um cluster de banco de dados do PostgreSQL. Você deve ser um superusuário do banco de dados para usar este comando.

    CREATE USER name [ [ WITH ] option [ ... ] ]
    
    where option can be:
        
          SYSID uid 
        | CREATEDB | NOCREATEDB
        | CREATEUSER | NOCREATEUSER
        | IN GROUP groupname [, ...]
        | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
        | VALID UNTIL 'abstime'
    
    

Parâmetros

• name: O nome do novo usuário.
• uid: A cláusula SYSID pode ser usada para escolher o ID do usuário do PostgreSQL do novo usuário. Isso normalmente não é necessário, mas pode ser útil se você precisar recriar o proprietário de um objeto órfão.

Se isso não for especificado, o ID do usuário atribuído mais alto mais um (com um mínimo de 100) será usado como padrão.

CREATEDB

• NOCREATEDB: Essas cláusulas definem a capacidade de um usuário de criar bancos de dados. Se CREATEDB for especificado, o usuário que está sendo definido terá permissão para criar seus próprios bancos de dados. O uso do NOCREATEDB negará ao usuário a capacidade de criar bancos de dados. Se não especificado, NOCREATEDB é o padrão.

CREATEUSER

• NOCREATEUSER: Essas cláusulas determinam se um usuário terá permissão para criar novos usuários. O CREATEUSER também fará do usuário um superusuário, que pode substituir todas as restrições de acesso. Se não especificado, NOCREATEUSER é o padrão.

• groupname: Um nome de um grupo existente no qual inserir o usuário como um novo membro. Vários nomes de grupos podem ser listados.
• password: Define a senha do usuário. Se você não planeja usar a autenticação de senha, pode omitir essa opção, mas o usuário não poderá se conectar se decidir mudar para a autenticação de senha. A senha pode ser definida ou alterada posteriormente, usando ALTER USER.

ENCRYPTED

• UNENCRYPTED: Essas palavras-chave controlam se a senha é armazenada criptografada nos catálogos do sistema. (Se nenhum for especificado, o comportamento padrão será determinado pelo parâmetro de configuração password_encryption.) Se a string de senha apresentada já estiver no formato criptografado MD5, ela será armazenada como está, independentemente de ser especificado ENCRYPTED ou UNENCRYPTED (desde o sistema não pode descriptografar a string de senha criptografada especificada). Isso permite o recarregamento de senhas criptografadas durante o dump/restore.

Observe que os clientes mais antigos podem não ter suporte para o mecanismo de autenticação MD5 necessário para trabalhar com senhas armazenadas criptografadas.

• abstime: A cláusula VALID UNTIL define um tempo absoluto após o qual a senha do usuário não é mais válida. Se esta cláusula for omitida, a senha será válida para todos os tempos.

pgAdmin III

Ao iniciarmos o pgAdmin teremos do lado esquerdo o object browser, que irá apresentar o grupo de servidores que utilizaremos. Ao darmos um duplo clique em “Servers”, precisaremos informar a senha de acesso, conforme apresentado na Figura 1. Em seguida, veremos alguns novos itens sendo apresentados, dentre os quais teremos Databases, Login Roles (Permissões de usuário) e Tablespaces, como mostra a Figura 2.

Em Databases teremos todas as bases de dados que criarmos sendo apresentadas onde, no nosso caso, ao selecionarmos postgres (tabela base), será apresentado o script SQL de criação no painel inferior do lado direito. Com relação ao Tablespace teremos a apresentação de dois itens pertencentes a ele: o pg_default e o o pg_global. Para que possamos escrever nossas queries, podemos seguir pelo menu e escolher a opção Tools e, em seguida, Query Tool, ou clicando no ícone, como mostra a Figura 3.

Psql

O psql é um arquivo executável, um cliente em modo texto que pode ser encontrado no diretório de instalação do Postgres, que no nosso caso encontra-se em C:\Program Files\PostgreSQL\9.4\bin. Para executar este arquivo precisamos abrir o prompt de comando, como podemos ver na Figura 4.

O acesso a nossa base de dados é especificado como:

    psql.exe –h localhost –p 5432 –U postgres –d postgres
    

Ao declararmos esta instrução, a senha de acesso será solicitada e em seguida estaremos aptos a trabalhar com a base de dados PostgreSQL utilizando comandos de texto. As linhas que são iniciadas como postgres=# são as utilizadas para o prompt de comando. Percebam que realizamos uma instrução SELECT na qual pedimos para saber a data de hoje. Percebam também que a instrução foi realizada em duas linhas e finalizamos utilizando o ponto-e-virgula (;). Para que possamos encerrar o prompt utilizamos a instrução \q.

Agora que tivemos o conhecimento inicial das duas formas de utilização iremos começar com a criação de uma base de dados e um tablespace, suas tabelas e atribuir permissões de acesso a elas.

Criando o Tablespace

Um Tablespace permite que o super usuário possa definir um local alternativo no sistema de arquivos de dados onde estes conterão os objetos de banco de dados, como é o caso de tabelas e índices. Claro que para a criação e utilização de Tablespaces é necessário que o usuário tenha privilégios necessários para isso, dessa forma, é possível criar nomes de tablespace para bancos de dados, tabelas, dentre outros itens dentro desse espaço de tabela especificado. Ou seja, um tablespace é na verdade um nome lógico que atribuímos a uma pasta física. Dito isso, iremos criar uma nova pasta localizada na unidade C:/, a qual chamaremos de funcionários. Em seguida, partiremos para o pgAdmin III e clicaremos com o botão direito sobre Tablespaces e adicionaremos um novo, o qual configuraremos de acordo com as Figuras 5 e 6.

Agora criaremos nosso Database clicando com o botão direito sobre Databases e, em seguida, selecionando a opção “New Database” e daremos a ele o nome de TesteDevmedia, como podemos ver na Figura 7. E para selecionarmos a nossa Tablespace faremos isso indo até a aba “Definition” e na tag Tablespace iremos selecionar o que criamos anteriormente, como mostra a Figura 8.

Criação do Schema

Um esquema é essencialmente um namespace que contém objetos nomeados (tabelas, tipos de dados, funções e operadores), cujos nomes podem duplicar os de outros objetos existentes em outros esquemas. Dessa forma, os objetos nomeados são acessados com a qualificação de seus nomes em conjunto com o nome do esquema criado como prefixo, ou mesmo definindo um caminho de pesquisa que pode incluir o esquema criado. Dito isso, criaremos o nosso schema com o nome de EmpresaDevmedia. O processo de criação é similar ao apresentado anteriormente para a criação do Database e do Tablespace, onde clicamos com o botão direito do mouse sobre Schema e em seguida, New Schema.

Tabela de testes

O PostgreSQL é um sistema de gerenciamento de banco de dados relacional, o que significa que ele é um sistema para gerenciar dados armazenados de acordo com seus relacionamentos. Estes são essencialmente termos matemático para tabelas. A noção de armazenar dados em tabelas se tornou bastante comum ultimamente, de forma a parecer bastante óbvio a sua utilização, mas há uma série de outras formas de organização de bancos de dados, como podemos citar os arquivos e diretórios em sistemas operacionais como Unix, que formam um exemplo de um banco de dados hierárquico.

Cada tabela é uma coleção constituída por linhas, onde cada uma possui o mesmo conjunto de colunas nomeadas e para cada uma dessas colunas temos um tipo de dados específico. Enquanto as colunas possuem uma ordem fixa em cada linha, é importante lembrarmos que o SQL não garante a ordem das linhas dentro da tabela.

Agora que temos a nossa estrutura de testes pronta, chegou a hora de criarmos nossa tabela de testes, a qual chamaremos de tb_funcionarios. As tabelas são estruturas dentro das quais temos a maior parte de nossos dados armazenados. Além delas temos também os índices. Dentro do nosso schema podemos ver a estrutura que tem criada, onde nela encontramos Collations, Functions, Views, Tables, dentre outros itens, como podemos ver na Figura 9.

Criaremos então nossa tabela clicando sobre Tables (com o botão direito do mouse) e na opção “New Table” definiremos para ela o Tablespace localizado em “Definition”. Em seguida, selecionaremos a guia Columns para que possamos criar nossas colunas, que serão código, nome, e-mail e telefone. Após criarmos as colunas teremos que definir a nossa chave primária, que no Postgres definimos indo até a guia Constraints, como podemos ver nas Figuras 10, 11 e 12.

Na Listagem 1 temos a estrutura da nossa tabela, onde temos a definição de cada um dos campos atribuídos a nossa tabela. Nesta estrutura podemos perceber uma diferença referente aos demais bancos de dados: os campos de texto normalmente são atribuídos como Varchar(), mas no Postgres é diferente, pois estes campos são definidos como character varying.

      CREATE TABLE "EmpresaDevmedia".tb_funcionarios
      (
        codigo integer NOT NULL,
        nome character varying(100),
        email character varying(100) NOT NULL,
        telefone character varying(14),
        CONSTRAINT pk_funcionario PRIMARY KEY (codigo)
      )
      WITH (
        OIDS=FALSE
      );
      ALTER TABLE "EmpresaDevmedia".tb_funcionarios
        OWNER TO postgres; 

Com nossa tabela criada podemos inserir alguns dados de testes, os quais utilizaremos o Query Tools do pgAdmin III, onde passaremos a query de acordo com a Listagem 2.

    INSERT INTO "EmpresaDevmedia".tb_funcionarios(codigo, nome, email, telefone)
        VALUES (1, 'Edson Dionisio', 'edson.dionisio@gmail.com', '81997402803');
    INSERT INTO "EmpresaDevmedia".tb_funcionarios(codigo, nome, email, telefone)
        VALUES (2, 'Marilia Kessia', 'mkessia@gmail.com', '81997402810');
    INSERT INTO "EmpresaDevmedia".tb_funcionarios(codigo, nome, email, telefone)
        VALUES (3, 'Caroline Franca', 'carol.dionisio@gmail.com', '81997402123'); 
    INSERT INTO "EmpresaDevmedia".tb_funcionarios(codigo, nome, email, telefone)
        VALUES (4, 'Maite Dionisio', 'maite.dionisio@gmail.com', '81997402322');
    INSERT INTO "EmpresaDevmedia".tb_funcionarios(codigo, nome, email, telefone)
        VALUES (5, 'Tatsu Yamashiro', 'tatsu.yamashiro@arrow.com', '99999740999');
    

Notem que todos os tipos de dados usam um formato de entrada simples, onde as constantes que não são valores numéricos, geralmente devem estar entre aspas simples (').

Para recuperarmos os dados de uma tabela utilizamos a instrução Select. A declaração é dividida em uma lista de seleção, uma lista de tabela e uma qualificação opcional, a qual especifica as restrições. Em seguida, utilizaremos esta instrução para vermos o que será retornado da nossa tabela que foi populada corretamente com as informações que atribuímos a ela, como podemos ver no código a seguir e na Figura 13:

    SELECT codigo, nome, email, telefone FROM "EmpresaDevmedia".tb_funcionarios;
    

Tipos de dados

No exemplo anterior tivemos a apresentação de um tipo de dados diferente dos vistos nos demais bancos de dados e, devido a isso, apresentaremos uma pequena lista contendo alguns dos tipos de dados presentes no Postgres, como segue:

• json - dados de texto em formato JSON;
• Character varying[(n)] - formato para tipos de dados string;
• Money - tipo de dado utilizado para valores monetários;
• point - ponto geométrico em um plano;
• serial - utilizado para realizar auto incremento de valores inteiros.

Com relação aos tipos de dados presentes no Postgres, eles são separados em tipos distintos, como é o caso do tipo JSON, presente desde a versão 9.2, mas com muito mais implementações na versão 9.4. Cada um dos tipos de dados possui uma representação externa determinada por funções de entrada e saída. Claro que além desses tipos de dados apresentados, o banco de dados Postgres possui muitos outros tipos.

Como simples exemplo dos tipos de dados utilizaremos o tipo json, pois ele pode ser usado para armazenar dados no formato JSON (Notação JavaScript Object). Estes dados podem também ser armazenados como texto, mas esse tipo tem a vantagem de verificar se cada valor armazenado é um valor JSON válido. O PostgreSQL permite apenas uma codificação do servidor por banco de dados, dessa forma, não é possível para o JSON ser utilizado de forma mais rígida com a sua especificação, a menos que o servidor de codificação seja UTF-8. Caso sejam incluídos caracteres diferentes de forma direta, estes irão falhar. Vejamos na Listagem 3 um exemplo de utilização dos tipos de dados json e serial.

      CREATE TABLE clientes ( id serial primary key, data json );
      INSERT INTO clientes VALUES (1, '{ "nome": "Marilia Kessia", 
      "email": "mkessia@gmail.com" }');
      INSERT INTO clientes VALUES (2, '{ "nome": "Caroline França",
      "email": "caroline.dionisio@gmail.com" }');
      INSERT INTO clientes VALUES (3, '{ "nome": "Heitor Dionisio",
      "email": "heitor.dionisio@gmail.com" }');
      INSERT INTO clientes VALUES (4, '{ "nome": "Edson Dionisio", 
      "email": "edson.dionisio@gmail.com" }');

Criamos uma nova tabela “clientes” e a ela atribuímos dois campos: a chave primária (como serial) e o campo do tipo json. Percebam que ao inserirmos os dados, passamos eles com aspas simples (‘). Já para recuperarmos os dados salvos na base podemos realizar a operação a seguir:

    SELECT id, data->>'nome' AS nome FROM clientes;

Percebam que nesta operação temos o operador (->), que retorna o tipo JSON original, podendo este ser um objeto, o que difere do símbolo (->>) que nos retorna um texto. Dessa forma, podemos utilizar o operador (->) para retornar um objeto aninhado e, com isso, associar os operadores. Essa versatilidade permite usarmos os operadores JSON combinados com funções agregadas do PostgreSQL, de forma a trazermos qualquer tipo de resultado.

Com relação ao tipo de dados serial, estes não são tipos verdadeiros, mas sim uma notação conveniente para a criação de colunas de identificadores exclusivos, bastante similares a colunas de auto incremento que podemos ver em outras bases de dados.

Um outro tipo de dados que podemos citar no momento é o Array, onde o PostgreSQL permite que colunas de uma tabela sejam definidas como matrizes multidimensionais de comprimento variável, aceitando Matrizes definidas pelo usuário, tipos de enumeração, ou mesmo tipos compostos, mas não matrizes de domínios, pois estas ainda não são suportadas.

Vejamos na Listagem 4 um exemplo de utilização de Arrays.

      CREATE TABLE funcionarios (nome character varying(100), sal_semestre  
      integer[], agenda text[][]);
      INSERT INTO funcionarios VALUES ('Edson Dionisio', '{2000, 2500, 3000, 
      3500}', '{{"Reunião", "Almoço"}, {"Projetos", "Reunião 2"}}');

Temos a criação de uma tabela funcionários na qual passamos o nome, um campo do tipo array de inteiros e uma matriz do tipo text. Em seguida, inserimos os dados e ao consultarmos recebemos o seguinte resultado apresentado na Figura 14.