dev-sql-snippets

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'])
-- ----------------------------------------------------

mariadb-snippets---permissoes-a-usuarios-mariadb.md

Passo 1: Acesse o MySQL com um usuário administrador

1. Abra o terminal e conecte-se ao MySQL como um usuário com privilégios administrativos (geralmente root):

mysql -u root -p

Você será solicitado a inserir a senha do usuário root.

Passo 2: Conceda permissões ao usuário mysql

2. Após acessar o MySQL, execute o seguinte comando para conceder todas as permissões ao usuário mysql para todos os bancos de dados:

GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' IDENTIFIED BY 'sua_senha' WITH GRANT OPTION;

Certifique-se de substituir 'sua_senha' pela senha desejada para o usuário mysql.

3. Atualize as permissões para garantir que as mudanças tenham efeito:

FLUSH PRIVILEGES;

Passo 3: Tente criar o banco de dados novamente

4. Saia do MySQL:

EXIT;

5. Agora, tente novamente criar o banco de dados usando o usuário mysql:

mysql -u mysql -p

E então execute:

CREATE DATABASE seu_novo_banco;

Exemplo

GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' IDENTIFIED BY 'D3vp0rt4l' WITH GRANT OPTION;
FLUSH PRIVILEGES;

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---como ver quanto espaço um banco consome no postgres.md

Como Verificar Qual Banco Consome Mais Espaço no PostgreSQL

Para identificar qual banco de dados consome mais espaço no PostgreSQL, você pode usar algumas consultas SQL que fornecem informações sobre o tamanho dos bancos de dados. Aqui estão os passos e um exemplo de consulta que você pode usar:

1. Conecte-se ao PostgreSQL

Primeiro, você precisa se conectar ao seu servidor PostgreSQL usando o terminal ou uma ferramenta de gerenciamento como o psql ou pgAdmin.

2. Use a Consulta SQL

Você pode usar a seguinte consulta SQL para listar todos os bancos de dados e seus tamanhos:

SELECT
    pg_database.datname AS nome_do_banco,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS tamanho
FROM
    pg_database
ORDER BY
    pg_database_size(pg_database.datname) DESC;

3. Explicação da Consulta

• pg_database: Esta tabela contém informações sobre todos os bancos de dados no servidor.
• pg_database_size(): Esta função retorna o tamanho do banco de dados especificado.
• pg_size_pretty(): Esta função formata o tamanho em uma forma legível (por exemplo, KB, MB, GB).
• ORDER BY: A consulta ordena os resultados pelo tamanho do banco de dados em ordem decrescente, permitindo que você veja rapidamente qual banco consome mais espaço.

4. Executando a Consulta

Após executar a consulta, você verá uma lista de bancos de dados com seus respectivos tamanhos. O banco de dados que aparece no topo da lista é o que consome mais espaço.

Conclusão

Usando a consulta SQL acima, você pode facilmente identificar qual banco de dados no PostgreSQL está consumindo mais espaço. Se precisar de mais informações ou ajuda com outras consultas, estou à disposição!

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

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.