postgres sql лист примеров

alexandr=> select now(), current_timestamp, current_date, current_time;

              now              |       current_timestamp       | current_date |    current_time    
-------------------------------+-------------------------------+--------------+--------------------
 2018-11-06 14:11:23.627229+07 | 2018-11-06 14:11:23.627229+07 | 2018-11-06   | 14:11:23.627229+07
(1 строка)

Округление

alexandr=> SELECT date_trunc('year', current_timestamp);
       date_trunc       
------------------------
 2018-01-01 00:00:00+07
(1 строка)

alexandr=> SELECT date_trunc('month', current_timestamp);
       date_trunc       
------------------------
 2018-11-01 00:00:00+07
(1 строка)

alexandr=> SELECT date_trunc('week', current_timestamp);
       date_trunc       
------------------------
 2018-11-05 00:00:00+07
(1 строка)

alexandr=> SELECT date_trunc('day', current_timestamp);
       date_trunc       
------------------------
 2018-11-06 00:00:00+07
(1 строка)

alexandr=> SELECT date_trunc('hour', current_timestamp);
       date_trunc       
------------------------
 2018-11-06 14:00:00+07

alexandr=> SELECT date_trunc('minute', current_timestamp);
       date_trunc       
------------------------
 2018-11-06 14:11:00+07
(1 строка)

PostgreSQL: получить список установленных расширений

select * from pg_available_extensions where installed_version is not null;

Дата, время

alexandr=> select clock_timestamp(), CURRENT_TIMESTAMP, timestamp 'now';
        clock_timestamp        |       current_timestamp       |         timestamp          
-------------------------------+-------------------------------+----------------------------
 2019-03-17 21:58:16.107802+07 | 2019-03-17 21:58:16.107658+07 | 2019-03-17 21:58:16.107658
(1 строка)

 

alexandr=> select transaction_timestamp(), CURRENT_TIMESTAMP, now();
     transaction_timestamp     |       current_timestamp       |              now              
-------------------------------+-------------------------------+-------------------------------
 2019-03-17 22:01:52.092357+07 | 2019-03-17 22:01:52.092357+07 | 2019-03-17 22:01:52.092357+07
(1 строка)

 

alexandr=> select now(),
alexandr-> CURRENT_TIMESTAMP,
alexandr-> timestamp 'now',
alexandr-> clock_timestamp(),
alexandr-> timeofday();

-[ RECORD 1 ]-----+------------------------------------
now               | 2019-03-17 22:09:57.154112+07
current_timestamp | 2019-03-17 22:09:57.154112+07
timestamp         | 2019-03-17 22:09:57.154112
clock_timestamp   | 2019-03-17 22:09:57.154271+07
timeofday         | Sun Mar 17 22:09:57.154272 2019 +07

Включение pg_stat_statements

Отредактируем файл postgresql.conf (предварительно найдем его в системе)

# find / -name 'postgresql.conf'

/var/lib/pgpro/std-11/data/postgresql.conf

 

Отредактируем / добавим в postgresql.conf строку:

было:
#shared_preload_libraries = ''

стало:
shared_preload_libraries = 'pg_stat_statements'

 

Далее

psql -U postgres -c "CREATE EXTENSION pg_stat_statements;"

COUNT(*)

select count(*) from table_book;
--------
 461955
(1 строка)

Время: 151,924 мс

SELECT reltuples::BIGINT AS estimate FROM pg_class WHERE oid = 'table_book'::regclass;
 estimate 
----------
   461955
(1 строка)

Время: 13,885 мс

 

Показать путь текущего лог файла

PG > = 10

psql -U postgres postgres -t -A -c "SELECT CASE WHEN substr(current_setting('log_directory'), 1, 1)
 <> '/' THEN current_setting('data_directory') ELSE '' END || '/' || pg_current_logfile()"

 /var/lib/pgpro/std-12/data/log/postgresql-2020-05-27_081230.log

 

иначе в консоли

 

ps uw -U postgres \
  | grep [l]ogger \
  | awk '{print "/proc/"$2"/fd"}' \
  | xargs ls -l \
  | grep `cd; psql -U postgres postgres -t -A -c \
  "SELECT CASE WHEN substr(current_setting('log_directory'), 1, 1) = '/' \
   THEN current_setting('log_directory') ELSE current_setting('data_directory') || '/' 
  || current_setting('log_directory') END"` \
  | awk '{print $NF}'

/var/lib/pgpro/std-12/data/log/postgresql-2020-05-27_081230.log