PostgreSQL Prático/DDL/Criação e Exclusão de Bancos, Esquemas, Tabelas, Views, Constraints, etc

DDL é o conjunto de comandos SQL responsáveis pela definição dos dados, ou seja, pela criação de bancos, esquemas, tabelas, campos, tipos de dados, constraints, etc.

3.1 - Criação e exclusão de bancos, esquemas, tabelas, views, etc

Obs.: Nomes de objetos e campos não podem usar hífen (-). Alternativamente usar sublinhado (_).

campo-1 Inválido

campo_1 Válido

Nomes de Identificadores

Utiliza-se por convenção as palavras chaves do SQL em maiúsculas e os identificadores dos objetos que criamos em minúsculas.

Identificadores digitados em maiúsculas serão gravados em minúsculas, a não ser que venham entre aspas “”.

Revisões da Linguagem SQL

SQL – 1989

SQL – 1992

SQL – 1999

SQL – 2003

Divisões da SQL

DML – Linguagem de Manipulação de Dados

DDL – Linguagem de Definição de Dados

DCL – Linguagem de Controle de Dados (autorização de dados e licença de usuários para controlar quem tem acesso aos dados).

DQL – Linguagem de Consulta de Dados (Tem apenas um comando: SELECT).

Criar Banco

banco=# \h create database

Comando: CREATE DATABASE

Descrição: cria um novo banco de dados

Sintaxe:

CREATE DATABASE nome

   [ [ WITH ] [ OWNER [=] dono_bd ]

          [ TEMPLATE [=] modelo ]

          [ ENCODING [=] codificação ]

          [ TABLESPACE [=] tablespace ] ]

          [ CONNECTION LIMIT [=] limite_con ] ]

CREATE DATABASE nomebanco;

Excluindo Um Banco

DROP DATABASE nomebanco;

Listar os bancos existentes:

\l - - No psql

psql -l (no prompt)

SELECT datname FROM pg_database;

Quando se cria um novo banco de dados sem indicar o modelo, o que de fato estamos fazendo é clonar o banco de dados template1.

Criar um banco para outro usuário:

CREATE DATABASE nomebanco OWNER nomeuser;

createdb -O nomeusuario nomebanco

Obs.: requer ser superusuário para poder criar banco para outro usuário.

Criar Tabela

postgres=# \h create table

Comando: CREATE TABLE

Descrição: define uma nova tabela

Sintaxe:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE nome_tabela ( [

   { nome_coluna tipo_dado [ DEFAULT expressão_padrão ] [ restrição_coluna [ ... ] ]

   | restrição_tabela

   | LIKE tabela_pai [ { INCLUDING | EXCLUDING } DEFAULTS ] }

   [, ... ]

] )

[ INHERITS ( tabela_pai [, ... ] ) ]

[ WITH OIDS | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace ]

onde restrição_coluna é:

[ CONSTRAINT nome_restrição ]

