tiagofrancafernandes/devsql_postgresql

## devsql_postgresql
#Instalando e usando How To Install and Use PostgreSQL 14 on Ubuntu 20
echo "deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" > /etc/apt/sources.list.d/pgdg.list

--

#Instalando e usando How To Install and Use PostgreSQL on Ubuntu 18.04 | DigitalOcean
https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04

-- Docker
https://www.youtube.com/watch?v=A8dErdDMqb0
-- docker run -dit --name nomecontainer --expose 5432 -p 5432:5432 -e POSTGRES_PASSWORD=123 -d postgres

-- Client PGSQL
https://github.com/paxa/postbird
https://snapcraft.io/postbird
-- ----------------------------------------------------
-- duplicate key value violates unique constraint \"users_pkey\"
SELECT setval(pg_get_serial_sequence('NOME_DA_TABELA', 'id'), coalesce(max(id)+1, 1), false) FROM NOME_DA_TABELA;

-- No Mysql Resolve-se assim:
-- set LAST_INSERT_ID= (SELECT id FROM teste.usuarios order by id desc limit 1)


-- duplicate key value violates unique constraint \"users_pkey\"
//No eloquent
use DB;
DB::select("SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id)+1, 1), false) FROM users;");
-- ----------------------------------------------------
-- ----------------------------------------------------
se criar um arquivo ~/.pgpass, as ferramentas CLI do postgres nao pedem mais senha!
https://www.postgresql.org/docs/8.3/libpq-pgpass.html
https://tableplus.com/blog/2019/09/how-to-use-pgpass-in-postgresql.html
-- ----------------------------------------------------
--Mudar prximo numero na sequencia - Fix: Unique violation: 7 ERROR: duplicate key value violates unique constraint “users_pkey”
-- https://stackoverflow.com/a/37972960/11716408

SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id)+1, 1), false) FROM users;

-- ----------------------------------------------------
-- postgres show active connections / mostrar conexões ativas
SELECT * FROM pg_stat_activity;
-- ----------------------------------------------------

select preferences->'beta' from users where (preferences->>'beta')::boolean is true;

-------------------------------------------------
--Pesquisar se JSON é vazio
-- Eloquent: ->whereJsonLength('domain_names', '<=', 0)
--Mesmo resultado
and t.domain_names::text  = '[]'
and json_array_length(("domain_names")::json) <= 0

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

--Select query regex? busca com regex
where lower(domain_names::text) similar to '%(gmail.com|yahoo.com)%'
where lower(resultado::text) similar to '%(positivo)%'
where resultado::text similar to '%(positivo)%'
-- ----------------------------------------------------
--conectar outra base (another database)
-- Connection test (https://stackoverflow.com/a/33299345/11716408)
-- SELECT dblink_connect('host=localhost user=usuario password=senha123 dbname=postgres');

--Cria a extensão
CREATE EXTENSION dblink

--lista as extensões do RDS
SHOW rds.extensions;
-- ----------------------------------------------------
--formatar data string
SELECT TO_CHAR(NOW() :: DATE, 'yyyy-mm-dd 00:00:00');
-- ----------------------------------------------------
-- Inline select (query inline)
export PGPASSWORD='SdfR5KJH4er'; psql -h server.rds.amazonaws.com  -U devuser -d dbcovid -c 'SELECT * FROM teste'
-- ----------------------------------------------------
-- Extrair data, hora, mes de um timestamp no banco
https://www.postgresqltutorial.com/postgresql-extract/

select year(date) from "agenda_horarios"
-- ----------------------------------------------------

-- Maps port to local
-- https://blog.trackets.com/2014/05/17/ssh-tunnel-local-and-remote-port-forwarding-explained-with-examples.html
/*
ssh -L local_port:127.0.0.1:host_port user@host_or_ip
psql -h 127.0.0.1 -U postgres
*/
-- ----------------------------------------------------
-- Install PostgreSQL 11 on Ubuntu 18.04 / Ubuntu 16.04

https://computingforgeeks.com/install-postgresql-11-on-ubuntu-18-04-ubuntu-16-04/
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
RELEASE=$(lsb_release -cs)
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee  /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt -y install postgresql-11

Success. You can now start the database server using:

    pg_ctlcluster 11 main start
-- --------
-- Query PgSQL inline CLI # https://stackoverflow.com/a/21859632/11716408
psql -U username -d mydatabase -c 'SELECT * FROM mytable'
-- --------
-- Acessando PGSQL
-- docker exec -it nomecontainer psql -U postgres

-- Uma linha password inline (https://stackoverflow.com/a/28959903/11716408)
export PGPASSWORD='123'; psql -h '127.0.0.1' -U 'postgres' -d 'database_name'
-- --------
-- Mudar senha
https://gist.github.com/AtulKsol/4470d377b448e56468baef85af7fd614
-- --------
-- Query
postgres=# CREATE DATABASE nomebanco;
postgres=# \c nomebanco
postgres=# \dt

-- ----------------------------------------------------
-- Horas no PG

SELECT TO_CHAR(NOW() :: DATE, 'yyyy-mm-dd 00:00:00');
SELECT NOW() - INTERVAL '1 DAY';

--Ontem, ontem hora específica, amanhã e hora 00
SELECT
'yesterday'::TIMESTAMP as ontem,
'tomorrow'::TIMESTAMP as amanha,
TO_CHAR('yesterday'::TIMESTAMP:: DATE, 'yyyy-mm-dd 12:00:00') as ontem_12h,
'allballs'::TIME as zero_hora
;


/* Formas de obter ONTEM */
/* Ontem longo */
SELECT TO_CHAR((NOW() - INTERVAL '1 DAY') :: DATE, 'yyyy-mm-dd 00:00:00');

/* Ontem simples */
SELECT 'yesterday'::TIMESTAMP
-- ----------------------------------------------------
-- ----------------------------------------------------
-- Listar tabelas
SELECT * FROM pg_catalog.pg_tables where schemaname = 'public';
-- ----------------------------------------------------
--grant connect and login to database
ALTER ROLE "stitchglobal" WITH LOGIN;
GRANT CONNECT ON DATABASE datawarehouse TO stitchglobal
-- ----------------------------------------------------
--Select query regex? busca com regex
where lower(domain_names::text) similar to '%(gmail.com|yahoo.com)%'
-- ----------------------------------------------------
--Pesquisar se JSON é vazio
-- Eloquent: ->whereJsonLength('domain_names', '<=', 0)
--Mesmo resultado
and t.domain_names::text  = '[]'
and json_array_length(("domain_names")::json) <= 0
-- ----------------------------------------------------

--Grant multi schemas
GRANT USAGE ON SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
GRANT USAGE ON SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
GRANT SELECT ON ALL TABLES IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
GRANT ALL ON ALL TABLES IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
GRANT ALL ON ALL SEQUENCES IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
-- ----------------------------------------------------
--Permissões de banco
/*
-- Para tabela especifica
GRANT
SELECT
-- ,INSERT
-- ,UPDATE
-- ,DELETE
ON public.hs_client_admins TO useropstwo;
*/


/*
--Para todas as tabelas do schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;
*/

-- ----------------------------------------------------
-- Lareavel .env (https://gist.github.com/prappo/3cb2841f4e1fa4e9095071c4b0f04a0c)
DB_CONNECTION=pgsql
DB_HOST=hostname
DB_PORT=5432
DB_DATABASE=Your database name
DB_USERNAME=Your database username
DB_PASSWORD=Your database password
-- ----------------------------------------------------
-- Instalar driver pdo no debain
sudo apt install php-pgsql
-- ----------------------------------------------------
--Variavel temporaria local na query
set session temp.id = '1';

select *
from partners
    where id = current_setting('temp.id')::int;
-- ----------------------------------------------------
-- SET TIMEZONE TO 'America/Sao_Paulo'; -- Horário oficial de Brasília/São Paulo
-- SELECT NOW();

-- SET TIMEZONE TO 'America/Fortaleza';
-- SELECT NOW();

-- SELECT * FROM pg_timezone_names where
-- name like '%razil%' or
-- abbrev ='-03'
-- order by abbrev
;

-- ----------------------------------------------------
--Date range query (https://stackoverflow.com/questions/23335970/postgresql-query-between-date-ranges)
-- where o_updated_at >= '2020-10-01' and   o_updated_at <= '2020-10-20'
-- WHERE '[2020-10-01, 2020-10-20]'::daterange @> t.o_updated_at::date
-- where o_updated_at BETWEEN SYMMETRIC '2020-10-01' AND '2020-10-20'
-- ----------------------------------------------------

-- ----------------------------------------------------
-- Install Postgres CLient v14
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt -y update
sudo apt -y install postgresql-14
psql --version
-- ----------------------------------------------------

-- Dump Import e export
https://axiomq.com/blog/backup-and-restore-a-postgresql-database/

-- Export
pg_dump -h 192.168.100.129 -U postgres copia_pontomais_local > db_dump_copia_pontomais_local_2019-12-04.sql

-- Export only schema (apenas schema da base sem dados) #https://stackoverflow.com/a/47104018/11716408
pg_dump --host dbhost --port 5432 --username "dbuser" --schema-only --file ./targetfile.sql sourcedatabasename

-- --

-- Import
psql -U postgres -p 5432 -h 192.168.100.129 teste_pontomais_local < backup_cesar.sql
-- Se quiser já inserir a senha:
PGPASSWORD="postgres" psql -U postgres -p 5432 -h 192.168.100.52 dd_name < sctipt-sql.sql

-- ----------------------------------------------------
-- postgresql update column from another column
-- Update column with value of another column or another column

UPDATE public.clients
SET corporate_name = COALESCE(name)
-- WHERE    id = '11'
-- ----------------------------------------------------
-- Drop column
ALTER TABLE public.opened_invoices
DROP COLUMN due_external_id
-- ----------------------------------------------------
-- export only table exportar apenas uma tabela para sql
-- https://carlosbecker.com/posts/dump-postgres-table-inserts/

-- CLI client
-- apt install postgresql-client-common postgresql-client-10
-- apt install postgresql-client-common postgresql-client-9.6

pg_dump \
  -h 192.168.100.129 \
  -p 5432 \
  -U postgres -W \
  --table="clients" \
  --data-only \
  --column-inserts \
  copia_pontomais_local > table_clients.sql

-- #####
-- Usando env
export SERVER="192.168.100.129" && export DB="pontomais_local" export PORT="5432" && export TABLE_EXPORT="commissions" \
&& export USER="postgres" \
&& pg_dump   -h ${SERVER}   -p ${PORT}   -U ${USER} -W --table="${TABLE_EXPORT}" --data-only   --column-inserts ${DB} > ${TABLE_EXPORT}_data_only_$(date +%Y-%d-%m-%H_%m_%S).sql

-- ----------------------------------------------------
-- Encontrando e Deletando duplicados
-- https://www.postgresqltutorial.com/how-to-delete-duplicate-rows-in-postgresql/
/*
CREATE TABLE basket(
    id SERIAL PRIMARY KEY,
    fruit VARCHAR(50) NOT NULL
);

-- -- --/

INSERT INTO basket(fruit) values('apple');
INSERT INTO basket(fruit) values('apple');

INSERT INTO basket(fruit) values('orange');
INSERT INTO basket(fruit) values('orange');
INSERT INTO basket(fruit) values('orange');

INSERT INTO basket(fruit) values('banana');

////


-- -- --/

SELECT
    fruit,
    COUNT( fruit )
FROM
    basket
GROUP BY
    fruit
HAVING
    COUNT( fruit )> 1
ORDER BY
    fruit;
/*
//Eloquent Laravel
    $dupli = DB::table('partners')
        ->select('code', DB::raw('COUNT( code )'))
        ->groupBy('code')
        ->havingRaw('COUNT( code )> 1')
        ->orderBy('code')
        ->get();
    SimpleLog::logThis( $dupli, ['append','file'=>'temp_teste']);
*/


-- -- --

DELETE
FROM
    basket a
        USING basket b
WHERE
    a.id < b.id
    AND a.fruit = b.fruit;

*/


select * from basket
-- ----------------------------------------------------

-- Corrige/trata Erro/mensagem:
-- Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
-- https://askubuntu.com/questions/50621/cannot-connect-to-postgresql-on-port-5432
-- https://askubuntu.com/a/534087

-- ----------------------------------------------------
-- array case-insensitive ilike
SELECT "employees".* FROM "employees"
WHERE "employees"."deleted_at" IS NULL
  AND "employees"."client_id" = 15001
  AND "employees"."active" = true
AND "employees"."first_name" ILIKE ANY(ARRAY['tiago','alex'])
-- ----------------------------------------------------

## mysql.txt
#mysql allow remote connection
#then grant root all privaleges to root
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

## postgresql-snippets---pgsql-duplicados-sql-e-eloquent.md

      
    Raw
  

              postgresql-snippets---pgsql-duplicados-sql-e-eloquent.md
            
          
    -- Duplicados
SELECT codigo_1, COUNT(*) FROM codigo GROUP BY codigo_1 HAVING COUNT(*) > 1;

-- Usando Eloquent DB
-- $produtosDuplicados = DB::table('produtos')
--     ->select('codigo', DB::raw('COUNT(*) as quantidade'))
--     ->groupBy('codigo')
--     ->havingRaw('COUNT(*) > 1')
--     ->get();

// SELECT codigo_1, COUNT(*) FROM codigo GROUP BY codigo_1 HAVING COUNT(*) > 1;

// Usando Eloquent DB
$produtosDuplicados = DB::table('produtos')
    ->select('codigo', DB::raw('COUNT(*) as quantidade'))
    ->groupBy('codigo')
    ->havingRaw('COUNT(*) > 1')
    ->get();
// Identificar itens duplicados com multiplas colunas
// preciso pegar os registros na tabela questionario_respostas cujas colunas 'questionary_id', 'roteiro_id', 'uuid' estão duplicadas

// select "questionary_id", "roteiro_id", "uuid", COUNT(*) as quantidade from "questionario_respostas" group by "questionary_id", "roteiro_id", "uuid" having COUNT(*) > 1

DB::table('questionario_respostas')->select('questionary_id', 'roteiro_id', 'uuid', DB::raw('COUNT(*) as quantidade'))->groupBy('questionary_id', 'roteiro_id', 'uuid')->havingRaw('COUNT(*) > 1')->get();

Para inserir respostas duplicadas:
Tenant::initById('catupiry')
$qr = (array) DB::table('questionario_respostas')->first();
unset($qr['id']); DB::table('questionario_respostas')->insert($qr);
Para veirifcar duplicados
 DB::table('questionario_respostas')->select('questionary_id', 'roteiro_id', 'uuid', DB::raw('COUNT(*) as quantidade'))->groupBy('questionary_id', 'roteiro_id', 'uuid')->havingRaw('COUNT(*) > 1')->get();
Deletar apenas os duplicados
DB::table('questionario_respostas')
    ->select('questionary_id', 'roteiro_id', 'uuid', DB::raw('COUNT(*) as quantidade'))
    ->groupBy('questionary_id', 'roteiro_id', 'uuid')
    ->havingRaw('COUNT(*) > 1')
    ->orderBy('questionary_id')
    ->get()
    ->each(
        fn ($item) => DB::table('questionario_respostas')
            ->where('uuid', $item?->uuid)
            ->orderBy('id')
            ->limit($item->quantidade - 1)
            ->get()
            ->each(
                fn ($rec) => DB::table('questionario_respostas')->where('id', $rec->id)->delete()
            )
    );

  
## sqlite-analise.sh
# sqlite analyze table

#Show all databases conected
sqlite> .databases
main: /home/ubuntu/db.sqlite

# Enable stats
sqlite> .stats on

# Analise all
ANALYZE;

# Analise one table
ANALYZE main.users;

## tutorials---joins.md

      
    Raw
  

              tutorials---joins.md
            
          
    Os joins no SQL são utilizados para combinar dados de duas ou mais tabelas com base em uma condição de junção. Eles permitem que você recupere informações relacionadas de diferentes tabelas em uma única consulta.
Existem vários tipos de joins no SQL, mas vou explicar dois dos mais comumente usados: INNER JOIN e LEFT JOIN.


INNER JOIN: O INNER JOIN retorna apenas os registros que têm correspondência nas duas tabelas envolvidas na junção. Ele combina os registros com base em uma condição de igualdade especificada. Por exemplo, se você tiver uma tabela de "Clientes" e uma tabela de "Pedidos", um INNER JOIN entre elas retornaria apenas os clientes que fizeram pedidos.


LEFT JOIN: O LEFT JOIN retorna todos os registros da tabela à esquerda (tabela A) e os registros correspondentes da tabela à direita (tabela B). Se não houver correspondência na tabela à direita, os valores serão preenchidos com NULL. Isso é útil quando você deseja recuperar todos os registros de uma tabela, independentemente de haver uma correspondência na tabela relacionada. Por exemplo, se você tiver uma tabela de "Clientes" e uma tabela de "Pedidos", um LEFT JOIN retornaria todos os clientes, mesmo que eles não tenham feito nenhum pedido.


Aqui está um exemplo simples para ilustrar como funciona um INNER JOIN:
SELECT clientes.nome, pedidos.numero
FROM clientes
INNER JOIN pedidos ON clientes.id = pedidos.cliente_id;
Neste exemplo, estamos selecionando o nome do cliente e o número do pedido. A condição de junção é que o ID do cliente na tabela "clientes" seja igual ao cliente_id na tabela "pedidos". Isso retornará apenas os clientes que fizeram pedidos, combinando as informações das duas tabelas.
	#Instalando e usando How To Install and Use PostgreSQL 14 on Ubuntu 20
	echo "deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" > /etc/apt/sources.list.d/pgdg.list

	--

	#Instalando e usando How To Install and Use PostgreSQL on Ubuntu 18.04 \| DigitalOcean
	https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04

	-- Docker
	https://www.youtube.com/watch?v=A8dErdDMqb0
	-- docker run -dit --name nomecontainer --expose 5432 -p 5432:5432 -e POSTGRES_PASSWORD=123 -d postgres

	-- Client PGSQL
	https://github.com/paxa/postbird
	https://snapcraft.io/postbird
	-- ----------------------------------------------------
	-- duplicate key value violates unique constraint \"users_pkey\"
	SELECT setval(pg_get_serial_sequence('NOME_DA_TABELA', 'id'), coalesce(max(id)+1, 1), false) FROM NOME_DA_TABELA;

	-- No Mysql Resolve-se assim:
	-- set LAST_INSERT_ID= (SELECT id FROM teste.usuarios order by id desc limit 1)


	-- duplicate key value violates unique constraint \"users_pkey\"
	//No eloquent
	use DB;
	DB::select("SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id)+1, 1), false) FROM users;");
	-- ----------------------------------------------------
	-- ----------------------------------------------------
	se criar um arquivo ~/.pgpass, as ferramentas CLI do postgres nao pedem mais senha!
	https://www.postgresql.org/docs/8.3/libpq-pgpass.html
	https://tableplus.com/blog/2019/09/how-to-use-pgpass-in-postgresql.html
	-- ----------------------------------------------------
	--Mudar prximo numero na sequencia - Fix: Unique violation: 7 ERROR: duplicate key value violates unique constraint “users_pkey”
	-- https://stackoverflow.com/a/37972960/11716408

	SELECT setval(pg_get_serial_sequence('users', 'id'), coalesce(max(id)+1, 1), false) FROM users;

	-- ----------------------------------------------------
	-- postgres show active connections / mostrar conexões ativas
	SELECT * FROM pg_stat_activity;
	-- ----------------------------------------------------

	select preferences->'beta' from users where (preferences->>'beta')::boolean is true;

	-------------------------------------------------
	--Pesquisar se JSON é vazio
	-- Eloquent: ->whereJsonLength('domain_names', '<=', 0)
	--Mesmo resultado
	and t.domain_names::text = '[]'
	and json_array_length(("domain_names")::json) <= 0

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

	--Select query regex? busca com regex
	where lower(domain_names::text) similar to '%(gmail.com\|yahoo.com)%'
	where lower(resultado::text) similar to '%(positivo)%'
	where resultado::text similar to '%(positivo)%'
	-- ----------------------------------------------------
	--conectar outra base (another database)
	-- Connection test (https://stackoverflow.com/a/33299345/11716408)
	-- SELECT dblink_connect('host=localhost user=usuario password=senha123 dbname=postgres');

	--Cria a extensão
	CREATE EXTENSION dblink

	--lista as extensões do RDS
	SHOW rds.extensions;
	-- ----------------------------------------------------
	--formatar data string
	SELECT TO_CHAR(NOW() :: DATE, 'yyyy-mm-dd 00:00:00');
	-- ----------------------------------------------------
	-- Inline select (query inline)
	export PGPASSWORD='SdfR5KJH4er'; psql -h server.rds.amazonaws.com -U devuser -d dbcovid -c 'SELECT * FROM teste'
	-- ----------------------------------------------------
	-- Extrair data, hora, mes de um timestamp no banco
	https://www.postgresqltutorial.com/postgresql-extract/

	select year(date) from "agenda_horarios"
	-- ----------------------------------------------------

	-- Maps port to local
	-- https://blog.trackets.com/2014/05/17/ssh-tunnel-local-and-remote-port-forwarding-explained-with-examples.html
	/*
	ssh -L local_port:127.0.0.1:host_port user@host_or_ip
	psql -h 127.0.0.1 -U postgres
	*/
	-- ----------------------------------------------------
	-- Install PostgreSQL 11 on Ubuntu 18.04 / Ubuntu 16.04

	https://computingforgeeks.com/install-postgresql-11-on-ubuntu-18-04-ubuntu-16-04/
	wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \| sudo apt-key add -
	RELEASE=$(lsb_release -cs)
	echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main \| sudo tee /etc/apt/sources.list.d/pgdg.list
	sudo apt update
	sudo apt -y install postgresql-11

	Success. You can now start the database server using:

	pg_ctlcluster 11 main start
	-- --------
	-- Query PgSQL inline CLI # https://stackoverflow.com/a/21859632/11716408
	psql -U username -d mydatabase -c 'SELECT * FROM mytable'
	-- --------
	-- Acessando PGSQL
	-- docker exec -it nomecontainer psql -U postgres

	-- Uma linha password inline (https://stackoverflow.com/a/28959903/11716408)
	export PGPASSWORD='123'; psql -h '127.0.0.1' -U 'postgres' -d 'database_name'
	-- --------
	-- Mudar senha
	https://gist.github.com/AtulKsol/4470d377b448e56468baef85af7fd614
	-- --------
	-- Query
	postgres=# CREATE DATABASE nomebanco;
	postgres=# \c nomebanco
	postgres=# \dt

	-- ----------------------------------------------------
	-- Horas no PG

	SELECT TO_CHAR(NOW() :: DATE, 'yyyy-mm-dd 00:00:00');
	SELECT NOW() - INTERVAL '1 DAY';

	--Ontem, ontem hora específica, amanhã e hora 00
	SELECT
	'yesterday'::TIMESTAMP as ontem,
	'tomorrow'::TIMESTAMP as amanha,
	TO_CHAR('yesterday'::TIMESTAMP:: DATE, 'yyyy-mm-dd 12:00:00') as ontem_12h,
	'allballs'::TIME as zero_hora
	;


	/* Formas de obter ONTEM */
	/* Ontem longo */
	SELECT TO_CHAR((NOW() - INTERVAL '1 DAY') :: DATE, 'yyyy-mm-dd 00:00:00');

	/* Ontem simples */
	SELECT 'yesterday'::TIMESTAMP
	-- ----------------------------------------------------
	-- ----------------------------------------------------
	-- Listar tabelas
	SELECT * FROM pg_catalog.pg_tables where schemaname = 'public';
	-- ----------------------------------------------------
	--grant connect and login to database
	ALTER ROLE "stitchglobal" WITH LOGIN;
	GRANT CONNECT ON DATABASE datawarehouse TO stitchglobal
	-- ----------------------------------------------------
	--Select query regex? busca com regex
	where lower(domain_names::text) similar to '%(gmail.com\|yahoo.com)%'
	-- ----------------------------------------------------
	--Pesquisar se JSON é vazio
	-- Eloquent: ->whereJsonLength('domain_names', '<=', 0)
	--Mesmo resultado
	and t.domain_names::text = '[]'
	and json_array_length(("domain_names")::json) <= 0
	-- ----------------------------------------------------

	--Grant multi schemas
	GRANT USAGE ON SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
	GRANT USAGE ON SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
	GRANT SELECT ON ALL TABLES IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
	GRANT SELECT ON ALL SEQUENCES IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
	GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
	GRANT ALL ON ALL TABLES IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
	GRANT ALL ON ALL SEQUENCES IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
	GRANT ALL ON ALL FUNCTIONS IN SCHEMA commercial_ops, cs_ops, financial_ops, ops_ger TO useropsthree ;
	-- ----------------------------------------------------
	--Permissões de banco
	/*
	-- Para tabela especifica
	GRANT
	SELECT
	-- ,INSERT
	-- ,UPDATE
	-- ,DELETE
	ON public.hs_client_admins TO useropstwo;
	*/


	/*
	--Para todas as tabelas do schema
	GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;
	*/

	-- ----------------------------------------------------
	-- Lareavel .env (https://gist.github.com/prappo/3cb2841f4e1fa4e9095071c4b0f04a0c)
	DB_CONNECTION=pgsql
	DB_HOST=hostname
	DB_PORT=5432
	DB_DATABASE=Your database name
	DB_USERNAME=Your database username
	DB_PASSWORD=Your database password
	-- ----------------------------------------------------
	-- Instalar driver pdo no debain
	sudo apt install php-pgsql
	-- ----------------------------------------------------
	--Variavel temporaria local na query
	set session temp.id = '1';

	select *
	from partners
	where id = current_setting('temp.id')::int;
	-- ----------------------------------------------------
	-- SET TIMEZONE TO 'America/Sao_Paulo'; -- Horário oficial de Brasília/São Paulo
	-- SELECT NOW();

	-- SET TIMEZONE TO 'America/Fortaleza';
	-- SELECT NOW();

	-- SELECT * FROM pg_timezone_names where
	-- name like '%razil%' or
	-- abbrev ='-03'
	-- order by abbrev
	;

	-- ----------------------------------------------------
	--Date range query (https://stackoverflow.com/questions/23335970/postgresql-query-between-date-ranges)
	-- where o_updated_at >= '2020-10-01' and o_updated_at <= '2020-10-20'
	-- WHERE '[2020-10-01, 2020-10-20]'::daterange @> t.o_updated_at::date
	-- where o_updated_at BETWEEN SYMMETRIC '2020-10-01' AND '2020-10-20'
	-- ----------------------------------------------------

	-- ----------------------------------------------------
	-- Install Postgres CLient v14
	sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
	wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc \| sudo apt-key add -
	apt -y update
	sudo apt -y install postgresql-14
	psql --version
	-- ----------------------------------------------------

	-- Dump Import e export
	https://axiomq.com/blog/backup-and-restore-a-postgresql-database/

	-- Export
	pg_dump -h 192.168.100.129 -U postgres copia_pontomais_local > db_dump_copia_pontomais_local_2019-12-04.sql

	-- Export only schema (apenas schema da base sem dados) #https://stackoverflow.com/a/47104018/11716408
	pg_dump --host dbhost --port 5432 --username "dbuser" --schema-only --file ./targetfile.sql sourcedatabasename

	-- --

	-- Import
	psql -U postgres -p 5432 -h 192.168.100.129 teste_pontomais_local < backup_cesar.sql
	-- Se quiser já inserir a senha:
	PGPASSWORD="postgres" psql -U postgres -p 5432 -h 192.168.100.52 dd_name < sctipt-sql.sql

	-- ----------------------------------------------------
	-- postgresql update column from another column
	-- Update column with value of another column or another column

	UPDATE public.clients
	SET corporate_name = COALESCE(name)
	-- WHERE id = '11'
	-- ----------------------------------------------------
	-- Drop column
	ALTER TABLE public.opened_invoices
	DROP COLUMN due_external_id
	-- ----------------------------------------------------
	-- export only table exportar apenas uma tabela para sql
	-- https://carlosbecker.com/posts/dump-postgres-table-inserts/

	-- CLI client
	-- apt install postgresql-client-common postgresql-client-10
	-- apt install postgresql-client-common postgresql-client-9.6

	pg_dump \
	-h 192.168.100.129 \
	-p 5432 \
	-U postgres -W \
	--table="clients" \
	--data-only \
	--column-inserts \
	copia_pontomais_local > table_clients.sql

	-- #####
	-- Usando env
	export SERVER="192.168.100.129" && export DB="pontomais_local" export PORT="5432" && export TABLE_EXPORT="commissions" \
	&& export USER="postgres" \
	&& pg_dump -h ${SERVER} -p ${PORT} -U ${USER} -W --table="${TABLE_EXPORT}" --data-only --column-inserts ${DB} > ${TABLE_EXPORT}_data_only_$(date +%Y-%d-%m-%H_%m_%S).sql

	-- ----------------------------------------------------
	-- Encontrando e Deletando duplicados
	-- https://www.postgresqltutorial.com/how-to-delete-duplicate-rows-in-postgresql/
	/*
	CREATE TABLE basket(
	id SERIAL PRIMARY KEY,
	fruit VARCHAR(50) NOT NULL
	);

	-- -- --/

	INSERT INTO basket(fruit) values('apple');
	INSERT INTO basket(fruit) values('apple');

	INSERT INTO basket(fruit) values('orange');
	INSERT INTO basket(fruit) values('orange');
	INSERT INTO basket(fruit) values('orange');

	INSERT INTO basket(fruit) values('banana');

	////


	-- -- --/

	SELECT
	fruit,
	COUNT( fruit )
	FROM
	basket
	GROUP BY
	fruit
	HAVING
	COUNT( fruit )> 1
	ORDER BY
	fruit;
	/*
	//Eloquent Laravel
	$dupli = DB::table('partners')
	->select('code', DB::raw('COUNT( code )'))
	->groupBy('code')
	->havingRaw('COUNT( code )> 1')
	->orderBy('code')
	->get();
	SimpleLog::logThis( $dupli, ['append','file'=>'temp_teste']);
	*/


	-- -- --

	DELETE
	FROM
	basket a
	USING basket b
	WHERE
	a.id < b.id
	AND a.fruit = b.fruit;

	*/


	select * from basket
	-- ----------------------------------------------------

	-- Corrige/trata Erro/mensagem:
	-- Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
	-- https://askubuntu.com/questions/50621/cannot-connect-to-postgresql-on-port-5432
	-- https://askubuntu.com/a/534087

	-- ----------------------------------------------------
	-- array case-insensitive ilike
	SELECT "employees".* FROM "employees"
	WHERE "employees"."deleted_at" IS NULL
	AND "employees"."client_id" = 15001
	AND "employees"."active" = true
	AND "employees"."first_name" ILIKE ANY(ARRAY['tiago','alex'])
	-- ----------------------------------------------------
	#mysql allow remote connection
	#then grant root all privaleges to root
	GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
	FLUSH PRIVILEGES;
	# sqlite analyze table

	#Show all databases conected
	sqlite> .databases
	main: /home/ubuntu/db.sqlite

	# Enable stats
	sqlite> .stats on

	# Analise all
	ANALYZE;

	# Analise one table
	ANALYZE main.users;