PostgreSql - Criar um banco de dados parte III

Na primeira e segunda parte do artigo, descrevemos alguns exemplos da criação de um banco de dados através da linha de comando. Nesta terceira parte do artigo, apresentamos um vídeo mostrando como criar um banco através de interface gráfica.

Existem algumas ferramentas gráficas como o PgAdmin, PhpPgAdmin, entre outras.

Este vídeo mostra como criar um banco através do PgAdmin III.

Veja também:

PostgreSql - Criar tabela primeira parte
Postgresql - Criar tabela segunda parte

PostgreSql - Criar um banco de dados parte II

Nesta segunda parte do artigo, vamos descrever como utilizar o utilitário "createdb" para criar um banco de dados.

SINTAXE BÁSICA

createdb  <nome_do_banco> <opções> <descrição> 

Inicialmente, abra o terminal.  Para  exibir as opções disponíveis no utilitário "createdb"  execute o comando:

createdb --help

Após executar o comando, serão exibidas as opções disponíveis, conforme a imagem abaixo:

A seguir, apresentamos um exemplo com a utilização de duas dessas opções.

1º Exemplo

Criar um banco chamado "db_loja" e fazer a conexão em uma máquina local utilizando o usuário "postgres".

Solução

Vamos utilizar os seguinte parâmetros:

-h: indica qual a máquina que está localizada o servidor de banco de dados. Há duas possibilidades passar o ip ou o nome da máquina. Como a minha máquina é local posso passar o ip 127.0.0.1 ou nome "localhost";

-U: usuário que vai ser utilizado para se conectar ao servidor;

createdb  db_loja  -h127.0.0.1 -Upostgres

Após executarmos o comando, o banco foi criado e podemos fazer a conexão. Veja a imagem abaixo:

Relembrando, conforme citamos na primeira parte do artigo, que para criar um banco o usuário do banco deve ser um super usuário, exemplo postgres, ou deve ser um usuário com o privilégio de CREATEDB.

Veja também:
PostgreSql - Criar um banco de dados parte I
PostgreSql - Criar um banco de dados parte III
PostgreSql - Criar tabela primeira parte
Postgresql - Criar tabela segunda parte

Faça um comentário no final do artigo, suas sugestões ou críticas serão bem-vindas.

PostgreSql - Criar um banco de dados parte I

Vamos apresentar alguns exemplos da criação de um banco de dados no postgresql.
Podemos criar um banco das seguintes formas:

• linha de comando via sql;
• linha de comando via utilitário createdb. Veja a segunda parte do artigo;
• através de interface gráfica. Exemplo PgAdmin, PhpPgAdmin, entre outras. Veja a terceira parte do artigo;

Criar um banco através da linha de comando via sql

Antes de criar o banco vamos fazer a conexão com o servidor:

psql -h <nome ou ip do host> -U <nome do usuário>  -d <nome do banco dados>

Exemplo

psql -h 127.0.0.1 -U  postgres -d postgres

OU

psql -h 127.0.0.1 -U  postgres

Parâmetros

-h: como estou executando localmente na minha máquina utilizei o ip 127.0.0.1.
Também poderia ter passado o nome localhost;

-U: nome do usuário, no meu caso postgres.
Para você criar um banco você deve ser um super usuário ou ser um usuário com o privilégio de "CREATEDB".
O usuário postgres é um super usuário. No final do artigo, mostramos como listar os privilégios dos usuários

-d: nome do banco que será conectado
Neste exemplo, nos conectamos ao banco de dados "postgres". Este banco é padrão do postgresql, pois já foi criado na instalação.
Quando o nome de usuário é igual ao do banco, como neste caso, não é necessário passar o parâmetro "-d" com o nome do banco, pois quando omitimos o nome do banco, o psql considera que é o nome do banco é igual ao nome do usuário;

Após ter se conectado com o servidor, você pode criar o banco executando a sentença sql conforme o exemplo a seguir:

SINTAXE

CREATE DATABASE nome_do_banco;

Exemplo

Vamos criar um banco de chamado db_livraria

CREATE DATABASE db_livraria;

Veja a imagem abaixo da criação do banco de dados db_livraria:

Agora que já criamos o banco, como vamos acessá-lo?

Para acessarmos um banco utilizamos o comando psql "\c".

\c <nome do banco>

No caso de "db_livraria", ficará ;

\c db_livraria

Agora vamos listar todos os bancos do postgresql para verificar com quais parâmetros eles são criados, utilize o comando psql "\l", conforme exibido na imagem abaixo:

Ao observar o banco db_livraria, percebemos que alguns parâmetros foram incluídos por padrão como o proprietário do banco, a codificação "UTF-8" entre outros.
Estas configurações foram baseadas no banco de dados "template1". O template1 é um modelo para a criação de outros bancos de dados. Ele é criado na instalação do postgresql.   Em outros artigos, falaremos mais sobre o template1. 
Porém caso tenhamos necessidade, podemos passar os parâmetros na criação de um banco. Aqui descrevemos dois deles:  "OWNER" e "CONNECTION LIMIT". Para consultar todos os parâmetros, verifique o manual do postgresql.

CREATE DATABASE nome_do_banco
WITH 
OWNER                        =  <Nome do usuário proprietário do banco>
CONNECTION LIMIT =  <número máximo de conexões do banco.>

PARÂMETRO OWNER

Após o parâmetro OWNER colocamos o nome do usuário que será proprietário do novo banco, caso você não utilize este parâmetro, o usuário que está executando o comando será o proprietário do banco. 

Exemplo do parâmetro OWNER

Vamos criar um banco chamado db_financeiro. A proprietária deste banco será a Ana.
O nome de usuário da Ana vai ser: ana.

Solução

Antes de criarmos o banco, vamos criar a usuária ana, caso ela não exista.

CREATE ROLE ana LOGIN PASSWORD 'cs486338#';

Após executarmos a sentença acima, criamos a usuária ana. Agora vamos criar o banco db_financeiro. 

CREATE DATABASE db_financeiro
WITH 
OWNER = ana;

Observações

Após o WITH passamos os parâmetros que queremos utilizar no banco. Porém o uso do  WITH é opcional, ou seja também é possível passar os parâmetros sem utilizá-lo na sentença:
O sinal de '=' também é opcional.
A sentença também pode ser escrita desta forma.

CREATE DATABASE db_financeiro OWNER  ana;

Para acessarmos o banco "db_financeiro" utilize o comando psql:

\c db_financeiro

PARÂMETRO CONNECTION LIMIT

CONNECTION LIMIT: número conexões podem ser feitas simultaneamente (ao mesmo tempo) em um banco. Caso você não passe este parâmetro não haverá limite de conexões, ou seja por padrão o valor deste parâmetro será -1.

Exemplo do parâmetro CONNECTION LIMIT

Criar um banco chamado db_escola. Serão permitidos, no máximo, duas conexões e a proprietária será a ana.

CREATE DATABASE db_escola
WITH 
CONNECTION LIMIT = 2
OWNER = ana;

Após a criação do banco, vamos sair da conexão com o usuário postgres, para isso vamos utilizar o comando psql "\q".

Agora vamos nos conectar com a usuária ana.
Quando a usuária ana tentar fazer a terceira conexão,  haverá um erro devido ao limite de 2 conexões. Veja a imagem abaixo. 

Observações

Caso você queira aumentar o limite de conexões do banco "db_escola" para 10 execute a sentença abaixo, na 1ª ou na 2ª conexão:

ALTER DATABASE db_escola CONNECTION LIMIT 10;

Importante: se você precisar limitar o número de conexões no servidor postgresql e não em um banco em específico, você deve alterar a diretiva "max_connections" no arquivo "postgresql.conf" e reiniciar o serviço, mas isso é assunto para um próximo post. 

Verificar os privilégios de usuários

Como prometemos no início do artigo, vamos mostrar como verificar os privilégio de usuário. Para isso, vamos utilizar o comando psql "\du".

O resultado da execução deste comando é exibido na imagem a seguir:

Como podemos ver na imagem acima, os usuários carlos, caio e postgres terão privilégio 
para criar banco de dados, pois os dois primeiros tem a permissão de CREATEDB e o 
último tem a permissão de super-usuário.

A ana não terá permissão para criar novos bancos de dados.

Veja também:

PostgreSql - Criar um banco de dados parte II

PostgreSql - Criar um banco de dados parte III
PostgreSql - Criar tabela primeira parte
Postgresql - Criar tabela segunda parte

Faça um comentário no final do artigo, suas sugestões ou críticas serão bem-vindas.

PostgreSql - Função substring

A função substring retorna parte (um pedaço) de uma string. Seu retorno depende dos argumentos passados. Os argumentos passados podem ser:

• posição de caracteres de uma string;
• expressão regular no padrão POSIX;

Neste artigo serão demostrados 6 exemplos, caso tenha interesse faça o download dos scripts ou veja no GitHub.

SINTAXE

substring(string from x for y)

Onde:

• x: posição inicial da string.;
• y: quantidade de caracteres que serão exibidos a partir da posição inicial;

1º Exemplo

Queremos extrair os 3 primeiros caracteres da string "Rio Grande do Sul" a partir da primeira posição.

SELECT SUBSTRING('Rio Grande do Sul' from 1 for 3) ;

Após a execução da sentença, temos o resultado exibido na imagem abaixo:

2º Exemplo

Queremos extrair os 10 primeiros caracteres da string "Rio Grande do Sul" a partir da primeira posição.

SELECT SUBSTRING('Rio Grande do Sul' from 1 for 10) ;

Após a execução da sentença, temos o resultado exibido na imagem abaixo:

Observação: o espaço também é um carácter.

3º Exemplo

Queremos extrair os 9 primeiros caracteres da string "Rio Grande do Sul" a partir da quinta posição.

SELECT SUBSTRING('Rio Grande do Sul' from 5 for 9) ;

Após a execução da sentença, temos o resultado exibido na imagem abaixo:

4ºExemplo

Queremos exibir os 6 primeiros caracteres dos nomes dos alunos. 

Para exibir os caracteres, vamos utilizar a função substring na coluna "nome" da tabela "alunos".

A tabela alunos é exibida abaixo:

SELECT

cod_matricula,

nome,

SUBSTRING(nome FROM 1 FOR 6)

FROM alunos;

Após a execução da sentença, obtemos o resultado exibido abaixo:

Quando utilizamos a função substring, o nome da coluna passa a ter o mesmo nome da função. Se quisermos que a coluna tenha um outro nome devemos criar um alias (apelido) depois do "AS". 

Neste exemplo, eu preferi ela se chame "caracteres_iniciais", então coloquei "caracteres_iniciais" depois do "AS".

SELECT

cod_matricula,

nome,

SUBSTRING(nome FROM 1 FOR 6) AS caracteres_iniciais

FROM alunos;

Após a execução da sentença, o nome da coluna será exibido como caracteres iniciais.

Também é possível, passar como argumento na função substring uma expressão regular do padrão POSIX.
A expressão regular é uma forma de procurar um trecho em determinadas posições como no início ou no fim de uma linha ou palavra. Aqui serão apresentados 2 exemplos de expressão regular. 

Há um livro muito bom sobre expressões regulares, o seu título é "Expressões Regulares - Guia de Consulta Rápida" do autor "Aurélio Marinho Vargas" editora "Novatec", este livro também está disponível na internet de forma gratuita.

SINTAXE

substring(string from expressão_regular)

5º Exemplo

Queremos extrair o prefixo dos telefones residenciais (4 primeiros dígitos) dos clientes de uma loja.

Para extrairmos o prefixo vamos utilizar a função substring na coluna "telefone" da tabela "contatos". A tabela contatos é exibida na imagem abaixo:

SELECT

cliente_id, 

telefone,

substring(telefone from '^[0-9]{4}')

FROM contatos;

Observações

O acento circunflexo ^ indica que a string começa com determinado(s) caracter(es). 

No nosso caso, começa com dígitos no intervalo de zero a nove [0-9]. Entre colchetes colocamos a lista de caracteres que podem ser utilizados.

São extraídos 4 dígitos: {4}. A quantidade de dígitos deve ser colocada entre chaves {}.

Após a execução da sentença, temos o resultado exibido na imagem abaixo:

6º Exemplo

Queremos extrair os 4 últimos dígitos dos telefones dos clientes de uma loja.

Vamos utilizar a função substring na coluna "telefone" da tabela "contatos". A tabela contatos é exibida na imagem abaixo:

SELECT

cliente_id, 

telefone,

substring(telefone from '[0-9]{4}$')

FROM contatos;

Observações

O cifrão $ indica que a string termina com determinado(s) caracter(es). 

No nosso caso, termina com dígitos no intervalo de zero a nove [0-9]. Entre colchetes colocamos a lista de caracteres que podem ser utilizados.

São extraídos 4 dígitos: {4}. A quantidade de dígitos deve ser colocada entre chaves {}.

Após a execução da sentença, temos o resultado exibido na imagem abaixo:

Sua opinião é muito importante, criticas ou sugestões serão bem-vindas.

PostgreSql - Função char_length e length

Este artigo apresenta três exemplos de duas funções no PostgreSql:

• char_length ou character_length: utilizaçao desta função na manipulação de string;
• length: utilizaçao desta função na manipulação de string;
• length: utilizaçao desta função na manipulação de dados do tipo bytea;

Caso tenha interesse faça o download dos exemplos ou veja os scripts no GitHub

Função char_lenght

A função char_length retorna a quantidade de caracteres de uma string.

SINTAXE

char_length(string)

Exemplo

Queremos saber a quantidade de caracteres dos nomes dos alunos. 
Para exibir o número de caracteres, vamos utilizar a função char_length na coluna "nome" da tabela "alunos".
A tabela alunos é exibida abaixo:

SELECT

cod_matricula,

nome,

CHAR_LENGTH(nome)

FROM alunos;

Após a execução da sentença, a função retornará a quantidade carácter do nome. Podemos ver o resultado na tabela abaixo:

Quando utilizamos a função char_length, o nome da coluna passa a ter o mesmo nome da função. Se quisermos que a coluna tenha um outro nome devemos criar um alias (apelido) depois do "AS". 

Neste exemplo, eu preferi ela se chame "qtd_caracteres", então coloquei "qtd_caracteres" depois do "AS".

SELECT

cod_matricula,

nome,

CHAR_LENGTH(nome) AS qtd_caracteres

FROM alunos;

Após a execução da sentença a coluna passa se chamar "qtd_caracteres", conforme exibido na tabela a seguir:

Função lenght na manipulação de string

A função length também retorna a quantidade de caracteres de uma string.

SINTAXE

length(string)

Exemplo

Este exemplo é similar ao anterior, queremos saber a quantidade de caracteres dos nomes dos alunos, mas vamos utilizar a função length na coluna "nome" da tabela "alunos".
A tabela alunos é exibida abaixo:

SELECT

cod_matricula,

nome,

LENGTH(nome)

FROM alunos;

Após a execução da sentença, a função retornará a quantidade caracteres do nome. Podemos ver o resultado na tabela abaixo:

Quando utilizamos a função length, o nome da coluna passa a ter o mesmo nome da função. Se quisermos que a coluna tenha um outro nome devemos criar um alias (apelido) depois do "AS". 

Neste exemplo, eu preferi ela se chame "qtd_caracteres", então coloquei "qtd_caracteres" depois do "AS".

SELECT

cod_matricula,

nome,

LENGTH(nome) AS qtd_caracteres

FROM alunos;

Após a execução da sentença a coluna passa se chamar "qtd_caracteres", conforme exibido na tabela a seguir:

Função lenght na manipulação de dados do tipo bytea

O tipo de dado bytea permite o armazenamento de cadeias binárias.

A função retorna o número de caracteres de uma string de acordo com a codificação.

SINTAXE

length(string bytea, encoding)

Exemplo

Vamos criar a tabela "chave" para demonstra o uso do length.

CREATE TABLE chave 
(
seq_caracteres bytea
);

INSERT INTO chave(seq_caracteres)
VALUES
('dkfjdfjdkfjire'),
('jkljkljfkmmjjfdkfjdk'),
('jfijdfidosf');

A seguir temos a imagem da tabela "chave";

Vamos utilizar a função length na coluna "seq_caracteres".