{ NOT NULL |

 NULL |

 UNIQUE [ USING INDEX TABLESPACE tablespace ] |

 PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |

 CHECK (expressão) |

 REFERENCES tabela_ref [ ( coluna_ref ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]

   [ ON DELETE ação ] [ ON UPDATE ação ] }

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

e restrição_tabela é:

[ CONSTRAINT nome_restrição ]

{ UNIQUE ( nome_coluna [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |

 PRIMARY KEY ( nome_coluna [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |

 CHECK ( expressão ) |

 FOREIGN KEY ( nome_coluna [, ... ] ) REFERENCES tabela_ref [ ( coluna_ref [, ... ] ) ]

   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE ação ] [ ON UPDATE ação ] }

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Obs.: Atenção: nesta versão (8.1.3) WITH OID é opcional. As tabelas são criadas sem OID.

\d – visualizar tabelas e outros objetos

\d nometabela – visualizar estrutura da tabela

CREATE TABLE tabela (

campo1 integer,

campo2 text

);

Excluindo Tabela

DROP TABLE primeira_tabela;

Valor Default (padrão) Para Campos

Ao definir um valor default para um campo, ao ser cadastrado o registro e este campo não for informado, o valor default é assumido. Caso não seja declarado explicitamente um valor default, o valor nulo (NULL) será o valor default.

CREATE TABLE produtos (

  produto_no integer,

  descricao text,

  preco numeric DEFAULT 9.99

);

Constraints (Restrições)

CHECK

Ao criar uma tabela podemos prever que o banco exija que o valor de um campo satisfaça uma expressão

CREATE TABLE produtos (

  produto_no integer,

  descricao text,

  preco numeric CHECK (preco > 0)

);

Dando nome à restrição check. Isso ajuda a tornar mais amigáveis as mensagens de erro e a poder referenciar de uma consulta.

CREATE TABLE produtos (

  produto_no integer,

  descricao text,

  preco numeric CONSTRAINT preco_positivo CHECK (preco > 0)

);

CREATE TABLE produtos (

  produto_no integer,

  descricao text,

  desconto numeric CHECK (desconto > 0 AND desconto < 0.10),

  preco numeric CONSTRAINT preco_positivo CHECK (preco > 0),

  check (preco > desconto)

);

Constraint NOT NULL

Obrigar o preenchimento de um campo. Ideal para campos importantes que não devem ficar sem preenchimento. Mas devemos ter em mente que até um espaço em branco atende a esta restrição.

CREATE TABLE produtos (

  cod_prod   integer NOT NULL CHECK (cod_prod > 0),

  nome       text    NOT NULL,

  preco      numeric NOT NULL

);

Obs importante: nulos não são checados. UNIQUE não aceita valores repetidos, mas aceita vários nulos (já que estes não são checados). Cuidado com NULLs.

Unique Constraint

Obrigar valores exclusivos para cada campo em todos os registros

CREATE TABLE produtos (

  cod_prod   integer UNIQUE,

  nome       text,

  preco      numeric

);

CREATE TABLE produtos (

  cod_prod   integer,

  nome       text,

  preco      numeric,

  UNIQUE (cod_prod)

);

CREATE TABLE exemplo (

  a integer,

  b integer,

  c integer,

  UNIQUE (a, c)

);

CREATE TABLE produtos (

  cod_prod   integer CONSTRAINT unq_cod_prod UNIQUE,

  nome       text,

  preco      numeric

);

Evitando duplicação com nulos:
create table teste(
id serial not null,
parent integer null,
component integer not null
);

postgres=# create unique index naoduplic on teste using btree (component) where (parent is null);
Chaves Primárias (Primary Key)
A chave primária de uma tabela é formada internamente pela combinação das constraints UNIQUE e NOT NULL. Uma tabela pode ter no máximo uma chave primária. A teoria de bancos de dados relacional dita que toda tabela deve ter uma chave primária. O PostgreSQL não obriga que uma tabela tenha chave primária, mas é recomendável, a não ser que esteja criando uma tabela para importar de outra que contenha registros duplicados para tratamento futuro ou algo parecido ou apenas para testes.
CREATE TABLE produtos (

  cod_prod   integer UNIQUE NOT NULL,

  nome       text,

  preco      numeric

);

CREATE TABLE produtos (

  cod_prod   integer PRIMARY KEY,

  nome       text,

  preco      numeric

);

Composta (formada por mais de um campo)

CREATE TABLE exemplo (

  a integer,

  b integer,

  c integer,

  PRIMARY KEY (a, c)

);

Chave Estrangeira (Foreign Key)
Criadas com o objetivo de relacionar duas tabelas, mantendo a integridade referencial entre ambas. Especifica que o valor da coluna (ou grupo de colunas) deve corresponder a algum valor existente em um registro da outra tabela. Normalmente queremos que na tabela estrangeira existam somente registros que tenham um registro relacionado na tabela principal. Como também controla a remoção de registros na tabela principal que tenha registros relacionados na estrangeira.
Tabela principal

CREATE TABLE produtos (

  cod_prod   integer PRIMARY KEY,

  nome       text,

  preco      numeric

);

Tabela referenciada
CREATE TABLE pedidos (

  cod_pedido  integer PRIMARY KEY,

  cod_prod    integer,

  quantidade  integer,

  CONSTRAINT pedidos_fk FOREIGN KEY (cod_prod) REFERENCES produtos (cod_prod)

);

CREATE TABLE t0 (

a integer PRIMARY KEY,

b integer,

c integer,

FOREIGN KEY (b, c) REFERENCES outra_tabela	- - a coluna de destino será a PK

);

CREATE TABLE t1 (

a integer PRIMARY KEY,

b integer,

c integer,

FOREIGN KEY (b, c) REFERENCES outra_tabela (c1, c2)

);

Obs.: O número de colunas e tipo na restrição devem ser semelhantes ao número e tipo das colunas referenciadas.

Simulando ENUM no PostgreSQL

Para simular a constraint enum do MySQL, podemos usar a constraint check.
Dica do site "PostgreSQL & PHP Tutorials".

CREATE TABLE pessoa(
codigo int null primary key,
cor_favorita varchar(255) not null,
check (cor_favorita IN ('vermelha', 'verde', 'azul'))
);

INSERT INTO pessoa (codigo, cor_favorita) values (1, 'vermelha'); -- OK
INSERT INTO pessoa (codigo, cor_favorita) values (1, 'amarela'); -- Erro, amarelo não consta

Herança
Podemos criar uma tabela que herda todos os campos de outra tabela existente.

CREATE TABLE cidades (

  nome        text,

  populacao   float,

  altitude    int     -- (em pés)

);

CREATE TABLE capitais (

  estado      char(2)

) INHERITS (cidades);
capitais assim passa a ter também todos os campos da tabela cidades.

Segundo uma entrevista (vide DBFree Magazine No. 2) com a equipe de desenvolvimento do PostgreSQL, evite utilizar herança de tabelas.

Esquemas (Schema)
\dn – visualizar esquemas
Um banco de dados pode conter vários esquemas e dentro de cada um desses podemos criar várias tabelas. Ao invés de criar vários bancos de dados, criamos um e criamos esquemas dentro desse. Isso permite uma maior flexibilidade, pois uma única conexão ao banco permite acessar todos os esquemas e suas tabelas. Portanto devemos planejar bem para saber quantos bancos precisaremos, quantos esquemas em cada banco e quantas tabelas em cada esquema.
Cada banco ao ser criado traz um esquema public, que é onde ficam todas as tabelas, caso não seja criado outro esquema. Este esquema public não é padrão ANSI. Caso se pretenda ao portável devemos excluir este esquema public e criar outros. Por default todos os usuários criados tem privilégio CREATE e USAGE para o esquema public.
Criando Um Esquema
CREATE SCHEMA nomeesquema;

Excluindo Um Esquema
DROP SCHEMA nomeesquema;

Aqui, quando o esquema tem tabelas em seu interior, não é possível apagar dessa forma, temos que utilizar:

DROP SCHEMA nomeesquema CASCADE;
Que apaga o esquema e todas as suas tabelas, portanto muito cuidado.

Obs.: O padrão SQL exige que se especifique RESTRICT (default no PostgreSQL) OU CASCADE, mas nenhum SGBD segue esta recomendação.

Obs.: é recomendado ser explícito quanto aos campos a serem retornados, ao invés de usar * para todos, entrar com os nomes de todos os campos. Assim fica mais claro. Além do mais a consulta terá um melhor desempenho.

Acessando Tabelas Em Esquemas

SELECT * FROM nomeesquema.nometabela;

Privilégios Em Esquemas

\dp – visualizar permissões

REVOKE CREATE ON SCHEMA public FROM PUBLIC; - - Remove o privilégio CREATE de todos os usuários.

Obtendo Informações sobre os Esquemas:

\dn

\df current_schema*

SELECT current_schema();

SELECT current_schemas(true);

SELECT current_schemas(false);

Visões (views)

\dp – visualizar views e outros objetos

Que são VIEWS?

São uma maneira simples de executar e exibir dados selecionados de consultas complexas em bancos. Em que elas são úteis? Elas economizam grande quantidade de digitação e esforço e apresentam somente os dados que desejamos.

Criando Uma View

CREATE VIEW recent_shipments

       AS SELECT count(*) AS num_shipped, max(ship_date), title

          FROM shipments

          JOIN editions USING (isbn)

          NATURAL JOIN books AS b (book_id)

          GROUP BY b.title

          ORDER BY num_shipped DESC;

Usando Uma View

SELECT * FROM recent_shipments;

SELECT * FROM recent_shipments

       ORDER BY max DESC

       LIMIT 3;

Destruindo Uma View

DROP VIEW nomeview;

Criar as Tabelas que servirão de Base

CREATE TABLE client (

clientid SERIAL NOT NULL PRIMARY KEY,

clientname VARCHAR(255)

);

CREATE TABLE clientcontact (

contactid SERIAL NOT NULL PRIMARY KEY,

clientid int CONSTRAINT client_contact_check REFERENCES client(clientid),

name VARCHAR(255),

phone VARCHAR(255),

fax VARCHAR(255),

emailaddress VARCHAR(255)

);

CREATE VIEW client_contact_list AS

SELECT client.clientid, clientname, name, emailaddress FROM client, clientcontact

WHERE client.clientid = clientcontact.clientid;

Estando no psql e digitando \d podemos visualizar também as views.

O nome da visão deve ser distinto do nome de qualquer outra visão, tabela, seqüência ou índice no mesmo esquema. A visão não é materializada fisicamente. Em vez disso, a consulta é executada toda vez que a visão é referenciada em uma consulta. Fazer livre uso de visões é um aspecto chave de um bom projeto de banco de dados SQL.

As visões podem ser utilizadas em praticamente todos os lugares onde uma tabela real pode ser utilizada. Construir visões baseadas em visões não é raro. Atualmente, as visões são somente para leitura: o sistema não permite inserção, atualização ou exclusão em uma visão. É possível obter o efeito de uma visão atualizável criando regras que reescrevem as inserções, etc. na visão como ações apropriadas em outras tabelas. Para obter informações adicionais consulte o comando CREATE RULE.

CREATE VIEW vista AS SELECT 'Hello World';

é ruim por dois motivos: o nome padrão da coluna é ?column?, e o tipo de dado padrão da coluna é unknown. Se for desejado um literal cadeia de caracteres no resultado da visão deve ser utilizado algo como CREATE VIEW vista AS SELECT text 'Hello World' AS hello;

Veja capítulo 4 do Livro "Pratical PostgreSQL"

Supondo que uma consulta seja de particular interesse para uma aplicação, mas que não se deseja digitar esta consulta toda vez que for necessária, então é possível criar uma view baseada na consulta, atribuindo um nome a esta consulta pelo qual será possível referenciá-la como se fosse uma tabela comum.

CREATE VIEW minha_view AS

  SELECT cidade, temp_min, temp_max, prcp, data, localizacao

      FROM clima, cidades

      WHERE cidade = nome;

SELECT * FROM minha_visao;

Fazer livre uso de visões é um aspecto chave de um bom projeto de banco de dados SQL. As visões permitem encapsular, atrás de interfaces que não mudam, os detalhes da estrutura das tabelas, que podem mudar na medida em que as aplicações evoluem.

As visões podem ser utilizadas em praticamente todos os lugares onde uma tabela real pode ser utilizada. Construir visões baseadas em visões não é raro.

RULES

O comando CREATE RULE cria uma regra aplicada à tabela ou visão especificada.

Uma regra faz com que comandos adicionais sejam executados quando um determinado comando é executado em uma determinada tabela.

É importante perceber que a regra é, na realidade, um mecanismo de transformação de comando, ou uma macro de comando.

É possível criar a ilusão de uma visão atualizável definindo regras ON INSERT, ON UPDATE e ON DELETE, ou qualquer subconjunto destas que seja suficiente para as finalidades desejadas, para substituir as ações de atualização na visão por atualizações apropriadas em outras tabelas.

Existe algo a ser lembrado quando se tenta utilizar regras condicionais para atualização de visões: é obrigatório haver uma regra incondicional INSTEAD para cada ação que se deseja permitir na visão. Se a regra for condicional, ou não for INSTEAD, então o sistema continuará a rejeitar as tentativas de realizar a ação de atualização, porque acha que poderá acabar tentando realizar a ação sobre a tabela fictícia da visão em alguns casos.

banco=# \h create rule

Comando: CREATE RULE

Descrição: define uma nova regra de reescrita

Sintaxe:

CREATE [ OR REPLACE ] RULE nome AS ON evento

   TO tabela [ WHERE condição ]

   DO [ ALSO | INSTEAD ] { NOTHING | comando | ( comando ; comando ... ) }

O comando CREATE RULE cria uma regra aplicada à tabela ou visão especificada.

evento

Evento é um entre SELECT, INSERT, UPDATE e DELETE.

condição

Qualquer expressão condicional SQL (retornando boolean). A expressão condicional não pode fazer referência a nenhuma tabela, exceto NEW e OLD, e não pode conter funções de agregação.

INSTEAD

INSTEAD indica que os comandos devem ser executados em vez dos (instead of) comandos originais.

ALSO

ALSO indica que os comandos devem ser executados adicionalmente aos comandos originais. Se não for especificado nem ALSO nem INSTEAD, ALSO é o padrão.

comando

O comando ou comandos que compõem a ação da regra. Os comandos válidos são SELECT, INSERT, UPDATE, DELETE e NOTIFY.

Dentro da condição e do comando, os nomes especiais de tabela NEW e OLD podem ser usados para fazer referência aos valores na tabela referenciada. O NEW é válido nas regras ON INSERT e ON UPDATE, para fazer referência à nova linha sendo inserida ou atualizada. O OLD é válido nas regras ON UPDATE e ON DELETE, para fazer referência à linha existente sendo atualizada ou excluída.

Obs.: É necessário possuir o privilégio RULE na tabela para poder definir uma regra para a mesma.

Exemplos:

CREATE RULE me_notifique AS ON UPDATE TO datas DO ALSO NOTIFY datas;

CREATE RULE r1 AS ON INSERT TO TBL1 DO

(INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);

CREATE RULE "_RETURN" AS ON SELECT TO minha_visão DO INSTEAD

SELECT * FROM minha_tabela; -- Ao invés de selecionar da visão seleciona da tabela.

Banco de dados modelo intocado

Existe um modelo de banco de dados que sempre se preserva original, que é o template0. O template template1 pode incorporar objetos e acaba algumas vezes ficando inviável seu uso como modelo. Quando isso acontece podemos substitui-lo com uma cópia do template0.

Criando banco de dados baseado em outro modelo

CREATE DATABSASE nomebanco TEMPLATE template0;

createdb -T template0 nomebanco

Recriando o template1

\c testes

postgres=# UPDATE pg_database SET datistemplate=false WHERE datname='template1';

testes=# DROP DATABASE template1;

testes=# CREATE DATABASE template1 TEMPLATE template0 ENCODING 'latin1';

testes=# \c template1

template1=# VACUUM FULL FREEZE;

template1=# VACUUM FULL;

template1=# UPDATE pg_database SET datistemplate=true WHERE datname='template1';

Agora temos um template1 original e limpo.