Debian, PostgreSQL e o (concluido)

[hugo]

[Você precisa estar registrado e conectado para ver esta imagem.]

O PostgreSQL é um sistema para gerenciamento de banco de dados relacional, ou SGBD, e é desenvolvido sob uma licença nos padrões BSD, servindo como alternativa a outros gerenciadores de banco de dados, livres (MySQL, FireBird) ou proprietários (Oracle, SyBase, Microsoft SQL Server). A licença garante o livre uso, a cópia, distribuição ou modificação do software e de toda a sua documentação, desde que mantida a licença.

Dica quente
Instalando o debian 5.04 e marcando a opcao banco de dados sql o postgresql 8.3 ja sera instalado

ATENCAO:

Código:

O material de onde estou estudando trata do postgreesql 8.1, logo estou adaptando todo material para o 8.3, se aparecer alguma citacao de 8.1 considerem 8.3!

Código:

Para saber a versão instalada de um pacote basta utilizar o comando:
dpkg -s <nome do pacote>


Créditos:
cdtc

[hugo]

Um pouco da história do PostgreSQL

A origem do PostgreSQL está ligada ao projeto Ingres da Universidade de Berkeley, na Califórnia. O líder desse projeto era Michael Stonebreaker, um dos pioneiros das bases de dados relacionais. Em 1985, na tentativa de solucionar muitos dos problemas que haviam na época acerca de bancos de dados relacionais, Stonebreaker deu início, na própria Universidade, a um novo projeto post-Ingres, ou em bom português "pós-Ingres", que viria a ser chamado de Postgres.

Pouco tempo depois, em 1993, a Universidade de Berkeley abandonou o projeto Postgres, e este, apoiado pela sua licença BSD, passou a ser sustentado pela comunidade. Em 1995, foi adicionado suporte à linguagem SQL, substituindo a antiga QUEL (linguagem utilizada no Ingres) e o projeto passou a se chamar, inicialmente, Postgres95 e logo depois de PostgreSQL.

Assim, hoje, como muitos outros softwares livres, o PostgreSQL não é desenvolvido por uma só empresa, mas mantido por uma comunidade global de desenvolvedores e de empresas.

[hugo]

Características do PostgreSQL

O PostgreSQL é um sistema gerenciador de banco de dados objeto-relacional (SGBDOR), baseado no POSTGRES versão 4.2 desenvolvido pelo Departamento de Ciência da Computação da Universidade da Califórnia em Berkeley. O POSTGRES foi pioneiro em vários conceitos que somente se tornaram disponíveis muito mais tarde em alguns sistemas de banco de dados comerciais.

O PostgreSQL é um descendente de código fonte aberto deste código original de Berkeley. É suportada grande parte do padrão SQL:2003, além de serem oferecidas muitas funcionalidades modernas, como:

* comandos complexos;
* chaves estrangeiras;
* gatilhos;
* visões;
* integridade transacional;
* controle de simultaneidade multiversão (MVCC).

Além disso, o PostgreSQL pode ser estendido pelo usuário de muitas maneiras como, por exemplo, adicionando novos:

* tipos de dados;
* funções;
* operadores;
* funções de agregação;
* métodos de índice;
* linguagens procedurais.

Devido à sua licença liberal, o PostgreSQL pode ser utilizado, modificado e distribuído por qualquer pessoa para qualquer finalidade, seja privada, comercial ou acadêmica, livre de encargos.

Na versão 8.3, o PostgreSQL possui suporte nativo para Microsoft Windows e Sistemas Unix e Linux.

[hugo]

Instalando com o apt-get

Utilizando a ferramenta apt-get do Debian, instalaremos a mais nova versão do PostgreSQL, a versão 8.3. Adicionamos os endereços desses repositórios no arquivo de repositórios do apt-get. O nome do arquivo é "sources.list" e está localizado na pasta /etc/apt. Apenas adicione as seguintes linhas no arquivo sources.list e salve.

Código:

deb http://[Você precisa estar registrado e conectado para ver este link.] stable main contrib non-free
deb-src http://[Você precisa estar registrado e conectado para ver este link.] stable main contrib

O apt-get deverá ser capaz de reconhecer sem problemas esses repositórios.A seguir devemos atualizar a lista de pacotes disponíveis. Para isso digite, como root:

Código:

