postgre SQL pratico, Notas de estudo de Análise de Sistemas de Engenharia

Baixe postgre SQL pratico e outras Notas de estudo em PDF para Análise de Sistemas de Engenharia, somente na Docsity!

PostgreSQL Prático

(versão 8.1.4)

Ribamar FS – [email protected] – http://ribafs.tk 17 de setembro de 2006

ÍNDICE

Capítulo Página 1 – Introdução.......... 4 2 - Instalação......... 8 2.1 - No Linux 2.2 - No Windows 3 - DDL (Data Definition Language)....... 13 3.1 - Criação e exclusão de bancos, esquemas, tabelas, views, Constraints, etc 3.2 - Alterações nos objetos dos bancos 3.3 - Índices, Tipos de Dados e Integridade Referencial 4 - DML (Data Manipulation Language)....... 34 4.1 - Consultas (select,insert, update e delete) 4.2 - Consultas JOINS 4.3 - Sub Consultas 5 - Funções Internas........ 45 5.1 - Strings 5.2 - Matemáticas 5.3 - Agrupamento (Agregação) 5.4 - Data/Hora 5.5 - Formatação de Tipos de Dados 5.6 - Conversão de Tipos (CAST) 6 - Funções Definidas pelo Usuário e Triggers...... 55 6.1 - SQL 6.2 - PlpgSQL 6.3 – Triggers 7 - DCL (Data Control Language) - Administração..... 68 7.1 - Usuários, grupos e privilégios 8 - Transações.......... 72 9 – Administração.......... 75 9.1 - Backup e Restore 9.2 - Importar e Exportar 9.3 - Converter 9.4 - Otimização e Desempenho 10 - Replicação.......... 84 11 - Configurações.......... 86 10.1 - Copiar o script de inicialização dos contribs 10.2 - Adicionar ao Path 10.3 - Configurar acessos (pg_hba.conf) 10.4- Configurações diversas (postgresql.conf) 12 – Metadados (Catálogo)........ 92 13 - Conectividade.......... 105 13.1 - Com Java (JDBC) 13.2 - Com aplicativos Windows (ODBC) 13.3 - Com PHP 13.4 - Exemplos de conexão com PHP, Java e VB

1 - Introdução História dos SGDBs Anos 60 - utilizados sistemas gerenciadores de arquivos (ISAM e VSAM), usados até hoje. Anos 70 - Gerenciadores de Bancos de dados de rede. Extinguiram-se nos anos 90. Anos 80 - SGBDRs (Oracle, DB2, SQLServer) Anos 90 - SGBDOR (Oracle, DB2, PostgreSQL e Informix) Anos 90 - SGBDOO (Caché) SGBD = Composto por programas de gerenciamento, armazenamento e acesso aos dados, com a finalidade de tornar ágil e eficiente a manipulação dos dados. Dicionário de dados - metadados, dados sobre os dados, ou seja, informações sobre a estrutura dos bancos de dados (nomes de tabelas, de campos, tipos de dados, etc). DBA - Database Administrator, com as funções de:

• Definir e modificar esquemas, estruturas de armazenamento e métodos de acesso
• Liberar privilégios de acesso
• Especificação de restrição de integridade Simplificando temos (no PostgreSQL), em termos de estrutura:
• Um SGBD é formado por bancos de dados, tablespaces, usuários e alguns programas auxiliares;
• Um banco de dados é formado pelos esquemas e linguagens;
• Um esquema é formado por funções de agrupamento, funções, triggers, procedures, sequências, tabelas e views;
• Tabelas são formadas por campos, constraints, índices e triggers.
• Em termos de dados uma tabela é formada por registros e campos. Segundo a Wikipedia (http://pt.wikipedia.org): ... A apresentação dos dados pode ser semelhante à de uma planilha eletrônica, porém os sistemas de gestão de banco de dados possuem características especiais para o armazenamento, classificação e recuperação dos dados. Os bancos de dados são utilizados em muitas aplicações, abrangendo praticamente todo o campo dos programas de computador. Os bancos de dados são o método de armazenamento preferencial para aplicações multiusuário, nas quais é necessário haver coordenação entre vários usuários. Entretanto, são convenientes também para indivíduos, e muitos programas de correio eletrônico e organizadores pessoais baseiam-se em tecnologias padronizadas de bancos de dados. Em Março, 2004 , AMR Research (como citado em um artigo da CNET News.com listado na secção de "Referências") previu que aplicações de banco de dados de código aberto seriam amplamente aceitas em 2006. Esquemas – são subdivisões de bancos de dados, cuja função é permitir um melhor nível de organização. Projetos de mesma categoria, que precisem acessar uns aos outros devem ficar em um mesmo banco, podendo ficar em esquemas separados.

Tabelas – são subdivisões de um esquema, nelas realmente ficam armazenados os dados de um banco. Uma tabela parece realmente com uma tabela em papel, tipo planilha, com linhas e colunas. Cada linha representa um registro de banco de dados e cada cruzamento de coluna com linha representa um campo de tabela. Tipo de Dados de um campo restringe o conjunto de valores (domínio) que pode ser atribuído ao campo e atribui semântica aos dados armazenados. Um campo do tipo numérico não aceita dados do tipo texto ou similar. Citação da Introdução do documento sobre otimização do PostgreSQL POSTGRESQL é um SGBD objeto-relational (SGBDOR) desenvolvido via Internet por um grupo de desenvolvedores espalhados pelo globo. É uma alternativa de código fonte-aberta para SGBDs comerciais como Oracle e Informix. O POSTGRESQL foi desenvolvido originalmente na Universidade de Califórnia em Berkeley. Em 1996, um grupo começou o desenvolvimento do SGBD na Internet. Eles usam e-mail para compartilhar idéias e servidores de arquivos para compartilhar código. POSTGRESQL é agora comparável à SGBDs comerciais em termos de características, desempenho e confiança. Hoje tem transações, views, procedimentos armazenados, e constranints de integridade referencial. Apóia um número grande de interfaces de programação, como ODBC, Java (JDBC), TCL/TK, PHP, Perl e Python, entre outros. POSTGRESQL continua avançando a um tremendo passo, graças a um grupo talentoso de desenvolvedores via Internet. (Bruce Momjian - 16th January 2003) Projeto POSTGRES (1986-1994): Partiu do projeto do SGBD Ingres de Berkeley. Projetista: Michael Stonebraker. Em 1995 dois estudantes de Berkeley (Jolly Chen e Andrew Yu) adicionam suporte a SQL. Seu novo nome: Postgres95. Foi totalmente reescrito em C e também adotou a SQL. Foi originalmente patrocinado pelo DARPA, ARO, NSF e ESL Inc. Em 1996: Disponibilizado na Internet sob o nome de PostgreSQL. O PostgreSQL aniversariou no dia 08/07/2006, quando completou 10 anos (08/07/1996). Seu décimo aniversário foi comemorado nos dias 08 e 09 de julho próximo, em Toronto, Canadá, com algumas conferências sobre o mesmo. Atualmente está na versão 8.1.4 (14/09/2006). Para saber mais sobre a história do PostgreSQL visite o site oficial em: http://www.postgresql.org/docs/current/interactive/history.html Ou em português em: http://pgdocptbr.sourceforge.net/pg80/history.html Características:

• O PostgreSQL suporta grande parte do SQL ANSI, inclusive do SQL 2003, além de oferecer outros recursos importantes, como:
• Comandos complexos
• Chaves estrangeiras (Foreign Key)
• Gatilhos (Triggers)
• Visões (views)
• Integridade de Transações
• Controle de Simultaneidade Multiversão (MVCC)
• Suporta múltiplas transações online concorrentes entre usuários.
• Suporte a Rules (sistema de regras que reescreve diretivas SQL)
• Criação de tabelas temporárias (CREATE TEMP TABLE nome(listadecampos tipos);)

Citação de: http://www.dib.com.br/dib%20cd/LC2003/P%C3%A1ginas/LC2003_Conf.html Metrô de São Paulo e DATAPREV também utilizam o PostgreSQL. Sobre o Autor Ribamar FS Desenvolvedor de aplicativos web para a Intranet do DNOCS (Departamento Nacional de Obras Contra as Secas). Desenvolve atualmente em PHP com PostgreSQL. Trabalhou no DNOCS por algum tempo como administrador de redes Linux e FreeBSD. É graduado em Engenharia Civil pela Universidade de Fortaleza (UNIFOR) Com especialização em Irrigação e Drenagem pela UFC/IRYDA Cursando Especialização em Java na UNIFOR Concluiu o Curso de PostgreSQL pela dbExpert (São Paulo) e pelo Evolução (Fortaleza) Concluiu o curso de Administração Linux pelo Evolução (Fortaleza) Foi escritor colaborador da Revista Forum Access (na área de Access) É escritor colaborador da Revista Web Mobile (artigo sobre Joomla 02/2006) Foi professor de cursos de extensão na UNIFOR (PHP+MySQL e PHP + PostgreSQL) em 2005 e 2006 Apresentou palestra sobre PostgreSQL na UNIFOR no dia 29/03/2006. Apresentou palestra sobre PostgreSQL na UFC no dia 21/09/2006 (II Semana de Software Livre da UFC). Compartilha seus conhecimentos através do site: http://ribafs.tk (http://www.geocities.com/ribafsindex) e http://www.ribafs.net

2 – Instalação Instalação no Windows XP Lembrar que: não instala em sistema de arquivos FAT-32, mesmo que seja o XP em FAT-32, ele não instala. Precisa instalar em NTFS e não instala no XP Start Edition.

• Fazer download do site oficial (www.postgresql.org) (hoje postgresql-8.1.4-1.zip)
• Executar o arquivo postgresql-8.1.msi
• Selecionar idioma e Start. Depois em Próximo.
• Na tela Informações de Instalações existem muitas informações importantes:
  • Sugere a leitura da FAQ
  • Fala das licenças dos diversos softwares a serem instalados
  • As versões 95, 98 e Me do Windows não são suportadas pelo PostgreSQL
  • Usar obrigatoriamente em sistema de arquivos NTFS
  • Instalar como serviço (mesmo que deixe como manual)
  • O PostgreSQL não executa com usuário que tenha privilégios de administrador
  • Os drivers jdbc estão no subdiretório \jdbc, que deve ser adicionada ao CLASSPATH
• Na Tela "Opções de Instalação" marque:
  • Suporte para idioma nativo (importante para ter as mensagens em pt_BR)
  • E outros que considere importantes e clique em Próximo
• Na tela "Configuração do Serviço":
  • Poderá optar entre instalar como serviço ou não. Como serviço é mais prático. Clique em Próximo (ele criará uma senha)
  • Obs.: Caso já tenha instalado o PostgreSQL antes nesta máquina deverá remover o usuário "postgres" antes de continuar:
  • Painel de controle - Ferramentas administrativas - Gerenciamento do computador - Usuários e grupos locais - Usuários. Remova o "postgres"
  • Agora clique em Próximo e Sim
• Na tela "Inicializar o agrupamento de bancos de dados:
  • Caso precise acessar sua máquina de outra remota marque Endereços
  • Em Locale selecione Português Brasil
  • Em Codificação selecione LATIN
  • Entre com uma senha e repita. Altere o usuário se for o caso e Próximo.
• Na tela "Habilitar Linguagens Procedurais" deixe marcada PL/pgsql e Próximo
• Na tela "Habilitar Módulos Contrib" marque os desejados e Próximo
• Na tela "Habilitar PostGIS em templae1" marque se precisar que todos os bancos tragam o PostGIS e Próximo e Próximo.
• Após instalar, na tela "Instalação concluída" recomenda-se que se cadastrar na lista pgsql-announce, que envia informações semanais sobre novas versões e correções de error. Basta clicar no botão, fazer o cadastro e Concluir. Editar postgresql.conf e adicionar a linha (datestyle = 'sql european'), após a existente.

Copiar o script de inicialização “linux” para o /etc/init.d (Debians): De /usr/local/src/postgresql-8.1.4/contrib/start-script/linux para /etc/init.d/postgresql Dar permissão de execução: chmod u+x /etc/init.d/postgresql Se no Ubuntu ou outro Debian: su - postgres gedit .bash_profile (e adicione a linha): PATH=/usr/local/pgsql/bin:$PATH Pós Instalação (sh,bash,ksh e zsh): LD_LIBRARY_PATH=/usr/local/pgsql/lib export LD_LIBRARY_PATH Ou no ~/.bash_profile do usuário postgres initdb – inicializa o cluster, cria os scripts de configuração default. postmaster – inicia o processo do servidor responsável por escutar por pedidos de conexão. Para suporte aos locales do Brasil usar: /usr/local/pgsql/bin/initdb -locale=pt_BR -D /usr/local/pgsql/data A instalação via fontes (sources) em algumas distribuições muito enxutas, voltadas para para desktop, pode não funcionar da primeira vez, pois faltarão algumas bibliotecas, compiladores, etc. Após a instalação está criado o agrupamento principal (cluster main) de bancos de dados do PostgreSQL. Caso não se tenha confiança nos usuários locais é recomendável utilizar a opção -W, -- pwprompt ou –pwfile do initdb, que atribuirá uma senha ao superusuário. No arquivo pg_hba.conf utilizar autenticação tipo md5, password ou cript, antes de iniciar o servidor pela primeira vez. Quando o programa que inicia o servidor (postmaster) está em execução, é criado um PID e armazenado dentro do arquivo postmaster.pid, dentro do subdiretório data. Ele impede que mais de um processo postmaster seja executado usando o mesmo cluster e diretório de dados. Baixar PostgreSQL via Anonymous CVS: Baixar CVS de - http://www.nongnu.org/cvs/ Instalar e Logar com qualquer senha: cvs -d :pserver:[email protected]:/projects/cvsroot login Baixar fontes: cvs -z3 -d :pserver:[email protected]:/projects/cvsroot co -P pgsql Isto irá instalar o PostgreSQL num subdiretório pgsql do diretório atual. Atualizar a última instalação via CVS: Acesse o diretório pgsql e execute - cvs -z3 update -d -P Isto irá baixar somente as alterações ocorridas após a última instalação.

Também podemos criar um arquivo .cvsrc no home do usuário com as duas linhas: cvs -z update -d -P Atualização do PostgreSQL entre Versões Caso você tenha uma versão que não seja 8.1.x e esteja querendo instalar a 8.1.4, então precisa fazer um backup dos seus dados e restaurar logo após a instalação como sugerido em seguida. Será assumido que sua instalação foi em: /usr/local/pgsql e seus dados no sub data. Caso contrário atenha-se ao seu path para ajustes. 1 – Atenção para que seus bancos não estejam recebendo atualização durante o backup. Se preciso proíba acesso no pg_hba.conf. 2 – Efetuando backup: pg_dumpall > bancos.sql .Para preservar os OIDs use a opção -o no pg_dumpall. 3 – Pare o servidor pg_ctl stop ou outro comando Caso queira instalar a nova versão no mesmo diretório da anterior mv /usr/local/pgsql /usr/local/pgsql.old Então instale a nova versão, crie o diretório de dados e start o novo servidor. /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data Finalmente, restore seus dados com /usr/local/pgsql/bin/psql -d postgres -f bancos.sql Para mais detalhes sobre os procedimentos de instalação, veja itens 14.5 e 14.6 do manual. Plataformas Suportadas Atualmente o PostgreSQL suporta muitas plataformas, entre elas o Windows, Linux, FreeBSD, NetBSD, OpenBSD, Mac OS e diversos outros. Plataformas suportadas e as não suportadas na seção 14.7 do manual oficial. No PostgreSQL o processo postmaster escuta por conexões dos clientes. Existem mais dois processos também iniciados, ambos com nome postgres. Eles cuidam da gravação dos logs ou tabelas e da manutenção das estatísticas. Para cada conexão com uma aplicação cliente é criado um novo processo com o mesmo nome do usuário da conexão. Por isso é importante que cada aplicativo tenha seu usuário e se tenha um maior controle. Os arquivos de configuração (postgresql.conf, pg_hba.conf e pg_ident.conf) a partir da versão 8 podem ficar em diretório diferente do PGDATA.

3 - DDL (Data Definition Language) 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).

Exemplo Gráfico de Consultas (Tabela, com campos C1, C2) (Adaptação de exemplo da Wikipedia (http://pt.wikipedia.org) Tabela T Consulta Resultado C1 C2 SELECT * FROM T C1 C 1 a 1 a 2 b 2 b C1 C2 SELECT C1 FROM T C 1 a 1 2 b 2 C1 C2 SELECT * FROM T WHERE C1=1 C1 C 1 a 1 a 2 b C1 C2 SELECT C1 FROM T WHERE C2=b C 1 A 2 2 B 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.

CREATE TABLE primeira_tabela ( primeiro_campo text, segundo_campo integer ); 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. ); 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ável as mensagens de erro. 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 ); 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);

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.: Preferir sempre criar FK, utilizando a palavra reservada FOREIGN KEY e não somente com REFERENCES. Obviamente, o número de colunas e tipo na restrição devem ser semelhantes ao número e tipo das colunas referenciadas. SIMULANDO ENUM 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?