PostgreSQL 14 새기능 이야기
1.postgres=# CREATE PROCEDURE p_test(OUT a integer)
LANGUAGE plpgsql
AS $$
begin
a := 1;
end;
$$;
CREATE PROCEDURE
postgres=# call p_test(1);
a
---
1
2.
postgres=# create table tree (id int, link int, data text);
CREATE TABLE
postgres=# copy tree from stdin;
한 줄에 한 레코드씩 데이터를 입력하고
자료입력이 끝나면 backslash 점 (\.) 마지막 줄 처음에 입력하는 EOF 시그널을 보내세요.
>> 100 200 data100
>> 55 400 data55
>> 10 200 data10
>> 200 \N data200
>> 1000 \N data1000
>> 70 200 data70
>> 5 10 data5
>> \.
COPY 7
postgres=# select * from tree;
id | link | data
------+------+----------
100 | 200 | data100
55 | 400 | data55
10 | 200 | data10
200 | | data200
1000 | | data1000
70 | 200 | data70
5 | 10 | data5
(7개 행)
postgres=# WITH RECURSIVE search_tree(id, link, data) AS (
SELECT t.id, t.link, t.data
FROM tree t where link is null
UNION ALL
SELECT t.id, t.link, t.data
FROM tree t, search_tree st
WHERE t.link = st.id
) SELECT * FROM search_tree;
id | link | data
------+------+----------
200 | | data200
1000 | | data1000
100 | 200 | data100
10 | 200 | data10
70 | 200 | data70
5 | 10 | data5
(6개 행)
postgres=# WITH RECURSIVE search_tree(id, link, data) AS (
SELECT t.id, t.link, t.data
FROM tree t where link is null
UNION ALL
SELECT t.id, t.link, t.data
FROM tree t, search_tree st
WHERE t.link = st.id
) SEARCH DEPTH FIRST BY id SET ordercol SELECT * FROM search_tree
order by ordercol;
id | link | data | ordercol
------+------+----------+------------------
200 | | data200 | {(200)}
10 | 200 | data10 | {(200),(10)}
5 | 10 | data5 | {(200),(10),(5)}
70 | 200 | data70 | {(200),(70)}
100 | 200 | data100 | {(200),(100)}
1000 | | data1000 | {(1000)}
(6개 행)
3.
postgres=# SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release'];;
jsonb
-------
14
postgres=# SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release'];;
jsonb
-------
14
4.
postgres=# SELECT int4multirange(int4range(1, 14), int4range(20, 25), int4range(16,19));
;
int4multirange
--------------------------
{[1,14),[16,19),[20,25)}
5.
postgres=# \h create statistics
명령: CREATE STATISTICS
설명: 새 확장 통계정보 만들기
문법:
CREATE STATISTICS [ IF NOT EXISTS ] 통계정보_이름
ON ( 표현식 )
FROM 테이블이름
CREATE STATISTICS [ IF NOT EXISTS ] 통계정보_이름
[ ( 통계정보_종류 [, ... ] ) ]
ON { 칼럼이름 | ( 표현식 ) }, { 칼럼이름 | ( 표현식 ) } [, ...]
FROM 테이블이름
6.
root@ddb23215b67c:/# psql --version
psql (PostgreSQL) 9.6.21
root@ddb23215b67c:/# psql -h 172.19.0.2 -U postgres
postgres 사용자의 암호:
psql(9.6.21, 14.0 (Debian 14.0-1.pgdg110+1) 서버)
경고: psql 메이저 버전 9.6, 서버 메이저 버전 14.
일부 psql 기능이 작동하지 않을 수도 있습니다.
도움말을 보려면 "help"를 입력하십시오.
postgres=# \q
---
root@42b8260b3623:/# psql --version
psql (PostgreSQL) 9.5.5
root@42b8260b3623:/# psql -h 172.19.0.2 -U postgres
psql: 10 인증 방법이 지원되지 않음
7.
psql (13.3 (Debian 13.3-1.pgdg100+1))
도움말을 보려면 "help"를 입력하십시오.
postgres=# select extract(hour from current_date);
date_part
-----------
0
(1개 행)
vs.
psql (14.0 (Debian 14.0-1.pgdg110+1))
도움말을 보려면 "help"를 입력하십시오.
postgres=# select extract(hour from current_date);
오류: date units "hour" not supported
8.
psql (13.3 (Debian 13.3-1.pgdg100+1))
도움말을 보려면 "help"를 입력하십시오.
postgres=# select factorial(-1);
factorial
-----------
1
(1개 행)
vs.
psql (14.0 (Debian 14.0-1.pgdg110+1))
도움말을 보려면 "help"를 입력하십시오.
postgres=# select factorial(-1);
오류: factorial of a negative number is undefined
9.
postgres=# show vacuum_cleanup_index_scale_factor;
오류: 알 수 없는 환경 매개 변수 이름: "vacuum_cleanup_index_scale_factor"
10.
postgres=# \h vacuum
명령: VACUUM
설명: 물리적인 자료 정리 작업 - 쓰레기값 청소
문법:
VACUUM [ ( 옵션 [, ...] ) ] [ 테이블과_칼럼 [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ 테이블과_칼럼 [, ...] ]
옵션 사용법:
FULL [ boolean ]
FREEZE [ boolean ]
VERBOSE [ boolean ]
ANALYZE [ boolean ]
DISABLE_PAGE_SKIPPING [ boolean ]
SKIP_LOCKED [ boolean ]
INDEX_CLEANUP { AUTO | ON | OFF }
PROCESS_TOAST [ boolean ]
TRUNCATE [ boolean ]
PARALLEL 정수
테이블과_칼럼 사용법:
테이블이름 [ ( 칼럼이름 [, ...] ) ]
URL: https://www.postgresql.org/docs/14/sql-vacuum.html
11.
\h alter table
ALTER TABLE [ IF EXISTS ] 이름
DETACH PARTITION 파티션_이름 [ CONCURRENTLY | FINALIZE ]
12. 답답함을 풀어줍니다.
postgres=# select * from pg_stat_progress_copy ;
pid | datid | datname | relid | command | type | bytes_processed | bytes_total | tuples_processed | tuples_excluded
-----+-------+---------+-------+---------+------+-----------------+-------------+------------------+-----------------
postgres=# select * from pg_stat_wal \gx
-[ RECORD 1 ]----+-----------------------------
wal_records | 12052
wal_fpi | 266
wal_bytes | 6289054
wal_buffers_full | 1763
wal_write | 1896
wal_sync | 132
wal_write_time | 17.65
wal_sync_time | 3705.877
stats_reset | 2021-10-05 12:54:01.75027+09
postgres=# select pg_stat_reset_shared('wal');
pg_stat_reset_shared
----------------------
(1개 행)
postgres=# select * from pg_stat_wal \gx
-[ RECORD 1 ]----+------------------------------
wal_records | 0
wal_fpi | 0
wal_bytes | 0
wal_buffers_full | 0
wal_write | 0
wal_sync | 0
wal_write_time | 0
wal_sync_time | 0
stats_reset | 2021-10-05 16:22:36.509571+09
postgres=# show in_hot_standby ;
in_hot_standby
----------------
off
명령: CREATE TRIGGER
설명: 새 트리거 만들기
문법:
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER 이름 { BEFORE | AFTER | INSTEAD OF } { 이벤트 [ OR ... ] }