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

 

Автор: Александр