PostgreSQL - Guia rápido

PostgreSQL é um poderoso sistema de banco de dados objeto-relacional de código aberto. Ele tem mais de 15 anos de fase de desenvolvimento ativo e uma arquitetura comprovada que lhe valeu uma forte reputação de confiabilidade, integridade de dados e correção.

Este tutorial fornecerá um início rápido com o PostgreSQL e deixará você confortável com a programação do PostgreSQL.

O que é PostgreSQL?

PostgreSQL (pronunciado como post-gress-Q-L) é um sistema de gerenciamento de banco de dados relacional de código aberto (DBMS) desenvolvido por uma equipe mundial de voluntários. O PostgreSQL não é controlado por nenhuma empresa ou outra entidade privada e o código-fonte está disponível gratuitamente.

Uma breve história do PostgreSQL

PostgreSQL, originalmente chamado Postgres, foi criado na UCB por um professor de ciência da computação chamado Michael Stonebraker. Stonebraker fundou o Postgres em 1986 como um projeto subsequente ao seu antecessor, Ingres, agora propriedade da Computer Associates.

1977-1985 - Foi desenvolvido um projeto denominado INGRES.
- Prova de conceito para bancos de dados relacionais
- Fundou a empresa Ingres em 1980
- Comprado pela Computer Associates em 1994
1986-1994 - POSTGRES
- Desenvolvimento dos conceitos no INGRES com foco na orientação a objetos e na linguagem de consulta - Quel
- A base de código do INGRES não foi usada como base para o POSTGRES
- Comercializado como Illustra (comprado por Informix, comprado pela IBM)
1994-1995 - Postgres95
- Suporte para SQL foi adicionado em 1994
- Lançado como Postgres95 em 1995
- Relançado como PostgreSQL 6.0 em 1996
- Estabelecimento da Equipe de Desenvolvimento Global PostgreSQL

Principais recursos do PostgreSQL

PostgreSQL é executado em todos os principais sistemas operacionais, incluindo Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) e Windows. Ele suporta texto, imagens, sons e vídeo e inclui interfaces de programação para C / C ++, Java, Perl, Python, Ruby, Tcl e Open Database Connectivity (ODBC).

PostgreSQL suporta uma grande parte do padrão SQL e oferece muitos recursos modernos, incluindo o seguinte -

Consultas SQL complexas
SQL Sub-selects
Chaves estrangeiras
Trigger
Views
Transactions
Controle de simultaneidade multiversão (MVCC)
Replicação de streaming (a partir de 9.0)
Hot Standby (a partir de 9.0)

Você pode verificar a documentação oficial do PostgreSQL para entender os recursos mencionados acima. O PostgreSQL pode ser estendido pelo usuário de várias maneiras. Por exemplo, adicionando novo -

Tipos de dados
Functions
Operators
Funções agregadas
Métodos de índice

Suporte para linguagens procedurais

PostgreSQL suporta quatro linguagens procedurais padrão, o que permite aos usuários escrever seu próprio código em qualquer uma das linguagens e pode ser executado pelo servidor de banco de dados PostgreSQL. Essas linguagens procedurais são - PL / pgSQL, PL / Tcl, PL / Perl e PL / Python. Além disso, outras linguagens procedurais não padronizadas como PL / PHP, PL / V8, PL / Ruby, PL / Java, etc., também são suportadas.

Para começar a entender os fundamentos do PostgreSQL, primeiro vamos instalar o PostgreSQL. Este capítulo explica como instalar o PostgreSQL nas plataformas Linux, Windows e Mac OS.

Instalando PostgreSQL no Linux / Unix

Siga as etapas fornecidas para instalar o PostgreSQL em sua máquina Linux. Certifique-se de estar conectado comoroot antes de prosseguir para a instalação.

Escolha o número da versão do PostgreSQL que deseja e, o mais exatamente possível, a plataforma que você deseja do EnterpriseDB
Eu baixei postgresql-9.2.4-1-linux-x64.runpara minha máquina CentOS-6 de 64 bits. Agora, vamos executá-lo da seguinte maneira -

[root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run
[root@host]# ./postgresql-9.2.4-1-linux-x64.run
------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.

------------------------------------------------------------------------
Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.2]:

Depois de iniciar o instalador, ele faz algumas perguntas básicas como localização da instalação, senha do usuário que usará o banco de dados, número da porta, etc. Portanto, mantenha todos em seus valores padrão, exceto senha, que você pode fornecer senha conforme sua escolha. Ele instalará o PostgreSQL em sua máquina Linux e exibirá a seguinte mensagem -

Please wait while Setup installs PostgreSQL on your computer.

 Installing
 0% ______________ 50% ______________ 100%
 #########################################

-----------------------------------------------------------------------
Setup has finished installing PostgreSQL on your computer.

Siga as seguintes etapas de pós-instalação para criar seu banco de dados -

[root@host]# su - postgres
Password:
bash-4.1$ createdb testdb bash-4.1$ psql testdb
psql (8.4.13, server 9.2.4)

test=#

Você pode iniciar / reiniciar o servidor postgres caso ele não esteja sendo executado usando o seguinte comando -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Se sua instalação foi correta, você terá o prompt do PotsgreSQL test=# como mostrado acima.

Instalação do PostgreSQL no Windows

Siga as etapas fornecidas para instalar o PostgreSQL em sua máquina Windows. Certifique-se de ter desativado o antivírus de terceiros durante a instalação.

Escolha o número da versão do PostgreSQL que deseja e, o mais exatamente possível, a plataforma que você deseja do EnterpriseDB
Eu baixei postgresql-9.2.4-1-windows.exe para meu PC com Windows executando no modo de 32 bits, então vamos executar postgresql-9.2.4-1-windows.execomo administrador para instalar o PostgreSQL. Selecione o local onde deseja instalá-lo. Por padrão, ele é instalado na pasta Arquivos de programas.

A próxima etapa do processo de instalação seria selecionar o diretório onde seus dados seriam armazenados. Por padrão, ele é armazenado no diretório "dados".

Em seguida, a configuração pede uma senha, para que você possa usar sua senha favorita.

O próximo passo; mantenha a porta como padrão.

Na próxima etapa, quando perguntado por "Locale", selecionei "English, United States".
Demora um pouco para instalar o PostgreSQL em seu sistema. Após a conclusão do processo de instalação, você obterá a seguinte tela. Desmarque a caixa de seleção e clique no botão Concluir.

Após a conclusão do processo de instalação, você pode acessar o pgAdmin III, StackBuilder e o shell PostgreSQL a partir do menu do programa em PostgreSQL 9.2.

Instalação do PostgreSQL no Mac

Siga as etapas fornecidas para instalar o PostgreSQL em sua máquina Mac. Certifique-se de estar conectado comoadministrator antes de prosseguir para a instalação.

Escolha o número da versão mais recente do PostgreSQL para Mac OS disponível em EnterpriseDB
Eu baixei postgresql-9.2.4-1-osx.dmgpara meu Mac OS rodando com OS X versão 10.8.3. Agora, vamos abrir a imagem dmg no Finder e apenas clicar duas vezes nela, o que lhe dará o instalador do PostgreSQL na janela seguinte -

Em seguida, clique no postgres-9.2.4-1-osxícone, que dará uma mensagem de aviso. Aceite o aviso e prossiga para a instalação posterior. Ele solicitará a senha do administrador, conforme mostrado na janela a seguir -

Digite a senha, prossiga com a instalação e, após esta etapa, reinicie sua máquina Mac. Se você não vir a janela a seguir, reinicie a instalação.

Depois de iniciar o instalador, ele faz algumas perguntas básicas como localização da instalação, senha do usuário que usará o banco de dados, número da porta etc. Portanto, mantenha todos eles em seus valores padrão, exceto a senha, que você pode fornecer conforme sua escolha. Ele instalará o PostgreSQL em sua máquina Mac na pasta de aplicativos que você pode verificar -

Agora, você pode iniciar qualquer programa para começar. Vamos começar com o SQL Shell. Ao iniciar o SQL Shell, basta usar todos os valores padrão exibidos, exceto inserir sua senha, que você selecionou no momento da instalação. Se tudo correr bem, então você estará dentro do banco de dados postgres e umpostgress# prompt será exibido como mostrado abaixo -

Parabéns!!! Agora você tem seu ambiente pronto para iniciar a programação de banco de dados PostgreSQL.

Este capítulo fornece uma lista dos comandos SQL do PostgreSQL, seguidos pelas regras de sintaxe precisas para cada um desses comandos. Este conjunto de comandos é obtido da ferramenta de linha de comando psql. Agora que você instalou o Postgres, abra o psql como -

Program Files → PostgreSQL 9.2 → SQL Shell(psql).

Usando o psql, você pode gerar uma lista completa de comandos usando o comando \ help. Para a sintaxe de um comando específico, use o seguinte comando -

postgres-# \help <command_name>

A instrução SQL

Uma instrução SQL é composta de tokens onde cada token pode representar uma palavra-chave, identificador, identificador entre aspas, constante ou símbolo de caractere especial. A tabela fornecida a seguir usa uma instrução SELECT simples para ilustrar uma instrução SQL básica, mas completa, e seus componentes.

	SELECIONE	id, nome	DE	estados
Tipo de Token	Palavra-chave	Identificadores	Palavra-chave	Identificador
Descrição	Comando	Colunas de id e nome	Cláusula	Nome da tabela

Comandos PostgreSQL SQL

ABORTAR

Aborte a transação atual.

ABORT [ WORK | TRANSACTION ]

ALTER AGGREGATE

Altere a definição de uma função agregada.

ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner

ALTER CONVERSION

Altere a definição de uma conversão.

ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO new_owner

ALTER DATABASE

Altere um parâmetro específico do banco de dados.

ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO new_owner

ALTER DOMAIN

Altere a definição de um parâmetro específico do domínio.

ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name { SET | DROP } NOT NULL
ALTER DOMAIN name ADD domain_constraint
ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN name OWNER TO new_owner

ALTER FUNCTION

Altere a definição de uma função.

ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name
ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner

ALTER GROUP

Altere um grupo de usuários.

ALTER GROUP groupname ADD USER username [, ... ]
ALTER GROUP groupname DROP USER username [, ... ]
ALTER GROUP groupname RENAME TO new_name

ALTER INDEX

Altere a definição de um índice.

ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name
ALTER INDEX name RENAME TO new_name

ALTER LANGUAGE

Altere a definição de uma linguagem procedural.

ALTER LANGUAGE name RENAME TO new_name

ALTER OPERATOR

Altere a definição de um operador.

ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } )
OWNER TO new_owner

ALTER OPERATOR CLASS

Altere a definição de uma classe de operadores.

ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner

ALTER SCHEMA

Altere a definição de um esquema.

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner

ALTER SEQUENCE

Altere a definição de um gerador de sequência.

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

ALTERAR A TABELA

Altere a definição de uma mesa.

ALTER TABLE [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name

Onde a ação é uma das seguintes linhas -

ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name

ALTER TABLESPACE

Altere a definição de um espaço de tabela.

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_owner

ALTER TRIGGER

Altere a definição de um gatilho.

ALTER TRIGGER name ON table RENAME TO new_name

ALTER TYPE

Altere a definição de um tipo.

ALTER TYPE name OWNER TO new_owner

ALTER USER

Altere uma conta de usuário do banco de dados.

ALTER USER name [ [ WITH ] option [ ... ] ]
ALTER USER name RENAME TO new_name
ALTER USER name SET parameter { TO | = } { value | DEFAULT }
ALTER USER name RESET parameter

Onde a opção pode ser -

[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| VALID UNTIL 'abstime'

ANALISAR

Colete estatísticas sobre um banco de dados.

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

INÍCIO

Inicie um bloco de transação.

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

Onde transaction_mode é um de -

ISOLATION LEVEL { 
   SERIALIZABLE | REPEATABLE READ | READ COMMITTED
   | READ UNCOMMITTED
}
READ WRITE | READ ONLY

CHECKPOINT

Força um ponto de verificação do log de transações.

CHECKPOINT

FECHAR

Feche um cursor.

CLOSE name

GRUPO

Agrupe uma tabela de acordo com um índice.

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

COMENTE

Defina ou altere o comentário de um objeto.

COMMENT ON {
   TABLE object_name |
   COLUMN table_name.column_name |
   AGGREGATE agg_name (agg_type) |
   CAST (source_type AS target_type) |
   CONSTRAINT constraint_name ON table_name |
   CONVERSION object_name |
   DATABASE object_name |
   DOMAIN object_name |
   FUNCTION func_name (arg1_type, arg2_type, ...) |
   INDEX object_name |
   LARGE OBJECT large_object_oid |
   OPERATOR op (left_operand_type, right_operand_type) |
   OPERATOR CLASS object_name USING index_method |
   [ PROCEDURAL ] LANGUAGE object_name |
   RULE rule_name ON table_name |
   SCHEMA object_name |
   SEQUENCE object_name |
   TRIGGER trigger_name ON table_name |
   TYPE object_name |
   VIEW object_name
} 
IS 'text'

COMPROMETE

Confirme a transação atual.

COMMIT [ WORK | TRANSACTION ]

CÓPIA DE

Copie dados entre um arquivo e uma tabela.

COPY table_name [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
COPY table_name [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

CRIAR AGREGADO

Defina uma nova função de agregação.

CREATE AGGREGATE name (
   BASETYPE = input_data_type,
   SFUNC = sfunc,
   STYPE = state_data_type
   [, FINALFUNC = ffunc ]
   [, INITCOND = initial_condition ]
)

CRIAR CAST

Defina um novo elenco.

CREATE CAST (source_type AS target_type)
WITH FUNCTION func_name (arg_types)
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]

CRIAR CONSTRAINT TRIGGER

Defina um novo gatilho de restrição.

CREATE CONSTRAINT TRIGGER name
AFTER events ON
table_name constraint attributes
FOR EACH ROW EXECUTE PROCEDURE func_name ( args )

CRIAR CONVERSÃO

Defina uma nova conversão.

CREATE [DEFAULT] CONVERSION name
FOR source_encoding TO dest_encoding FROM func_name

CRIAR BASE DE DADOS

Crie um novo banco de dados.

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ]
   [ TEMPLATE [=] template ]
   [ ENCODING [=] encoding ]
   [ TABLESPACE [=] tablespace ] 
]

CRIAR DOMÍNIO

Defina um novo domínio.

CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

Onde a restrição é -

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

CRIAR FUNÇÃO

Defina uma nova função.

CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )
RETURNS ret_type
{ LANGUAGE lang_name
   | IMMUTABLE | STABLE | VOLATILE
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
   | AS 'definition'
   | AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]

CRIAR GRUPO

Defina um novo grupo de usuários.

CREATE GROUP name [ [ WITH ] option [ ... ] ]
Where option can be:
SYSID gid
| USER username [, ...]

CRIAR ÍNDICE

Defina um novo índice.

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

CRIAR IDIOMA

Defina uma nova linguagem procedural.

CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR val_function ]

CRIAR OPERADOR

Defina um novo operador.

CREATE OPERATOR name (
   PROCEDURE = func_name
   [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
   [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
   [, RESTRICT = res_proc ] [, JOIN = join_proc ]
   [, HASHES ] [, MERGES ]
   [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
   [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
)

CRIAR AULA DE OPERADOR

Defina uma nova classe de operador.

CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
USING index_method AS
{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ]
   | FUNCTION support_number func_name ( argument_type [, ...] )
   | STORAGE storage_type
} [, ... ]

CRIAR REGRA

Defina uma nova regra de reescrita.

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

CRIAR ESQUEMA

Defina um novo esquema.

CREATE SCHEMA schema_name
[ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username
[ schema_element [ ... ] ]

CRIAR SEQUÊNCIA

Defina um novo gerador de sequência.

CREATE [ TEMPORARY | TEMP ] SEQUENCE name
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

CRIAR A TABELA

Defina uma nova tabela.

CREATE [ [ GLOBAL | LOCAL ] { 
   TEMPORARY | TEMP } ] TABLE table_name ( { 
      column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
      | table_constraint
      | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] 
   } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

Onde column_constraint é -

[ CONSTRAINT constraint_name ] { 
   NOT NULL |
   NULL |
   UNIQUE [ USING INDEX TABLESPACE tablespace ] |
   PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
   CHECK (expression) |
   REFERENCES ref_table [ ( ref_column ) ]
   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
   [ ON DELETE action ] [ ON UPDATE action ] 
}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

E table_constraint é -

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] )
REFERENCES ref_table [ ( ref_column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

CRIAR TABELA COMO

Defina uma nova tabela a partir dos resultados de uma consulta.

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query

CRIAR TABLESPACE

Defina um novo espaço de tabela.

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

CRIAR TRIGGER

Defina um novo gatilho.

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE func_name ( arguments )

CRIAR TIPO

Defina um novo tipo de dados.

CREATE TYPE name AS
( attribute_name data_type [, ... ] )
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[, RECEIVE = receive_function ]
[, SEND = send_function ]
[, ANALYZE = analyze_function ]
[, INTERNALLENGTH = { internal_length | VARIABLE } ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = alignment ]
[, STORAGE = storage ]
[, DEFAULT = default ]
[, ELEMENT = element ]
[, DELIMITER = delimiter ]
)

CRIAR USUÁRIO

Defina uma nova conta de usuário do banco de dados.

CREATE USER name [ [ WITH ] option [ ... ] ]

Onde a opção pode ser -

SYSID uid
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP group_name [, ...]
| VALID UNTIL 'abs_time'

CRIAR VISUALIZAÇÃO

Defina uma nova visão.

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

DEALOCAR

Desalocar uma declaração preparada.

DEALLOCATE [ PREPARE ] plan_name

DECLARAR

Defina um cursor.

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

EXCLUIR

Exclua linhas de uma tabela.

DELETE FROM [ ONLY ] table [ WHERE condition ]

DROP AGGREGATE

Remova uma função agregada.

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

DROP CAST

Remova um gesso.

DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]

DROP CONVERSION

Remova uma conversão.

DROP CONVERSION name [ CASCADE | RESTRICT ]

DROP DATABASE

Remova um banco de dados.

DROP DATABASE name

DROP DOMAIN

Remova um domínio.

DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]

FUNÇÃO DROP

Remova uma função.

DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

DROP GROUP

Remova um grupo de usuários.

DROP GROUP name

DROP INDEX

Remova um índice.

DROP INDEX name [, ...] [ CASCADE | RESTRICT ]

DROP LANGUAGE

Remova uma linguagem procedural.

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

OPERADOR DE SOLDA

Remova um operador.

DROP OPERATOR name ( { left_type | NONE }, { right_type | NONE } )
[ CASCADE | RESTRICT ]

DROP OPERATOR CLASS

Remova uma classe de operadores.

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

REGRA DE DROP

Remova uma regra de reescrita.

DROP RULE name ON relation [ CASCADE | RESTRICT ]

ESQUEMA DROP

Remova um esquema.

DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]

DROP SEQUENCE

Remova uma sequência.

DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]

DROP TABLE

Remova uma mesa.

DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

DROP TABLESPACE

Remova um espaço de tabela.

DROP TABLESPACE tablespace_name

DROP TRIGGER

Remova um gatilho.

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

DROP TYPE

Remova um tipo de dados.

DROP TYPE name [, ...] [ CASCADE | RESTRICT ]

DROP USER

Remova uma conta de usuário do banco de dados.

DROP USER name

DROP VIEW

Remova uma vista.

DROP VIEW name [, ...] [ CASCADE | RESTRICT ]

FIM

Confirme a transação atual.

END [ WORK | TRANSACTION ]

EXECUTAR

Execute uma declaração preparada.

EXECUTE plan_name [ (parameter [, ...] ) ]

EXPLICAR

Mostre o plano de execução de uma declaração.

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

BUSCAR

Recupere linhas de uma consulta usando um cursor.

FETCH [ direction { FROM | IN } ] cursor_name

Onde a direção pode ser vazia ou uma de -

NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL

CONCEDER

Defina os privilégios de acesso.

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

INSERIR

Crie novas linhas em uma tabela.

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

OUÇO

Ouça uma notificação.

LISTEN name

CARGA

Carregue ou recarregue um arquivo de biblioteca compartilhada.

LOAD 'filename'

LOCK

Tranque uma mesa.

LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]

Onde lock_mode é um de -

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

MOVER

Posicione um cursor.

MOVE [ direction { FROM | IN } ] cursor_name

NOTIFICAR

Gere uma notificação.

NOTIFY name

PREPARAR

Prepare uma declaração para execução.

PREPARE plan_name [ (data_type [, ...] ) ] AS statement

REINDEX

Recompile índices.

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

RELEASE SAVEPOINT

Destrua um ponto de salvamento previamente definido.

