PostgreSQL

Базовые команды

    psql -c "create database db_name"
    psql -d ergu_reg_srvc -c "create schema stage;"
    psql -c "create user user_name with password '1234';"
    psql -c "grant all privileges on database db_name to user_name;"
    psql -d db_name -f /tmp/script.sql

Импорт CSV

    psql -d db_name -c "COPY public.table(column1,column2) FROM '/path/to/file.csv' WITH DELIMITER ';' CSV HEADER;"

Редактирование csv

    sed -i 's/,/;/g' file.csv
    sed -i 's/NULL//g' file.csv

    update public.table_name set column_1= NULL where column_1='';
    delete from схема.таблица where что то = чему то;

Выдать права суперпользователя

    ALTER USER user_name SUPERUSER;
    ALTER USER user_name NOSUPERUSER;

Права пользователей

Посмотреть права пользователей на таблицу

    select * from information_schema.table_privileges where table_name='table_name';

Выдать полные права пользователю на таблицу

    GRANT ALL ON TABLE table_name TO user_name;

Выдать права пользователю на схему

    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_name;
    GRANT ALL ON SCHEMA STAGE TO user_name;

Выдавать права на чтение пользователю к таблице

    GRANT SELECT ON table_name to user_name;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_name;

Размер таблицы

SELECT pg_size_pretty( pg_total_relation_size( 'table_name' ) );

Срез 20 табличек по размеру

    SELECT
      schemaname AS schema_name,
      tablename AS table_name,
      pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS table_size
    FROM
      pg_tables
    WHERE
      schemaname NOT LIKE 'pg_%' AND
      schemaname != 'information_schema'
    ORDER BY
      pg_total_relation_size(schemaname || '.' || tablename) DESC LIMIT 20;

Срез 20 бд по размеру

    SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
    FROM pg_database
    WHERE datistemplate = false
    ORDER BY pg_database_size(datname) DESC
    LIMIT 20;

Расположение конфига

    SHOW config_file;

Расположение pg_hba

    SHOW hba_file;

Значение из конфига

    SHOW max_connections;

Текущее значение

    SELECT COUNT(*) FROM pg_stat_activity;

Посмотреть что занимает подключения

    SELECT application_name, COUNT(*)
    FROM pg_stat_activity
    GROUP BY application_name;

Посмотреть процесс

    SELECT * FROM pg_stat_activity WHERE pid = 25548

Сбросить все подключения от бд

    SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.pid != pg_backend_pid();

Проверить слот репликации

    select * from pg_replication_slots;

LIKE / NOT LIKE

    SELECT *
    FROM your_table
    WHERE your_column NOT LIKE 'паттерн%';

Для того чтобы вывести уникальные значения из колонки и показать в каких колонках они повторяются

    SELECT value, string_agg(column_name, ', ') AS columns
    FROM (
      SELECT column_name, value
      FROM (
        SELECT column_name, value, COUNT(*) OVER (PARTITION BY value) AS count
        FROM (
          SELECT column_name, value
          FROM your_table
        ) AS subquery
      ) AS subquery
      WHERE count > 1
    ) AS subquery
    GROUP BY value;

Столбец "value" содержит уникальные значения из колонки, а столбец "columns" содержит список названий колонок, в которых эти значения повторяются.

ON CONFLICT DO UPDATE

    CREATE TABLE stage.artemis_queue (
        broker varchar(40) NULL,
        queue varchar(40) NULL,
        CONSTRAINT qual_name UNIQUE (broker, queue)
    );

запрос для такой бд

    INSERT INTO stage.artemis_queue (broker, queue) VALUES ('int-01', 'TEST1' ) 
    ON CONFLICT (broker, queue) DO NOTHING;

Пример второй, где обновлется дата

    CREATE TABLE stage.qm (
    broker varchar(40) NULL,
    "groups" varchar(40) NULL,
    application varchar(40) NULL,
    "date" date NULL,
    CONSTRAINT qual_namef_unique UNIQUE (broker, groups, application)
    );

    INSERT INTO stage.qm (broker, groups, application, date) 
    VALUES ('$BRK', '$eg', '${array[$i]}', TO_DATE('${darray[$i]}', 'MM/DD/YY') ) 
    ON CONFLICT (broker, groups, application) 
    DO UPDATE SET date = TO_DATE('${darray[$i]}', 'MM/DD/YY');

Список статей