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;
	#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;