# 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
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 ( [
[ 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 |
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
e restrição_tabela é:
[ CONSTRAINT nome_restrição ]
{ UNIQUE ( nome_coluna [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
[ 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 (
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 (
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 (
CREATE TABLE produtos (
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 (
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 (
CREATE TABLE produtos (
CREATE TABLE exemplo (
CREATE TABLE produtos (
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 (
CREATE TABLE produtos (
Composta (formada por mais de um campo)
CREATE TABLE exemplo (
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 (
Tabela referenciada CREATE TABLE pedidos (
CREATE TABLE t0 (
CREATE TABLE t1 (
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 (
CREATE TABLE capitais (
) 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
Usando Uma View
SELECT * FROM recent_shipments;
SELECT * FROM recent_shipments
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 * 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
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.