RELEASE [ SAVEPOINT ] savepoint_name

REDEFINIR

Restaure o valor de um parâmetro de tempo de execução para o valor padrão.

RESET name
RESET ALL

REVOGAR

Remova os privilégios de acesso.

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

ROLLBACK

Aborte a transação atual.

ROLLBACK [ WORK | TRANSACTION ]

ROLLBACK TO SAVEPOINT

Reverta para um ponto de salvamento.

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

SALVAR PONTO

Defina um novo ponto de salvamento na transação atual.

SAVEPOINT savepoint_name

SELECIONE

Recupere linhas de uma tabela ou exibição.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

from_item

[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
function_name ( [ argument [, ...] ] )
[ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item
[ ON join_condition | USING ( join_column [, ...] ) ]

SELECIONE EM

Defina uma nova tabela a partir dos resultados de uma consulta.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

CONJUNTO

Altere um parâmetro de tempo de execução.

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }

DEFINIR RESTRIÇÕES

Defina os modos de verificação de restrição para a transação atual.

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

DEFINIR AUTORIZAÇÃO DE SESSÃO

Defina o identificador do usuário da sessão e o identificador do usuário atual da sessão atual.

SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
RESET SESSION AUTHORIZATION

DEFINIR A TRANSAÇÃO

Defina as características da transação atual.

SET TRANSACTION transaction_mode [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

Onde transaction_mode é um de -

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

EXPOSIÇÃO

Mostra o valor de um parâmetro de tempo de execução.

SHOW name
SHOW ALL

INICIAR TRANSAÇÃO

Inicie um bloco de transação.

START TRANSACTION [ transaction_mode [, ...] ]

Onde transaction_mode é um de -

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

TRUNCAR

Esvazie uma mesa.

TRUNCATE [ TABLE ] name

UNLISTEN

Pare de ouvir uma notificação.

UNLISTEN { name | * }

ATUALIZAR

Atualize as linhas de uma tabela.

UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM from_list ]
[ WHERE condition ]

VÁCUO

Colete o lixo e, opcionalmente, analise um banco de dados.

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

Neste capítulo, discutiremos sobre os tipos de dados usados no PostgreSQL. Ao criar a tabela, para cada coluna, você especifica um tipo de dados, ou seja, que tipo de dados deseja armazenar nos campos da tabela.

Isso permite vários benefícios -

Consistency - As operações em colunas do mesmo tipo de dados fornecem resultados consistentes e geralmente são as mais rápidas.
Validation - O uso adequado de tipos de dados implica validação de formato de dados e rejeição de dados fora do escopo do tipo de dados.
Compactness - Como uma coluna pode armazenar um único tipo de valor, ele é armazenado de forma compacta.
Performance- O uso adequado de tipos de dados fornece o armazenamento de dados mais eficiente. Os valores armazenados podem ser processados rapidamente, o que melhora o desempenho.

PostgreSQL oferece suporte a um amplo conjunto de tipos de dados. Além disso, os usuários podem criar seus próprios tipos de dados personalizados usando o comando CREATE TYPE SQL. Existem diferentes categorias de tipos de dados no PostgreSQL. Eles são discutidos abaixo.

Tipos Numéricos

Os tipos numéricos consistem em números inteiros de dois, quatro e oito bytes, números de ponto flutuante de quatro e oito bytes e decimais de precisão selecionável. A tabela a seguir lista os tipos disponíveis.

Nome	Tamanho de Armazenamento	Descrição	Alcance
smallint	2 bytes	inteiro de pequeno alcance	-32768 a +32767
inteiro	4 bytes	escolha típica para inteiro	-2147483648 a +2147483647
bigint	8 bytes	inteiro de grande alcance	-9223372036854775808 a 9223372036854775807
decimal	variável	precisão especificada pelo usuário, exata	até 131072 dígitos antes da vírgula decimal; até 16383 dígitos após o ponto decimal
numérico	variável	precisão especificada pelo usuário, exata	até 131072 dígitos antes da vírgula decimal; até 16383 dígitos após o ponto decimal
real	4 bytes	precisão variável, inexata	Precisão de 6 dígitos decimais
dupla precisão	8 bytes	precisão variável, inexata	Precisão de 15 dígitos decimais
pequeno	2 bytes	pequeno inteiro de incremento automático	1 a 32767
serial	4 bytes	número inteiro autoincrementado	1 a 2147483647
grande série	8 bytes	grande número inteiro de incremento automático	1 a 9223372036854775807

Tipos monetários

O tipo de dinheiro armazena uma quantia em moeda com uma precisão fracionária fixa. Os valores dos tipos de dados numéricos, int e bigint podem ser convertidos em dinheiro . O uso de números de ponto flutuante não é recomendado para lidar com dinheiro devido ao potencial de erros de arredondamento.

Nome	Tamanho de Armazenamento	Descrição	Alcance
dinheiro	8 bytes	quantidade de moeda	-92233720368547758,08 a +92233720368547758,07

Tipos de personagem

A tabela abaixo lista os tipos de caracteres de uso geral disponíveis no PostgreSQL.

S. No.	Nome e Descrição
1	character varying(n), varchar(n) comprimento variável com limite
2	character(n), char(n) comprimento fixo, preenchido em branco
3	text comprimento ilimitado variável

Tipos de dados binários

O tipo de dados bytea permite o armazenamento de strings binárias como na tabela abaixo.

Nome	Tamanho de Armazenamento	Descrição
tchau	1 ou 4 bytes mais a string binária real	string binária de comprimento variável

Tipos de data / hora

O PostgreSQL suporta um conjunto completo de tipos de data e hora SQL, conforme mostrado na tabela abaixo. As datas são contadas de acordo com o calendário gregoriano. Aqui, todos os tipos têm resolução de1 microsecond / 14 digits exceto date tipo, cuja resolução é day.

Nome	Tamanho de Armazenamento	Descrição	Baixo valor	Valor alto
carimbo de data / hora [(p)] [sem fuso horário]	8 bytes	data e hora (sem fuso horário)	4713 AC	294276 DC
TIMESTAMPTZ	8 bytes	data e hora, com fuso horário	4713 AC	294276 DC
encontro	4 bytes	data (sem hora do dia)	4713 AC	5874897 DC
hora [(p)] [sem fuso horário]	8 bytes	hora do dia (sem data)	00:00:00	24:00:00
hora [(p)] com fuso horário	12 bytes	somente horas do dia, com fuso horário	00: 00: 00 + 1459	24: 00-1459
intervalo [campos] [(p)]	12 bytes	intervalo de tempo	-178000000 anos	178000000 anos

Tipo Booleano

PostgreSQL fornece o tipo Booleano SQL padrão. O tipo de dados booleano pode ter os estados verdadeiro , falso e um terceiro estado, desconhecido , que é representado pelo valor nulo SQL.

Nome	Tamanho de Armazenamento	Descrição
boleano	1 byte	estado de verdadeiro ou falso

Tipo Enumerado

Tipos enumerados (enum) são tipos de dados que compreendem um conjunto de valores estáticos e ordenados. Eles são equivalentes aos tipos de enum suportados em várias linguagens de programação.

Ao contrário de outros tipos, os tipos enumerados precisam ser criados usando o comando CREATE TYPE. Esse tipo é usado para armazenar um conjunto de valores estáticos e ordenados. Por exemplo, direções de bússola, ou seja, NORTE, SUL, EAST e OESTE ou dias da semana como mostrado abaixo -

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

Enumerado, uma vez criado, pode ser usado como qualquer outro tipo.

Tipo Geométrico

Os tipos de dados geométricos representam objetos espaciais bidimensionais. O tipo mais fundamental, o ponto, forma a base de todos os outros tipos.

Nome	Tamanho de Armazenamento	Representação	Descrição
ponto	16 bytes	Aponte em um avião	(x, y)
linha	32 bytes	Linha infinita (não totalmente implementada)	((x1, y1), (x2, y2))
lseg	32 bytes	Segmento de linha finita	((x1, y1), (x2, y2))
caixa	32 bytes	Caixa Retangular	((x1, y1), (x2, y2))
caminho	16 + 16n bytes	Caminho fechado (semelhante ao polígono)	((x1, y1), ...)
caminho	16 + 16n bytes	Caminho aberto	[(x1, y1), ...]
polígono	40 + 16n	Polígono (semelhante ao caminho fechado)	((x1, y1), ...)
círculo	24 bytes	Círculo	<(x, y), r> (ponto central e raio)

Tipo de endereço de rede

PostgreSQL oferece tipos de dados para armazenar endereços IPv4, IPv6 e MAC. É melhor usar esses tipos em vez de tipos de texto simples para armazenar endereços de rede, porque esses tipos oferecem verificação de erro de entrada e operadores e funções especializados.

Nome	Tamanho de Armazenamento	Descrição
cidr	7 ou 19 bytes	Redes IPv4 e IPv6
inet	7 ou 19 bytes	Hosts e redes IPv4 e IPv6
macaddr	6 bytes	Endereços MAC

Tipo de seqüência de bits

Os tipos de sequência de bits são usados para armazenar máscaras de bits. Eles são 0 ou 1. Existem dois tipos de bits SQL:bit(n) e bit varying(n), onde n é um número inteiro positivo.

Tipo de pesquisa de texto

Esse tipo oferece suporte à pesquisa de texto completo, que é a atividade de pesquisar uma coleção de documentos em linguagem natural para localizar aqueles que melhor correspondem a uma consulta. Existem dois tipos de dados para isso -

S. No.	Nome e Descrição
1	tsvector Esta é uma lista classificada de palavras distintas que foram normalizadas para mesclar diferentes variantes da mesma palavra, chamadas de "lexemas".
2	tsquery Isso armazena lexemas que devem ser pesquisados e os combina respeitando os operadores booleanos & (AND), \| (Ou e ! (NÃO). Os parênteses podem ser usados para forçar o agrupamento dos operadores.

Tipo UUID

Um UUID (Universally Unique Identifiers) é escrito como uma sequência de dígitos hexadecimais minúsculos, em vários grupos separados por hifens, especificamente um grupo de oito dígitos, seguido por três grupos de quatro dígitos, seguido por um grupo de 12 dígitos, para um total de 32 dígitos que representam os 128 bits.

Um exemplo de UUID é - 550e8400-e29b-41d4-a716-446655440000

Tipo XML

O tipo de dados XML pode ser usado para armazenar dados XML. Para armazenar dados XML, primeiro você deve criar valores XML usando a função xmlparse da seguinte forma -

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Tipo JSON

O tipo de dados json pode ser usado para armazenar dados JSON (JavaScript Object Notation). Esses dados também podem ser armazenados como texto , mas o tipo de dados json tem a vantagem de verificar se cada valor armazenado é um valor JSON válido. Também estão disponíveis funções de suporte relacionadas, que podem ser usadas diretamente para lidar com o tipo de dados JSON da seguinte maneira.

Exemplo	Resultado de exemplo
array_to_json ('{{1,5}, {99,100}}' :: int [])	[[1,5], [99,100]]
row_to_json (linha (1, 'foo'))	{"f1": 1, "f2": "foo"}

Tipo de Matriz

O PostgreSQL oferece a oportunidade de definir uma coluna de uma tabela como um array multidimensional de comprimento variável. Matrizes de qualquer tipo de base, tipo enum ou tipo composto integrado ou definido pelo usuário podem ser criadas.

Declaração de Matrizes

O tipo de matriz pode ser declarado como

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

ou usando a palavra-chave "ARRAY" como

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

Inserindo valores

Os valores da matriz podem ser inseridos como uma constante literal, colocando os valores dos elementos entre chaves e separando-os por vírgulas. Um exemplo é mostrado abaixo -

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

Acessando matrizes

Um exemplo para acessar Arrays é mostrado abaixo. O comando dado abaixo selecionará as pessoas cujas economias são maiores no segundo trimestre do que no quarto trimestre.

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

Modificação de matrizes

Um exemplo de modificação de matrizes é mostrado abaixo.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

ou usando a sintaxe de expressão ARRAY -

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

Pesquisando matrizes

Um exemplo de pesquisa de matrizes é mostrado abaixo.

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

Se o tamanho da matriz for conhecido, o método de pesquisa fornecido acima pode ser usado. Caso contrário, o exemplo a seguir mostra como pesquisar quando o tamanho não é conhecido.

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

Tipos Compostos

Este tipo representa uma lista de nomes de campos e seus tipos de dados, ou seja, estrutura de uma linha ou registro de uma tabela.

Declaração de tipos compostos

O exemplo a seguir mostra como declarar um tipo composto

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
);

Este tipo de dados pode ser usado nas tabelas de criação conforme abaixo -

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

Entrada de valor composto

Os valores compostos podem ser inseridos como uma constante literal, colocando os valores dos campos entre parênteses e separando-os por vírgulas. Um exemplo é mostrado abaixo -

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Isso é válido para o inventário_item definido acima. A palavra-chave ROW é opcional, desde que você tenha mais de um campo na expressão.

Acessando Tipos Compostos

Para acessar um campo de uma coluna composta, use um ponto seguido do nome do campo, da mesma forma que selecionar um campo em um nome de tabela. Por exemplo, para selecionar alguns subcampos de nossa tabela de exemplo on_hand, a consulta seria como mostrado abaixo -

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

Você também pode usar o nome da tabela (por exemplo, em uma consulta multi-tabelas), como este -

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Tipos de intervalo

Os tipos de intervalo representam tipos de dados que usam um intervalo de dados. O tipo de intervalo pode ser intervalos discretos (por exemplo, todos os valores inteiros de 1 a 10) ou intervalos contínuos (por exemplo, qualquer ponto no tempo entre 10h00 e 11h00).

Os tipos de intervalos integrados disponíveis incluem os seguintes intervalos -

int4range - Intervalo de inteiro
int8range - Alcance de bigint
numrange - Variação numérica
tsrange - Intervalo de carimbo de data / hora sem fuso horário
tstzrange - Intervalo de carimbo de data / hora com fuso horário
daterange - Intervalo de data

Os tipos de intervalos personalizados podem ser criados para disponibilizar novos tipos de intervalos, como intervalos de endereços IP usando o tipo inet como base, ou intervalos flutuantes usando o tipo de dados flutuante como base.

Os tipos de intervalo oferecem suporte a limites de intervalo inclusivos e exclusivos usando os caracteres [] e (), respectivamente. Por exemplo, '[4,9)' representa todos os inteiros começando de e incluindo 4 até, mas não incluindo 9.

Tipos de identificador de objeto

Os identificadores de objeto (OIDs) são usados internamente pelo PostgreSQL como chaves primárias para várias tabelas do sistema. Se WITH OIDS for especificado ou a variável de configuração default_with_oids estiver habilitada, somente então, em tais casos, OIDs serão adicionados às tabelas criadas pelo usuário. A tabela a seguir lista vários tipos de alias. Os tipos de alias OID não têm operações próprias, exceto para rotinas de entrada e saída especializadas.

Nome	Referências	Descrição	Exemplo de valor
oid	qualquer	identificador numérico de objeto	564182
regproc	pg_proc	nome da função	soma
procedimento normativo	pg_proc	função com tipos de argumento	soma (int4)
regoper	pg_operator	nome do operador	+
regoperador	pg_operator	operador com tipos de argumento	* (inteiro, inteiro) ou - (NENHUM, inteiro)
regclass	pg_class	nome da relação	pg_type
regtype	pg_type	nome do tipo de dados	inteiro
regconfig	pg_ts_config	configuração de pesquisa de texto	Inglês
regdictionary	pg_ts_dict	dicionário de pesquisa de texto	simples

Pseudo tipos

O sistema de tipos do PostgreSQL contém várias entradas de propósito especial que são chamadas coletivamente de pseudo-tipos. Um pseudo-tipo não pode ser usado como um tipo de dados de coluna, mas pode ser usado para declarar o argumento de uma função ou tipo de resultado.

A tabela abaixo lista os pseudo-tipos existentes.

S. No.	Nome e Descrição
1	any Indica que uma função aceita qualquer tipo de dado de entrada.
2	anyelement Indica que uma função aceita qualquer tipo de dados.
3	anyarray Indica que uma função aceita qualquer tipo de dados de matriz.
4	anynonarray Indica que uma função aceita qualquer tipo de dados que não seja de matriz.
5	anyenum Indica que uma função aceita qualquer tipo de dados enum.
6	anyrange Indica que uma função aceita qualquer tipo de dado de intervalo.
7	cstring Indica que uma função aceita ou retorna uma string C terminada em nulo.
8	internal Indica que uma função aceita ou retorna um tipo de dados interno do servidor.
9	language_handler Um manipulador de chamada de linguagem procedural é declarado para retornar language_handler.
10	fdw_handler Um manipulador de wrapper de dados externos é declarado para retornar fdw_handler.
11	record Identifica uma função que retorna um tipo de linha não especificado.
12	trigger Uma função de gatilho é declarada para retornar o gatilho.
13	void Indica que uma função não retorna nenhum valor.

Este capítulo discute como criar um novo banco de dados em seu PostgreSQL. PostgreSQL fornece duas maneiras de criar um novo banco de dados -

Usando CREATE DATABASE, um comando SQL.
Usando createdb um executável de linha de comando.

Usando CREATE DATABASE

Este comando criará um banco de dados a partir do prompt do shell PostgreSQL, mas você deve ter o privilégio apropriado para criar um banco de dados. Por padrão, o novo banco de dados será criado clonando o modelo de banco de dados do sistema padrão1 .

Sintaxe

A sintaxe básica da instrução CREATE DATABASE é a seguinte -

CREATE DATABASE dbname;

onde dbname é o nome de um banco de dados a ser criado.

Exemplo

A seguir está um exemplo simples, que criará testdb em seu esquema PostgreSQL

postgres=# CREATE DATABASE testdb;
postgres-#

Usando o comando createdb

O executável de linha de comando do PostgreSQL createdb é um invólucro do comando SQL CREATE DATABASE . A única diferença entre este comando e o comando SQL CREATE DATABASE é que o primeiro pode ser executado diretamente a partir da linha de comando e permite que um comentário seja adicionado ao banco de dados, tudo em um comando.

Sintaxe

A sintaxe para createdb é mostrada abaixo -

createdb [option...] [dbname [description]]

Parâmetros

A tabela abaixo lista os parâmetros com suas descrições.

S. No.	Parâmetro e Descrição
1	dbname O nome de um banco de dados a ser criado.
2	description Especifica um comentário a ser associado ao banco de dados recém-criado.
3	options argumentos de linha de comando, que o createdb aceita.

Opções

A tabela a seguir lista os argumentos de linha de comando que createdb aceita -

S. No.	Opção e descrição
1	-D tablespace Especifica o espaço de tabela padrão para o banco de dados.
2	-e Faça eco dos comandos que o createdb gera e envia para o servidor.
3	-E encoding Especifica o esquema de codificação de caracteres a ser usado neste banco de dados.
4	-l locale Especifica o local a ser usado neste banco de dados.
5	-T template Especifica o banco de dados de modelo a partir do qual construir este banco de dados.
6	--help Mostre ajuda sobre os argumentos da linha de comando do createdb e saia.
7	-h host Especifica o nome do host da máquina na qual o servidor está sendo executado.
8	-p port Especifica a porta TCP ou a extensão do arquivo de soquete do domínio Unix local no qual o servidor está escutando as conexões.
9	-U username Nome de usuário com o qual se conectar.
10	-w Nunca emita um prompt de senha.
11	-W Força createdb a solicitar uma senha antes de conectar-se a um banco de dados.

Abra o prompt de comando e vá para o diretório onde o PostgreSQL está instalado. Vá para o diretório bin e execute o seguinte comando para criar um banco de dados.

createdb -h localhost -p 5432 -U postgres testdb
password ******

O comando fornecido acima irá solicitar a senha do usuário administrador do PostgreSQL, que é postgres, por padrão. Portanto, forneça uma senha e prossiga para criar seu novo banco de dados

Depois que um banco de dados é criado usando um dos métodos mencionados acima, você pode verificá-lo na lista de bancos de dados usando \l, ou seja, barra invertida el comando da seguinte forma -

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

postgres-#

Este capítulo explica vários métodos de acesso ao banco de dados. Suponha que já criamos um banco de dados em nosso capítulo anterior. Você pode selecionar o banco de dados usando um dos seguintes métodos -

Prompt de banco de dados SQL
Prompt de comando do sistema operacional

Prompt de banco de dados SQL

Suponha que você já tenha iniciado seu cliente PostgreSQL e tenha acessado o seguinte prompt SQL -

postgres=#

Você pode verificar a lista de banco de dados disponível usando \l, ou seja, barra invertida el comando da seguinte forma -

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

postgres-#

Agora, digite o seguinte comando para conectar / selecionar um banco de dados desejado; aqui, vamos nos conectar ao banco de dados testdb .

postgres=# \c testdb;
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

Prompt de comando do sistema operacional

Você pode selecionar seu banco de dados no próprio prompt de comando no momento em que efetuar login em seu banco de dados. A seguir está um exemplo simples -

psql -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

Agora você está logado no PostgreSQL testdb e pronto para executar seus comandos dentro do testdb. Para sair do banco de dados, você pode usar o comando \ q.

Neste capítulo, discutiremos como excluir o banco de dados no PostgreSQL. Existem duas opções para excluir um banco de dados -

Usando DROP DATABASE, um comando SQL.
Usando dropdb um executável de linha de comando.

Tenha cuidado antes de usar esta operação porque a exclusão de um banco de dados existente resultaria na perda de todas as informações armazenadas no banco de dados.

Usando DROP DATABASE

Este comando elimina um banco de dados. Ele remove as entradas do catálogo para o banco de dados e exclui o diretório que contém os dados. Ele só pode ser executado pelo proprietário do banco de dados. Este comando não pode ser executado enquanto você ou qualquer outra pessoa estiver conectada ao banco de dados de destino (conecte-se ao postgres ou qualquer outro banco de dados para emitir este comando).

Sintaxe

A sintaxe para DROP DATABASE é fornecida abaixo -

DROP DATABASE [ IF EXISTS ] name

Parâmetros

A tabela lista os parâmetros com suas descrições.

S. No.	Parâmetro e Descrição
1	IF EXISTS Não lance um erro se o banco de dados não existir. Um aviso é emitido neste caso.
2	name O nome do banco de dados a ser removido.

Não podemos descartar um banco de dados que tenha conexões abertas, incluindo nossa própria conexão do psql ou pgAdmin III . Devemos mudar para outro banco de dados ou template1 se quisermos deletar o banco de dados ao qual estamos conectados. Portanto, pode ser mais conveniente usar o programa dropdb , que é um wrapper para esse comando.

Exemplo

A seguir está um exemplo simples, que excluirá testdb do seu esquema PostgreSQL -

postgres=# DROP DATABASE testdb;
postgres-#

Usando o comando dropdb

Executável de linha de comando PostgresSQL dropdbé um wrapper de linha de comando em torno do comando SQL DROP DATABASE . Não há diferença efetiva entre eliminar bancos de dados por meio desse utilitário e por meio de outros métodos de acesso ao servidor. dropdb destrói um banco de dados PostgreSQL existente. O usuário que executa este comando deve ser um superusuário do banco de dados ou o proprietário do banco de dados.

Sintaxe

A sintaxe para dropdb é mostrada abaixo -

dropdb  [option...] dbname

Parâmetros

A tabela a seguir lista os parâmetros com suas descrições

S. No.	Parâmetro e Descrição
1	dbname O nome de um banco de dados a ser excluído.
2	option argumentos de linha de comando, que o dropdb aceita.

Opções

A tabela a seguir lista os argumentos de linha de comando que o dropdb aceita -

S. No.	Opção e descrição
1	-e Mostra os comandos que estão sendo enviados ao servidor.
2	-i Emite um prompt de verificação antes de fazer algo destrutivo.
3	-V Imprima a versão do dropdb e saia.
4	--if-exists Não lance um erro se o banco de dados não existir. Um aviso é emitido neste caso.
5	--help Mostre ajuda sobre os argumentos de linha de comando do dropdb e saia.
6	-h host Especifica o nome do host da máquina na qual o servidor está sendo executado.
7	-p port Especifica a porta TCP ou a extensão do arquivo de soquete de domínio UNIX local no qual o servidor está atendendo as conexões.
8	-U username Nome de usuário com o qual se conectar.
9	-w Nunca emita um prompt de senha.
10	-W Força o dropdb a solicitar uma senha antes de se conectar a um banco de dados.
11	--maintenance-db=dbname Especifica o nome do banco de dados ao qual se conectar para eliminar o banco de dados de destino.

Exemplo

O exemplo a seguir demonstra a exclusão de um banco de dados do prompt de comando do sistema operacional -

dropdb -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****

O comando acima elimina o banco de dados testdb. Aqui, eu usei opostgres (encontrado em pg_roles de template1) nome de usuário para eliminar o banco de dados.

A instrução CREATE TABLE do PostgreSQL é usada para criar uma nova tabela em qualquer banco de dados fornecido.

Sintaxe

A sintaxe básica da instrução CREATE TABLE é a seguinte -

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

CREATE TABLE é uma palavra-chave que diz ao sistema de banco de dados para criar uma nova tabela. O nome ou identificador exclusivo da tabela segue a instrução CREATE TABLE. Inicialmente, a tabela vazia no banco de dados atual pertence ao usuário que está emitindo o comando.

A seguir, entre colchetes, vem a lista, definindo cada coluna da tabela e que tipo de tipo de dado é. A sintaxe ficará clara com um exemplo fornecido a seguir.

Exemplos

A seguir está um exemplo, que cria uma tabela COMPANY com ID como chave primária e NOT NULL são as restrições que mostram que esses campos não podem ser NULL ao criar registros nesta tabela -

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Vamos criar mais uma tabela, que usaremos em nossos exercícios nos capítulos subsequentes -

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Você pode verificar se a sua tabela foi criada com sucesso usando \d comando, que será usado para listar todas as tabelas em um banco de dados anexado.

testdb-# \d

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

Usar \d tablename para descrever cada tabela conforme mostrado abaixo -

testdb-# \d company

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

Table "public.company"
  Column   |     Type      | Modifiers
-----------+---------------+-----------
 id        | integer       | not null
 name      | text          | not null
 age       | integer       | not null
 address   | character(50) |
 salary    | real          |
 join_date | date          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

A instrução PostgreSQL DROP TABLE é usada para remover uma definição de tabela e todos os dados, índices, regras, gatilhos e restrições associados a essa tabela.

Você deve ter cuidado ao usar este comando porque uma vez que uma tabela é excluída, todas as informações disponíveis na tabela também serão perdidas para sempre.

Sintaxe

A sintaxe básica da instrução DROP TABLE é a seguinte -

DROP TABLE table_name;

Exemplo

Havíamos criado as tabelas DEPARTAMENTO e EMPRESA no capítulo anterior. Primeiro, verifique essas tabelas (use\d para listar as tabelas) -

testdb-# \d

Isso produziria o seguinte resultado -

List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

Isso significa que as tabelas DEPARTAMENTO e EMPRESA estão presentes. Então, vamos soltá-los da seguinte forma -

testdb=# drop table department, company;

Isso produziria o seguinte resultado -

DROP TABLE
testdb=# \d
relations found.
testdb=#

A mensagem retornada DROP TABLE indica que o comando drop foi executado com sucesso.

UMA schemaé uma coleção nomeada de tabelas. Um esquema também pode conter visualizações, índices, sequências, tipos de dados, operadores e funções. Os esquemas são análogos aos diretórios no nível do sistema operacional, exceto que os esquemas não podem ser aninhados. A instrução CREATE SCHEMA do PostgreSQL cria um esquema.

Sintaxe

A sintaxe básica de CREATE SCHEMA é a seguinte -

CREATE SCHEMA name;

Onde nome é o nome do esquema.

Sintaxe para criar uma tabela no esquema

A sintaxe básica para criar uma tabela no esquema é a seguinte -

CREATE TABLE myschema.mytable (
...
);

Exemplo

Vejamos um exemplo para a criação de um esquema. Conecte-se ao banco de dados testdb e crie um esquema meu esquema da seguinte maneira -

testdb=# create schema myschema;
CREATE SCHEMA

A mensagem "CREATE SCHEMA" significa que o esquema foi criado com sucesso.

Agora, vamos criar uma tabela no esquema acima da seguinte maneira -

testdb=# create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Isso criará uma mesa vazia. Você pode verificar a tabela criada com o comando fornecido abaixo -

testdb=# select * from myschema.company;

Isso produziria o seguinte resultado -

id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

Sintaxe para eliminar o esquema

Para eliminar um esquema se ele estiver vazio (todos os objetos nele foram eliminados), use o comando -

DROP SCHEMA myschema;

Para eliminar um esquema incluindo todos os objetos contidos, use o comando -

DROP SCHEMA myschema CASCADE;

Vantagens de usar um esquema

Ele permite que muitos usuários usem um banco de dados sem interferir uns com os outros.
Ele organiza objetos de banco de dados em grupos lógicos para torná-los mais gerenciáveis.
Os aplicativos de terceiros podem ser colocados em esquemas separados para que não entrem em conflito com os nomes de outros objetos.

O PostgreSQL INSERT INTOinstrução permite inserir novas linhas em uma tabela. Pode-se inserir uma única linha de cada vez ou várias linhas como resultado de uma consulta.

Sintaxe

A sintaxe básica da instrução INSERT INTO é a seguinte -

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Aqui, coluna1, coluna2, ... colunaN são os nomes das colunas na tabela na qual você deseja inserir dados.
Os nomes das colunas de destino podem ser listados em qualquer ordem. Os valores fornecidos pela cláusula VALUES ou consulta são associados à lista de colunas explícita ou implícita da esquerda para a direita.

Você pode não precisar especificar o (s) nome (s) da (s) coluna (s) na consulta SQL se estiver adicionando valores para todas as colunas da tabela. No entanto, certifique-se de que a ordem dos valores esteja na mesma ordem das colunas da tabela. A sintaxe SQL INSERT INTO seria a seguinte -

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Resultado

A tabela a seguir resume as mensagens de saída e seu significado -

S. No.	Mensagem de saída e descrição
1	INSERT oid 1 Mensagem retornada se apenas uma linha foi inserida. oid é o OID numérico da linha inserida.
2	INSERT 0 # Mensagem retornada se mais de uma linha for inserida. # é o número de linhas inseridas.

Exemplos

Vamos criar a tabela COMPANY em testdb como segue -

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE	  DATE
);

