Database

PostgreSQL

• Log in:
  psql -h localhost -p 5432 -U <user> -d <database>
• List db:
  \list or \c
• Switch db:
  \connect dbname or \c db
• List table:
  \dt
• Docker all in:
  docker exec -it <container_id> psql -U <user> -d <db>
• List tables:
  SELECT * FROM pg_catalog.pg_tables;
• List namespace:
  select * from pg_catalog.pg_namespace;
• Clean db:
  DROP SCHEMA property CASCADE; CREATE SCHEMA property;
• Show columns:
  SELECT table_name, column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = '' order by column_name;
• Show constraints:
  SELECT con.* FROM pg_catalog.pg_constraint con INNER JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace WHERE rel.relname = '';
• Create db:
  create database <db>;
• Query Jsonb field:
  select from permissions where metadata->'failed' != '[]' and metadata->'failed' @> '[{"executor_ref":"sonarqubeExecutor"}]' and creation_date > '2025-11-01';
• Group by year and month:

select date_part('year', creation_date) AS txn_year, date_part('month', creation_date) AS txn_month, count(*) as monthly_sum from table group by txn_year, txn_month order by monthly_sum desc;`

• Query to select a nested field in jsonb:

WITH unnested AS ( SELECT jsonb_array_elements(metadata -> 'failed') AS element FROM permissions where creation_date > '2025-12-01') SELECT element ->> 'executor_ref' AS executor_ref, COUNT(*) AS occurrence FROM unnested WHERE element ->> 'project_ref' = 'sdoscsp' GROUP BY executor_ref;

MySql

• Connect to a db:
  mysql -udb_name -ppwd
• Remove liquibase lock:

UPDATE CONFIGURATION_ADMINISTRATION_DATABASECHANGELOGLOCK
SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null ;

• clean db:

DROP database db_name;
CREATE database db_name;

• select db:
  use db_name;
• List tables:
  show tables;