[PostgreSQL] 기본 데이터 타입 정리 - hstore

---

• Character types / why should we use char instead of varchar?
• Numeric types
• Boolean types
• Temporal types
• UUID for storing Universally Unique Identifiers
• Array for storing array strings, numbers, etc...
• JSON stores JSON data
• hstore stores key-value pair
• User defined data

---

hstore

키 ⇒ 값 쌍을 hstore에 저장.

쿼리 빈도가 낮은 속성이 많은 데이터나 행이 많은 경우 쓴다.

인덱싱이 빠르다는 장점이 있다.

 

키와 값은 오로지 스트링.

 

작업 이전에 hstore extension을 활성화시켜야한다.

CREATE EXTENSION hstore;

 

테이블 생성

CREATE TABLE books (
	id serial primary key,
	title VARCHAR (255),
	attr hstore
);

데이터 삽입

INSERT INTO books (title, attr)
VALUES
	(
		'PostgreSQL Tutorial',
		'"paperback" => "243",
	   "publisher" => "postgresqltutorial.com",
	   "language"  => "English",
	   "ISBN-13"   => "978-1449370000",
		 "weight"    => "11.2 ounces"'
	);
    
    
    
INSERT INTO books (title, attr)
VALUES
	(
		'PostgreSQL Cheat Sheet',
		'
"paperback" => "5",
"publisher" => "postgresqltutorial.com",
"language"  => "English",
"ISBN-13"   => "978-1449370001",
"weight"    => "1 ounces"'
	);

 

select 사용시

SELECT
	attr
FROM
	books;

 

-> 를 사용해서 특정 키의 값만 따로 뽑아낼 수 있다.

SELECT
	attr -> 'ISBN-13' AS isbn
FROM
	books;

where절에서 ->을 사용하여 특정 데이터를 뽑아낼 수 있다.

SELECT
	title, attr -> 'weight' AS weight
FROM
	books
WHERE
	attr -> 'ISBN-13' = '978-1449370000';

key-value 한쌍을 추가할 수 있다.

UPDATE books
SET attr = attr || '"freeshipping"=>"yes"' :: hstore;

 

value값을 수정할 수 있다.

UPDATE books
SET attr = attr || '"freeshipping"=>"no"' :: hstore;

 

key 를 수정할 수 있다.

select
	attr ('freeshipping=>no' :: hstore, 'freeshipping', 'gohome');

//결과
    attr
-------------
"gohome"=>"no"

 

key-value 한쌍을 지울 수 있다.

UPDATE books 
SET attr = delete(attr, 'freeshipping');

//delete(컬럼, key)

 

where절에서 ? 를 사용하면 값을 따로 빼올 수 있다.

SELECT
  title,
  attr->'publisher' as publisher,
  attr
FROM
	books
WHERE
	attr ? 'publisher';

 

where절에서 @>를 사용하면 key-value 한쌍에 대해서 체크 가능

SELECT
	title
FROM
	books
WHERE
	attr @> '"weight"=>"11.2 ounces"' :: hstore;

?& 를 사용하여 여러 key가 들어있는 행을 쿼리할 수 있다.

SELECT
	title
FROM
	books
WHERE
	attr ?& ARRAY [ 'language', 'weight' ];

akeys() 를 사용하면 모든 key를 각 row로 뽑아올 수 있다.

SELECT
	akeys (attr)
FROM
	books;

skeys() 사용하면 모든 key를 한 column으로 뽑아올 수 있다.

SELECT
	skeys (attr)
FROM
	books;

avals() 사용하면 모든 value를 각 row로 뽑아올 수 있다.

SELECT
	avals (attr)
FROM
	books;

svals() 사용하면 모든 value를 한 column으로 뽑아올 수 있다.

SELECT
	svals (attr)
FROM
	books;

 

hstore data to JSON

 

hstore_to_json() function을 사용하면 hstore data를 JSON으로 변환할 수 있다.

SELECT
  title,
  hstore_to_json (attr) json
FROM
  books;

 

hstore data to sets

 

each() function을 사용하면 hstore data를 key column, value column 세트로 변환할 수 있다.

SELECT
	title,
	(EACH(attr) ).*
FROM
	books;