O exemplo a seguir insere uma linha na tabela COMPANY -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');

O exemplo a seguir é para inserir uma linha; aqui a coluna de salário é omitida e, portanto, terá o valor padrão -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');

O exemplo a seguir usa a cláusula DEFAULT para a coluna JOIN_DATE em vez de especificar um valor -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );

O exemplo a seguir insere várias linhas usando a sintaxe multirow VALUES -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');

Todas as declarações acima criariam os seguintes registros na tabela COMPANY. O próximo capítulo ensinará como exibir todos esses registros de uma tabela.

ID        NAME        AGE        ADDRESS     SALARY	  JOIN_DATE
----      ----------  -----      ----------  -------      --------
1         Paul        32         California  20000.0      2001-07-13
2         Allen       25         Texas                    2007-12-13
3         Teddy       23         Norway      20000.0
4         Mark        25         Rich-Mond   65000.0      2007-12-13
5         David       27         Texas       85000.0      2007-12-13

PostgreSQL SELECTdeclaração é usada para buscar os dados de uma tabela de banco de dados, que retorna dados na forma de tabela de resultados. Essas tabelas de resultados são chamadas de conjuntos de resultados.

Sintaxe

A sintaxe básica da instrução SELECT é a seguinte -

SELECT column1, column2, columnN FROM table_name;

Aqui, coluna1, coluna2 ... são os campos de uma tabela, cujos valores você deseja buscar. Se você deseja buscar todos os campos disponíveis no campo, você pode usar a seguinte sintaxe -

SELECT * FROM table_name;

Exemplo

Considere a tabela COMPANHIA com os registros a seguir -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A seguir está um exemplo, que buscaria os campos ID, Nome e Salário dos clientes disponíveis na tabela CLIENTES -

testdb=# SELECT ID, NAME, SALARY FROM COMPANY ;

Isso produziria o seguinte resultado -

id | name  | salary
 ----+-------+--------
   1 | Paul  |  20000
   2 | Allen |  15000
   3 | Teddy |  20000
   4 | Mark  |  65000
   5 | David |  85000
   6 | Kim   |  45000
   7 | James |  10000
(7 rows)

Se você deseja buscar todos os campos da tabela CUSTOMERS, use a seguinte consulta -

testdb=# SELECT * FROM COMPANY;

Isso produziria o seguinte resultado -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

O que é um operador no PostgreSQL?

Um operador é uma palavra reservada ou um caractere usado principalmente na cláusula WHERE de uma instrução PostgreSQL para realizar operação (ões), como comparações e operações aritméticas.

Os operadores são usados para especificar condições em uma instrução PostgreSQL e servir como conjunções para várias condições em uma instrução.

Operadores aritméticos
Operadores de comparação
Operadores lógicos
Operadores bit a bit

Operadores aritméticos PostgreSQL

Assumir variável a contém 2 e variável b segura 3, então -

Exemplo

Operador	Descrição	Exemplo
+	Adição - adiciona valores em ambos os lados do operador	a + b dará 5
-	Subtração - subtrai o operando direito do operando esquerdo	a - b dará -1
*	Multiplicação - Multiplica valores em ambos os lados do operador	a * b dará 6
/	Divisão - Divide operando esquerdo pelo operando direito	b / a dará 1
%	Módulo - Divide operando esquerdo pelo operando direito e retorna o resto	b% a dará 1
^	Exponenciação - Fornece o valor expoente do operando à direita	a ^ b dará 8
\| /	raiz quadrada	\| / 25.0 dará 5
\|\| /	raiz cúbica	\|\| / 27,0 dará 3
!	fatorial	5! vai dar 120
!!	fatorial (operador de prefixo)	!! 5 dará 120

Operadores de comparação PostgreSQL

Suponha que a variável a tenha 10 e a variável b tenha 20, então -

Mostrar exemplos

Operador	Descrição	Exemplo
=	Verifica se os valores dos dois operandos são iguais ou não, se sim a condição torna-se verdadeira.	(a = b) não é verdade.
! =	Verifica se os valores de dois operandos são iguais ou não, se os valores não são iguais, a condição se torna verdadeira.	(a! = b) é verdade.
<>	Verifica se os valores de dois operandos são iguais ou não, se os valores não são iguais, a condição se torna verdadeira.	(a <> b) é verdadeiro.
>	Verifica se o valor do operando esquerdo é maior que o valor do operando direito, se sim então a condição torna-se verdadeira.	(a> b) não é verdade.
<	Verifica se o valor do operando esquerdo é menor que o valor do operando direito; se sim, a condição torna-se verdadeira.	(a <b) é verdadeiro.
> =	Verifica se o valor do operando esquerdo é maior ou igual ao valor do operando direito, se sim a condição torna-se verdadeira.	(a> = b) não é verdade.
<=	Verifica se o valor do operando esquerdo é menor ou igual ao valor do operando direito; em caso afirmativo, a condição torna-se verdadeira.	(a <= b) é verdadeiro.

Operadores lógicos PostgreSQL

Aqui está uma lista de todos os operadores lógicos disponíveis no PostgresSQL.

Mostrar exemplos

S. No.	Operador e descrição
1	AND O operador AND permite a existência de várias condições na cláusula WHERE de uma instrução PostgresSQL.
2	NOT O operador NOT inverte o significado do operador lógico com o qual é usado. Por exemplo. NÃO EXISTE, NÃO ENTRE, NÃO ENTRE, etc.This is negate operator.
3	OR O operador OR é usado para combinar várias condições na cláusula WHERE de uma instrução PostgresSQL.

Operadores de string de bits PostgreSQL

O operador bit a bit funciona em bits e executa a operação bit a bit. A tabela de verdade para & e | é o seguinte -

p	q	p & q	p \| q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

Suponha que A = 60; e B = 13; agora em formato binário serão os seguintes -

A = 0011 1100

B = 0000 1101

-----------------

A&B = 0000 1100

A | B = 0011 1101

~ A = 1100 0011

Mostrar exemplos

Os operadores Bitwise suportados pelo PostgreSQL estão listados na tabela a seguir -

Operador	Descrição	Exemplo
E	O operador Binário AND copia um bit para o resultado se ele existir em ambos os operandos.	(A e B) dará 12, que é 0000 1100
\|	O operador binário OR copia um bit se ele existir em qualquer operando.	(A \| B) dará 61, que é 0011 1101
~	O operador de complemento binários é unário e tem o efeito de 'inverter' bits.	(~ A) dará -61 que é 1100 0011 na forma de complemento de 2 devido a um número binário com sinal.
<<	Operador binário de deslocamento à esquerda. O valor dos operandos à esquerda é movido para a esquerda pelo número de bits especificado pelo operando à direita.	Um << 2 dará 240, que é 1111 0000
>>	Operador binário de deslocamento à direita. O valor dos operandos à esquerda é movido para a direita pelo número de bits especificado pelo operando à direita.	Um >> 2 dará 15, que é 0000 1111
#	XOR bit a bit.	A # B dará 49, que é 0100 1001

Uma expressão é uma combinação de um ou mais valores, operadores e funções PostgresSQL que avaliam um valor.

As EXPRESSÕES do PostgreSQL são como fórmulas e são escritas em linguagem de consulta. Você também pode usar para consultar o banco de dados para um conjunto específico de dados.

Sintaxe

Considere a sintaxe básica da instrução SELECT da seguinte maneira -

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

Existem diferentes tipos de expressões PostgreSQL, que são mencionados abaixo -

PostgreSQL - Expressões Booleanas

As expressões booleanas do PostgreSQL buscam os dados com base na correspondência de um único valor. A seguir está a sintaxe -

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

Considere a tabela COMPANHIA com os registros a seguir -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Aqui está um exemplo simples que mostra o uso de Expressões Booleanas PostgreSQL -

testdb=# SELECT * FROM COMPANY WHERE SALARY = 10000;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address  | salary
----+-------+-----+----------+--------
  7 | James |  24 | Houston  |  10000
(1 row)

PostgreSQL - Expressão Numérica

Essas expressões são usadas para realizar qualquer operação matemática em qualquer consulta. A seguir está a sintaxe -

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

Aqui, numerical_expression é usada para expressão matemática ou qualquer fórmula. A seguir está um exemplo simples que mostra o uso de Expressões Numéricas SQL -

testdb=# SELECT (15 + 6) AS ADDITION ;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

addition
----------
       21
(1 row)

Existem várias funções integradas, como avg (), sum (), count () para realizar o que é conhecido como cálculos de dados agregados em uma tabela ou coluna específica da tabela.

testdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

RECORDS
---------
       7
(1 row)

PostgreSQL - Expressões de data

As expressões de data retornam os valores atuais de data e hora do sistema e essas expressões são usadas em várias manipulações de dados.

testdb=#  SELECT CURRENT_TIMESTAMP;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

now
-------------------------------
 2013-05-06 14:38:28.078+05:30
(1 row)

A cláusula PostgreSQL WHERE é usada para especificar uma condição ao buscar os dados de uma única tabela ou unir-se a várias tabelas.

Se a condição fornecida for satisfeita, somente então ele retornará um valor específico da tabela. Você pode filtrar as linhas que não deseja incluir no conjunto de resultados usando a cláusula WHERE.

A cláusula WHERE não é apenas usada na instrução SELECT, mas também é usada na instrução UPDATE, DELETE, etc., que examinaremos nos capítulos subsequentes.

Sintaxe

A sintaxe básica da instrução SELECT com a cláusula WHERE é a seguinte -

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

Você pode especificar uma search_condition usando comparação ou operadores lógicos. like>, <, =, LIKE, NOT, etc. Os exemplos a seguir tornariam este conceito claro.

Exemplo

Considere a tabela COMPANHIA com os registros a seguir -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Aqui estão alguns exemplos simples que mostram o uso de Operadores Lógicos PostgreSQL. A seguir a instrução SELECT listará todos os registros onde AGE é maior ou igual a 25AND salário é maior ou igual a 65.000,00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age |  address   | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

A seguinte instrução SELECT lista todos os registros onde AGE é maior ou igual a 25 OR salário é maior ou igual a 65.000,00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(4 rows)

A seguinte instrução SELECT lista todos os registros onde AGE não é NULL, o que significa todos os registros, porque nenhum dos registros tem AGE igual a NULL -

testdb=#  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   1 | Paul  |  32 | California |  20000
   2 | Allen |  25 | Texas      |  15000
   3 | Teddy |  23 | Norway     |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   5 | David |  27 | Texas      |  85000
   6 | Kim   |  22 | South-Hall |  45000
   7 | James |  24 | Houston    |  10000
(7 rows)

A seguinte instrução SELECT lista todos os registros onde NAME começa com 'Pa', não importa o que venha depois de 'Pa'.

testdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name | age |address    | salary
----+------+-----+-----------+--------
  1 | Paul |  32 | California|  20000

A seguinte instrução SELECT lista todos os registros onde o valor de AGE é 25 ou 27 -

testdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

A seguinte instrução SELECT lista todos os registros em que o valor de AGE não é 25 nem 27 -

testdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(4 rows)

A seguinte instrução SELECT lista todos os registros onde o valor de idade está entre 25 E 27 -

testdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

A seguinte instrução SELECT faz uso da subconsulta SQL onde a subconsulta encontra todos os registros com o campo AGE tendo SALARY> 65000 e a cláusula WHERE posterior está sendo usada junto com o operador EXISTS para listar todos os registros onde AGE da consulta externa existe no resultado retornado por subconsulta -

testdb=# SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

age
-----
  32
  25
  23
  25
  27
  22
  24
(7 rows)

A seguinte instrução SELECT faz uso da subconsulta SQL onde a subconsulta encontra todos os registros com o campo AGE tendo SALARY> 65000 e a cláusula WHERE posterior está sendo usada junto com o operador> para listar todos os registros onde AGE da consulta externa é maior que a idade em o resultado retornado pela subconsulta -

testdb=# SELECT * FROM COMPANY
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000

O PostgreSQL AND e ORoperadores são usados para combinar várias condições para restringir os dados selecionados em uma instrução PostgreSQL. Esses dois operadores são chamados de operadores conjuntivos.

Esses operadores fornecem um meio de fazer várias comparações com diferentes operadores na mesma instrução PostgreSQL.

O operador AND

o ANDoperador permite a existência de várias condições na cláusula WHERE de uma instrução PostgreSQL. Ao usar o operador AND, a condição completa será considerada verdadeira quando todas as condições forem verdadeiras. Por exemplo, [condição1] E [condição2] serão verdadeiras apenas quando ambas as condições1 e 2 forem verdadeiras.

Sintaxe

A sintaxe básica do operador AND com a cláusula WHERE é a seguinte -

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

Você pode combinar um número N de condições usando o operador AND. Para que uma ação seja realizada pela instrução PostgreSQL, seja uma transação ou consulta, todas as condições separadas por AND devem ser TRUE.

Exemplo

Considere a tabela COMPANHIA com os registros a seguir -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A seguinte instrução SELECT lista todos os registros onde AGE é maior ou igual a 25 AND salário é maior ou igual a 65.000,00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

O operador OR

O operador OR também é usado para combinar várias condições na cláusula WHERE de uma instrução PostgreSQL. Ao usar o operador OR, a condição completa será considerada verdadeira quando pelo menos qualquer uma das condições for verdadeira. Por exemplo, [condição1] OU [condição2] será verdadeira se a condição1 ou a condição2 for verdadeira.

Sintaxe

A sintaxe básica do operador OR com a cláusula WHERE é a seguinte -

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

Você pode combinar um número N de condições usando o operador OR. Para que uma ação seja realizada pela instrução PostgreSQL, seja uma transação ou consulta, apenas UMA das condições separadas por OR deve ser TRUE.

Exemplo

Considere a tabela COMPANY , tendo os seguintes registros -

# select * from COMPANY;
  id | name  | age | address   | salary
 ----+-------+-----+-----------+--------
   1 | Paul  |  32 | California|  20000
   2 | Allen |  25 | Texas     |  15000
   3 | Teddy |  23 | Norway    |  20000
   4 | Mark  |  25 | Rich-Mond |  65000
   5 | David |  27 | Texas     |  85000
   6 | Kim   |  22 | South-Hall|  45000
   7 | James |  24 | Houston   |  10000
(7 rows)

A seguinte instrução SELECT lista todos os registros onde AGE é maior ou igual a 25 OR salário é maior ou igual a 65.000,00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(4 rows)

O PostgreSQL UPDATEA consulta é usada para modificar os registros existentes em uma tabela. Você pode usar a cláusula WHERE com a consulta UPDATE para atualizar as linhas selecionadas. Caso contrário, todas as linhas seriam atualizadas.

Sintaxe

A sintaxe básica da consulta UPDATE com a cláusula WHERE é a seguinte -

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Você pode combinar um número N de condições usando os operadores AND ou OR.

Exemplo

Considere a tabela EMPRESA , tendo os registros a seguir -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A seguir está um exemplo, que atualizaria ADDRESS para um cliente, cujo ID é 6 -

testdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

Agora, a tabela COMPANY teria os seguintes registros -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  3 | Teddy |  23 | Norway     |  15000
(7 rows)

Se você deseja modificar todos os valores das colunas ADDRESS e SALARY na tabela COMPANY, você não precisa usar a cláusula WHERE e a consulta UPDATE seria a seguinte -

testdb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

Agora, a tabela COMPANY terá os seguintes registros -

id | name  | age | address | salary
----+-------+-----+---------+--------
  1 | Paul  |  32 | Texas   |  20000
  2 | Allen |  25 | Texas   |  20000
  4 | Mark  |  25 | Texas   |  20000
  5 | David |  27 | Texas   |  20000
  6 | Kim   |  22 | Texas   |  20000
  7 | James |  24 | Texas   |  20000
  3 | Teddy |  23 | Texas   |  20000
(7 rows)

O PostgreSQL DELETEA consulta é usada para excluir os registros existentes de uma tabela. Você pode usar a cláusula WHERE com a consulta DELETE para excluir as linhas selecionadas. Caso contrário, todos os registros seriam excluídos.

Sintaxe

A sintaxe básica da consulta DELETE com a cláusula WHERE é a seguinte -

DELETE FROM table_name
WHERE [condition];

Você pode combinar um número N de condições usando os operadores AND ou OR.

Exemplo

Considere a tabela EMPRESA , tendo os registros a seguir -

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A seguir está um exemplo, que EXCLUIRIA um cliente cujo ID é 7 -

testdb=# DELETE FROM COMPANY WHERE ID = 2;

Agora, a tabela COMPANY terá os seguintes registros -

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
(6 rows)

Se você deseja DELETE todos os registros da tabela COMPANY, você não precisa usar a cláusula WHERE com consultas DELETE, que seriam as seguintes -

testdb=# DELETE FROM COMPANY;

Agora, a tabela COMPANY não possui nenhum registro porque todos os registros foram excluídos pela instrução DELETE.

O PostgreSQL LIKEoperador é usado para comparar valores de texto com um padrão usando curingas. Se a expressão de pesquisa puder ser correspondida à expressão padrão, o operador LIKE retornará verdadeiro, que é1.

Existem dois curingas usados em conjunto com o operador LIKE -

O sinal de porcentagem (%)
O sublinhado (_)

O sinal de porcentagem representa zero, um ou vários números ou caracteres. O sublinhado representa um único número ou caractere. Esses símbolos podem ser usados em combinações.

Se qualquer um desses dois sinais não for usado em conjunto com a cláusula LIKE, então o LIKE atua como o operador igual.

Sintaxe

A sintaxe básica de% e _ é a seguinte -

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

Você pode combinar um número N de condições usando os operadores AND ou OR. Aqui, XXXX pode ser qualquer valor numérico ou string.

Exemplo

Aqui estão alguns exemplos que mostram a parte WHERE com cláusulas LIKE diferentes com os operadores '%' e '_' -

S. No.	Declaração e descrição
1	WHERE SALARY::text LIKE '200%' Encontra todos os valores que começam com 200
2	WHERE SALARY::text LIKE '%200%' Encontra qualquer valor que tenha 200 em qualquer posição
3	WHERE SALARY::text LIKE '_00%' Encontra qualquer valor que tenha 00 na segunda e terceira posições
4	WHERE SALARY::text LIKE '2_%_%' Encontra qualquer valor que comece com 2 e tenha pelo menos 3 caracteres de comprimento
5	WHERE SALARY::text LIKE '%2' Encontra todos os valores que terminam com 2
6	WHERE SALARY::text LIKE '_2%3' Encontra qualquer valor que tenha 2 na segunda posição e termine com 3
7	WHERE SALARY::text LIKE '2___3' Encontra qualquer valor em um número de cinco dígitos que começa com 2 e termina com 3

Postgres LIKE é apenas comparação de strings. Portanto, precisamos converter explicitamente a coluna inteira em string como nos exemplos acima.

Tomemos um exemplo real, considere a tabela COMPANY , tendo os registros a seguir -

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A seguir está um exemplo, que exibiria todos os registros da tabela COMPANY onde AGE começa com 2 -

testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';

Isso produziria o seguinte resultado -

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
(7 rows)

A seguir está um exemplo, que exibiria todos os registros da tabela COMPANY onde ADDRESS terá um hífen (-) dentro do texto -

testdb=# SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

Isso produziria o seguinte resultado -

id | name | age |                      address              | salary
----+------+-----+-------------------------------------------+--------
  4 | Mark |  25 | Rich-Mond                                 |  65000
  6 | Kim  |  22 | South-Hall                                |  45000
(2 rows)

O PostgreSQL LIMIT A cláusula é usada para limitar a quantidade de dados retornada pela instrução SELECT.

Sintaxe

A sintaxe básica da instrução SELECT com a cláusula LIMIT é a seguinte -

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

A seguir está a sintaxe da cláusula LIMIT quando ela é usada junto com a cláusula OFFSET -

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

LIMIT e OFFSET permitem que você recupere apenas uma parte das linhas que são geradas pelo resto da consulta.

Exemplo

Considere a tabela COMPANHIA com os registros a seguir -

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A seguir está um exemplo, que limita a linha na tabela de acordo com o número de linhas que você deseja buscar da tabela -

testdb=# SELECT * FROM COMPANY LIMIT 4;

Isso produziria o seguinte resultado -

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
(4 rows)

No entanto, em certas situações, pode ser necessário selecionar um conjunto de registros de um deslocamento específico. Aqui está um exemplo que pega três registros a partir da terceira posição -

testdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

Isso produziria o seguinte resultado -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(3 rows)

O PostgreSQL ORDER BY cláusula é usada para classificar os dados em ordem crescente ou decrescente, com base em uma ou mais colunas.

Sintaxe

A sintaxe básica da cláusula ORDER BY é a seguinte -

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Você pode usar mais de uma coluna na cláusula ORDER BY. Certifique-se de qualquer coluna que você está usando para classificar, essa coluna deve estar disponível na lista de colunas.

Exemplo

Considere a tabela COMPANHIA com os registros a seguir -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A seguir está um exemplo, que classificaria o resultado em ordem crescente por SALÁRIO -

testdb=# SELECT * FROM COMPANY ORDER BY AGE ASC;

Isso produziria o seguinte resultado -

id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   6 | Kim   |  22 | South-Hall |  45000
   3 | Teddy |  23 | Norway     |  20000
   7 | James |  24 | Houston    |  10000
   8 | Paul  |  24 | Houston    |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   2 | Allen |  25 | Texas      |  15000
   5 | David |  27 | Texas      |  85000
   1 | Paul  |  32 | California |  20000
   9 | James |  44 | Norway     |   5000
  10 | James |  45 | Texas      |   5000
(10 rows)

A seguir está um exemplo, que classificaria o resultado em ordem crescente por NOME e SALÁRIO -

testdb=# SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

Isso produziria o seguinte resultado -

id | name  | age | address      | salary
----+-------+-----+--------------+--------
  2 | Allen |  25 | Texas        |  15000
  5 | David |  27 | Texas        |  85000
 10 | James |  45 | Texas        |   5000
  9 | James |  44 | Norway       |   5000
  7 | James |  24 | Houston      |  10000
  6 | Kim   |  22 | South-Hall   |  45000
  4 | Mark  |  25 | Rich-Mond    |  65000
  1 | Paul  |  32 | California   |  20000
  8 | Paul  |  24 | Houston      |  20000
  3 | Teddy |  23 | Norway       |  20000
(10 rows)

A seguir está um exemplo, que classificaria o resultado em ordem decrescente por NOME -

testdb=# SELECT * FROM COMPANY ORDER BY NAME DESC;

Isso produziria o seguinte resultado -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  3 | Teddy |  23 | Norway     |  20000
  1 | Paul  |  32 | California |  20000
  8 | Paul  |  24 | Houston    |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  9 | James |  44 | Norway     |   5000
 10 | James |  45 | Texas      |   5000
  5 | David |  27 | Texas      |  85000
  2 | Allen |  25 | Texas      |  15000
(10 rows)

O PostgreSQL GROUP BYA cláusula é usada em colaboração com a instrução SELECT para agrupar essas linhas em uma tabela que possui dados idênticos. Isso é feito para eliminar a redundância na saída e / ou agregados de computação que se aplicam a esses grupos.

A cláusula GROUP BY segue a cláusula WHERE em uma instrução SELECT e precede a cláusula ORDER BY.

Sintaxe

A sintaxe básica da cláusula GROUP BY é fornecida a seguir. A cláusula GROUP BY deve seguir as condições da cláusula WHERE e deve preceder a cláusula ORDER BY, se uma for usada.

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

Você pode usar mais de uma coluna na cláusula GROUP BY. Certifique-se de qualquer coluna que você está usando para agrupar, essa coluna deve estar disponível na lista de colunas.

Exemplo

Considere a tabela COMPANHIA com os registros a seguir -

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Se você quiser saber o valor total do salário de cada cliente, a consulta GROUP BY seria a seguinte -

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

Isso produziria o seguinte resultado -

name  |  sum
 -------+-------
  Teddy | 20000
  Paul  | 20000
  Mark  | 65000
  David | 85000
  Allen | 15000
  Kim   | 45000
  James | 10000
(7 rows)

Agora, vamos criar mais três registros na tabela COMPANY usando as seguintes instruções INSERT -

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

Agora, nossa tabela tem os seguintes registros com nomes duplicados -

id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

Novamente, vamos usar a mesma instrução para agrupar por todos os registros usando a coluna NAME da seguinte forma -

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

Isso produziria o seguinte resultado -

name  |  sum
-------+-------
 Allen | 15000
 David | 85000
 James | 20000
 Kim   | 45000
 Mark  | 65000
 Paul  | 40000
 Teddy | 20000
(7 rows)

Vamos usar a cláusula ORDER BY junto com a cláusula GROUP BY da seguinte maneira -

testdb=#  SELECT NAME, SUM(SALARY)
         FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

Isso produziria o seguinte resultado -

name  |  sum
-------+-------
 Teddy | 20000
 Paul  | 40000
 Mark  | 65000
 Kim   | 45000
 James | 20000
 David | 85000
 Allen | 15000
(7 rows)

No PostgreSQL, a consulta WITH fornece uma maneira de escrever instruções auxiliares para uso em uma consulta maior. Ajuda a dividir consultas complicadas e grandes em formulários mais simples, que são facilmente legíveis. Essas instruções geralmente chamadas de Expressões de Tabela Comuns ou CTEs podem ser consideradas como definições de tabelas temporárias que existem apenas para uma consulta.

A consulta WITH sendo uma consulta CTE, é particularmente útil quando a subconsulta é executada várias vezes. É igualmente útil no lugar de tabelas temporárias. Ele calcula a agregação uma vez e nos permite fazer referência a ela por seu nome (pode ser várias vezes) nas consultas.

A cláusula WITH deve ser definida antes de ser usada na consulta.

Sintaxe

A sintaxe básica da consulta WITH é a seguinte -

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

Onde name_for_summary_data é o nome dado à cláusula WITH. O name_for_summary_data pode ser igual a um nome de tabela existente e terá precedência.

Você pode usar instruções de modificação de dados (INSERT, UPDATE ou DELETE) em WITH. Isso permite que você execute várias operações diferentes na mesma consulta.

Recursivo COM

Exemplo

Considere a tabela COMPANHIA com os registros a seguir -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Agora, vamos escrever uma consulta usando a cláusula WITH para selecionar os registros da tabela acima, como segue -

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Agora, vamos escrever uma consulta usando a palavra-chave RECURSIVE junto com a cláusula WITH, para encontrar a soma dos salários abaixo de 20000, como segue -

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

sum
-------
 25000
(1 row)

Vamos escrever uma consulta usando instruções de modificação de dados junto com a cláusula WITH, conforme mostrado abaixo.

Primeiro, crie uma tabela COMPANY1 semelhante à tabela COMPANY. A consulta no exemplo move efetivamente as linhas de COMPANY para COMPANY1. O DELETE em WITH apaga as linhas especificadas de COMPANY, retornando seu conteúdo por meio de sua cláusula RETURNING; e, em seguida, a consulta primária lê essa saída e a insere em COMPANY1 TABLE -

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

INSERT 0 3

Agora, os registros nas tabelas COMPANY e COMPANY1 são os seguintes -

testdb=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


testdb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)

A cláusula HAVING nos permite selecionar linhas específicas onde o resultado da função atende a alguma condição.

A cláusula WHERE coloca condições nas colunas selecionadas, enquanto a cláusula HAVING coloca condições nos grupos criados pela cláusula GROUP BY.

Sintaxe

A seguir está a posição da cláusula HAVING em uma consulta SELECT -

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

A cláusula HAVING deve seguir a cláusula GROUP BY em uma consulta e também deve preceder a cláusula ORDER BY, se usada. A seguir está a sintaxe da instrução SELECT, incluindo a cláusula HAVING -

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Exemplo

Considere a tabela COMPANHIA com os registros a seguir -

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A seguir está um exemplo, que exibiria o registro para o qual a contagem de nomes é menor que 2 -

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

Isso produziria o seguinte resultado -

name
 -------
  Teddy
  Paul
  Mark
  David
  Allen
  Kim
  James
(7 rows)

Agora, vamos criar mais três registros na tabela COMPANY usando as seguintes instruções INSERT -

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

Agora, nossa tabela tem os seguintes registros com nomes duplicados -

id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

A seguir está o exemplo, que exibiria o registro para o qual a contagem de nomes é maior que 1 -

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

Isso produziria o seguinte resultado -

name
-------
 Paul
 James
(2 rows)

O PostgreSQL DISTINCT A palavra-chave é usada em conjunto com a instrução SELECT para eliminar todos os registros duplicados e buscar apenas registros únicos.

Pode haver uma situação em que você tenha vários registros duplicados em uma tabela. Ao buscar esses registros, faz mais sentido buscar apenas registros únicos em vez de buscar registros duplicados.

Sintaxe

A sintaxe básica da palavra-chave DISTINCT para eliminar registros duplicados é a seguinte -

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

Exemplo

Considere a tabela COMPANHIA com os registros a seguir -

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Vamos adicionar mais dois registros a esta tabela da seguinte maneira -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (9, 'Allen', 25, 'Texas', 15000.00 );

Agora, os registros na tabela COMPANY seriam -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  8 | Paul  |  32 | California |  20000
  9 | Allen |  25 | Texas      |  15000
(9 rows)

Primeiro, vamos ver como a seguinte consulta SELECT retorna registros de salários duplicados -

testdb=# SELECT name FROM COMPANY;

Isso produziria o seguinte resultado -

name
-------
 Paul
 Allen
 Teddy
 Mark
 David
 Kim
 James
 Paul
 Allen
(9 rows)

Agora, vamos usar DISTINCT palavra-chave com a consulta SELECT acima e veja o resultado -

testdb=# SELECT DISTINCT name FROM COMPANY;

Isso produziria o seguinte resultado, onde não temos nenhuma entrada duplicada -

name
-------
 Teddy
 Paul
 Mark
 David
 Allen
 Kim
 James
(7 rows)

As restrições são as regras aplicadas às colunas de dados da tabela. Eles são usados para evitar que dados inválidos sejam inseridos no banco de dados. Isso garante a precisão e confiabilidade dos dados no banco de dados.

As restrições podem ser nível de coluna ou nível de tabela. As restrições de nível de coluna são aplicadas apenas a uma coluna, enquanto as restrições de nível de tabela são aplicadas a toda a tabela. Definir um tipo de dados para uma coluna é uma restrição em si. Por exemplo, uma coluna do tipo DATE restringe a coluna a datas válidas.

A seguir estão as restrições comumente usadas disponíveis no PostgreSQL.

NOT NULL Constraint - Garante que uma coluna não possa ter valor NULL.
UNIQUE Constraint - Garante que todos os valores em uma coluna sejam diferentes.
PRIMARY Key - Identifica exclusivamente cada linha / registro em uma tabela de banco de dados.
FOREIGN Key - Restringe os dados com base nas colunas de outras tabelas.
CHECK Constraint - A restrição CHECK garante que todos os valores em uma coluna satisfaçam certas condições.
EXCLUSION Constraint - A restrição EXCLUDE garante que, se quaisquer duas linhas forem comparadas na (s) coluna (s) ou expressão (ões) especificada (s) usando o (s) operador (es) especificado (s), nem todas essas comparações retornarão TRUE.

Restrição NOT NULL

Por padrão, uma coluna pode conter valores NULL. Se você não quiser que uma coluna tenha um valor NULL, então você precisa definir tal restrição nesta coluna especificando que NULL agora não é permitido para aquela coluna. Uma restrição NOT NULL é sempre escrita como uma restrição de coluna.

Um NULL não é o mesmo que nenhum dado; em vez disso, representa dados desconhecidos.

Exemplo

Por exemplo, a seguinte instrução PostgreSQL cria uma nova tabela chamada COMPANY1 e adiciona cinco colunas, três das quais, ID e NAME e AGE, especificam para não aceitar valores NULL -

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Restrição única

A restrição UNIQUE impede que dois registros tenham valores idênticos em uma coluna específica. Na tabela COMPANY, por exemplo, você pode querer evitar que duas ou mais pessoas tenham a mesma idade.

Exemplo

Por exemplo, a seguinte instrução PostgreSQL cria uma nova tabela chamada COMPANY3 e adiciona cinco colunas. Aqui, a coluna AGE é definida como UNIQUE, de modo que você não pode ter dois registros com a mesma idade -

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

Restrição PRIMARY KEY

A restrição PRIMARY KEY identifica exclusivamente cada registro em uma tabela de banco de dados. Pode haver mais colunas UNIQUE, mas apenas uma chave primária em uma tabela. As chaves primárias são importantes ao projetar as tabelas do banco de dados. As chaves primárias são ids únicos.

Nós os usamos para se referir às linhas da tabela. As chaves primárias se tornam chaves estrangeiras em outras tabelas, ao criar relações entre as tabelas. Devido a uma 'supervisão de codificação de longa data', as chaves primárias podem ser NULL no SQLite. Este não é o caso de outros bancos de dados

Uma chave primária é um campo em uma tabela, que identifica exclusivamente cada linha / registro em uma tabela de banco de dados. As chaves primárias devem conter valores exclusivos. Uma coluna de chave primária não pode ter valores NULL.

Uma tabela pode ter apenas uma chave primária, que pode consistir em um ou vários campos. Quando vários campos são usados como uma chave primária, eles são chamados decomposite key.

Se uma tabela tem uma chave primária definida em qualquer campo (s), então você não pode ter dois registros com o mesmo valor desse campo (s).

Exemplo

Você já viu vários exemplos acima, onde criamos a tabela COMAPNY4 com ID como chave primária -

CREATE TABLE COMPANY4(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Restrição FOREIGN KEY

Uma restrição de chave estrangeira especifica que os valores em uma coluna (ou grupo de colunas) devem corresponder aos valores que aparecem em alguma linha de outra tabela. Dizemos que isso mantém a integridade referencial entre duas tabelas relacionadas. Elas são chamadas de chaves estrangeiras porque as restrições são estrangeiras; isto é, fora da mesa. As chaves estrangeiras às vezes são chamadas de chave de referência.

Exemplo

Por exemplo, a seguinte instrução PostgreSQL cria uma nova tabela chamada COMPANY5 e adiciona cinco colunas.

CREATE TABLE COMPANY6(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Por exemplo, a seguinte instrução PostgreSQL cria uma nova tabela chamada DEPARTMENT1, que adiciona três colunas. A coluna EMP_ID é a chave estrangeira e faz referência ao campo ID da tabela COMPANY6.

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
);

Restrição CHECK

A restrição CHECK permite que uma condição verifique o valor que está sendo inserido em um registro. Se a condição for avaliada como falsa, o registro viola a restrição e não é inserido na tabela.

Exemplo

Por exemplo, a seguinte instrução PostgreSQL cria uma nova tabela chamada COMPANY5 e adiciona cinco colunas. Aqui, adicionamos um CHEQUE com a coluna SALÁRIO, para que você não possa ter nenhum SALÁRIO como Zero.

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

Restrição de EXCLUSÃO

As restrições de exclusão garantem que, se quaisquer duas linhas forem comparadas nas colunas ou expressões especificadas usando os operadores especificados, pelo menos uma dessas comparações de operador retornará falso ou nulo.

Exemplo

Por exemplo, a seguinte instrução PostgreSQL cria uma nova tabela chamada COMPANY7 e adiciona cinco colunas. Aqui, adicionamos uma restrição EXCLUDE -

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH <>)
);

Aqui, USING gist é o tipo de índice a ser construído e usado para aplicação.

Você precisa executar o comando CREATE EXTENSION btree_gist , uma vez por banco de dados. Isso instalará a extensão btree_gist, que define as restrições de exclusão em tipos de dados escalares simples.

Como impusemos que a idade tem que ser a mesma, vamos ver isso inserindo registros na tabela -

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
INSERT INTO COMPANY7 VALUES(3, 'Paul', 42, 'California', 20000.00 );

Para as duas primeiras instruções INSERT, os registros são adicionados à tabela COMPANY7. Para a terceira instrução INSERT, o seguinte erro é exibido -

ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL:  Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).

Eliminando restrições

Para remover uma restrição, você precisa saber seu nome. Se o nome for conhecido, é fácil abandoná-lo. Caso contrário, você precisa descobrir o nome gerado pelo sistema. O nome da tabela do comando psql \ d pode ser útil aqui. A sintaxe geral é -

ALTER TABLE table_name DROP CONSTRAINT some_name;

O PostgreSQL Joinscláusula é usada para combinar registros de duas ou mais tabelas em um banco de dados. Um JOIN é um meio de combinar campos de duas tabelas usando valores comuns a cada uma.

Os tipos de junção no PostgreSQL são -

O CROSS JOIN
O INNER JOIN
O LEFT OUTER JOIN
O DIREITO OUTER JOIN
O FULL OUTER JOIN

Antes de prosseguirmos, consideremos duas tabelas, EMPRESA e DEPARTAMENTO. Já vimos instruções INSERT para preencher a tabela COMPANY. Então, vamos supor que a lista de registros disponíveis na tabela COMPANY -

id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13

Outra tabela é DEPARTAMENTO, tem a seguinte definição -

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Aqui está a lista de instruções INSERT para preencher a tabela DEPARTMENT -

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

Por fim, temos a seguinte lista de registros disponíveis na tabela DEPARTAMENTO -

id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7

O CROSS JOIN

Um CROSS JOIN corresponde a cada linha da primeira tabela com cada linha da segunda tabela. Se as tabelas de entrada tiverem colunas xey, respectivamente, a tabela resultante terá colunas x + y. Como os CROSS JOINs têm o potencial de gerar tabelas extremamente grandes, deve-se tomar cuidado para usá-los somente quando apropriado.

A seguir está a sintaxe de CROSS JOIN -

SELECT ... FROM table1 CROSS JOIN table2 ...

Com base nas tabelas acima, podemos escrever um CROSS JOIN da seguinte forma -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

A consulta fornecida acima produzirá o seguinte resultado -

emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance

O INNER JOIN

Um INNER JOIN cria uma nova tabela de resultados combinando valores de coluna de duas tabelas (tabela1 e tabela2) com base no predicado de junção. A consulta compara cada linha da tabela1 com cada linha da tabela2 para encontrar todos os pares de linhas que satisfazem o predicado de junção. Quando o predicado de junção é satisfeito, os valores da coluna para cada par correspondente de linhas de table1 e table2 são combinados em uma linha de resultado.

Um INNER JOIN é o tipo mais comum de junção e é o tipo padrão de junção. Você pode usar a palavra-chave INNER opcionalmente.

A seguir está a sintaxe de INNER JOIN -

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

Com base nas tabelas acima, podemos escrever um INNER JOIN da seguinte forma -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

A consulta fornecida acima produzirá o seguinte resultado -

emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering

O LEFT OUTER JOIN

O OUTER JOIN é uma extensão do INNER JOIN. O padrão SQL define três tipos de OUTER JOINs: LEFT, RIGHT e FULL e o PostgreSQL oferece suporte a todos eles.

No caso de LEFT OUTER JOIN, uma junção interna é executada primeiro. Então, para cada linha na tabela T1 que não satisfaz a condição de junção com nenhuma linha na tabela T2, uma linha junta é adicionada com valores nulos nas colunas de T2. Portanto, a tabela associada sempre tem pelo menos uma linha para cada linha em T1.

A seguir está a sintaxe de LEFT OUTER JOIN -

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

Com base nas tabelas acima, podemos escrever uma junção interna da seguinte maneira -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

A consulta fornecida acima produzirá o seguinte resultado -

emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

O DIREITO OUTER JOIN

Primeiro, uma junção interna é executada. Então, para cada linha na tabela T2 que não satisfaça a condição de junção com nenhuma linha na tabela T1, uma linha junta é adicionada com valores nulos nas colunas de T1. Este é o inverso de uma junção à esquerda; a tabela de resultados sempre terá uma linha para cada linha em T2.

A seguir está a sintaxe de RIGHT OUTER JOIN -

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

Com base nas tabelas acima, podemos escrever uma junção interna da seguinte maneira -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

A consulta fornecida acima produzirá o seguinte resultado -

emp_id | name  | dept
--------+-------+--------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance

O FULL OUTER JOIN

Primeiro, uma junção interna é executada. Então, para cada linha na tabela T1 que não satisfaz a condição de junção com nenhuma linha na tabela T2, uma linha junta é adicionada com valores nulos nas colunas de T2. Além disso, para cada linha de T2 que não satisfaz a condição de junção com nenhuma linha de T1, é adicionada uma linha juntada com valores nulos nas colunas de T1.

A seguir está a sintaxe de FULL OUTER JOIN -

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

Com base nas tabelas acima, podemos escrever uma junção interna da seguinte maneira -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

A consulta fornecida acima produzirá o seguinte resultado -

emp_id | name  | dept
--------+-------+---------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

O PostgreSQL UNION cláusula / operador é usado para combinar os resultados de duas ou mais instruções SELECT sem retornar nenhuma linha duplicada.

Para usar UNION, cada SELECT deve ter o mesmo número de colunas selecionadas, o mesmo número de expressões de coluna, o mesmo tipo de dados e tê-los na mesma ordem, mas não precisam ter o mesmo comprimento.

Sintaxe

A sintaxe básica de UNION é o seguinte -

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Aqui, determinada condição pode ser qualquer expressão com base em seus requisitos.

Exemplo

Considere as duas tabelas a seguir, (a) a tabela COMPANY é a seguinte -

testdb=# SELECT * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

(b) Outra tabela é DEPARTAMENTO da seguinte forma -

testdb=# SELECT * from DEPARTMENT;
 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |      1
  2 | Engineering |      2
  3 | Finance     |      7
  4 | Engineering |      3
  5 | Finance     |      4
  6 | Engineering |      5
  7 | Finance     |      6
(7 rows)

Agora vamos juntar essas duas tabelas usando a instrução SELECT junto com a cláusula UNION da seguinte maneira -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Isso produziria o seguinte resultado -

emp_id | name  |  dept
--------+-------+--------------
      5 | David | Engineering
      6 | Kim   | Finance
      2 | Allen | Engineering
      3 | Teddy | Engineering
      4 | Mark  | Finance
      1 | Paul  | IT Billing
      7 | James | Finance
(7 rows)

A cláusula UNION ALL

O operador UNION ALL é usado para combinar os resultados de duas instruções SELECT incluindo linhas duplicadas. As mesmas regras que se aplicam ao UNION também se aplicam ao operador UNION ALL.

Sintaxe

A sintaxe básica de UNION ALL é o seguinte -

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Aqui, determinada condição pode ser qualquer expressão com base em seus requisitos.

Exemplo

Agora, vamos juntar as duas tabelas mencionadas acima em nossa instrução SELECT da seguinte maneira -

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Isso produziria o seguinte resultado -

emp_id | name  | dept
--------+-------+--------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
(14 rows)

O PostgreSQL NULLé o termo usado para representar um valor ausente. Um valor NULL em uma tabela é um valor em um campo que parece estar em branco.

Um campo com valor NULL é um campo sem valor. É muito importante entender que um valor NULL é diferente de um valor zero ou de um campo que contém espaços.

Sintaxe

A sintaxe básica de uso NULL ao criar uma tabela é o seguinte -

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Aqui, NOT NULLsignifica que a coluna deve sempre aceitar um valor explícito do tipo de dados fornecido. Existem duas colunas onde não usamos NOT NULL. Portanto, isso significa que essas colunas podem ser NULL.

Um campo com valor NULL é aquele que foi deixado em branco durante a criação do registro.

Exemplo

O valor NULL pode causar problemas na seleção dos dados, pois ao comparar um valor desconhecido com qualquer outro valor, o resultado é sempre desconhecido e não é incluído no resultado final. Considere a tabela a seguir, EMPRESA com os seguintes registros -

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Vamos usar a instrução UPDATE para definir alguns valores anuláveis como NULL da seguinte maneira -

testdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

Agora, a tabela COMPANY deve ter os seguintes registros -

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 |             |
  7 | James |  24 |             |
(7 rows)

A seguir, vamos ver o uso de IS NOT NULL operador para listar todos os registros onde SALARY não é NULL -

testdb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY
   FROM COMPANY
   WHERE SALARY IS NOT NULL;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(5 rows)

A seguir está o uso de IS NULL operador que irá listar todos os registros onde SALARY é NULL -

testdb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age | address | salary
----+-------+-----+---------+--------
  6 | Kim   |  22 |         |
  7 | James |  24 |         |
(2 rows)

Você pode renomear uma tabela ou coluna temporariamente, dando outro nome, que é conhecido como ALIAS. O uso de apelidos de tabela significa renomear uma tabela em uma instrução PostgreSQL particular. A renomeação é uma mudança temporária e o nome real da tabela não muda no banco de dados.

Os apelidos de coluna são usados para renomear as colunas de uma tabela com o propósito de uma consulta PostgreSQL específica.

Sintaxe

A sintaxe básica de table alias é o seguinte -

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

A sintaxe básica de column alias é o seguinte -

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

Exemplo

Considere as duas tabelas a seguir, (a) a tabela COMPANY é a seguinte -

testdb=# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

(b) Outra tabela é DEPARTAMENTO da seguinte forma -

id | dept         | emp_id
----+--------------+--------
  1 | IT Billing   |      1
  2 | Engineering  |      2
  3 | Finance      |      7
  4 | Engineering  |      3
  5 | Finance      |      4
  6 | Engineering  |      5
  7 | Finance      |      6
(7 rows)

Agora, a seguir está o uso de TABLE ALIAS onde usamos C e D como aliases para as tabelas COMPANY e DEPARTMENT, respectivamente -

testdb=# SELECT C.ID, C.NAME, C.AGE, D.DEPT
   FROM COMPANY AS C, DEPARTMENT AS D
   WHERE  C.ID = D.EMP_ID;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

id | name  | age |  dept
----+-------+-----+------------
  1 | Paul  |  32 | IT Billing
  2 | Allen |  25 | Engineering
  7 | James |  24 | Finance
  3 | Teddy |  23 | Engineering
  4 | Mark  |  25 | Finance
  5 | David |  27 | Engineering
  6 | Kim   |  22 | Finance
(7 rows)

Vamos ver um exemplo para o uso de COLUMN ALIAS onde COMPANY_ID é um alias da coluna ID e COMPANY_NAME é um alias da coluna de nome -

testdb=# SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
   FROM COMPANY AS C, DEPARTMENT AS D
   WHERE  C.ID = D.EMP_ID;

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

company_id | company_name | age | dept
------------+--------------+-----+------------
      1     | Paul         |  32 | IT Billing
      2     | Allen        |  25 | Engineering
      7     | James        |  24 | Finance
      3     | Teddy        |  23 | Engineering
      4     | Mark         |  25 | Finance
      5     | David        |  27 | Engineering
      6     | Kim          |  22 | Finance
(7 rows)

PostgreSQL Triggers são funções de retorno de chamada de banco de dados, que são executadas / chamadas automaticamente quando ocorre um evento de banco de dados especificado.

A seguir estão alguns pontos importantes sobre os gatilhos do PostgreSQL -

O gatilho PostgreSQL pode ser especificado para disparar
- Antes que a operação seja tentada em uma linha (antes que as restrições sejam verificadas e INSERT, UPDATE ou DELETE seja tentada)
- Depois que a operação for concluída (depois que as restrições forem verificadas e INSERT, UPDATE ou DELETE for concluído)
- Em vez da operação (no caso de inserções, atualizações ou exclusões em uma visualização)
Um gatilho marcado PARA CADA LINHA é chamado uma vez para cada linha que a operação modifica. Em contraste, um gatilho marcado como FOR EACH STATEMENT é executado apenas uma vez para qualquer operação, independentemente de quantas linhas ele modifica.
Tanto a cláusula WHEN quanto as ações de gatilho podem acessar elementos da linha sendo inserida, excluída ou atualizada usando referências do formulário NEW.column-name e OLD.column-name, em que nome-da-coluna é o nome de uma coluna da tabela à qual o acionador está associado.
Se uma cláusula WHEN for fornecida, as instruções do PostgreSQL especificadas serão executadas apenas para linhas para as quais a cláusula WHEN é verdadeira. Se nenhuma cláusula WHEN for fornecida, as instruções PostgreSQL serão executadas para todas as linhas.
Se vários gatilhos do mesmo tipo forem definidos para o mesmo evento, eles serão disparados em ordem alfabética por nome.
As palavras-chave BEFORE, AFTER ou INSTEAD OF determinam quando as ações do gatilho serão executadas em relação à inserção, modificação ou remoção da linha associada.
Os gatilhos são eliminados automaticamente quando a tabela à qual estão associados é eliminada.
A tabela a ser modificada deve existir no mesmo banco de dados que a tabela ou visão à qual o gatilho está anexado e deve-se usar apenas tablename, não database.tablename.
Uma opção CONSTRAINT, quando especificada, cria um gatilho de restrição . É o mesmo que um gatilho normal, exceto que o tempo de disparo do gatilho pode ser ajustado usando SET CONSTRAINTS. Espera-se que os gatilhos de restrição levantem uma exceção quando as restrições que implementam forem violadas.

Sintaxe

A sintaxe básica para criar um trigger é o seguinte -

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

Aqui, event_namepode ser a operação de banco de dados INSERT, DELETE, UPDATE e TRUNCATE na tabela mencionadatable_name. Você pode opcionalmente especificar FOR EACH ROW após o nome da tabela.

A seguir está a sintaxe de criação de um gatilho em uma operação UPDATE em uma ou mais colunas especificadas de uma tabela da seguinte maneira -

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
];

Exemplo

Vamos considerar um caso em que queremos manter um teste de auditoria para cada registro sendo inserido na tabela COMPANY, que criaremos novamente como segue (Abandone a tabela COMPANY se você já a tiver).

testdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Para manter o teste de auditoria, criaremos uma nova tabela chamada AUDIT onde as mensagens de log serão inseridas sempre que houver uma entrada na tabela COMPANY para um novo registro -

testdb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Aqui, ID é o ID do registro de AUDIT e EMP_ID é o ID, que virá da tabela COMPANY, e DATE manterá a data e hora de quando o registro será criado na tabela COMPANY. Então, agora, vamos criar um gatilho na tabela COMPANY da seguinte forma -

testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

Onde auditlogfunc () é um PostgreSQL procedure e tem a seguinte definição -

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Agora, vamos começar o trabalho real. Vamos começar inserindo um registro na tabela COMPANY que deve resultar na criação de um registro de log de auditoria na tabela AUDIT. Então, vamos criar um registro na tabela COMPANY da seguinte forma -

testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

Isso criará um registro na tabela COMPANY, que é a seguinte -

id | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000

Ao mesmo tempo, um registro será criado na tabela AUDIT. Este registro é o resultado de uma trigger, que criamos na operação INSERT na tabela COMPANY. Da mesma forma, você pode criar seus gatilhos em operações UPDATE e DELETE com base em seus requisitos.

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

Listando TRIGGERS

Você pode listar todos os gatilhos no banco de dados atual de pg_trigger tabela da seguinte forma -

testdb=# SELECT * FROM pg_trigger;

A instrução PostgreSQL fornecida acima listará todos os gatilhos.

Se você quiser listar os gatilhos em uma tabela específica, use a cláusula AND com o nome da tabela da seguinte maneira -

testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

A instrução PostgreSQL fornecida acima também listará apenas uma entrada da seguinte forma -

tgname
-----------------
 example_trigger
(1 row)

Soltando TRIGGERS

A seguir está o comando DROP, que pode ser usado para descartar um gatilho existente -

testdb=# DROP TRIGGER trigger_name;

Os índices são tabelas de pesquisa especiais que o mecanismo de pesquisa do banco de dados pode usar para acelerar a recuperação de dados. Simplificando, um índice é um ponteiro para dados em uma tabela. Um índice em um banco de dados é muito semelhante a um índice no final de um livro.

Por exemplo, se quiser fazer referência a todas as páginas de um livro que discute um determinado tópico, você deve primeiro consultar o índice, que lista todos os tópicos em ordem alfabética e, em seguida, referir-se a um ou mais números de página específicos.