SELECT

seq_caracteres,

LENGTH(seq_caracteres, 'UTF8')

FROM chave;

Após a execução da sentença, a função retornará a quantidade de caracteres da coluna "seq_caracteres". Podemos ver o resultado na tabela abaixo:

PostgreSql - Funçoes lower, upper e initcap

Este artigo apresenta exemplos da utilização de três funções de manipulação de string do PostgreSql:

• lower;
• upper;
• initcap;

Caso tenha interesse faça o download dos exemplos ou veja os scripts no GitHub.

Função lower

A função lower converte todos os caracteres de uma string para minúsculo.

SINTAXE

LOWER(string)

Exemplo

Queremos converter os dados da coluna logradouro para minúsculo. A coluna logradouro pertence a tabela "locacao_filiais", conforme exibido a seguir:

Vamos executar a sentença abaixo para que os dados da coluna logradouro sejam exibidos em minúsculo.

SELECT

tipo_logradouro,

LOWER(logradouro),

numero,

complemento,

bairro

FROM locacao_filiais;

Após a execução da sentença os dados da coluna logradouro serão exibidos em minúsculo, conforme podemos visualizar na tabela abaixo:

Quando utilizamos a função lower, o nome da coluna passa a ter o mesmo nome da função.

Se quisermos que o nome da coluna continue se chamando "logradouro" ou quisermos que tenha um outro nome devemos criar um alias (apelido) depois do AS. 

Neste exemplo eu preferi que ela continue se chamando "logradouro", então coloquei logradouro depois do AS.

SELECT

tipo_logradouro,

LOWER(logradouro) AS logradouro,

numero,

complemento,

bairro

FROM locacao_filiais;

Após a execução da sentença a coluna continua a se chamar logradouro, conforme exibido na tabela a seguir:

Função upper

A função upper converte todos os caracteres de uma string para maiúsculo.

SINTAXE

UPPER(string)

Exemplo

Queremos converter o dados da coluna logradouro para maiúsculo. A coluna logradouro pertence a tabela "locacao_filiais", conforme exibido a seguir:

Vamos executar a sentença abaixo para que os dados da coluna logradouro sejam exibidos em maiúsculo.

SELECT

tipo_logradouro,

UPPER(logradouro),

numero,

complemento,

bairro

FROM locacao_filiais;

Após a execução da sentença os dados da coluna logradouro serão exibidos em maiúsculo, conforme podemos visualizar na tabela abaixo:

Quando utilizamos a função upper, o nome da coluna passa a ter o mesmo nome da função.

Se quisermos que o nome da coluna continue se chamando "logradouro" ou quisermos que tenha um outro nome devemos criar um alias (apelido) depois do AS. 

Neste exemplo eu preferi que ela continue se chamando "logradouro", então coloquei logradouro depois do AS.

SELECT

tipo_logradouro,

UPPER(logradouro) AS logradouro,

numero,

complemento,

bairro

FROM locacao_filiais;

Após a execução da sentença a coluna continua a se chamar logradouro, conforme exibido na tabela a seguir:

Função initcap

Converte a primeiro carácter de cada palavra para maiúsculo. O restante dos caracteres ficam em minúsculo.

SINTAXE

INITCAP(string)

Exemplo

Vamos utilizar a função initcap na coluna "nome" da tabela "alunos". A tabela alunos é exibida abaixo:

SELECT

cod_matricula,

INITCAP(nome)

FROM alunos;

Após a execução da sentença o primeiro carácter de cada parte do nome foi convertida para maiúsculo e o restante dos caracteres ficaram em minúsculo. Podemos ver o resultado na tabela abaixo:

Quando utilizamos a função initcap, o nome da coluna passa a ter o mesmo nome da função.

Se quisermos que o nome da coluna continue se chamando "nome" ou quisermos que tenha um outro nome devemos criar um alias (apelido) depois do AS. 

Neste exemplo eu preferi que ela continue se chamando "nome", então coloquei "nome" depois do "AS".

SELECT

cod_matricula,

INITCAP(nome) AS nome

FROM alunos;

Após a execução da sentença a coluna continua a se chamar nome, conforme exibido na tabela a seguir: