PostgreSQL 사용하기 (with JSON)

OpenStack Fuel에서는 Database로 PostgreSQL을 사용한다. PostgreSQL는 이야기만 들어봤지 써보지는 않고 MySQL이랑 MongoDB만 사용해봤는데 이번에 디버깅하느라 접하게 되었다. db-engines.com가 매긴 RDBMS 순위로 4번째에 위치하면서(Oracle, MySQL, MSSQL, PostgreSQL순) 그 위력을 자랑하고 있으며, 특징으로는 완전무료라는 점과 '전설적인 신뢰성과 안정성'이 강조된다고 한다. 특히 인상적인것은 PostgreSQL 9.2 버전 이후부터는 MongoDB처럼 데이터베이스 자체에서 json처리를 지원한다는 점이었다. 

실제 Fuel에서 PostgreSQL를 사용하는 큰 이유도 json을 활용하기 위함이었고, SQL구문을 사용하여 이를 쉽게 처리할 수 있다는것을 보고 나중에라도 써먹어야겠다는 생각에 글을 남겨본다. 사실 PostgreSQL에서 JSON을 지원한다는 사실을 디버깅 후에야 알게되어서, 엄청난 분량의 JSON 문자열을 처리해야하는 상황에서 어찌해야할지 엄두가 안나 아래와 같이 해결하기도 했다. 하지만 더이상은 그럴 일은 없을듯하다.

update attributes set editable = replace(editable, '"value": null', '"value": ["10.20.0.2"]') ;

* PostgreSQL 조회하기

postgres 접속하기 (특정 데이터베이스를 사용하려면 뒤에 database명을 붙여주면 된다)

sudo -u postgres psql

mysql: SHOW DATABASES

postgresql: \l

postgresql: SELECT datname FROM pg_database;

mysql: USE database

postgresql: \c database

mysql: SHOW TABLES

postgresql: \d

postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

mysql: DESCRIBE TABLE (SHOW COLUMNS FROM table)

postgresql: \d+ table

postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

또는 간단하게 select * from table_name where false;

그리고 \?는 도움말, \q는 종료, \i는 쉘 명령어 실행.

참고: Postgresql: show tables, show databases, show columns, describe table

* PostgreSQL에서 JSON 사용하기

CREATE TABLE orders (

 ID serial NOT NULL PRIMARY KEY, info json NOT NULL

);

INSERT INTO orders (info) VALUES (

 '{ "customer": "John Doe", "items": { "product": [ "Beer", "Soju" ], "qty": 6}}'

 );

SELECT info -> 'customer' AS customer FROM orders;

> "John Doe" (JSON 형태로 반환)

SELECT info ->> 'customer' AS customer FROM orders;

> John Doe (Text 형태로 반환)

SELECT info -> 'items' ->> 'product' FROM orders WHERE CAST ( info -> 'items' ->> 'qty' AS INTEGER ) = 6;

> ["Beer", "Soju"]

SELECT info -> 'items' #>> '{product, 1}' FROM orders WHERE info ->> 'customer' = 'John Doe';

> Soju

참고: PostgreSQL JSON

참고자료 : JSON Processing in the Database using PostgreSQL 9.4 :: Data Wranglers DC :: January 7, 2015 from Ryan B Harvey, CSDP, CSM