Um índice ajuda a acelerar consultas SELECT e cláusulas WHERE; no entanto, torna a entrada de dados mais lenta, com instruções UPDATE e INSERT. Os índices podem ser criados ou eliminados sem nenhum efeito nos dados.

A criação de um índice envolve a instrução CREATE INDEX, que permite nomear o índice, especificar a tabela e qual coluna ou colunas indexar e indicar se o índice está em ordem crescente ou decrescente.

Os índices também podem ser exclusivos, semelhantes à restrição UNIQUE, no sentido de que o índice evita entradas duplicadas na coluna ou combinação de colunas nas quais há um índice.

O comando CREATE INDEX

A sintaxe básica de CREATE INDEX é o seguinte -

CREATE INDEX index_name ON table_name;

Tipos de índice

O PostgreSQL fornece vários tipos de índice: B-tree, Hash, GiST, SP-GiST e GIN. Cada tipo de índice usa um algoritmo diferente que é mais adequado para diferentes tipos de consultas. Por padrão, o comando CREATE INDEX cria índices de árvore B, que se ajustam às situações mais comuns.

Índices de coluna única

Um índice de coluna única é aquele criado com base em apenas uma coluna da tabela. A sintaxe básica é a seguinte -

CREATE INDEX index_name
ON table_name (column_name);

Índices de várias colunas

Um índice de várias colunas é definido em mais de uma coluna de uma tabela. A sintaxe básica é a seguinte -

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Seja para criar um índice de coluna única ou um índice de várias colunas, leve em consideração a (s) coluna (s) que você pode usar com muita frequência na cláusula WHERE de uma consulta como condições de filtro.

Se houver apenas uma coluna usada, um índice de coluna única deve ser a escolha. Se houver duas ou mais colunas que são frequentemente usadas na cláusula WHERE como filtros, o índice de várias colunas seria a melhor escolha.

Índices Únicos

Índices exclusivos são usados não apenas para desempenho, mas também para integridade de dados. Um índice exclusivo não permite que nenhum valor duplicado seja inserido na tabela. A sintaxe básica é a seguinte -

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Índices Parciais

Um índice parcial é um índice construído sobre um subconjunto de uma tabela; o subconjunto é definido por uma expressão condicional (chamada de predicado do índice parcial). O índice contém entradas apenas para as linhas da tabela que satisfazem o predicado. A sintaxe básica é a seguinte -

CREATE INDEX index_name
on table_name (conditional_expression);

Índices implícitos

Índices implícitos são índices criados automaticamente pelo servidor de banco de dados quando um objeto é criado. Os índices são criados automaticamente para restrições de chave primária e restrições exclusivas.

Exemplo

O seguinte é um exemplo em que criaremos um índice na tabela COMPANY para a coluna de salários -

# CREATE INDEX salary_index ON COMPANY (salary);

Agora, vamos listar todos os índices disponíveis na tabela COMPANY usando \d company comando.

# \d company

Isso produzirá o seguinte resultado, onde company_pkey é um índice implícito, que foi criado quando a tabela foi criada.

Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

Você pode listar todo o banco de dados de índices usando o \di comando -

O comando DROP INDEX

Um índice pode ser eliminado usando PostgreSQL DROPcomando. Deve-se ter cuidado ao descartar um índice, pois o desempenho pode ser reduzido ou melhorado.

A sintaxe básica é a seguinte -

DROP INDEX index_name;

Você pode usar a seguinte instrução para excluir o índice criado anteriormente -

# DROP INDEX salary_index;

Quando os índices devem ser evitados?

Embora os índices tenham como objetivo aprimorar o desempenho de um banco de dados, há momentos em que eles devem ser evitados. As seguintes diretrizes indicam quando o uso de um índice deve ser reconsiderado -

Os índices não devem ser usados em tabelas pequenas.
Tabelas que possuem operações de inserção ou atualização de lote frequentes e grandes.
Os índices não devem ser usados em colunas que contêm um grande número de valores NULL.
As colunas que são frequentemente manipuladas não devem ser indexadas.

O PostgreSQL ALTER TABLE comando é usado para adicionar, excluir ou modificar colunas em uma tabela existente.

Você também usaria o comando ALTER TABLE para adicionar e eliminar várias restrições em uma tabela existente.

Sintaxe

A sintaxe básica de ALTER TABLE adicionar uma nova coluna em uma tabela existente é o seguinte -

ALTER TABLE table_name ADD column_name datatype;

A sintaxe básica de ALTER TABLE para DROP COLUMN em uma tabela existente é o seguinte -

ALTER TABLE table_name DROP COLUMN column_name;

A sintaxe básica de ALTER TABLE para alterar o DATA TYPE de uma coluna em uma tabela é o seguinte -

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

A sintaxe básica de ALTER TABLE para adicionar um NOT NULL restrição a uma coluna em uma tabela é a seguinte -

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

A sintaxe básica de ALTER TABLE para ADD UNIQUE CONSTRAINT a uma mesa é o seguinte -

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

A sintaxe básica de ALTER TABLE para ADD CHECK CONSTRAINT a uma mesa é o seguinte -

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

A sintaxe básica de ALTER TABLE para ADD PRIMARY KEY restrição a uma tabela é a seguinte -

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

A sintaxe básica de ALTER TABLE para DROP CONSTRAINT de uma mesa é o seguinte -

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

Se você estiver usando MySQL, o código é o seguinte -

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

A sintaxe básica de ALTER TABLE para DROP PRIMARY KEY restrição de uma tabela é a seguinte -

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

Se você estiver usando MySQL, o código é o seguinte -

ALTER TABLE table_name
DROP PRIMARY KEY;

Exemplo

Considere que nossa tabela COMPANY tem os seguintes registros -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

O seguinte é o exemplo para ADICIONAR uma nova coluna em uma tabela existente -

testdb=# ALTER TABLE COMPANY ADD GENDER char(1);

Agora, a tabela COMPANY foi alterada e o seguinte seria a saída da instrução SELECT -

id | name  | age | address     | salary | gender
----+-------+-----+-------------+--------+--------
  1 | Paul  |  32 | California  |  20000 |
  2 | Allen |  25 | Texas       |  15000 |
  3 | Teddy |  23 | Norway      |  20000 |
  4 | Mark  |  25 | Rich-Mond   |  65000 |
  5 | David |  27 | Texas       |  85000 |
  6 | Kim   |  22 | South-Hall  |  45000 |
  7 | James |  24 | Houston     |  10000 |
(7 rows)

A seguir está o exemplo para DROP a coluna de gênero da tabela existente -

testdb=# ALTER TABLE COMPANY DROP GENDER;

Agora, a tabela COMPANY foi alterada e o seguinte seria a saída da instrução SELECT -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

O PostgreSQL TRUNCATE TABLEcomando é usado para excluir dados completos de uma tabela existente. Você também pode usar o comando DROP TABLE para excluir a tabela completa, mas removeria a estrutura completa da tabela do banco de dados e você precisaria recriar esta tabela novamente se desejar armazenar alguns dados.

Ele tem o mesmo efeito de DELETE em cada tabela, mas como não verifica as tabelas de fato, é mais rápido. Além disso, ele recupera espaço em disco imediatamente, em vez de exigir uma operação VACUUM subsequente. Isso é mais útil em grandes tabelas.

Sintaxe

A sintaxe básica de TRUNCATE TABLE é o seguinte -

TRUNCATE TABLE  table_name;

Exemplo

Considere que a tabela COMPANY possui os seguintes registros -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(7 rows)

A seguir está o exemplo para truncar -

testdb=# TRUNCATE TABLE COMPANY;

Agora, a tabela COMPANY está truncada e o seguinte seria a saída da instrução SELECT -

testdb=# SELECT * FROM CUSTOMERS;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

As visualizações são pseudotabelas. Ou seja, não são mesas reais; no entanto, aparecem como tabelas comuns para SELECT. Uma visão pode representar um subconjunto de uma tabela real, selecionando certas colunas ou certas linhas de uma tabela comum. Uma visão pode até representar tabelas unidas. Como as visualizações são atribuídas permissões separadas, você pode usá-las para restringir o acesso à tabela para que os usuários vejam apenas linhas ou colunas específicas de uma tabela.

Uma visualização pode conter todas as linhas de uma tabela ou linhas selecionadas de uma ou mais tabelas. Uma visão pode ser criada a partir de uma ou várias tabelas, o que depende da consulta PostgreSQL escrita para criar uma visão.

As visualizações, que são uma espécie de tabelas virtuais, permitem que os usuários façam o seguinte -

Estruture os dados de uma maneira que os usuários ou classes de usuários considerem natural ou intuitiva.
Restrinja o acesso aos dados de forma que um usuário só possa ver dados limitados em vez da tabela completa.
Resuma os dados de várias tabelas, que podem ser usadas para gerar relatórios.

Visto que as visualizações não são tabelas comuns, você pode não ser capaz de executar uma instrução DELETE, INSERT ou UPDATE em uma visualização. No entanto, você pode criar uma RULE para corrigir este problema de usar DELETE, INSERT ou UPDATE em uma visualização.

Criando Vistas

As visualizações do PostgreSQL são criadas usando o CREATE VIEWdeclaração. As visualizações do PostgreSQL podem ser criadas a partir de uma única tabela, várias tabelas ou outra visualização.

A sintaxe básica de CREATE VIEW é a seguinte -

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Você pode incluir várias tabelas em sua instrução SELECT de maneira muito semelhante à utilizada na consulta SELECT normal do PostgreSQL. Se a palavra-chave opcional TEMP ou TEMPORARY estiver presente, a visualização será criada no espaço temporário. As visualizações temporárias são eliminadas automaticamente no final da sessão atual.

Exemplo

Considere, a tabela COMPANY está tendo os seguintes registros -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000

Agora, a seguir está um exemplo para criar uma visão da tabela COMPANY. Esta visualização seria usada para ter apenas algumas colunas da tabela COMPANY -

testdb=# CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

Agora, você pode consultar COMPANY_VIEW de maneira semelhante à de uma tabela real. A seguir está o exemplo -

testdb=# SELECT * FROM COMPANY_VIEW;

Isso produziria o seguinte resultado -

id | name  | age
----+-------+-----
  1 | Paul  |  32
  2 | Allen |  25
  3 | Teddy |  23
  4 | Mark  |  25
  5 | David |  27
  6 | Kim   |  22
  7 | James |  24
(7 rows)

Queda de visualizações

Para eliminar uma visão, basta usar a instrução DROP VIEW com o view_name. A sintaxe básica de DROP VIEW é a seguinte -

testdb=# DROP VIEW view_name;

O comando a seguir excluirá a visualização COMPANY_VIEW, que criamos na última seção -

testdb=# DROP VIEW COMPANY_VIEW;

Uma transação é uma unidade de trabalho executada em um banco de dados. As transações são unidades ou sequências de trabalho realizadas em uma ordem lógica, seja de forma manual por um usuário ou automaticamente por algum tipo de programa de banco de dados.

Uma transação é a propagação de uma ou mais alterações no banco de dados. Por exemplo, se você estiver criando um registro, atualizando um registro ou excluindo um registro da tabela, você está executando uma transação na tabela. É importante controlar as transações para garantir a integridade dos dados e lidar com os erros do banco de dados.

Praticamente, você agrupará muitas consultas PostgreSQL em um grupo e executará todas elas juntas como parte de uma transação.

Propriedades das transações

As transações têm as quatro propriedades padrão a seguir, geralmente conhecidas pela sigla ACID -

Atomicity- Garante que todas as operações dentro da unidade de trabalho sejam concluídas com sucesso; caso contrário, a transação é abortada no ponto de falha e as operações anteriores são revertidas para seu estado anterior.
Consistency - Garante que o banco de dados mude de estado corretamente após uma transação confirmada com sucesso.
Isolation - Permite que as transações operem de forma independente e transparente entre si.
Durability - Garante que o resultado ou efeito de uma transação confirmada persiste em caso de falha do sistema.

Controle de transações

Os seguintes comandos são usados para controlar transações -

BEGIN TRANSACTION - Para iniciar uma transação.
COMMIT - Para salvar as alterações, como alternativa, você pode usar END TRANSACTION comando.
ROLLBACK - Para reverter as alterações.

Os comandos de controle transacional são usados apenas com os comandos DML INSERT, UPDATE e DELETE. Eles não podem ser usados ao criar tabelas ou eliminá-los porque essas operações são confirmadas automaticamente no banco de dados.

O comando BEGIN TRANSACTION

As transações podem ser iniciadas usando BEGIN TRANSACTION ou simplesmente o comando BEGIN. Essas transações geralmente persistem até que o próximo comando COMMIT ou ROLLBACK seja encontrado. Mas uma transação também ROLLBACK se o banco de dados for fechado ou se ocorrer um erro.

A seguir está a sintaxe simples para iniciar uma transação -

BEGIN;

or

BEGIN TRANSACTION;

O Comando COMMIT

O comando COMMIT é o comando transacional usado para salvar as alterações invocadas por uma transação no banco de dados.

O comando COMMIT salva todas as transações no banco de dados desde o último comando COMMIT ou ROLLBACK.

A sintaxe do comando COMMIT é a seguinte -

COMMIT;

or

END TRANSACTION;

O comando ROLLBACK

O comando ROLLBACK é o comando transacional usado para desfazer transações que ainda não foram salvas no banco de dados.

O comando ROLLBACK só pode ser usado para desfazer transações desde que o último comando COMMIT ou ROLLBACK foi emitido.

A sintaxe do comando ROLLBACK é a seguinte -

ROLLBACK;

Exemplo

Considere que a tabela COMPANY possui os seguintes registros -

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

Now, let us start a transaction and delete records from the table having age = 25 and finally we use ROLLBACK command to undo all the changes.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

If you will check COMPANY table is still having the following records −

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

Now, let us start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

If you will check the COMPANY table, it still has the following records −

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)

Locks or Exclusive Locks or Write Locks prevent users from modifying a row or an entire table. Rows modified by UPDATE and DELETE are then exclusively locked automatically for the duration of the transaction. This prevents other users from changing the row until the transaction is either committed or rolled back.

The only time when users must wait for other users is when they are trying to modify the same row. If they modify different rows, no waiting is necessary. SELECT queries never have to wait.

The database performs locking automatically. In certain cases, however, locking must be controlled manually. Manual locking can be done by using the LOCK command. It allows specification of a transaction's lock type and scope.

Syntax for LOCK command

The basic syntax for LOCK command is as follows −

LOCK [ TABLE ]
name
 IN
lock_mode

name − The name (optionally schema-qualified) of an existing table to lock. If ONLY is specified before the table name, only that table is locked. If ONLY is not specified, the table and all its descendant tables (if any) are locked.
lock_mode − The lock mode specifies which locks this lock conflicts with. If no lock mode is specified, then ACCESS EXCLUSIVE, the most restrictive mode, is used. Possible values are: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE.

Once obtained, the lock is held for the remainder of the current transaction. There is no UNLOCK TABLE command; locks are always released at the transaction end.

DeadLocks

Deadlocks can occur when two transactions are waiting for each other to finish their operations. While PostgreSQL can detect them and end them with a ROLLBACK, deadlocks can still be inconvenient. To prevent your applications from running into this problem, make sure to design them in such a way that they will lock objects in the same order.

Advisory Locks

PostgreSQL provides means for creating locks that have application-defined meanings. These are called advisory locks. As the system does not enforce their use, it is up to the application to use them correctly. Advisory locks can be useful for locking strategies that are an awkward fit for the MVCC model.

For example, a common use of advisory locks is to emulate pessimistic locking strategies typical of the so-called "flat file" data management systems. While a flag stored in a table could be used for the same purpose, advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of the session.

Example

Consider the table COMPANY having records as follows −

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The following example locks the COMPANY table within the testdb database in ACCESS EXCLUSIVE mode. The LOCK statement works only in a transaction mode −

testdb=#BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

The above given PostgreSQL statement will produce the following result −

LOCK TABLE

The above message indicates that the table is locked until the transaction ends and to finish the transaction you will have to either rollback or commit the transaction.

A subquery or Inner query or Nested query is a query within another PostgreSQL query and embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE and DELETE statements along with the operators like =, <, >, >=, <=, IN, etc.

There are a few rules that subqueries must follow −

Subqueries must be enclosed within parentheses.
A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
Subqueries that return more than one row can only be used with multiple value operators, such as the IN, EXISTS, NOT IN, ANY/SOME, ALL operator.
The BETWEEN operator cannot be used with a subquery; however, the BETWEEN can be used within the subquery.

Subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

Example

Consider the COMPANY table having the following records −

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Now, let us check the following sub-query with SELECT statement −

testdb=# SELECT *
   FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY
      WHERE SALARY > 45000) ;

This would produce the following result −

id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(2 rows)

Subqueries with the INSERT Statement

Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.

The basic syntax is as follows −

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Example

Consider a table COMPANY_BKP, with similar structure as COMPANY table and can be created using the same CREATE TABLE using COMPANY_BKP as the table name. Now, to copy complete COMPANY table into COMPANY_BKP, following is the syntax −

testdb=# INSERT INTO COMPANY_BKP
   SELECT * FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY) ;

Subqueries with the UPDATE Statement

The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.

The basic syntax is as follows −

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example

Assuming, we have COMPANY_BKP table available, which is backup of the COMPANY table.

The following example updates SALARY by 0.50 times in the COMPANY table for all the customers, whose AGE is greater than or equal to 27 −

testdb=# UPDATE COMPANY
   SET SALARY = SALARY * 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

This would affect two rows and finally the COMPANY table would have the following records −

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  1 | Paul  |  32 | California  |  10000
  5 | David |  27 | Texas       |  42500
(7 rows)

Subqueries with the DELETE Statement

The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.

The basic syntax is as follows −

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example

Assuming, we have COMPANY_BKP table available, which is a backup of the COMPANY table.

The following example deletes records from the COMPANY table for all the customers, whose AGE is greater than or equal to 27 −

testdb=# DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE > 27 );

This would affect two rows and finally the COMPANY table would have the following records −

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  5 | David |  27 | Texas       |  42500
(6 rows)

PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases.

If you wish a serial column to have a unique constraint or be a primary key, it must now be specified, just like any other data type.

The type name serial creates an integer columns. The type name bigserial creates a bigint column. bigserial should be used if you anticipate the use of more than 2³¹ identifiers over the lifetime of the table. The type name smallserial creates a smallint column.

Syntax

The basic usage of SERIAL dataype is as follows −

CREATE TABLE tablename (
   colname SERIAL
);

Example

Consider the COMPANY table to be created as follows −

testdb=# CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Now, insert the following records into table COMPANY −

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );


INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

Isso irá inserir sete tuplas na tabela EMPRESA e EMPRESA terá os seguintes registros -

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000

Sempre que um objeto é criado em um banco de dados, um proprietário é atribuído a ele. O proprietário é geralmente aquele que executou a instrução de criação. Para a maioria dos tipos de objetos, o estado inicial é que apenas o proprietário (ou um superusuário) pode modificar ou excluir o objeto. Para permitir que outras funções ou usuários o usem, privilégios ou permissão devem ser concedidos.

Diferentes tipos de privilégios no PostgreSQL são -

SELECT,
INSERT,
UPDATE,
DELETE,
TRUNCATE,
REFERENCES,
TRIGGER,
CREATE,
CONNECT,
TEMPORARY,
EXECUTE, e
USAGE

Dependendo do tipo de objeto (tabela, função, etc.), os privilégios são aplicados ao objeto. Para atribuir privilégios aos usuários, o comando GRANT é usado.

Sintaxe para GRANT

A sintaxe básica para o comando GRANT é a seguinte -

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

privilege - os valores podem ser: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
object- O nome de um objeto ao qual conceder acesso. Os objetos possíveis são: tabela, visão, sequência
PUBLIC - Um pequeno formulário que representa todos os usuários.
GRUPO group - Um grupo a quem conceder privilégios.
username- O nome de um usuário a quem conceder privilégios. PUBLIC é uma forma abreviada que representa todos os usuários.

Os privilégios podem ser revogados usando o comando REVOKE.

Sintaxe para REVOKE

A sintaxe básica para o comando REVOKE é a seguinte -

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

privilege - os valores podem ser: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
object- O nome de um objeto ao qual conceder acesso. Os objetos possíveis são: tabela, visão, sequência
PUBLIC - Um pequeno formulário que representa todos os usuários.
GRUPO group - Um grupo a quem conceder privilégios.
username- O nome de um usuário a quem conceder privilégios. PUBLIC é uma forma abreviada que representa todos os usuários.

Exemplo

Para entender os privilégios, vamos primeiro criar um USUÁRIO da seguinte maneira -

testdb=# CREATE USER manisha WITH PASSWORD 'password';
CREATE ROLE

A mensagem CREATE ROLE indica que o USER "manisha" foi criado.

Considere a tabela COMPANHIA com os registros a seguir -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Em seguida, vamos conceder todos os privilégios em uma empresa de mesa ao usuário "manisha" da seguinte forma -

testdb=# GRANT ALL ON COMPANY TO manisha;
GRANT

A mensagem GRANT indica que todos os privilégios são atribuídos ao USUÁRIO.

Em seguida, revogemos os privilégios do USUÁRIO "manisha" da seguinte forma -

testdb=# REVOKE ALL ON COMPANY FROM manisha;
REVOKE

A mensagem REVOKE indica que todos os privilégios foram revogados do USUÁRIO.

Você pode até mesmo excluir o usuário da seguinte forma -

testdb=# DROP USER manisha;
DROP ROLE

A mensagem DROP ROLE indica que USER 'Manisha' foi excluído do banco de dados.

Havíamos discutido sobre os tipos de dados Data / Hora no capítulo Tipos de dados . Agora, vamos ver os operadores e funções de data / hora.

A tabela a seguir lista os comportamentos dos operadores aritméticos básicos -

Operador	Exemplo	Resultado
+	data '2001-09-28' + inteiro '7'	data '2001-10-05'
+	data '2001-09-28' + intervalo '1 hora'	timestamp '2001-09-28 01:00:00'
+	data '2001-09-28' + hora '03: 00 '	timestamp '2001-09-28 03:00:00'
+	intervalo '1 dia' + intervalo '1 hora'	intervalo '1 dia 01:00:00'
+	carimbo de data / hora '2001-09-28 01:00' + intervalo '23 horas '	timestamp '2001-09-29 00:00:00'
+	hora '01: 00 '+ intervalo' 3 horas '	hora '04: 00: 00 '
-	- intervalo '23 horas '	intervalo '-23: 00: 00'
-	data '2001-10-01' - data '2001-09-28'	inteiro '3' (dias)
-	data '2001-10-01' - inteiro '7'	data '2001-09-24'
-	data '2001-09-28' - intervalo '1 hora'	timestamp '2001-09-27 23:00:00'
-	hora '05: 00 '- hora '03: 00'	intervalo '02: 00: 00 '
-	hora '05: 00 '- intervalo' 2 horas '	hora '03: 00: 00 '
-	timestamp '2001-09-28 23:00' - intervalo '23 horas '	timestamp '2001-09-28 00:00:00'
-	intervalo '1 dia' - intervalo '1 hora'	intervalo '1 dia -01: 00: 00'
-	timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'	intervalo '1 dia 15:00:00'
*	900 * intervalo '1 segundo'	intervalo '00: 15: 00 '
*	21 * intervalo '1 dia'	intervalo '21 dias '
*	precisão dupla '3,5' * intervalo '1 hora'	intervalo '03: 30: 00 '
/	intervalo '1 hora' / precisão dupla '1,5'	intervalo '00: 40: 00 '

A seguir está a lista de todas as funções importantes relacionadas à data e hora disponíveis.

S. No.	Descrição da função
1	ERA() Subtrair argumentos
2	DATA / HORA ATUAL () Data e hora atuais
3	DATE_PART () Obter subcampo (equivalente a extrair)
4	EXTRAIR() Obter subcampo
5	ISFINITE () Teste para data, hora e intervalo finitos (não +/- infinito)
6	JUSTIFICAR Ajustar intervalo

AGE (timestamp, timestamp), AGE (timestamp)

S. No.	Descrição da função
1	AGE(timestamp, timestamp) Quando chamado com a forma TIMESTAMP do segundo argumento, AGE () subtrai argumentos, produzindo um resultado "simbólico" que usa anos e meses e é do tipo INTERVAL.
2	AGE(timestamp) Quando chamado apenas com o TIMESTAMP como argumento, AGE () subtrai da data_atual (à meia-noite).

Exemplo da função AGE (timestamp, timestamp) é -

testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13');

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

age
-------------------------
 43 years 9 mons 27 days

Exemplo da função AGE (timestamp) é -

testdb=# select age(timestamp '1957-06-13');

A instrução PostgreSQL fornecida acima produzirá o seguinte resultado -

age
--------------------------
 55 years 10 mons 22 days

DATA / HORA ATUAL ()

O PostgreSQL fornece várias funções que retornam valores relacionados à data e hora atuais. A seguir estão algumas funções -

S. No.	Descrição da função
1	CURRENT_DATE Fornece a data atual.
2	CURRENT_TIME Entrega valores com fuso horário.
3	CURRENT_TIMESTAMP Entrega valores com fuso horário.
4	CURRENT_TIME(precision) Opcionalmente, aceita um parâmetro de precisão, que faz com que o resultado seja arredondado para aquela quantidade de dígitos fracionários no campo de segundos.
5	CURRENT_TIMESTAMP(precision) Opcionalmente, aceita um parâmetro de precisão, que faz com que o resultado seja arredondado para aquela quantidade de dígitos fracionários no campo de segundos.
6	LOCALTIME Entrega valores sem fuso horário.
7	LOCALTIMESTAMP Entrega valores sem fuso horário.
8	LOCALTIME(precision) Opcionalmente, aceita um parâmetro de precisão, que faz com que o resultado seja arredondado para aquela quantidade de dígitos fracionários no campo de segundos.
9	LOCALTIMESTAMP(precision) Opcionalmente, aceita um parâmetro de precisão, que faz com que o resultado seja arredondado para aquela quantidade de dígitos fracionários no campo de segundos.

Exemplos usando as funções da tabela acima -

testdb=# SELECT CURRENT_TIME;
       timetz
--------------------
 08:01:34.656+05:30
(1 row)


testdb=# SELECT CURRENT_DATE;
    date
------------
 2013-05-05
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP;
              now
-------------------------------
 2013-05-05 08:01:45.375+05:30
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP(2);
         timestamptz
------------------------------
 2013-05-05 08:01:50.89+05:30
(1 row)


testdb=# SELECT LOCALTIMESTAMP;
       timestamp
------------------------
 2013-05-05 08:01:55.75
(1 row)

O PostgreSQL também fornece funções que retornam a hora de início da instrução atual, bem como a hora atual atual no instante em que a função é chamada. Essas funções são -

S. No.	Descrição da função
1	transaction_timestamp() É equivalente a CURRENT_TIMESTAMP, mas é nomeado para refletir claramente o que retorna.
2	statement_timestamp() Ele retorna a hora de início da instrução atual.
3	clock_timestamp() Ele retorna a hora atual real e, portanto, seu valor muda mesmo dentro de um único comando SQL.
4	timeofday() Ele retorna a hora atual real, mas como uma string de texto formatada em vez de um carimbo de data / hora com valor de fuso horário.
5	now() É um PostgreSQL tradicional equivalente a transaction_timestamp ().

DATE_PART (texto, timestamp), DATE_PART (texto, intervalo), DATE_TRUNC (texto, timestamp)

S. No.	Descrição da função
1	DATE_PART('field', source) Essas funções obtêm os subcampos. O parâmetro de campo precisa ser um valor de string, não um nome. Os nomes de campo válidos são: século, dia, década, dow, doy, epoch, hour, isodow, isoyear, microssegundos, milênio, milissegundos, minuto, mês, trimestre, segundo, fuso horário, fuso-horário_hora, fuso-horário_minuto, semana, ano.
2	DATE_TRUNC('field', source) Esta função é conceitualmente semelhante à função trunc para números. source é uma expressão de valor do tipo timestamp ou intervalo. campo seleciona com qual precisão truncar o valor de entrada. O valor de retorno é do tipo timestamp ou intervalo . Os valores válidos para o campo são: microssegundos, milissegundos, segundo, minuto, hora, dia, semana, mês, trimestre, ano, década, século, milênio

A seguir estão exemplos para funções DATE_PART ( 'campo' , fonte) -

testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)


testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
 date_part
-----------
         4
(1 row)

A seguir estão exemplos para funções DATE_TRUNC ( 'campo' , fonte) -

testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-02-16 20:00:00
(1 row)


testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-01-01 00:00:00
(1 row)

EXTRACT (campo do carimbo de data / hora), EXTRACT (campo do intervalo)

o EXTRACT(field FROM source)função recupera subcampos como ano ou hora de valores de data / hora. A origem deve ser uma expressão de valor do tipo carimbo de data / hora, hora ou intervalo . O campo é um identificador ou string que seleciona qual campo extrair do valor de origem. A função EXTRACT retorna valores do tipo precisão dupla .

A seguir estão nomes de campo válidos (semelhantes aos nomes de campo de função DATE_PART): século, dia, década, dow, doy, época, hora, isodow, isoyear, microssegundos, milênio, milissegundos, minuto, mês, trimestre, segundo, fuso horário, fuso-horário_hour , fuso horário_minuto, semana, ano.

A seguir estão exemplos de funções EXTRACT ( 'campo' , fonte) -

testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
 date_part
-----------
        20
(1 row)


testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)

ISFINITE (data), ISFINITE (carimbo de data / hora), ISFINITE (intervalo)

S. No.	Descrição da função
1	ISFINITE(date) Testes de data finita.
2	ISFINITE(timestamp) Testes para registro de tempo finito.
3	ISFINITE(interval) Testes de intervalo finito.

A seguir estão os exemplos das funções ISFINITE () -

testdb=# SELECT isfinite(date '2001-02-16');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(interval '4 hours');
 isfinite
----------
 t
(1 row)

JUSTIFY_DAYS (intervalo), JUSTIFY_HOURS (intervalo), JUSTIFY_INTERVAL (intervalo)

S. No.	Descrição da função
1	JUSTIFY_DAYS(interval) Ajusta o intervalo para que os períodos de 30 dias sejam representados como meses. Devolver ointerval tipo
2	JUSTIFY_HOURS(interval) Ajusta o intervalo para que os períodos de 24 horas sejam representados como dias. Devolver ointerval tipo
3	JUSTIFY_INTERVAL(interval) Ajusta o intervalo usando JUSTIFY_DAYS e JUSTIFY_HOURS, com ajustes de sinal adicionais. Devolver ointerval tipo

A seguir estão os exemplos para as funções ISFINITE () -

testdb=# SELECT justify_days(interval '35 days');
 justify_days
--------------
 1 mon 5 days
(1 row)


testdb=# SELECT justify_hours(interval '27 hours');
 justify_hours
----------------
 1 day 03:00:00
(1 row)


testdb=# SELECT justify_interval(interval '1 mon -1 hour');
 justify_interval
------------------
 29 days 23:00:00
(1 row)

PostgreSQL functions, também conhecidos como Procedimentos armazenados, permitem que você execute operações que normalmente levariam várias consultas e viagens de ida e volta em uma única função dentro do banco de dados. As funções permitem a reutilização do banco de dados, pois outros aplicativos podem interagir diretamente com seus procedimentos armazenados, em vez de um código de camada intermediária ou duplicado.

As funções podem ser criadas em uma linguagem de sua escolha, como SQL, PL / pgSQL, C, Python, etc.

Sintaxe

A sintaxe básica para criar uma função é a seguinte -

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$
   DECLARE
      declaration;
      [...]
   BEGIN
      < function_body >
      [...]
      RETURN { variable_name | value }
   END; LANGUAGE plpgsql;

Onde,

function-name especifica o nome da função.
A opção [OU REPLACE] permite modificar uma função existente.
A função deve conter um return declaração.
RETURNcláusula especifica o tipo de dados que você vai retornar da função. oreturn_datatype pode ser um tipo básico, composto ou de domínio, ou pode fazer referência ao tipo de uma coluna da tabela.
function-body contém a parte executável.
A palavra-chave AS é usada para criar uma função autônoma.
plpgsqlé o nome da linguagem na qual a função é implementada. Aqui, usamos esta opção para PostgreSQL, pode ser SQL, C, interno ou o nome de uma linguagem procedural definida pelo usuário. Para compatibilidade com versões anteriores, o nome pode ser colocado entre aspas simples.

Exemplo

O exemplo a seguir ilustra a criação e a chamada de uma função autônoma. Esta função retorna o número total de registros na tabela COMPANY. Usaremos a tabela COMPANY , que contém os seguintes registros -

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A função totalRecords () é a seguinte -

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
	total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

Quando a consulta acima é executada, o resultado seria -

testdb# CREATE FUNCTION

Agora, vamos executar uma chamada para esta função e verificar os registros na tabela COMPANY

testdb=# select totalRecords();

Quando a consulta acima é executada, o resultado seria -

totalrecords
--------------
      7
(1 row)

As funções embutidas do PostgreSQL, também chamadas de funções de agregação, são usadas para executar o processamento em strings ou dados numéricos.

A seguir está a lista de todas as funções internas do PostgreSQL de uso geral -

Função PostgreSQL COUNT - A função de agregação PostgreSQL COUNT é usada para contar o número de linhas em uma tabela de banco de dados.
Função PostgreSQL MAX - A função agregada PostgreSQL MAX permite selecionar o valor mais alto (máximo) para uma determinada coluna.
Função PostgreSQL MIN - A função agregada PostgreSQL MIN permite selecionar o valor mais baixo (mínimo) para uma determinada coluna.
Função PostgreSQL AVG - A função agregada PostgreSQL AVG seleciona o valor médio para determinada coluna da tabela.
Função PostgreSQL SUM - A função agregada PostgreSQL SUM permite selecionar o total para uma coluna numérica.
Funções do PostgreSQL ARRAY - A função de agregação do PostgreSQL ARRAY coloca os valores de entrada, incluindo nulos, concatenados em um array.
Funções numéricas do PostgreSQL - lista completa de funções do PostgreSQL necessárias para manipular números em SQL.
PostgreSQL String Functions - Lista completa de funções PostgreSQL necessárias para manipular strings no PostgreSQL.

Este tutorial vai usar libpqxxbiblioteca, que é a API do cliente C ++ oficial para PostgreSQL. O código-fonte para libpqxx está disponível sob a licença BSD, então você é livre para baixá-lo, passá-lo para outras pessoas, alterá-lo, vendê-lo, incluí-lo em seu próprio código e compartilhar suas alterações com quem quiser.

Instalação

A última versão do libpqxx está disponível para download no link Download Libpqxx . Portanto, baixe a versão mais recente e siga as seguintes etapas -

wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
tar xvfz libpqxx-4.0.tar.gz
cd libpqxx-4.0
./configure
make
make install

Antes de começar a usar a interface PostgreSQL C / C ++, encontre o pg_hba.conf arquivo no diretório de instalação do PostgreSQL e adicione a seguinte linha -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Você pode iniciar / reiniciar o servidor postgres caso ele não esteja sendo executado usando o seguinte comando -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

APIs de interface C / C ++

A seguir estão rotinas de interface importantes que podem ser suficientes para trabalhar com o banco de dados PostgreSQL de seu programa C / C ++. Se você está procurando um aplicativo mais sofisticado, pode consultar a documentação oficial do libpqxx ou usar APIs disponíveis comercialmente.

S. No.	API e descrição
1	pqxx::connection C( const std::string & dbstring ) Este é um typedef que será usado para conectar ao banco de dados. Aqui, dbstring fornece os parâmetros necessários para se conectar ao banco de dados, por exemplodbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432. Se a conexão for configurada com sucesso, ele cria C com o objeto de conexão que fornece várias funções públicas úteis.
2	C.is_open() O método is_open () é um método público de objeto de conexão e retorna um valor booleano. Se a conexão estiver ativa, este método retorna verdadeiro, caso contrário, retorna falso.
3	C.disconnect() Este método é usado para desconectar uma conexão de banco de dados aberta.
4	pqxx::work W( C ) Este é um typedef que será usado para criar um objeto transacional usando a conexão C, que em última análise será usado para executar instruções SQL no modo transacional. Se o objeto de transação for criado com sucesso, ele será atribuído à variável W, que será usada para acessar métodos públicos relacionados ao objeto de transação.
5	W.exec(const std::string & sql) Este método público do objeto transacional será usado para executar a instrução SQL.
6	W.commit() Este método público do objeto transacional será usado para confirmar a transação.
7	W.abort() Este método público do objeto transacional será usado para reverter a transação.
8	pqxx::nontransaction N( C ) Este é um typedef que será usado para criar um objeto não transacional usando a conexão C, que por fim será usado para executar instruções SQL no modo não transacional. Se o objeto de transação for criado com sucesso, ele será atribuído à variável N, que será usada para acessar métodos públicos relacionados ao objeto não transacional.
9	N.exec(const std::string & sql) Este método público de objeto não transacional será usado para executar a instrução SQL e retorna um objeto de resultado que é na verdade um interpretador que contém todos os registros retornados.

Conectando-se ao banco de dados

O segmento de código C a seguir mostra como se conectar a um banco de dados existente em execução na máquina local na porta 5432. Aqui, usei barra invertida \ para continuação de linha.

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }
}

Agora, vamos compilar e executar o programa acima para conectar ao nosso banco de dados testdb, que já está disponível em seu esquema e pode ser acessado usando postgres de usuário e senha pass123 .

Você pode usar o ID do usuário e a senha com base na configuração do banco de dados. Lembre-se de manter o -lpqxx e -lpq na ordem fornecida! Caso contrário, o vinculador reclamará amargamente sobre as funções ausentes com nomes que começam com "PQ".

$g++ test.cpp -lpqxx -lpq $./a.out
Opened database successfully: testdb

Crie uma mesa

O seguinte segmento de código C será usado para criar uma tabela no banco de dados criado anteriormente -

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "CREATE TABLE COMPANY("  \
      "ID INT PRIMARY KEY     NOT NULL," \
      "NAME           TEXT    NOT NULL," \
      "AGE            INT     NOT NULL," \
      "ADDRESS        CHAR(50)," \
      "SALARY         REAL );";

      /* Create a transactional object. */
      work W(C);
      
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Table created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Quando o programa fornecido acima for compilado e executado, ele criará a tabela COMPANY em seu banco de dados testdb e exibirá as seguintes instruções -

Opened database successfully: testdb
Table created successfully

Operação INSERT

O seguinte segmento de código C mostra como podemos criar registros em nossa tabela COMPANY criada no exemplo acima -

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

      /* Create a transactional object. */
      work W(C);
      
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Quando o programa fornecido acima for compilado e executado, ele criará determinados registros na tabela COMPANY e exibirá as duas linhas a seguir -

Opened database successfully: testdb
Records created successfully

SELECT Operação

O seguinte segmento de código C mostra como podemos buscar e exibir registros de nossa tabela COMPANY criada no exemplo acima -

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);
      
      /* Execute SQL query */
      result R( N.exec( sql ));
      
      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Quando o programa fornecido acima for compilado e executado, ele produzirá o seguinte resultado -

Opened database successfully: testdb
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 20000
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
Operation done successfully

Operação ATUALIZAR

O seguinte segmento de código C mostra como podemos usar a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados de nossa tabela COMPANY -

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      
      /* Create a transactional object. */
      work W(C);
      /* Create  SQL UPDATE statement */
      sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records updated successfully" << endl;
      
      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);
      
      /* Execute SQL query */
      result R( N.exec( sql ));
      
      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Quando o programa fornecido acima for compilado e executado, ele produzirá o seguinte resultado -

Opened database successfully: testdb
Records updated successfully
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

Operação DELETE

O seguinte segmento de código C mostra como podemos usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes de nossa tabela COMPANY -

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      
      /* Create a transactional object. */
      work W(C);
      /* Create  SQL DELETE statement */
      sql = "DELETE from COMPANY where ID = 2";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records deleted successfully" << endl;
      
      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);
      
      /* Execute SQL query */
      result R( N.exec( sql ));
      
      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Quando o programa fornecido acima for compilado e executado, ele produzirá o seguinte resultado -

Opened database successfully: testdb
Records deleted successfully
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

Instalação

Antes de começarmos a usar o PostgreSQL em nossos programas Java, precisamos ter certeza de que temos PostgreSQL JDBC e Java configurados na máquina. Você pode verificar o tutorial Java para instalação do Java em sua máquina. Agora vamos verificar como configurar o driver JDBC PostgreSQL.

Baixe a versão mais recente do postgresql- (VERSION) .jdbc.jar do repositório postgresql-jdbc .
Adicione o arquivo jar baixado postgresql- (VERSION) .jdbc.jar em seu caminho de classe, ou você pode usá-lo junto com a opção -classpath conforme explicado abaixo nos exemplos.

A seção a seguir assume que você tem pouco conhecimento sobre os conceitos Java JDBC. Se você não tiver, sugerimos que você gaste meia hora com o Tutorial JDBC para se familiarizar com os conceitos explicados abaixo.