# apt-get update

Com as listas atualizadas, podemos baixar e instalar o PostgreSQL.
Entre com o seguinte comando, no terminal, novamente como root:

Código:

# apt-get install postgresql-8.3

Isto deverá instalar os seguintes pacotes:

* postgresql-8.3;
* postgresql-client-8.3;
* postgresql-common.

Baixando um total de mais ou menos 17MB. É possível que o apt-get precise instalar outros pacotes que são pré-requisitos para o postgreSQL, no entanto, não se preocupe com isso. Apenas prossiga com a instalação.

Quando se instala utilizando a ferramenta apt-get, toda configuração do postgreSQL é feita automaticamente. Logo, ao terminar de instalar já deve ser possível testar o postgreSQL.

Digite os comandos abaixo:

Código:

$ su
# su postgres

Durante a configuração do postgreSQL, o super-usuário postgres é automaticamente criado. Podemos utilizá-lo aqui para testar a nossa instalação.

Após logar como postgres. Inicialize o terminal 'psql', que já vem instalado com o postgreSQL, e indique o banco de dados que será utilizado: 'template1':

Código:

$ psql template1

Esse banco de dados 'template1' é um banco de dados padrão utilizado como base para se criar outros bancos de dados.

Se tudo ocorrer bem, a saída deverá ser bem parecida com:

[Você precisa estar registrado e conectado para ver esta imagem.]

Indicando que a instalação foi um sucesso.
Para sair, apenas digite \q e tecle ENTER.

[hugo]

Instalando a partir do código-fonte

Caso desejar, você pode compilar inteiramente o programa, ao invés de instalar os binários com o apt-get. Para isso, baixe o código na seção de downloads na página do postgreSQL [Você precisa estar registrado e conectado para ver este link.] Na página, escolha a versão a baixar (no nosso caso, 8.3) e baixe o arquivo postgresql-8.3.tar.bz2.
Salve o arquivo em qualquer pasta de sua preferência. No terminal, entre na pasta onde você salvou o arquivo e descompacte-o:

Código:

$ tar xjvf postgresql-8.3.tar.bz2

Isto criará uma nova pasta chamada postgresql-8.3/ e todos os arquivos serão descompactados para dentro dessa pasta.

Agora inicie o script configure, que irá configurar os parâmetros necessários para a compilação, de acordo com a sua máquina.

Código:

$ cd postgresql-8.3
$ ./configure

Vale lembrar que a Biblioteca GNU Readline (utilizada para edição da linha de comando e histórico de comandos) é utilizada por padrão durante a execução do script configure. Se você não possui esta biblioteca instalada, ou por algum motivo ela não foi reconhecida durante a configuração, voce pode desabilitá-la com a opção --without-readline. O comando ficaria assim:

Código:

$ ./configure --without-readline

Em caso de sucesso ao terminar o script, podemos compilar o programa:

Código:

$ make

E instalar o programa no sistema (este comando deve ser executado como root):

Código:

$ su
# make install

Quando se instala a partir do código-fonte, é preciso configurar o postgreSQL manualmente. Primeiro deve-se criar o usuário postgres, que é usado pelo servidor postgreSQL.

Código:

# adduser postgres

O nome postgres é comumente usado, porém não é obrigatório, podendo ser mudado para outro qualquer. A seguir devemos escolher um espaço em disco no qual o postgreSQL irá armazenar os dados. A escolha é totalmente livre, porém a pasta /usr/local/pgsql/data é comumente usada. É ela que iremos utilizar aqui. O usuário criado acima(postgres) precisa ser o dono desta pasta. Primeiro devemos criá-la:

Código:

# mkdir /usr/local/pgsql/data

E alteramos o dono da pasta para postgres:

Código:

# chown postgres /usr/local/pgsql/data

Feito isso, devemos informar ao postgreSQL que esta pasta será usada como local de armazenamento dos dados. Isto deve ser feito como postgres:

Código:

# su postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

Agora podemos iniciar o servidor sem maiores problemas com o comando:

Código:

$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &

E testá-lo:

Código:

$ psql template1

A saída é a mesma da página anterior.

[hugo]

Estrutura do postgreSQL

Antes de tudo devemos entender o funcionamento da estrutura do postgreSQL. O postgreSQL adota um modelo conhecido como cliente/servidor. Ao acessar um banco de dados utilizando o postgreSQL, faz-se uso de no mínimo dois programas:

* O servidor. É ele quem gerencia os bancos de dados, recebe pedidos dos seus clientes, acessando e retornando os devidos dados. No postgreSQL, o servidor se chama postmaster.
* O cliente (front-end). É o programa do usuário. Através dele que é possível realizar operações nos bancos de dados. O psql é um front-end que já vem instalado com o postgreSQL e será usado aqui para fins de aprendizado.

Este modelo permite o acesso remoto ao servidor através de uma rede. Antes de tudo devemos entender o funcionamento da estrutura do postgreSQL. O postgreSQL adota um modelo conhecido como cliente/servidor.

[hugo]

Entendendo o psql

O psql é um front-end (programa cliente) do postgreSQL, e já foi utilizado por nós. Através dele é que fazemos "pedidos" ao servidor, requisitando todo o tipo de operações relacionadas aos bancos de dados.

O psql possui um conjunto de comandos próprios que são bem úteis para a utilização do programa, esses comandos são chamados de meta-comandos.

Abaixo está uma lista reduzida de alguns dos meta-comandos mais usados:

* \q – sai do psql.
* \c [NOME] – conecta a outro banco de dados. Por exemplo:
* \c template0 : conecta ao banco de dados template0;
* \l – lista todos os bancos de dados disponibilizados.
* \d [NOME] – descreve uma tabela, índice, visão ou sequência. Por exemplo:
* \d tabela : mostra todas as informações sobre as colunas da tabela 'tabela';
* \du – lista todos os usuários cadastrados no servidor;
* \dg – lista todos os grupos cadastrados no servidor;
* \dT – lista todos os tipos de dados.
* \h [NOME] – Mostra a sintaxe do comando SQL. Por exemplo:
* \h SELECT : mostra a sintaxe do comando SELECT;
* \? – lista todos os comandos disponíveis.

Recomendamos a testar os meta-comandos acima na medida do possível, familiarizando-se com eles. Como você pode notar, todos os comandos do psql começam com a barra invertida \ . Logo, todo comando que começar com \textbackslash daqui para frente será um meta-comando do psql. Para ver a lista de todos os meta-comandos apenas digite '\?' no prompt do psql.

Observe que meta-comandos do psql não necessita do delimitador 'ponto-e-vírgula' .

[hugo]

Criando bancos de dados

Podemos criar novos bancos de dados utilizando o prompt de comando do sistema ou o prompt do psql.

Utilizando o prompt do sistema, rodamos o script createdb para criar um novo banco de dados. Fazemos o seguinte:

Código:

$ createdb nome_do_banco

É necessário salientar que só é possível criar um banco de dados com um usuário com tais permissões (Veremos mais adiante como adicionar ou alterar usuários.) No nosso caso, ainda não vimos como criar novos usuários e lidar com suas permissões, portanto crie um novo banco de dados com o usuário postgres.

Para criar novos bancos de dados através do psql, fazemos uso da linguagem SQL:

Código:

CREATE DATABASE nome_do_banco;

Note a presença do delimitador ; no fim da query.
A saída, em caso de sucesso e em ambos os casos, será:

Código:

CREATE DATABASE

[hugo]

Acessando bancos de dados

Após criar o banco, obviamente você vai querer usá-lo. Para usar um banco de dados, deve-se iniciar o psql já indicando qual banco deverá ser usado. Lembram do comando psql template1?

Esse comando iniciava o psql utilizando o banco de dados padrão template1. Com um mínimo de esforço, pode-se deduzir o comando para iniciar o psql com o banco desejado:

Código:

$ psql nome_do_banco

Se você já inicializou o psql, é possível mudar o banco de dados que está sendo utilizado. Para fazer isso digite, no prompt do psql, o seguinte\ comando:

Código:

\c nome_do_banco

Como já vimos anteriormente, este é um comando próprio do psql. Caso ocorra como esperado, a seguinte saída será mostrada:

Você está conectado ao banco de dados "nome\_do\_banco" agora.

Removendo bancos de dados

Depois de usar um banco de dados, você pode não querer mais usá-lo. É possível portanto removê-lo. Novamente é possível fazer isso pela linha de comando do sistema, ou do psql.

Pelo prompt do sistema:

Código:

$ dropdb nome_do_banco

Pelo prompt do psql:

Código:

DROP DATABASE nome_do_banco;

A saída, em caso de sucesso e em ambos os casos, será:

Código:

DROP DATABASE

O comando dropdb apaga todos os arquivos relacionados ao banco de dados em questão, e não é reversível.

[hugo]

Criando tabelas

Em um banco de dados relacional as informações são armazenadas em tabelas, que podem ou não ter relação entre si. Vamos criar tabelas simples, somente para aprendermos a sintaxe. Mais à frente veremos como criar tabelas um pouco mais complexas.

Para se criar uma tabela, usamos o comando CREATE TABLE. Vejamos como fazer isso no psql:

Código:

CREATE TABLE tabela_de_teste (
valor1 int,
valor2 varchar(80),
valor3 int
);

Este comando cria uma tabela de nome 'tabela_de_teste' com as seguintes colunas: valor1, valor2 e valor3; cada uma com um tipo de dado específico. A saída deverá ser:

Código:


CREATE TABLE

A sintaxe do comando é:

Código:

CREATE TABLE nome_da_tabela (nome_da_coluna tipo_da_coluna );

Claro que normalmente se tem mais de uma coluna, portanto, separa-se as colunas com vírgulas, especificando o tipo de cada uma delas:

Código:

CREATE TABLE nome_da_tabela (
nome_da_coluna1 tipo_da_coluna1,
nome_da_coluna2 tipo_da_coluna2,
.
.
.
nome_da_colunaM tipo_da_colunaM,
nome_da_colunaN tipo_da_colunaN
);

Podemos ver se a tabela foi criada da forma desejada com o comando \d do psql. Apenas digite:

Código:

\d tabela_de_teste

Isto retornará uma tabela com as colunas de 'tabela\_de\_teste' e os respectivos tipos de dados e modificadores.

Uma observação importante: Ao digitar os comandos no psql, não é necessário que esteja tudo em uma só linha. É totalmente possível pular linhas em um mesmo comando, isso se deve ao fato de ser necessária a presença do delimitador ; em comandos SQL. O psql não executará o comando enquanto não encontrar o delimitador ; .

É possível, ainda, atribuir um valor padrão para qualquer uma das colunas criadas, fazemos isto na hora de criar uma tabela:

Código:

CREATE TABLE numeros (valor1 integer,
valor2 integer DEFAULT 0 );

Isto faz com que o valor 0 seja automaticamente inserido na coluna valor2, caso nenhum valor seja especificado.

[hugo]

Tipos de dados

Cada coluna de uma tabela guarda uma informação. Pode ser, por exemplo, uma data, um nome ou um número. Podemos querer armazenar valores em dinheiro, ou valores booleanos como Verdadeiro ou Falso. Para tratar os diferentes tipos de valores, existem diferentes tipos de dados. Estes tipos de dados informam o postgreSQL qual o tipo de informação está armazenada em uma dada coluna. Logo, é necessário especificar qual o tipo de dado de cada coluna em uma tabela. Vimos isso na página anterior, com o comando:

Código:

CREATE TABLE tabela_de_teste ( valor1 int, valor2 varchar(80),
valor3 int );

No postgreSQL, podemos separar os tipos de dados disponíveis em certos grupos lógicos, veremos a seguir alguns dos grupos mais usados:

Tipos Numéricos:

[Você precisa estar registrado e conectado para ver esta imagem.]

Clique com botao direito do mouse sobre a imagem e clique em "exibir imagem".

Cabe dizer aqui que os tipos serial e bigserial não são realmente tipos de dados. Mas apenas uma notação conveniente para criar colunas identificadoras com valores únicos e auto-incrementação (Outros bancos de dados utilizam a propriedade AUTO_INCREMENT para criar colunas com auto-incrementação).

Tipos de dados Data Hora

[Você precisa estar registrado e conectado para ver esta imagem.]

Clique com botao direito do mouse sobre a imagem e clique em "exibir imagem".

Tipos de dados Cadeia (String)

[Você precisa estar registrado e conectado para ver esta imagem.]

Clique com botao direito do mouse sobre a imagem e clique em "exibir imagem".

Para a declaração dos tipos pode-se usar tanto o nome, como o nome interno do tipo.
Isto fica à sua escolha. Há ainda tipos binários, tipos geométricos, tipos para endereços de rede, entre outros, que não foram explicitados aqui.
Para um lista completa dos tipos de dados suportados, digite o comando:

Código:

\dT+

Já vimos este comando, porém agora entramos com um sinal de +. Este sinal serve apenas para listar os tipos de dados com mais informações. Caso deseje saber mais sobre tipos de dados visite: [Você precisa estar registrado e conectado para ver este link.]

[hugo]

Restrições (Constraints)

Ao criar uma tabela podemos impor algum tipo de restrição, por exemplo, ao criar uma coluna que represente alguma quantidade é desejável que não se entre com valores menores do que zero. Talvez queiramos que um dado valor seja único, ou seja, não se repita em outras linhas da tabela. Tudo isso é possível através do uso de restrições.

Restrições de verificação:

Podemos fazer a verificação de um dado antes que ele seja inserido na tabela. Essa verificação será uma propriedade da tabela em questão e sempre será checada. Para fazer isso utilizamos a palavra-chave CHECK:

Código:

CREATE TABLE coleção (quantidade integer CHECK (quantidade >= 0) );

Com esse comando criamos uma tabela com a coluna quantidade, aonde a quantidade não poderá ser menor do que zero.
Podemos ainda fazer referência a outros valores da tabela na hora da verificação:

Código:

CREATE TABLE produtos( preço numeric, preço_desconto numeric, CHECK (preço > preço_desconto) );

Onde o preço entrado deve ser maior do que o preço com desconto.

Restrição de não-nulo:

A palavra-chave NOT NULL especifica que uma coluna não pode assumir um valor nulo. Não se deve confundir um valor nulo com o valor 0. Uma coluna com valor nulo é simplismente uma coluna vazia.

Código:

CREATE TABLE produtos (identificador varchar(4) NOT NULL,
nome varchar(80) NOT NULL,
preço numeric );

Este comando cria uma tabela onde todas as linhas devem possuir um identificador e um nome. Não é possível inserir um dado sem um identificador e sem um nome, é possível, porém, inserir um dado sem preço.

Restrição de unicidade:

Este tipo de restrição garante que o valor de uma linha é único na coluna, ou seja, não há valores repetidos na coluna que possui esta propriedade. Vejamos um exemplo para facilitar o entendimento:

Código:

CREATE TABLE unicidade (identificador integer UNIQUE,
nome varchar(80)
);

Este comando cria uma tabela, na qual a coluna identificador possui uma propriedade de unicidade. Logo, não é possível inserir duas linhas nesta tabela com o mesmo identificador. Cada linha terá um campo identificador diferente das outras.
É possível explicitar as colunas que terão propriedade UNIQUE fazendo da seguinte forma:

Código:

CREATE TABLE unicidade (identificador integer,
nome varchar(80),
UNIQUE (identificador)
);

e é claro, fazer isso com várias colunas:

Código:

CREATE TABLE exemplo (A integer,
B integer,
C integer,
D integer,
UNIQUE (A, C)
);


Chaves primárias e estrangeiras:

O conceito de chave primária é uma coluna que identifique cada linha de uma tabela, portanto, esta coluna deve ser única e não pode ter valores nulos. No postgreSQL, PRIMARY KEY é uma junção de UNIQUE com NOT NULL, logo:

Código:

CREATE TABLE exemplo (
chave integer PRIMARY KEY
);

é, em termos, igual a (possuem as mesmas restrições)

Código:

CREATE TABLE exemplo (
chave integer NOT NULL UNIQUE
);

Já uma chave estrangeira é uma referência a uma linha de outra tabela, ou seja, o valor de uma coluna de chave estrangeira é exatamente o valor da coluna que ela referencia. Vamos a um exemplo para facilitar o entendimento:
Suponha que possuímos um programa de agendamento que suporta vários usuários. Existiria uma tabela para todos os usuários cadastrados no programa:

Código:

CREATE TABLE tabela_usuarios (
cod_usuario integer PRIMARY KEY,
nome varchar(80),
senha varchar(10)
);

e poderíamos ter também uma outra tabela para agendar os compromissos dos usuários:

Código:

CREATE TABLE tabela_compromissos (
cod_compromisso integer PRIMARY KEY,
usuario integer REFERENCES tabela_usuarios(cod_usuario),
data date
);

Com esta montagem de tabelas, não é possível inserir um compromisso com usuário que não existe na tabela tabela_usuarios. Ou seja, no contexto do nosso programa, somente usuários cadastrados podem inserir compromissos. Dizemos que a tabela tabela_compromissos é a tabela que faz referência, enquanto que a tabela tabela_usuarios é a tabela referenciada.

A tabela tabela_compromissos poderia ser simplificada para:

Código:

CREATE TABLE tabela_compromissos (
cod_compromisso integer PRIMARY KEY,
usuario integer REFERENCES tabela_usuarios,
data date
);

Isto porque tabela_usuarios possui uma chave primária, e a palavra-chave REFERENCES toma como referência uma coluna de chave primária, caso uma coluna não seja especificada.

A partir daqui poderíamos imaginar: "E se fosse removido um usuário da tabela 'tabela_usuarios', mas este possuísse compromissos na tabela 'tabela_compromissos'?"

Poderíamos ou impedir que tal usuário fosse apagado do programa ou, então, que todos os seus compromissos fossem apagados também.\
Para isso, utilizamos ON DELETE, CASCADE e RESTRICT:
Faríamos, ao invés do comando anterior:

Código:

CREATE TABLE tabela_compromissos (
cod_compromisso integer PRIMARY KEY,
usuario integer REFERENCES tabela_usuarios ON DELETE CASCADE,
data date
);

Isto apagaria todos os compromissos do usuário, caso o usuário seja apagado da tabela tabela_usuarios.
Para impedir que o usuário seja apagado se ainda houver compromissos, simplesmente trocamos a palavra CASCADE por RESTRICT. Ficaria assim: ON DELETE RESTRICT.

Além de DELETE e RESTRICT existem ainda,
NO ACTION: gera um erro;
SET DEFAULT: muda o valor da coluna que faz referência para o valor padrão definido;
SET NULL: muda o valor da coluna que faz referência para um valor nulo.

[hugo]

Modificando tabelas

Ao criar uma tabela, é possível que a mesma não fique exatamente da forma como era desejada, seja por falta de especificação ou esquecimento na hora de construir a tabela. Por causa disso, é necessário que seja possível modificar uma tabela já existente, já que destruir e criar a tabela novamente talvez não seja uma opção muito viável.

Com o postgreSQL é possível adicionar novas colunas a uma tabela, remover colunas, alterar o tipo de dado de uma coluna, renomear a coluna e a tabela. Pode-se ainda modificar constraints e valores padrões de uma coluna.

Todas essas alterações listadas são feitas através do comando SQL, ALTER TABLE. Veremos a seguir:

Adicionando colunas:

Usamos ADD COLUMN para adicionar novas colunas. Ao adicionar novas colunas, a sintaxe usada é a mesma de quando criamos uma nova tabela, ou seja, informamos o nome da coluna e depois o tipo:

Código:

ALTER TABLE nome_da_tabela ADD COLUMN nova_coluna tipo_da_coluna;

Pode-se ainda definir restrições com a sintaxe usual de restrições ou com um valor padrão.

Removendo colunas:

Para remover uma coluna de uma tabela usamos DROP COLUMN seguido do nome da coluna a ser apagada:

Código:

ALTER TABLE nome_da_tabela DROP COLUMN coluna_a_remover;

Todos os dados e restrições desta coluna são apagados, porém se esta é uma coluna referenciada uma mensagem de erro surgirá.
Para apagar uma coluna referenciada e todos os objetos que dependem dela, usamos:

Código:

ALTER TABLE nome_da_tabela DROP COLUMN coluna_a_remover CASCADE;

Adicionar restrições:
Para adicionar restrições, fazemos:

Código:

ALTER TABLE nome_da_tabela ADD restrição;

Por exemplo:

Código:

ALTER TABLE nome_da_tabela ADD CHECK(coluna > 0);
ALTER TABLE nome_da_tabela ADD UNIQUE(coluna);
ALTER TABLE nome_da_tabela ADD PRIMARY KEY(nome_da_coluna);
ALTER TABLE nome_da_tabela ADD FOREIGN KEY(nome_da_coluna) REFERENCES tabela_referenciada;

Para o caso da restrição de não-nulo, a sintaxe é um pouco diferente:

Código:

ALTER TABLE nome_da_tabela ALTER COLUMN nome_da_coluna SET NOT NULL;

Remover restrições:

Para remover uma restrição é necessário saber o seu nome. Para isso utilize o comando \ d nome_da_tabela. Isto mostrará a tabela em questão incluindo todas as suas restrições com seus respectivos nomes.
De posse do nome da restrição a ser retirada, simplismente faça:

Código:


ALTER TABLE nome_da_tabela DROP CONSTRAINT nome_da_restrição;

Da mesma forma que adicionar uma restrição não-nulo, remover uma restrição de não-nulo é um pouco diferente:

Código:

ALTER TABLE nome_da_tabela ATER COLUMN nome_da_coluna DROP NOT NULL;

Alterar o valor padrão:
Simplesmente faça:

Código:

ALTER TABLE nome_da_tabela ALTER COLUMN nome_da_coluna SET DEFAULT valor_padrão;


Alterando o tipo:
Para alterar o tipo de dado de uma coluna usamos:

Código:

ALTER TABLE nome_da_tabela ALTER COLUMN nome_da_coluna TYPE novo_tipo;

Este comando só funciona se for possível converter todos os dados desta coluna para o novo tipo de dado.


Renomear uma coluna:
Para alterar o nome de uma coluna, simplismente faça:

Código:

ALTER TABLE nome_da_tabela RENAME COLUMN nome_da_coluna TO novo_nome;

Renomear a tabela:
E finalmente, para alterar o nome da tabela, faça:

Código:

ALTER TABLE nome_da_tabela RENAME TO novo_nome;

Removendo tabelas
Depois de todo o trabalho de criar as tabelas, eventualmente você vai precisar apagar algumas delas. Fazer isso é bem simples, veja a seguir:

Código:

DROP TABLE nome_da_tabela;

Caso existam objetos que referenciem esta tabela, a mesma não será apagada. Para removê-la e também os objetos que a referenciam, faça:

Código:

DROP TABLE nome_da_tabela CASCADE;

[hugo]

Inserindo dados na tabela

Depois de criar sua tabela, você precisa povoá-la. As instruções COPY e INSERT são úteis para isso.
Suponha que você crie a seguinte tabela:

Código:

CREATE TABLE tempo (
cidade varchar(80),
maximo numeric DEFAULT 40,
minimo numeric,
data date
);

Como você está começando com uma tabela vazia, uma forma simples de povoá-la é criar um arquivo texto contendo uma linha para cada um de seus animais, e depois carregar o conteúdo do arquivo para a tabela com uma simples instrução.

Você pode criar um arquivo texto 'dados.txt' contendo um registro por linha, com valores separados por tabulações e na mesma ordem em que as colunas foram listadas na instrução CREATE TABLE. Para valores em falta, você pode usar valores NULL. Para representá-lo em seu arquivo texto, use \ N (barra invertidam N maíusculo).

Para carregar o arquivo texto 'dados.txt' na tabela, você deve explicitar o caminho completo para o arquivo. Use este comando:

Código:

COPY nome_da_tabela FROM 'caminho_do_arquivo';

Por exemplo:

Código:

COPY tempo FROM '/home/usuario/Desktop/dados.txt';

Quando você desejar adicionar novos registros um a um, a instrução INSERT é usada. Na sua forma mais simples, você fornece valores para cada coluna, na ordem em que as colunas foram listadas na instrução CREATE TABLE.
Você pode adicionar um registro utilizando uma instrução INSERT desta forma:

Código:

INSERT INTO tempo VALUES('Brasilia', 32, 26, 'Mar-06-2006');

Que representam as temperaturas máximas e mínimas em Brasília, no dia 06 de março de 2006. Observe como o formato da data é utilizado. Você pode não estar acostumado com este formato, mas este não é o único formato que pode ser utilizado. Veremos mais adiante que formatos utilizar para inserir os diferentes tipos de dados em uma tabela de forma correta.
Veja também que o formato para a inserção de cadeias (strings) deve ser feito com aspas simples, enquanto valores numéricos não.

É possível também inserir dados em colunas específicas da tabela, bastando para isso identificá-la:

Código:

INSERT INTO tempo(cidade, data) VALUES ('Goiania', 'Jan-02-2006');

