33. Contrib Extensions - 데이터 타입

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

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)

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)

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)

[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 .

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를 신경써야함.

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라는 다른 이름의 이미지로 export lo_export ----------- 1 (1 row)

[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

--데이터는 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)

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)

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)