Conectando-se ao banco de dados

O código Java a seguir mostra como se conectar a um banco de dados existente. Se o banco de dados não existir, ele será criado e, finalmente, um objeto de banco de dados será retornado.

import java.sql.Connection;
import java.sql.DriverManager;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "postgres", "123");
      } catch (Exception e) {
         e.printStackTrace();
         System.err.println(e.getClass().getName()+": "+e.getMessage());
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

Antes de compilar e executar o programa acima, encontre pg_hba.conf arquivo no diretório de instalação do PostgreSQL e adicione a seguinte linha -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Você pode iniciar / reiniciar o servidor postgres caso ele não esteja sendo executado usando o seguinte comando -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Agora, vamos compilar e executar o programa acima para conectar com testdb. Aqui, estamos usandopostgres como ID de usuário e 123como senha para acessar o banco de dados. Você pode alterar isso de acordo com a configuração e instalação do banco de dados. Também estamos assumindo a versão atual do driver JDBCpostgresql-9.2-1002.jdbc3.jar está disponível no caminho atual.

C:\JavaPostgresIntegration>javac PostgreSQLJDBC.java
C:\JavaPostgresIntegration>java -cp c:\tools\postgresql-9.2-1002.jdbc3.jar;C:\JavaPostgresIntegration PostgreSQLJDBC
Open database successfully

Crie uma mesa

O seguinte programa Java será usado para criar uma tabela no banco de dados aberto anteriormente. Certifique-se de não ter essa tabela já em seu banco de dados de destino.

import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
            "(ID INT PRIMARY KEY     NOT NULL," +
            " NAME           TEXT    NOT NULL, " +
            " AGE            INT     NOT NULL, " +
            " ADDRESS        CHAR(50), " +
            " SALARY         REAL)";
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

Quando um programa é compilado e executado, ele criará a tabela COMPANY em testdb banco de dados e exibirá as duas linhas a seguir -

Opened database successfully
Table created successfully

Operação INSERT

O seguinte programa Java mostra como podemos criar registros em nossa tabela COMPANY criada no exemplo acima -

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

Quando o programa acima for compilado e executado, ele criará determinados registros na tabela COMPANY e exibirá as duas linhas a seguir -

Opened database successfully
Records created successfully

SELECT Operação

O seguinte programa Java mostra como podemos buscar e exibir registros de nossa tabela COMPANY criada no exemplo acima -

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Quando o programa é compilado e executado, ele produzirá o seguinte resultado -

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Operação ATUALIZAR

O código Java a seguir mostra como podemos usar a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados de nossa tabela COMPANY -

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Quando o programa é compilado e executado, ele produzirá o seguinte resultado -

Opened database successfully
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

Operation done successfully

Operação DELETE

O código Java a seguir mostra como podemos usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes de nossa tabela COMPANY -

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC6 {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID = 2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Quando o programa é compilado e executado, ele produzirá o seguinte resultado -

Opened database successfully
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
Operation done successfully

Instalação

A extensão PostgreSQL é habilitada por padrão nas últimas versões do PHP 5.3.x. É possível desativá-lo usando--without-pgsqlem tempo de compilação. Ainda assim, você pode usar o comando yum para instalar a interface PHP -PostgreSQL -

yum install php-pgsql

Antes de começar a usar a interface PHP PostgreSQL, encontre o pg_hba.conf arquivo no diretório de instalação do PostgreSQL e adicione a seguinte linha -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Você pode iniciar / reiniciar o servidor postgres, caso ele não esteja em execução, usando o seguinte comando -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Os usuários do Windows devem habilitar php_pgsql.dll para usar esta extensão. Esta DLL está incluída nas distribuições do Windows nas versões mais recentes do PHP 5.3.x

Para obter instruções detalhadas de instalação, consulte nosso tutorial de PHP e seu site oficial.

APIs de interface PHP

A seguir estão rotinas PHP importantes, que podem ser suficientes para trabalhar com o banco de dados PostgreSQL de seu programa PHP. Se você está procurando um aplicativo mais sofisticado, pode consultar a documentação oficial do PHP.

S. No.	API e descrição
1	resource pg_connect ( string $connection_string [, int $connect_type ] ) Isso abre uma conexão com um banco de dados PostgreSQL especificado por connection_string. Se PGSQL_CONNECT_FORCE_NEW for passado como connect_type, então uma nova conexão é criada no caso de uma segunda chamada para pg_connect (), mesmo se connection_string for idêntica a uma conexão existente.
2	bool pg_connection_reset ( resource $connection ) Esta rotina redefine a conexão. É útil para recuperação de erros. Retorna TRUE em caso de sucesso ou FALSE em caso de falha.
3	int pg_connection_status ( resource $connection ) Esta rotina retorna o status da conexão especificada. Retorna PGSQL_CONNECTION_OK ou PGSQL_CONNECTION_BAD.
4	string pg_dbname ([ resource $connection ] ) Esta rotina retorna o nome do banco de dados do recurso de conexão PostgreSQL fornecido.
5	resource pg_prepare ([ resource $connection ], string $stmtname, string $query ) Isso envia uma solicitação para criar uma instrução preparada com os parâmetros fornecidos e aguarda a conclusão.
6	resource pg_execute ([ resource $connection ], string $stmtname, array $params ) Essa rotina envia uma solicitação para executar uma instrução preparada com parâmetros fornecidos e espera pelo resultado.
7	resource pg_query ([ resource $connection ], string $query ) Esta rotina executa a consulta na conexão de banco de dados especificada.
8	array pg_fetch_row ( resource $result [, int $row ] ) Esta rotina busca uma linha de dados do resultado associado ao recurso de resultado especificado.
9	array pg_fetch_all ( resource $result ) Esta rotina retorna uma matriz que contém todas as linhas (registros) no recurso de resultado.
10	int pg_affected_rows ( resource $result ) Esta rotina retorna o número de linhas afetadas pelas consultas INSERT, UPDATE e DELETE.
11	int pg_num_rows ( resource $result ) Esta rotina retorna o número de linhas em um recurso de resultado PostgreSQL, por exemplo, número de linhas retornadas pela instrução SELECT.
12	bool pg_close ([ resource $connection ] ) Esta rotina fecha a conexão não persistente com um banco de dados PostgreSQL associado ao recurso de conexão fornecido.
13	string pg_last_error ([ resource $connection ] ) Esta rotina retorna a última mensagem de erro para uma determinada conexão.
14	string pg_escape_literal ([ resource $connection ], string $data ) Essa rotina escapa de um literal para inserção em um campo de texto.
15	string pg_escape_string ([ resource $connection ], string $data ) Essa rotina escapa de uma string para consultar o banco de dados.

Conectando ao banco de dados

O código PHP a seguir mostra como se conectar a um banco de dados existente em uma máquina local e, finalmente, um objeto de conexão de banco de dados será retornado.

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
?>

Agora, vamos executar o programa fornecido acima para abrir nosso banco de dados testdb: se o banco de dados for aberto com sucesso, ele apresentará a seguinte mensagem -

Opened database successfully

Crie uma mesa

O seguinte programa PHP será usado para criar uma tabela em um banco de dados criado anteriormente -

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   
   $sql =<<<EOF CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
   } else {
      echo "Table created successfully\n";
   }
   pg_close($db);
?>

Quando o programa fornecido acima for executado, ele criará a tabela COMPANY em seu testdb e exibirá as seguintes mensagens -

Opened database successfully
Table created successfully

Operação INSERT

O seguinte programa PHP mostra como podemos criar registros em nossa tabela COMPANY criada no exemplo acima -

<?php
   $host        = "host=127.0.0.1";
   $port = "port=5432"; $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) { echo "Error : Unable to open database\n"; } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;

   $ret = pg_query($db, $sql); if(!$ret) {
      echo pg_last_error($db); } else { echo "Records created successfully\n"; } pg_close($db);
?>

Quando o programa fornecido acima for executado, ele criará os registros fornecidos na tabela COMPANY e exibirá as duas linhas a seguir -

Opened database successfully
Records created successfully

SELECT Operação

O programa PHP a seguir mostra como podemos buscar e exibir registros de nossa tabela COMPANY criada no exemplo acima -

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
      exit;
   } 
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n"; echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n"; echo "SALARY = ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

Quando o programa fornecido acima é executado, ele produzirá o seguinte resultado. Observe que os campos são retornados na sequência em que foram usados ao criar a tabela.

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Operação ATUALIZAR

O código PHP a seguir mostra como podemos usar a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados de nossa tabela COMPANY -

<?php
   $host        = "host=127.0.0.1";
   $port = "port=5432"; $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) { echo "Error : Unable to open database\n"; } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
   $ret = pg_query($db, $sql); if(!$ret) {
      echo pg_last_error($db); exit; } else { echo "Record updated successfully\n"; } $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql); if(!$ret) {
      echo pg_last_error($db); exit; } while($row = pg_fetch_row($ret)) { echo "ID = ". $row[0] . "\n";
      echo "NAME = ". $row[1] ."\n"; echo "ADDRESS = ". $row[2] ."\n";
      echo "SALARY =  ".$row[4] ."\n\n"; } echo "Operation done successfully\n"; pg_close($db);
?>

Quando o programa fornecido acima for executado, ele produzirá o seguinte resultado -

Opened database successfully
Record updated successfully
ID = 2
NAME = Allen
ADDRESS = 25
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

Operação DELETE

O código PHP a seguir mostra como podemos usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes de nossa tabela COMPANY -

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF DELETE from COMPANY where ID=2; EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
      exit;
   } else {
      echo "Record deleted successfully\n";
   }
   
   $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
      exit;
   } 
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n"; echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n"; echo "SALARY = ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

Quando o programa fornecido acima for executado, ele produzirá o seguinte resultado -

Opened database successfully
Record deleted successfully
ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

Instalação

O PostgreSQL pode ser integrado ao Perl usando o módulo Perl DBI, que é um módulo de acesso ao banco de dados para a linguagem de programação Perl. Ele define um conjunto de métodos, variáveis e convenções que fornecem uma interface de banco de dados padrão.

Aqui estão os passos simples para instalar o módulo DBI em sua máquina Linux / Unix -

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz $ cd DBI-1.625
$ perl Makefile.PL $ make
$ make install

Se você precisar instalar o driver SQLite para DBI, ele pode ser instalado da seguinte maneira -

$ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz
$ tar xvfz DBD-Pg-2.19.3.tar.gz $ cd DBD-Pg-2.19.3
$ perl Makefile.PL $ make
$ make install

Antes de começar a usar a interface Perl PostgreSQL, encontre o pg_hba.conf arquivo no diretório de instalação do PostgreSQL e adicione a seguinte linha -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Você pode iniciar / reiniciar o servidor postgres, caso ele não esteja em execução, usando o seguinte comando -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

APIs de interface DBI

A seguir estão as importantes rotinas DBI, que podem ser suficientes para trabalhar com o banco de dados SQLite de seu programa Perl. Se você está procurando um aplicativo mais sofisticado, pode consultar a documentação oficial do Perl DBI.

S. No.	API e descrição
1	DBI→connect($data_source, "userid", "password", \%attr) Estabelece uma conexão de banco de dados, ou sessão, para o $ data_source solicitado. Retorna um objeto de identificador de banco de dados se a conexão for bem-sucedida. A fonte de dados tem o formato: DBI:Pg:dbname=$database;host=127.0.0.1;port=5432 Pg é o nome do driver PostgreSQL e testdb é o nome do banco de dados.
2	$dbh→do($sql) Essa rotina prepara e executa uma única instrução SQL. Retorna o número de linhas afetadas ou undef em caso de erro. Um valor de retorno de -1 significa que o número de linhas não é conhecido, não aplicável ou não disponível. Aqui $ dbh é um identificador retornado pela chamada DBI → connect ().
3	$dbh→prepare($sql) Essa rotina prepara uma instrução para execução posterior pelo mecanismo de banco de dados e retorna uma referência a um objeto de manipulação de instrução.
4	$sth→execute() Essa rotina executa todo o processamento necessário para executar a instrução preparada. Um undef é retornado se ocorrer um erro. Uma execução bem-sucedida sempre retorna verdadeiro, independentemente do número de linhas afetadas. Aqui$sth is a statement handle returned by $dbh → preparar chamada ($ sql).
5	$sth→fetchrow_array() Esta rotina busca a próxima linha de dados e a retorna como uma lista contendo os valores do campo. Os campos nulos são retornados como valores undef na lista.
6	$DBI::err Isso é equivalente a $ h → err, onde $h is any of the handle types like $dbh, $sth, or $drh. Isso retorna o código de erro do mecanismo de banco de dados nativo do último método de driver chamado.
7	$DBI::errstr Isso é equivalente a $ h → errstr, onde $h is any of the handle types like $dbh, $sth, or $drh. Isso retorna a mensagem de erro do mecanismo de banco de dados nativo do último método DBI chamado.
8	$dbh->disconnect() Esta rotina fecha uma conexão de banco de dados aberta anteriormente por uma chamada para DBI → connect ().

Conectando ao banco de dados

O código Perl a seguir mostra como se conectar a um banco de dados existente. Se o banco de dados não existir, ele será criado e, finalmente, um objeto de banco de dados será retornado.

#!/usr/bin/perl

use DBI;
use strict;

my $driver  = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
   or die $DBI::errstr;

print "Opened database successfully\n";

Agora, vamos executar o programa fornecido acima para abrir nosso banco de dados testdb; se o banco de dados for aberto com sucesso, ele apresentará a seguinte mensagem -

Open database successfully

Crie uma mesa

O seguinte programa Perl será usado para criar uma tabela no banco de dados criado anteriormente -

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL););
my $rv = $dbh->do($stmt); if($rv < 0) {
   print $DBI::errstr; } else { print "Table created successfully\n"; } $dbh->disconnect();

Quando o programa fornecido acima for executado, ele criará a tabela COMPANY em seu testdb e exibirá as seguintes mensagens -

Opened database successfully
Table created successfully

Operação INSERT

O seguinte programa Perl mostra como podemos criar registros em nossa tabela COMPANY criada no exemplo acima -

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "Pg"; my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432"; my $userid = "postgres";
my $password = "pass123"; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )); my $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;

print "Records created successfully\n";
$dbh->disconnect();

Quando o programa fornecido acima for executado, ele criará determinados registros na tabela COMPANY e exibirá as duas linhas a seguir -

Opened database successfully
Records created successfully

SELECT Operação

O seguinte programa Perl mostra como podemos buscar e exibir registros de nossa tabela COMPANY criada no exemplo acima -

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt ); my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) { print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

Quando o programa fornecido acima for executado, ele produzirá o seguinte resultado -

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Operação ATUALIZAR

O seguinte código Perl mostra como podemos usar a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados de nossa tabela COMPANY -

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "Pg"; my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432"; my $userid = "postgres";
my $password = "pass123"; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;); my $rv = $dbh->do($stmt) or die $DBI::errstr; if( $rv < 0 ) {
   print $DBI::errstr; }else{ print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;); my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr; if($rv < 0) {
   print $DBI::errstr; } while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n"; print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n"; print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

Quando o programa fornecido acima for executado, ele produzirá o seguinte resultado -

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Operação DELETE

O seguinte código Perl mostra como podemos usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes de nossa tabela COMPANY -

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) { print $DBI::errstr;
} else{
   print "Total number of rows deleted : $rv\n"; } $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt ); $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) { print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

Quando o programa fornecido acima for executado, ele produzirá o seguinte resultado -

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Instalação

O PostgreSQL pode ser integrado ao Python usando o módulo psycopg2. sycopg2 é um adaptador de banco de dados PostgreSQL para a linguagem de programação Python. psycopg2 foi escrito com o objetivo de ser muito pequeno, rápido e estável como uma rocha. Você não precisa instalar este módulo separadamente porque ele é enviado, por padrão, junto com o Python versão 2.5.x em diante.

Se você não o tiver instalado em sua máquina, você pode usar o comando yum para instalá-lo da seguinte maneira -

$yum install python-psycopg2

Para usar o módulo psycopg2, você deve primeiro criar um objeto Connection que representa o banco de dados e, em seguida, opcionalmente, pode criar um objeto cursor que o ajudará a executar todas as instruções SQL.

APIs de módulo Python psycopg2

A seguir estão as rotinas do módulo psycopg2 importantes, que podem ser suficientes para trabalhar com o banco de dados PostgreSQL do seu programa Python. Se você está procurando um aplicativo mais sofisticado, pode consultar a documentação oficial do módulo Python psycopg2.

S. No.	API e descrição
1	psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432") Esta API abre uma conexão com o banco de dados PostgreSQL. Se o banco de dados for aberto com sucesso, ele retorna um objeto de conexão.
2	connection.cursor() Esta rotina cria um cursor que será usado em toda a sua programação de banco de dados com Python.
3	cursor.execute(sql [, optional parameters]) Esta rotina executa uma instrução SQL. A instrução SQL pode ser parametrizada (ou seja, marcadores de posição em vez de literais SQL). O módulo psycopg2 suporta placeholder usando o sinal% s Por exemplo: cursor.execute ("inserir valores de pessoas (% s,% s)", (quem, idade))
4	cursor.executemany(sql, seq_of_parameters) Esta rotina executa um comando SQL em todas as sequências de parâmetros ou mapeamentos encontrados na sequência sql.
5	cursor.callproc(procname[, parameters]) Esta rotina executa um procedimento de banco de dados armazenado com o nome fornecido. A seqüência de parâmetros deve conter uma entrada para cada argumento que o procedimento espera.
6	cursor.rowcount Este atributo somente leitura que retorna o número total de linhas do banco de dados que foram modificadas, inseridas ou excluídas pelo último execute * ().
7	connection.commit() Este método confirma a transação atual. Se você não chamar este método, tudo o que você fez desde a última chamada para commit () não será visível em outras conexões de banco de dados.
8	connection.rollback() Este método reverte quaisquer mudanças no banco de dados desde a última chamada para commit ().
9	connection.close() Este método fecha a conexão com o banco de dados. Observe que isso não chama commit () automaticamente. Se você apenas fechar a conexão com o banco de dados sem chamar commit () primeiro, suas alterações serão perdidas!
10	cursor.fetchone() Este método busca a próxima linha de um conjunto de resultados de consulta, retornando uma única sequência, ou None quando não há mais dados disponíveis.
11	cursor.fetchmany([size=cursor.arraysize]) Essa rotina busca o próximo conjunto de linhas de um resultado de consulta, retornando uma lista. Uma lista vazia é retornada quando não há mais linhas disponíveis. O método tenta buscar quantas linhas forem indicadas pelo parâmetro de tamanho.
12	cursor.fetchall() Esta rotina busca todas as linhas (restantes) de um resultado de consulta, retornando uma lista. Uma lista vazia é retornada quando nenhuma linha está disponível.

Conectando ao banco de dados

O código Python a seguir mostra como se conectar a um banco de dados existente. Se o banco de dados não existir, ele será criado e, finalmente, um objeto de banco de dados será retornado.

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")

print "Opened database successfully"

Aqui, você também pode fornecer banco de dados testdb como nome e se o banco de dados for aberto com sucesso, então apresentará a seguinte mensagem -

Open database successfully

Crie uma mesa

O seguinte programa Python será usado para criar uma tabela no banco de dados criado anteriormente -

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);''')
print "Table created successfully"

conn.commit()
conn.close()

Quando o programa fornecido acima for executado, ele criará a tabela COMPANY em seu test.db e exibirá as seguintes mensagens -

Opened database successfully
Table created successfully

Operação INSERT

O programa Python a seguir mostra como podemos criar registros em nossa tabela COMPANY criada no exemplo acima -

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

Quando o programa fornecido acima for executado, ele criará determinados registros na tabela COMPANY e exibirá as duas linhas a seguir -

Opened database successfully
Records created successfully

SELECT Operação

O seguinte programa Python mostra como podemos buscar e exibir registros de nossa tabela COMPANY criada no exemplo acima -

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Quando o programa fornecido acima for executado, ele produzirá o seguinte resultado -

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

Operação ATUALIZAR

O código Python a seguir mostra como podemos usar a instrução UPDATE para atualizar qualquer registro e, em seguida, buscar e exibir os registros atualizados de nossa tabela COMPANY -

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Quando o programa fornecido acima for executado, ele produzirá o seguinte resultado -

Opened database successfully
Total number of rows updated : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

Operação DELETE

O código Python a seguir mostra como podemos usar a instrução DELETE para excluir qualquer registro e, em seguida, buscar e exibir os registros restantes de nossa tabela COMPANY -

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print "Total number of rows deleted :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Quando o programa fornecido acima for executado, ele produzirá o seguinte resultado -

Opened database successfully
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully