Contrib Extension 에서 데이터 타입을 추가로 제공하는 Extension을 모아서 정리해봤습니다.
- citext
- cube
- hstore
- isn
- lo
- ltree
- seg
citext
이름대로 citext라는 데이터 유형을 제공합니다. text와 거의 동일하게 동작하나, 대소문자를 구별하지 않고 lower 값을 비교할 때 호출됩니다. search_path에 citext를 등록해야 text보다 먼저 호출될 수 있습니다.
예시
nick 행이 text/citext 유형인 2개의 테이블을 만들어, select로 검사하여 citext의 동작을 확인하는 예시입니다.
CREATE TABLE users1 (
nick TEXT PRIMARY KEY,
pass TEXT NOT NULL –nick의 패스워드를 임의로랜덤으로 생성
);INSERT INTO users1 VALUES ( ‘larry’, sha256(random()::text::bytea) );
SELECT * FROM users1 WHERE nick = ‘Larry’;
— pg는 대문자 L과 소문자 l을 구분하여 Larry 데이터 검색이 되지 않음
nick | pass
——+——
(0 rows)CREATE TABLE users2 (
nick CITEXT PRIMARY KEY,
pass TEXT NOT NULL
);INSERT INTO users2 VALUES ( ‘larry’, sha256(random()::text::bytea) );
SELECT * FROM users2 WHERE nick = ‘Larry’;
— citext 데이터 유형을 통해 대문자 L과 소문자 l을 구분하지 않고 검색이 되어 larry의 데이터를 볼수 있음
nick | pass
——-+——————————————————————–
larry | xb0672e2f1de116dbb93fdef8826b49157358d7e7a7aa06f8da0310c432995588
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/citext.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/citext/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/citext/expected
cube
다차원 큐브를 나타내는 데이터 유형을 구현합니다.
- syntex
syntex 정리
| 구분 | 설명 |
|---|---|
| x 또는 (x) | 1차원 점 또는 길이가 0인 간격 |
| x1,x2,…,xn 또는 (x1,x2,…,xn) | 내부적으로 부피가 0인 큐브로 표현되는 n차원 공간의 한 점 |
| (x),(y) 또는 [(x),(y)] | x에서 시작해서 끝나는 1차원 간격 y(반대 순서 상관 없음) |
| (x1,…,xn),(y1,…,yn) 또는 [(x1,…,xn),(y1,…,yn)] | 대각선으로 마주보는 한 쌍의 모서리로 표현되는 n차원 정육면체 |
- precision
값은 내부적으로 64비트 부동 소수점 숫자로 저장됩니다. 유효 숫자가 16개 이상인 숫자는 잘립니다.
- 같이 제공되는 연산자
연산자 정리
| 연산자 종류 및 return | 설명 |
|---|---|
| cube && cube returns boolean | 큐브가 겹치는지 확인 |
| cube @> cube returns boolean | 첫번째 큐브에 두번째 큐브가 포함되어 있는지 확인 |
| cube <@ cube returns boolean | 첫번째 큐브가 두번째 큐브에 포함되어 있는지 확인 |
| cube → integer returns float8 | 큐브의 n-번째 좌표를 추출(1부터 계산) |
| cube ~> integer returns float8 | 다음과 같이 계산하여 큐브의 n-번째 좌표를 추출 n = 2 * k – 1 : k-번째 차원의 하한 n = 2 * k : 차원 k-의 상한을 의미 음수 n은 해당 양수 좌표의 역 값을 나타냄 |
| cube <→ cube returns float8 | 두 큐브 사이의 유클리드 거리를 계산 |
| cube <#> cube returns float8 | 두 튜브 사이의 택시(L-1미터법) 거리를 계산 |
| cube <⇒ cube returns float8 | 두 큐브 사이의 체바쇼프(L-inf미터법) 거리를 계산 |
- 같이 제공되는 함수
함수 정리
| 함수 | 설명 |
|---|---|
| cube ( float8 ) returns cube | 두 좌표가 동일한 1차원 정육면체 생성 |
| cube ( float8, float8 ) returns cube | 1차원 큐브 생성 |
| cube ( float8[] ) returns cube | 배열에 의해 정의된 좌표를 사용하여 부피가 0인 큐브 생성 |
| cube ( float8[], float8[] ) returns cube | 길이가 같아야 하는 두 배열에 의해 정의된 대로 오른쪽 위 및 왼쪽 아래 좌표로 큐브 생성 |
| cube ( cube, float8 ) returns cube | 새 좌표의 두 끝점에 대해 동일한 값을 사용하여 기존 큐브에 차원을 추가하여 새 큐브 생성 |
| cube ( cube, float8, float8 ) returns cube | 기존 큐브에 차원을 추가하여 새 큐브 생성 |
| cube_dim ( cube ) returns integer | 큐브의 차원의 수를 반환 |
| cube_ll_coord ( cube, integer ) returns float8 | 큐브의 왼쪽 아래 모서리에 대한 n-번째 좌표 값을 반환 |
| cube_ur_coord ( cube, integer ) returns float8 | 큐브의 오른쪽 위 모서리에 대한 n-번째 좌표 값을 반환 |
| cube_is_point ( cube ) returns boolean | 큐브가 점인 경우, true를 반환(두 모서리가 동일한 경우) |
| cube_distance ( cube, cube ) returns float8 | 두 큐브 사이의 거리를 반환 |
| cube_subset ( cube, integer[] ) returns cube | 배열의 차원 인덱스 목록을 사용하여 기존 큐브에서 새 큐브 생성 |
| cube_union ( cube, cube ) returns cube | 두 큐브의 합집합을 생성 |
| cube_inter ( cube, cube ) returns cube | 두 큐브의 교차점을 생성 |
| cube_enlarge ( c cube, r double, n integer ) returns cube | n차원에서 지정된 반지름 r 만큼 큐브 크기 증가. 반지름이 음수면 큐브가 대신 축소 |
예시
cube 데이터 유형을 이용해 cube에서 제공하는 연산자와 함수를 사용하는 예시입니다.
SELECT ‘1’::cube AS cube;
cube
(1)
(1 row)SELECT ‘-1’::cube AS cube;
cube
(-1)
(1 row)SELECT ‘(0),(0)’::cube AS cube;
cube
(0)
(1 row)SELECT ‘(0),(1)’::cube AS cube;
cube
(0),(1)
(1 row)SELECT ‘[(0,0,0,0),(0,0,0,0)]’::cube AS cube;
cube
(0, 0, 0, 0)
(1 row)SELECT cube(cube(1,2),3);
cube
(1, 3),(2, 3)
(1 row)SELECT cube_subset(cube(‘(1,3,5),(6,7,8)’), ARRAY[3,2,1,1]);
cube_subset
(5, 3, 1, 1),(8, 7, 6, 6)
(1 row)SELECT cube(‘(1,2),(1,2)’); — cube_in
cube
(1, 2)
(1 row)SELECT ’24, 33.20′::cube = ’24, 33.20′::cube AS bool;
bool
t
(1 row)SELECT ‘0,0,1’::cube @> ‘0,0,0’::cube AS bool;
bool
f
(1 row)SELECT cube_distance(‘(0)’::cube,'(2,2,2,2)’::cube);
cube_distance
4(1 row)
SELECT cube_dim(‘(0)’::cube);
cube_dim
1(1 row)
SELECT cube_ll_coord(‘(-1,1),(2,-2)’::cube, 1);
cube_ll_coord
-1(1 row)
SELECT cube_ur_coord(‘(-1,1),(2,-2)’::cube, 1);
cube_ur_coord
2(1 row)
SELECT cube_is_point(‘(0)’::cube);
cube_is_point
t
(1 row)SELECT cube_enlarge(‘(0)’::cube, 0, 0);
cube_enlarge
(0)
(1 row)SELECT cube_union(‘(1,2),(3,4)’::cube, ‘(5,6,7),(8,9,10)’::cube);
cube_union
(1, 2, 0),(8, 9, 10)
(1 row)SELECT cube_inter(‘(1,2),(10,11)’::cube, ‘(3,4), (16,15)’::cube); — intersects
cube_inter
(3, 4),(10, 11)
(1 row)SELECT distance_taxicab(‘(2,2),(10,10)’::cube, ‘(0,0),(5,5)’::cube);
distance_taxicab
0(1 row)
SELECT distance_chebyshev(‘(2,2),(10,10)’::cube, ‘(0,0),(5,5)’::cube);
distance_chebyshev
0(1 row)
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/cube.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/cube/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/cube/expected
hstore
단일 값 내에 key – value 세트를 저장하기 위한 데이터 유형을 구현합니다. key 와 value는 단순한 text 문자열입니다. hstore의 각 키는 고유합니다. 중복 키를 사용하여 선언하면 하나만 저장되며, 어떤 키가 유지될 지 보장할 수 없습니다. 또한 값은 NULL이 선언 될 수 있습니다. NULL을 일반 문자열로 처리하려면 “NULL” 과 같이 사용합니다.
- 같이 제공되는 연산자
연산자 정리
| 연산자 | 설명 |
|---|---|
| hstore → text returns text | 주어진 키와 관련된 값을 반환하거나 존재하지 않는 경우 NULL을 반환 |
| hstore → text[] returns text[] | 주어진 키와 관련된 값을 반환하거나 존재하지 않는 경우 NULL을 반환 |
| hstore | |
| hstore ? text returns boolean | hstore가 키를 포함하는지 확인 |
| hstore ?& text[] returns boolean | hstore가 특정한 모든 키를 포함하고 있는지 확인 |
| hstore ? | text[] returns boolean |
| hstore @> hstore returns boolean | 왼쪽 피연산자에 오른쪽이 포함되는지 확인 |
| hstore <@ hstore returns boolean | 왼쪽 피연산자가 오른쪽에 포함되는지 확인 |
| hstore – text returns hstore | 왼쪽 피연산자에서 키를 삭제 |
| hstore – text[] returns hstore | 키를 삭제 |
| hstore – hstore returns hstore | 오른쪽 피연산자의 쌍과 일치하는 쌍을 왼쪽 피연산자에서 삭제 |
| anyelement #= hstore returns anyelement | 왼쪽 피연산자(복합유형)의 필드를 hstore의 일치하는 값으로 변경 |
| %% hstore → text[] returns hstore | hstore을 키와 값의 배열로 변환 |
| %# hstore → text[] returns hstore | hstore을 2차원 키와 값의 배열로 변환 |
- 같이 제공되는 함수
함수 정리
| 함수 | 설명 |
|---|---|
| hstore ( record ) returns hstore | 레코드 또는 행에서 hstore을 구성 |
| hstore ( text[] ) returns hstore | 키/값 배열 또는 2차원 배열에서 hsotre을 구성 |
| hstore ( text[], text[] ) returns hstore | 별도의 키 및 값 배열에서 hstore을 구성 |
| hstore ( text, text ) returns hstore | hstore 단일 항목을 구성 |
| akeys ( hstore ) returns text[] | hstore의 키를 배열로 추출 |
| skeys ( hstore ) returns setof text | hstore의 키을 세트로 추출 |
| avals ( hstore ) returns text[] | hstore의 값를 배열로 추출 |
| svals ( hstore ) returns setof text | hstore의 값을 집합으로 추출 |
| hstore_to_array ( hstore ) returns text[] | hstore의 키와 값을 키와 값의 배열로 바꿔 추출 |
| hstore_to_matrix ( hstore ) returns text[] | hstore의 키와 값을 2차원 배열로 추출 |
| hstore_to_json ( hstore ) returns json | hstore를 json 값으로 변환하고, null이 아닌 모든 값을 json 문자열로 변환 |
| hstore_to_jsonb ( hstore ) returns jsonb | hstore를 jsonb 값으로 변환하고, null이 아닌 모든 값을 json 문자열로 변환 |
| hstore_to_json_loose ( hstore ) returns json | hstore를 json 값으로 변환하지만 숫자 값과 부울 값을 구별 |
| hstore_to_jsonb_loose ( hstore ) returns jsonb | hstore를 jsonb 값으로 변환하지만 숫자 값과 부울 값을 구별 |
| slice ( hstore, text[] ) returns hstore | 지정된 키만 포함하는 hstore의 하위 집합을 추출 |
| each ( hstore ) returns setof record( key text, value text) | hstore의 키와 값을 레코드 세트로 추출 |
| exist ( hstore, text ) returns boolean | hstore가 key를 가지고 있는지 확인 |
| defined ( hstore, text ) returns boolean | hstore가 NULL이 아닌 값을 가지고 있는지 확인 |
| delete ( hstore, text ) returns hstore | 일치하는 키가 있는 쌍을 삭제 |
| delete ( hstore, text[] ) returns hstore | 일치하는 키가 있는 쌍을 삭제 |
| delete ( hstore, hstore ) returns hstore | 두번째 인수와 일치하는 쌍을 삭제 |
| populate_record ( anyelement, hstore ) returns anyelement | 왼쪽 피연산자(복합유형)의 필드를 hstore에 일치하는 값으로 변경 |
예시
다음은 hstore에서 제공하는 기능을 통해 key – value를 다루는 예시입니다.
select ‘a=>b’::hstore;
hstore
“a”=>”b”
(1 row)select ‘”a”=> “b”‘::hstore;
hstore
“a”=>”b”
(1 row)select (‘aa=>”NULL”, c=>d , b=>16′::hstore->’aa’) is null;
?column?
f
(1 row)select exist(‘a=>NULL, b=>qq’, ‘a’);
exist
t
(1 row)select defined(‘a=>NULL, b=>qq’, ‘a’);
defined
f
(1 row)select delete(‘a=>1 , b=>2, c=>3’::hstore, ‘a’);
delete
“b”=>”2”, “c”=>”3”
(1 row)select pg_column_size(hstore(v))
= pg_column_size(‘a=>1, b=>”foo”, c=>”1.2″, d=>”3″, e=>”0″‘::hstore)
from testhstore1 v;?column?
t
(1 row)select populate_record(v, hstore(‘c’, ‘3.45’)) from testhstore1 v;
populate_record
(1,foo,3.45,3,0)
(1 row)select hstore_to_array(‘aa=>1, cq=>l, b=>g, fg=>NULL’::hstore);
hstore_to_array
{b,g,aa,1,cq,l,fg,NULL}
(1 row)CREATE TABLE test (col1 integer, col2 text, col3 text);
SELECT * FROM populate_record(null::test,
‘”col1″=>”456″, “col2″=>”zzz”‘);
col1 | col2 | col3
——+——+——
456 | zzz |
(1 row)
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/hstore.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/hstore/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/hstore/expected
isn
EAN13, UPC, ISBN(도서), ISMN(음악) 및 ISSN(연재물) 과 같은 국제 제품 번호 지정 표준에 대한 데이터 유형을 제공합니다. 숫자는 하드 코딩된 접두사 목록에 따라 입력시 valid check가 진행됩니다. 접두사 목록이 변경되면 다시 컴파일해야하기 때문에 추후 이 모듈의 접두사 valid check 지원이 중단 될 수 있습니다.
- 데이터 유형 정리
| 데이터유형 | 설명 |
|---|---|
| EAN13 | 유럽 품목 번호, 항상 EAN13 표시 형식으로 표시 |
| ISBN13 | 새로운 EAN13 표시 형식으로 표시될 국제 표준 도서 번호 |
| ISMN13 | 새로운 EAN13 표시 형식으로 표시되는 국제 표준 음악 번호 |
| ISSN13 | 새로운 EAN13 표시 형식으로 표시될 국제 표준 일련 번호 |
| ISBN | 기존의 짧은 표시 형식으로 표시되는 국제 표준 도서 번호 |
| ISMN | 기존의 짧은 표시 형식으로 표시되는 국제 표준 음악 번호 |
| ISSN | 기존의 짧은 표시 형식으로 표시되는 국제 표준 일련 번호 |
| UPC | 범용 제품 코드 |
- 같이 제공되는 함수
함수 정리
| 함수 | 설명 |
|---|---|
| isn_weak ( boolean ) returns boolean | weak input mode 세팅 및 신규 세팅을 return |
| isn_weak () returns boolean | 현재 weak mode를 return |
| make_valid ( isn ) returns isn | 잘못된 번호를 확인(invalid flag를 클리어) |
| is_valid ( isn ) returns boolean | invalid 플래그가 있는지 확인 |
weak mode라는 것은 유효하지 않은 데이터를 테이블에 삽입하는데 사용됩니다. invalid는 누락이아니라 check가 잘못되었음을 의미합니다. weak mode를 사용해 테이블에 유효하지 않은 숫자를 insert하면, 숫자가 수정되어 insert 되지만 끝에 느낌표와 함께 표시됩니다. 이는 is valid 함수로 확인하고, make_valid함수로 지울 수 있습니다.
예시
다음은 isn 데이터 유형 및 함수를 테스트하는 예시입니다.
CREATE TABLE test_isn (id isbn);
INSERT INTO test_isn VALUES(‘9780393040029’);
INSERT INTO test_isn VALUES(‘220500896?’);
INSERT INTO test_isn VALUES(‘978055215372?’);postgres=# select * from test_isn;
id
0-393-04002-X
2-205-00896-X
0-552-15372-9
(3 rows)SELECT isn_weak(true);
INSERT INTO test_isn VALUES(‘978-0-11-000533-4’);
INSERT INTO test_isn VALUES(‘9780141219307’);
INSERT INTO test_isn VALUES(‘2-205-00876-X’);
SELECT isn_weak(false);SELECT id FROM test_isn WHERE NOT is_valid(id); — 끝에 !로 유효하지 않은 숫자 확인
id
0-11-000533-3!
0-14-121930-0!
2-205-00876-5!
(3 rows)UPDATE test_isn SET id = make_valid(id) WHERE id = ‘2-205-00876-X!’;
SELECT * FROM test_isn;
id
0-393-04002-X
2-205-00896-X
0-552-15372-9
0-11-000533-3!
0-14-121930-0!
2-205-00876-5 — < make_valid로 유효하지 않은 2-205-00876-X! 데이터의 flag(!) 클리어
(6 rows)SELECT isbn13(id) FROM test_isn;
isbn13
978-0-393-04002-9
978-2-205-00896-8
978-0-552-15372-0
978-0-11-000533-1!
978-0-14-121930-1!
978-2-205-00876-0 — < isbn13으로 유효한 값 변환 확인
(6 rows)
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/isn.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/isn/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/isn/expected
lo
대형 개체(BLOB) 관리를 지원합니다. 데이터 유형 lo와 lo_manage라는 트리거를 지원합니다. 기존에도 blob형 데이터를 oid로 지정해서 다룰 수도 있지만, lo 모듈을 이용해 blob형 데이터를 lo로 지정하여 lo_manage 트리거를 통해 oid를 쿼리에서 다루지 않게 됩니다. 따라서 일반 데이터의 oid와 헷갈리지 않게되어 관리측면에서 유리해집니다.
예시
blob형 데이터를 다룰 테이블을 생성하고, 간단한 예시 이미지를 다루는 과정의 예시입니다.
이미지 파일 경로
[opensql@localhost:image]$ pwd
/opensql/image
[opensql@localhost:image]$ ls -arlt
total 100
-rw-r–r–. 1 opensql opensql 48248 Aug 24 00:51 pgele.png
drwx——. 16 opensql opensql 4096 Aug 24 00:51 ..
-rw-r–r–. 1 opensql opensql 48248 Aug 24 01:25 pgele2.png
drwxrwxr-x. 2 opensql opensql 41 Aug 24 01:25 .
기본 제공되는 oid 사용 시
CREATE TABLE image (
name text,
raster oid
);INSERT INTO image (name, raster) VALUES (‘beautiful image’, lo_import(‘/etc/motd’, 68583));
— 임의로 oid를 지정해서 넣게 되는데, 기존에 있던 일반 데이터의 oid일 수 있어서 다시 지정해줘야함.
— 일반 데이터의 oid와 헷갈리면 안되므로 insert/update/delete 실행할 때 마다 oid를 신경써야함.
lo 모듈 사용 시
CREATE TABLE a (fname name,image lo);
INSERT INTO a VALUES (’empty’);
INSERT INTO a VALUES (‘pgele.png’, lo_import(‘/opensql/image/pgele.png’)::lo); — lo타입을 써서 테이블에 insert
SELECT *,image::oid from a; — oid로 자동 배정 확인
SELECT oid FROM pg_largeobject_metadata; — largeobjet로 배정되는 내용 확인. oid가 생성되지만 oid로 데이터를 다루지는 않음oid
32740 — oid 는 다를 수 있음
(1 row)CREATE TRIGGER t_a
BEFORE UPDATE OR DELETE ON a
FOR EACH ROW
EXECUTE PROCEDURE lo_manage(image);INSERT INTO a VALUES (‘pgele2.png’, lo_import(‘/opensql/image/pgele2.png’));
— 구문에 lo를 쓰지않고도 트리거를 통해 저절로 lo 및 oid 배정됨SELECT * FROM a; — 내용 확인
SELECT oid FROM pg_largeobject_metadata;oid
32740
32775
(2 rows)delete from a where fname =’pgele2.png’;
SELECT * FROM a; –pgele2.png 삭제 후 내용 확인
SELECT oid FROM pg_largeobject_metadata; –삭제시에도 트리거가 자동으로 largeobject oid까지 삭제해줌oid
32740
(1 row)select lo_export(a.image, ‘/opensql/image/pgele_copy.png’) from a where fname=’pgele.png’;
— 기존 방식처럼 lo_export 사용 가능. 저장된 pgele.png 이미지를 pgele_copy.png라는 다른 이름의 이미지로 exportlo_export
1(1 row)
이미지파일 export 확인
[opensql@localhost:image]$ pwd
/opensql/image
[opensql@localhost:image]$ ls -arlt
total 100
-rw-r–r–. 1 opensql opensql 48248 Aug 24 00:51 pgele.png
drwx——. 16 opensql opensql 4096 Aug 24 00:51 ..
-rw-r–r–. 1 opensql opensql 48248 Aug 24 01:25 pgele2.png
drwxrwxr-x. 2 opensql opensql 41 Aug 24 01:25 .
-rw-r–r–. 1 opensql opensql 48248 Aug 24 01:32 pgele_copy.png
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/lo.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/lo/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/lo/expected
ltree
계층적 트리와 같은 구조에서 저장된 데이터의 레이블을 나타내기 위한 데이터 유형 ltree를 구현합니다. 레이블 트리를 통해 검색할 수 있는 광범위한 기능이 제공됩니다.
- 정의
레이블은 일련의 영숫자 문자와 밑줄입니다(예: C locale에서는 문자 A-Za-z0-9_가 허용됨 ). 레이블은 256자 미만이어야 합니다. 레이블 경로는 점으로 구분된 0개 이상의 레이블 시퀀스입니다. 레이블 경로의 길이는 65535개 레이블을 초과할 수 없습니다.
ltree는 레이블 경로를 저장합니다.
lquery는 ltree 값을 일치시키기 위한 정규식과 같은 패턴을 나타냅니다.
- 같이 제공되는 연산자
함수 정리
| 연산자 | 설명 |
|---|---|
| ltree @> ltree returns boolean | 왼쪽 인수가 오른쪽(또는 동일한)의 조상인지 확인 |
| ltree <@ ltree returns boolean | 왼쪽 인수가 오른쪽(또는 동일한)의 자손인지 확인 |
| ltree ~ lquery returns boolean lquery ~ ltree retruns boolean | ltree가 lquery와 일치하는지 확인 |
| ltree ? lquery[] returns boolean lquery[] ? ltree returns boolean | ltree가 배열의 lquery와 일치하는지 확인 |
| ltree @ ltxtquery returns boolean ltxtquery @ ltree returns boolean | ltree가 ltxtquery와 일치하는지 확인 |
| ltree || ltree returns ltree | ltree 경로를 연결 |
| ltree || text returns ltree text || ltree returns ltree | 텍스트를 ltree로 변환하고 연결 |
| ltree[] @> ltree returns boolean ltree <@ ltree[] returns boolean | 배열에 ltree의 조상이 포함되어있는지 확인 |
| ltree[] <@ ltree returns boolean ltree @> ltree[] returns boolean | 배열에 ltree의 자손이 포함되어있는지 확인 |
| ltree[] ~ lquery returns boolean lquery ~ ltree[] returns boolean | 배열에 lquery와 일치하는 경로가 있는지 확인 |
| ltree[] ? lquery[] returns boolean lquery[] ? ltree[] returns boolean | ltree 배열에 lquery와 일치하는 경로가 있는지 확인 |
| ltree[] @ ltxtquery returns boolean ltxtquery @ ltree[] returns boolean | 배열에 ltxtquery와 일치하는 경로가 있는지 확인 |
| ltree[] ?@> ltree returns ltree | ltree의 조상인 첫 번째 배열 항목을 반환하거나 없는 경우 NULL을 반환 |
| ltree[] ?<@ ltree returns ltree | ltree의 자손인 첫 번째 배열 항목을 반환하거나 없는 경우 NULL을 반환 |
| ltree[] ?~ lquery returns ltree | lquery와 일치하는 첫 번째 배열 항목을 반환하거나 일치하지 않으면 NULL을 반환 |
| ltree[] ?@ ltxtquery returns ltree | ltxtquery와 일치하는 첫 번째 배열 항목을 반환하거나 일치하지 않으면 NULL을 반환 |
- 같이 제공되는 함수
함수 정리
| 함수 | 설명 |
|---|---|
| subltree ( ltree, start integer, end integer ) returns ltree | 위치 시작에서 위치 끝-1 까지 ltree의 하위 경로를 반환(0부터 계산) |
| subpath ( ltree, offset integer, len integer ) returns ltree | 오프셋 위치에서 시작하여 길이가 len인 ltree의 하위 경로를 반환. 오프셋이 음수이면 하위 경로는 경로 끝에서 멀리 시작 |
| subpath ( ltree, offset integer ) returns ltree | 오프셋 위치에서 시작하여 경로 끝까지 확장되는 ltree의 하위 경로를 반환. 오프셋이 음수이면 하위 경로는 경로 끝에서 멀리 시작 |
| nlevel ( ltree ) returns integer | 경로의 레이블 수를 반환 |
| index ( a ltree, b ltree ) returns integer | a에서 b가 처음으로 나타나는 위치를 반환하거나 찾을 수 없으면 -1을 반환 |
| index ( a ltree, b ltree, offset integer ) returns integer | a에서 b가 처음으로 나타나는 위치를 반환하거나, 찾을 수 없으면 -1을 반환 |
| text2ltree ( text ) returns ltree | 텍스트를 ltree로 캐스트 |
| ltree2text ( ltree ) returns text | ltree를 텍스트로 캐스트 |
| lca ( ltree [, ltree [, … ]] ) returns ltree | 경로의 가장 긴 공통 조상을 계산(최대 8개의 인수가 지원됨) |
| lca ( ltree[] ) returns ltree | 배열에 있는 경로의 가장 긴 공통 조상을 계산 |
예시
ltree에 대한 예시와 이를 설명합니다. 다음은 테이블을 생성하여 데이터를 insert하는데, 트리형태로 그림과같이 표현됩니다.
–데이터는 contrib/ltree/ltreetest.sql에서도 제공합니다.
CREATE TABLE test (path ltree);
INSERT INTO test VALUES (‘Top’);
INSERT INTO test VALUES (‘Top.Science’);
INSERT INTO test VALUES (‘Top.Science.Astronomy’);
INSERT INTO test VALUES (‘Top.Science.Astronomy.Astrophysics’);
INSERT INTO test VALUES (‘Top.Science.Astronomy.Cosmology’);
INSERT INTO test VALUES (‘Top.Hobbies’);
INSERT INTO test VALUES (‘Top.Hobbies.Amateurs_Astronomy’);
INSERT INTO test VALUES (‘Top.Collections’);
INSERT INTO test VALUES (‘Top.Collections.Pictures’);
INSERT INTO test VALUES (‘Top.Collections.Pictures.Astronomy’);
INSERT INTO test VALUES (‘Top.Collections.Pictures.Astronomy.Stars’);
INSERT INTO test VALUES (‘Top.Collections.Pictures.Astronomy.Galaxies’);
INSERT INTO test VALUES (‘Top.Collections.Pictures.Astronomy.Astronauts’);
CREATE INDEX path_gist_idx ON test USING GIST (path);
CREATE INDEX path_idx ON test USING BTREE (path);
Top / | Science Hobbies Collections / | Astronomy Amateurs_Astronomy Pictures / |Astrophysics Cosmology Astronomy
/ |
Galaxies Stars Astronauts
경로 일치 예시입니다.
SELECT path FROM test WHERE path ~ ‘.Astronomy.‘;
path
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
Top.Collections.Pictures.Astronomy
Top.Collections.Pictures.Astronomy.Stars
Top.Collections.Pictures.Astronomy.Galaxies
Top.Collections.Pictures.Astronomy.Astronauts
(7 rows)SELECT path FROM test WHERE path ~ ‘.!pictures@.Astronomy.‘;
path
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(3 rows)
전테 텍스트 검색의 예시입니다.
SELECT path FROM test WHERE path @ ‘Astro*% & !pictures@’;
path
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
Top.Hobbies.Amateurs_Astronomy
(4 rows)SELECT path FROM test WHERE path @ ‘Astro* & !pictures@’;
path
Top.Science.Astronomy
Top.Science.Astronomy.Astrophysics
Top.Science.Astronomy.Cosmology
(3 rows)
함수를 사용한 경로 구성의 예시입니다.
SELECT subpath(path,0,2)||'Space'||subpath(path,2) FROM test WHERE path <@ 'Top.Science.Astronomy';
?column?
------------------------------------------
Top.Science.Space.Astronomy
Top.Science.Space.Astronomy.Astrophysics
Top.Science.Space.Astronomy.Cosmology
(3 rows)
경로의 지정된 위치에 레이블을 삽입하는 SQL 함수를 만들어 이를 단순화 할 수 있습니다.
CREATE FUNCTION ins_label(ltree, int, text) RETURNS ltree
AS 'select subpath($1,0,$2) || $3 || subpath($1,$2);'
LANGUAGE SQL IMMUTABLE;
SELECT ins_label(path,2,'Space') FROM test WHERE path <@ 'Top.Science.Astronomy';
ins_label
------------------------------------------
Top.Science.Space.Astronomy
Top.Science.Space.Astronomy.Astrophysics
Top.Science.Space.Astronomy.Cosmology
(3 rows)
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/ltree.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/ltree/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/ltree/expected
seg
선분 또는 부동 소수점 간의 간격을 나타내는 데이터 유형을 지원합니다. 또한 다른 분야의 학문에서 요구하는 세부적인 수치를 구현하는데도 용이합니다. 예를 들어, 측정학에서는 6.5와 6.50이라는 수치는 다르게 인식되는데, 기본 float 형식으로 6.50을 저장하게되면 PostgreSQL에서는 6.5로 출력이 되어 데이터를 다루기 힘들어집니다. text 형태로 저장하는 방법도 있겠지만, 숫자 간격으로 데이터를 다루고 싶을 때는 text 데이터 유형은 이를 활용하지 못합니다.
예시
float8 데이터유형과 seg로 소수점 데이터를 표현하는 예시와, 연산자를 활용하는 예시입니다.
select ‘6.50’ :: float8 as “pH”;
pH
6.5
(1 row)select ‘6.50’::seg as “pH”;
pH
6.50 –똑같은 6.50 데이터를 표현하는 방식이 다름
(1 row)SELECT ‘-1e7’::seg AS seg;
seg
-1e+07 — 지수를 표현할 때도 세부적으로 저장됨을 보여줌
(1 row)SELECT ‘0(+-)1’::seg AS seg;
seg
-1 .. 1
(1 row)SELECT ‘0(+-)1.0’::seg AS seg;
seg
-1.0 .. 1.0 — delta(간격)에 따라 그에 맞는 값을 출력
(1 row)SELECT ’24 .. 33.20′::seg = ’24 .. 33.20′::seg AS bool;
bool
t
(1 row)SELECT ’24 .. 33.2′::seg = ’24 .. 33.20′::seg AS bool;
bool
f — 확실하게 소수점 아래 0자리도 다른 숫자로 구분함을 보여줌
(1 row)
참고사이트
- 공식 홈페이지 : https://www.postgresql.org/docs/current/seg.html
- 공식 git hub contrib 참고 : sample sql https://github.com/postgres/postgres/tree/master/contrib/seg/sql sample sql 결과 https://github.com/postgres/postgres/tree/master/contrib/seg/expected
지금까지 ‘PostgreSQL의 Contrib Extensions – 데이터 타입’에 관해 알아보았습니다
‘PostgreSQL의 pgcryto’을 바로 이어서 확인해보세요!