Quando se faz isso, deve-se ficar atento a propriedade NOT NULL das colunas. Se existir uma coluna NOT NULL um valor deve, obrigatoriamente, ser inserido. Portanto, a instrução acima não funcionaria caso as colunas máximo e mínimo tivessem a restrição NOT NULL.

Para contornar isso pode-se definir um valor padrão para a coluna. Assim, se não for especificado um valor a ser inserido, o valor padrão será automaticamente inserido naquela coluna.

Podemos ainda utilizar as palavras-chave default e NULL para inserir os valores padrão e nulo, respectivamente:

Código:

INSERT INTO tempo VALUES ('Rio', default, 34, NULL);

A instrução faria com que fosse inserido na tabela seguinte registro:

[Você precisa estar registrado e conectado para ver esta imagem.]

Clique com o botao direito do mouse sobre a imagem e clique em "Exibir Imagem".

Formato de datas:

[Você precisa estar registrado e conectado para ver esta imagem.]

Clique com o botao direito do mouse sobre a imagem e clique em "Exibir Imagem".

Formato de horas:

[Você precisa estar registrado e conectado para ver esta imagem.]

Clique com o botao direito do mouse sobre a imagem e clique em "Exibir Imagem".

Insira alguns registros na tabela tempo, iremos utilizá-la novamente em lições seguintes. Use a sua imaginação!

[hugo]

A instrução SELECT

Agora que possuímos uma tabela povoada, vamos precisar recuperar informações dessa tabela. Fazemos isto com a instrução SELECT. Esta é sua sintaxe:

Código:

SELECT colunas FROM nome_da_tabela WHERE condições;

onde colunas é uma lista das colunas que se deseja ver, nome_da_tabela é a tabela da qual se deseja obter as informações, e condições são condições que as informações devem satisfazer para que sejam selecionadas. A parte 'WHERE condições' da instrução acima não é obrigatória.
Vamos utilizar a tabela tempo criada na lição anterior. Para selecionar todas as colunas da tabela tempo, fazemos o seguinte:

Código:

SELECT * FROM tempo;

O retorno é uma tabela com todas as linhas e colunas da tabela tempo.

Podemos selecionar apenas as colunas que são relevantes para nós, fazendo:

Código:

SELECT cidade, maximo FROM tempo;

Esta instrução retorna apenas as colunas cidade e máximo da tabela tempo. Podemos fazer isso com qualquer número de colunas.

Este tipo de seleção não está restrito apenas às colunas na tabela, pode-se utilizar expressões que envolvem as colunas. Por exemplo:

Código:

SELECT cidade, (maximo + minimo)/2 AS media, data FROM tempo;

Aqui introduzimos também a cláusula AS. A cláusula AS apenas atribui um nome a esta coluna, e pode ser usada com qualquer coluna.
Teremos como resultado 3 colunas: cidade, media e data.

Até agora os comandos acima selecionam todas as linhas da tabela, restringindo sua escolha apenas as colunas desejadas. Mas e se precisarmos obter apenas determinadas linhas? Isso mesmo! Podemos fazer isso com o uso da cláusula WHERE:

Código:

SELECT * FROM tempo WHERE cidade = 'Brasilia';

A instrução seleciona todas as colunas da tabelas, mas apenas as linhas onde o campo cidade é igual a 'Brasilia'.
Na verdade a cláusula WHERE é um verificador booleano. Ela avalia a expressão e retorna apenas as linhas para as quais a expressão é verdadeira.
Podemos utilizar os operadores AND, OR e NOT para ampliar as expressões de WHERE. Por exemplo:

Código:

SELECT * FROM tempo WHERE cidade = 'Brasilia' AND data = '2006-04-06';

Isto nos mostra apenas as linhas da tabela em que cidade é igual a Brasília e data é igual a 6 de abril de 2006.

Quando se faz uma busca, pode ser que existam linhas com valores iguais. Por exemplo, podem existir várias linhas na nossa tabela tempo em que o campo cidade seja Brasília. Podemos fazer com que seja exibido apenas uma dessas linhas com a instrução DISTINCT:

Código:

SELECT DISTINCT cidade FROM tempo;

Isto mostraria todas as cidades existentes na tabela, sem repetição.

Podemos ainda ordenar os resultados de acordo com alguma coluna:

Código:

SELECT * FROM tempo ORDER BY cidade;

Isto retornaria o resultado com as cidades em ordem alfabética.