This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE EXTENSION tablefunc; | |
CREATE TABLE sales(year int, month int, qty int); | |
INSERT INTO sales VALUES(2007, 1, 1000); | |
INSERT INTO sales VALUES(2007, 2, 1500); | |
INSERT INTO sales VALUES(2007, 7, 500); | |
INSERT INTO sales VALUES(2007, 11, 1500); | |
INSERT INTO sales VALUES(2007, 12, 2000); | |
INSERT INTO sales VALUES(2008, 1, 1000); | |
INSERT INTO sales VALUES(2009, 5, 2500); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO public.usuario | |
(id, cadastro, ativo, nome, administrador, alerta_certidao, atendente, digitalizador, financeiro, gestor, login, painel_atendimento, sangria, senha, valor_caixa, departamento_id) | |
VALUES(uuid_generate_v4(), CURRENT_TIMESTAMP, true, 'johnny', true, true, true, true, true, true, 'johnny', true, 0, '$2a$10$N1RmIRN4CNwuY7EIfTQhJOnJzFRmNSoa.maqWe2i3fFb6h2I2td8G', 0, 'b8e62c98-f28c-4af7-9620-90696aab89c6'); | |
-- após criação do schema public, antes dos backups, rodar: | |
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; | |
CREATE EXTENSION IF NOT EXISTS "unaccent"; | |
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import java.io.BufferedReader; | |
import java.io.ByteArrayInputStream; | |
import java.io.ByteArrayOutputStream; | |
import java.io.IOException; | |
import java.io.InputStreamReader; | |
import java.util.zip.GZIPInputStream; | |
import java.util.zip.GZIPOutputStream; | |
public class Gzip { |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- show running queries (pre 9.2) | |
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query | |
FROM pg_stat_activity | |
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' | |
ORDER BY query_start desc; | |
-- show running queries (9.2) | |
SELECT pid, age(clock_timestamp(), query_start), usename, query | |
FROM pg_stat_activity | |
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION jsonb_remove_keys( | |
jdata JSONB, | |
keys TEXT[] | |
) | |
RETURNS JSONB AS $$ | |
DECLARE | |
result JSONB; | |
len INT; | |
target TEXT; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table migracao.lote as | |
select id as imovel, uuid_generate_v4() protocolo, row_number() over() as ordem | |
from indicador_real where livro = 'MATRICULA' and status != 'PENDENTE' and codigo in (); | |
with tempCodigo as ( | |
select max(codigo) as codigo from protocolo where dominio = 'PROTOCOLO_RI' and tipo_protocolo = 'NORMAL' | |
) | |
INSERT INTO protocolo ( | |
id, codigo, atualizacao, cadastro, cancelamento_automatico, | |
custas, dominio, emolumentos_busca, encerramento, entrega, entregue, exigencias, indices, legado, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table migracao.atos_utilizados ( | |
cartorio varchar, | |
protocolo_pedido varchar, | |
pessoa_ato varchar, | |
data_utilizacao varchar, | |
retornado varchar, | |
tipo_ato varchar, | |
nome_serventuario varchar, | |
inutilizado varchar, | |
justificativa_inutilizacao varchar, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select i.documento, TO_CHAR(p.registro, 'MM-YYYY') mes, sum(pf.valor) total, count(p.id) protocolos | |
from protocolo p | |
join protocolo_financeiro pf on p.id = pf.protocolo_id | |
join indicador_pessoal_versao i on i.id = p.tomador_id or i.id = p.interessado_id or i.id = p.solicitante_id | |
where p.registro::date between '2021-01-01' and '2021-02-28' and i.documento is not null | |
and pf.forma_pagamento = 'DINHEIRO' | |
group by 1,2 | |
having sum(pf.valor) >= 30000 | |
order by 1,2 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
update livro_protocolo set ordem = null where "data" >= '2020-12-05'; | |
with maximo as ( | |
select max(ordem) as maximo from livro_protocolo lp | |
), | |
ordens as ( | |
select lp.id, lp."data", lp.tipo, m.maximo + row_number() over(order by lp."data" asc, p.codigo asc, lp.tipo asc) ordem | |
from livro_protocolo lp | |
join protocolo p on p.id = lp.protocolo_id | |
join maximo m on 1=1 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
ALTER TABLE indicador_pessoal_versao DROP CONSTRAINT uk_2s3c25we0a91ejyy5moa8623j; | |
update indicador_pessoal_versao | |
set hashcode = md5(CONCAT_WS(';', | |
coalesce(indicador_Pessoal_Id::varchar, '') | |
, coalesce(nome, '') | |
, coalesce(documento, '') | |
, coalesce(documento_Desconhecido::varchar, '') | |
, coalesce(denominacao, '') |
OlderNewer