sexta-feira, 30 de dezembro de 2016

Retirar Zeros a Esquerda com PostgreSQL

Exemplo:

create table funcionarios
(
codigo character varying(10) not null,
nome character varying(100) not null,
salario numeric(15, 2)
);

insert into funcionarios (codigo, nome, salario) values ('000232', 'Zorrilho', 672.40);
insert into funcionarios (codigo, nome, salario) values ('000239', 'Graxaim', 672.40);
insert into funcionarios (codigo, nome, salario) values ('001431', 'Lebre', 1402.46);

select
trim(leading '0' from codigo) as codigo,
nome,
salario
from funcionarios;


sexta-feira, 9 de dezembro de 2016

Listar BUSCAS/SELECTs que já foram feitos a uma tabela.

SELECT sum(seq_scan+idx_scan) AS total_de_leituras
FROM pg_stat_user_tables
WHERE relname=’nome_da_tabela’;

Obs.: Um select com um JOIN considera duas BUSCAS

Lista as tabelas ligadas por chave estrangeira a uma determinada tabela.

SELECT tab_pk.relname AS “tab PK”, tab_fk.relname AS “tab_FK”
FROM pg_class tab_pk
JOIN pg_constraint     ON tab_pk.oid=pg_constraint.confrelid
JOIN pg_class tab_fk  ON pg_constraint.conrelid=tab_fk.oid
and tab_fk.relname=’cliente’;

work mem

Quando algum processo pesado é executado, por exemplo a geracao de indices do drupal ou consultas que usam muito ORDER BY, um diretorio pgsqltemp será criado caso o uso da memoria setado na variavel WORKMEM seja execedido.
Nestes caso o aconselhável é aumentar - perante monitoramento - o parametro WORK_MEM na sessão em que está executando a query ou rodando os indices.
SET WORK_MEM TO "64MB"
Esse valor é por consulta, se colocar um valor muito alto a máquina vai sentar bonito, procure um valor entre 64 e 128, monitorando o servidor para verificar o impacto.

quinta-feira, 8 de dezembro de 2016

Listar tabelas de um BD em Postgresql

SELECT * FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY schemaname, tablename

segunda-feira, 16 de maio de 2016

Postgresql - Formatar CPF com REGEXP_REPLACE

Neste artigo, vamos mostrar 3 exemplos de como utilizar a função REGEXP_REPLACE para formatar o CPF.


1º Exemplo

Para formatar o CPF vamos utilizar a tabela "tb_alunos", exibida na imagem a seguir:


 Solução


Observe que na função REGEXP_REPLACE:
  • Utilizamos parenteses "( )" para separar cada parte da string, neste caso o CPF;
  • Utilizamos colchetes   "[ ]" para indicar quais os caracteres que iremos utilizar em cada parte, neste caso utilizamos caracteres numéricos de "0" até "9", representados pela expressão 0-9;
  • Indicamos dentro das chaves "{}" a quantidade de dígitos que vamos utilizar; 
  • Utilizamos contra-barra "\" antes de cada parte criada;
Após a execução da sentença, teremos o seguinte resultado:


 2º Exemplo

Também podemos substituir a expressão "0-9" que indica a utilização de caracteres numéricos de 0 até 9, pela expressão [:digit:]. O resultado será o mesmo. 

Solução



Após a execução da sentença, teremos o seguinte resultado:


3º Exemplo

Para facilitar, podemos substituir a expressão "[[:digit:]]" que indica a utilização de caracteres numéricos de 0 até 9, pela expressão abreviada "\d". O resultado será o mesmo.

Solução



Após a execução da sentença, teremos o seguinte resultado:

sexta-feira, 13 de maio de 2016

COMANDOS PSQL

Além de realizar comandos SQL, o psql também inclui alguns comandos próprios. Por exemplo, para sair do psql, você deve usar o comando \q (note que é uma barra invertida mesmo). 

Existem várias opções interessantes, veja alguns exemplos:
  • \d: lista as tabelas do banco de dados
  • \dv: lista as views do banco de dados
  • \di: lista os índices do banco de dados
  • \db: lista as tablespaces
  • \l: lista os bancos de dados
  • \dg: lista as roles existentes (usuários ou grupos)
  • \conninfo: apresenta informações sobre a conexão atual
  • \h: lista os comandos SQL
  • \h comando: apresenta detalhes sobre o comando
  • \dn: lista os esquemas do banco de dados
  • \c BASE: conecta a base
  • \d TABELA: apresenta a estrutura da tabela


Suponha que eu queira fazer uma conexão ao banco de dados BANCO, com o usuário USER, que fica no servidor 10.10.10.10


Para isto, eu deveria usar o comando: 

# psql -h 10.10.10.10 -U USER-d BANCO
OU
# psql -U USER


Se vc esquecer o usuário que está usando, basta usar o comando:

#select current_user; 


É importante lembrar que o PostgreSQL faz subdivisões no banco de dados, os esquemas. Por padrão, o psql tenta se conectar primeiro a um esquema cujo nome seja igual ao do usuário. 

Caso esse esquema não exista, o psql conecta-se ao esquema public. Se você quiser, por exemplo, se conectar ao esquema testes, deverá executar o comando: 

#set search_path to TESTE; (conecta ao esquema TESTE)

sábado, 26 de março de 2016

Criando Ranks no Postgres

Vamos supor que você tenha a seguinte tabela:









E você tenha que ordená-la de acordo com os seguintes critérios: a maior média por departamento, seguido de maior nota a, maior nota b e finalmente maior nota c. Fica bem simples se usarmos a cláusula order by:

SELECT department, name, grade_a, grade_b, grade_c
FROM candidates
ORDER BY department, ((grade_a + grade_b + grade_c) / 3) DESC,
grade_a DESC, grade_b DESC, grade_c DESC

Resultado:









Mas agora queremos criar uma qualificação indicando a posição do candidato de acordo com os critérios acima e esta posição deve ser relativa ao departamento que ele pertence, ou seja queremos saber os trê melhores qualificados no departamento "Development" e os três melhores no departamento "Marketing". Talvez muitos já estejam pensando em usar sua linguagem de programação favorita para resolver este problema mas no Postgres temos as Window Functions:

SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates

Resultado:









Legal, agora temos um número indicando a posição de cada candidato relativo ao seu departamento. Mas digamos agora que apenas os dois melhores candidatos de cada departamento devem ser retornados. Neste caso a seguinte query deve ser satisfatória:

SELECT * FROM (SELECT department, name, grade_a, grade_b, grade_c,
rank() OVER(PARTITION BY department ORDER BY ((grade_a + grade_b + grade_c) / 3) DESC, grade_a DESC, grade_b DESC, grade_c DESC)
FROM candidates) AS sub_query
WHERE rank < 3

Resultado:

Número de conexões PostgreSQL

Comando:
select datname, count(*) from pg_stat_activity group by datname;

Com este comando podemos ver o número de conexões ativas no momento de execução agrupando-as pelo nome do banco de dados.
A saída será semelhante a isso:

  datname  | count
-----------+-------
 testdb    |     5
 template1 |     1
(2 rows)

A tabela pg_stat_activity também tem outras colunas com outras informações interessantes:

datid - oid do banco de dados
datname - nome do banco de dados
procpid - id do processo
usesysid - OID do usuário
usename - nome do usuário
current_query - query sendo executada atualmente
waiting - status de espera da query
xact_start - horário em que a transação atual começou a executar
query_start - horário em que a query começou a executar
backend_start - horário em que o processo foi iniciado
client_addr - Endereço do cliente
client_port - Porta do cliente

As colunas que mostram informações sobre a query só podem ser vistas pelo super usuário ou se o usuário que estiver vendo as informações for o mesmo do processo que está sendo listado.

quinta-feira, 28 de maio de 2015

Tipos de backup do PostgreSQL

Diferentes maneiras de fazer backup dos bancos de dados do PostgreSQL.
O PostgreSQL possui varias formar de se fazer backup, podendo ser através de arquivos SQL, tar, ou customizado, além de poder fazer backup de apenas uma base ou todos de uma só vez, é para o caso de backups muito grandes a opção de dividir o arquivo em vários; vou mostrar as formas mais utilizadas de backup no PostgreSQL.

Backup de um único banco em formato SQL
pg_dump -U usuario -d banco > backup.sql

Backup de um único banco em formato tar
pg_dump -Ft -U usuario -d banco > backup.tar

Backup de um único banco em formato especifico do PostgreSQL
pg_dump -Fc -U usuario -d banco > backup

Por padrão o pg_dump não faz backup de objeto grandes dentro do banco, para ativar esta opção é necessário utilizar a opção "-b" como por exemplo.
Backup de um único banco em formato especifico e com objetos grandes
pg_dump -Fc -b -U usuario -d banco > backup

Backup de uma única tabela de único banco
pg_dump -Fc -b -U usuario -d banco -t nomedatabela > backup

Backup de todos os bancos do servidor 
pg_dumpall -Fc -b -U usuario > backup

Backup remoto de um banco
pg_dump -h hostremoto -d nomebanco | psql -h hostlocal -d banco

Backup em multivolumes (exemplo de volumes de 600MB)
pg_dump nomebanco | split -m 600 nomearquivo

OBS: m para 1Mega, k para 1K, b para 512bytes

Pessoalmente eu prefiro utilizar como backup o formato customizado com a opção dos blocos grandes ativados. "-Fc -b", esse tipo de backup é mais rápido tanto na hora de fazer como na hora de voltar além de sair em um tamanho bem reduzido em relação aos demais.

terça-feira, 12 de maio de 2015

Listando as 10 maiores tabelas no PostgreSQL

Para listar as maiores tabelas do seu banco de dados, utilize a consulta abaixo:

WITH table_stats AS (
  SELECT
    schemaname,
    tablename,
    pg_relation_size(schemaname || '.'|| tablename) as table_size,
    (pg_total_relation_size(schemaname || '.'|| tablename) - pg_relation_size(schemaname || '.'|| tablename)) as index_size,
    pg_total_relation_size(schemaname || '.'|| tablename) as total_size
  FROM
    pg_tables
)
SELECT
  table_stats.schemaname,
  table_stats.tablename,
  pg_size_pretty(table_stats.table_size) as table_size,
  pg_size_pretty(table_stats.index_size) as index_size,
  pg_size_pretty(table_stats.total_size) as total_size
FROM
  table_stats

WHERE
  -- ajuste o filtro conforme sua necessidade!
  table_stats.schemaname = 'public'
ORDER BY
  table_stats.total_size desc,
  table_stats.index_size desc,
  table_stats.table_size desc
LIMIT 10;