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.

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.

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.

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.

Abertura do PostgreSQL
Figura 1. Abertura do PostgreSQL
Layout padrão do PostgreSQL
Figura 2. Layout padrão do PostgreSQL

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.

Abrir o Query Tool
Figura 3. Abrir o Query Tool

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.

Prompt de comando
Figura 4. Prompt de comando

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.

Atribuindo nome do Tablespace em properties
Figura 5. Atribuindo nome do Tablespace em properties
Adicionando o diretório para o Tablespace em definitions
Figura 6. Adicionando o diretório para o Tablespace em "definitions"

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.

Criando o banco de dados TesteDevmedia
Figura 7. Criando o banco de dados TesteDevmedia
 Atribuindo o Tablespace ao nosso banco de dados
Figura 8. Atribuindo o Tablespace ao nosso banco de dados

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.

Apresentação da árvore schema EmpresaDevmedia
Figura 9. Apresentação da árvore schema EmpresaDevmedia

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.

Adicionando chave primária
Figura 10. Adicionando chave primária
Atribuindo um nome para a chave primária
Figura 11. Atribuindo um nome para a chave primária
Selecionando a coluna para ser a chave primária
Figura 12. Selecionando a coluna para ser a chave primária

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; 
Listagem 1. Criação da tabela tb_funcionarios

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');
Listagem 2. Adicionando dados a nossa tabela

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;
Dados adicionados na tabela tb_funcionarios
Figura 13. Dados adicionados na tabela 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" }');
Listagem 3. Exemplo de utilização dos tipos de dados json e serial

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"}}');
Listagem 4. Utilizando Arrays

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.

Dados retornados da consulta
Figura 14. Dados retornados da consulta