[ 살펴보기 ] PostgreSQL - Data Types

PostgreSQL을 통해 table을 생성할 때 목적에 따라 각 column에 저장할 데이터의 data type을 설정한다. PostgreSQL은 다양한 data type을 지원하며 해당 포스트는 그 중 대표적인 data type과 특징을 살펴본다.

Numeric Types

PostgreSQL에서 사용할 수 있는 numeric type은 다음과 같다.

• smallint : -32768 to +32767 범위의 값을 저장할 수 있는 2 bytes 크기의 integer type

• interger : -2147483648 to +2147483647 범위의 값을 저장할 수 있는 4 bytes 크기의 integer type

• bigint : -9223372036854775808 to +9223372036854775807 범위의 값을 저장할 수 있는 8 bytes 크기의 integer type

SELECT 32767::smallint AS result; // 정상 범위
SELECT 32768::smallint AS result; // 정상 범위 초과

SELECT 2147483647::integer AS result; // 정상 범위
SELECT 2147483648::integer AS result; // 정상 범위 초과

SELECT 9223372036854775807::bigint AS result; // 정상 범위
SELECT 9223372036854775808::bigint AS result; // 정상 범위 초과

• real : 소수점 전, 후 여섯 자리 숫자까지만 정확성을 보장하는 4 bytes data type

• double precision : 소수점 전, 후 열 다섯 자리 숫자까지만 정확성을 보장하는 8 bytes data type

• numeric ( precision, scale ): 사용자가 직접 decimal digit precision과 scale을 설정할 수 있는 data type.

SELECT 1.123456789::real AS result;
/*
- 결과 : 1.1234568 
- 소수점 전,후 여섯 자리 숫자까지 정확성 보장
*/

SELECT 1.123456789123456789::double precision AS result;
/*
- 결과 : 1.1234567891234568
- 소수점 전,후 열 다섯 자리 숫자까지 정확성 보장
*/


SELECT 1.12345::numeric(7,3) AS result;
/*
- 결과 : 1.123
- precision : 소수점 전, 후 숫자 모두 포함하여 사용할 수 있는
  수의 갯수를 제한.

- scale : 소수점 이후 표현할 숫자 제한, 주어진 값 보다 더 큰
  숫자가 설정되면 뒤에 0을 추가하고 더 작은 숫자가 설정되면
  scale에 설정된 숫자 다음 숫자에 따라 반올림 여부가 결정된다.
*/

SELECT 11.12::numeric(5,3) AS result;
/*
- 결과 : 11.120
- 11.12라는 값에 소수점 이후 자리 값은 2인데 scale은 3이 설정되었다
그러므로 sacle에 설정된 자리 수인 3을 맞추기위해 결과 값 뒤에 
0을 추가하여 반환한다.
*/


SELECT 11.12::numeric(5,4) AS result;
/*
- 결과 : 오류 발생
- 위의 예제에선 scale이 4이므로 scale 자릿수에 맞추기 위해
11.1200 형식으로 0을 두 개 추가하려고 할 것이다.
하지만 precision은 5이므로 위의 값은 소수점 전,후 모든 숫자의
갯수가 6개다. 즉, precision에 선언한 값을 벗어나므로 오류가 발생한다.
*/

SELECT 11.123::numeric(7,2) AS result;
/*
- 결과 : 11.12
- scale이 2이므로 소수점 이후 두 번째 자릿수 까지 표시한다.
- scale이 2이므로 두 번째 숫자 다음의 숫자(3)를 점검하고 5이상이면
마지막 숫자를 반올림해서 return한다.
- 위의 예제에서는 두 번째 숫자 다음의 숫자가 3이므로 반올림
되지 않고 return된다.
*/


SELECT 11.1263::numeric(7,2) AS result;
/*
- 결과 : 11.13
- scale이 2이므로 소수점 이후 두 번째 자릿수 까지 표시한다.
- scale이 2이므로 두 번째 숫자 다음의 숫자(6)를 점검하고 5이상이면
반올림하여 return한다. 
- 위의 예제에서는 두 번째 다음의 숫자가 6이므로 반올림되어 1.3이 
return 된다.
*/

Character Types

PostgreSQL에서 사용할 수 있는 character type은 다음과 같다.

• character(n), char(n) : 고정 length를 가진 character type. (n)에 지정한 숫자보다 짧은 값을 추가하면 n에 지정한 length에 맞추기 위해 추가 공백을 추가한다. 만약 (n)값이 명시적으로 설정되어 있지 않으면 default로 char(1)이 적용된다.

• varchar(n), character varying(n) : (n)에 지정한 숫자가 추가할 수 있는 최대 length가 되는 character type. 만약 (n)값이 명시적으로 설정되어 있지 않으면 length 제한 없이 값을 추가할 수 있다.

• text : length limit이 없는 character type.

char(n), varchar(n) type에서 n에 전달하는 값을 통해 제한하는 것은 추가하는 값의 length이며 실제 저장되는 byte 값이 아니다.

만약 다음과 같은 table이 있다고 가정해보자.

CREATE TABLE test_a (
    id INT PRIMARY KEY,
    title char(10)
)

Char type의 length를 10으로 설정했으므로 length가 10이상인 값은 추가할 수 없다.

INSERT INTO test_a (id, title) VALUES (3, '12345678912');
/* 오류발생 */

이제 다음 두 데이터를 추가해보자.

INSERT INTO test_a (id, title) VALUES (1, 'Hello');
INSERT INTO test_a (id, title) VALUES (2, '안녕하세요');

데이터를 추가한 다면 title 데이터를 조회할 때 length를 통해 추가된 문자의 length를 조회하고 octet_length 함수를 통해 추가된 문자를 저장하는데 사용된 실제 byte 크기를 조회한다.

SELECT title, length(title), octet_length(title) FROM test_a;

위와 같이 조회를 실행하면 결과는 다음과 같다.

title         |  length  |  octet_length
----------------------+--------+--------------
Hello             5           10
안녕하세요          5           20

위의 결과에서 볼 수 있듯이 title column의 type은 char(10)으로 설정되어 있기에 character의 length가 5인 값이 추가되면 공백을 추가해서 length 10을 맞춘다. ‘안녕하세요’ 라는 값의 length가 5임에도 불구하고 octet_length가 20인 이유는 한글을 저장할 때 한 글자당 1 byte 이상 차지하기 때문이다.

이제 title column이 varchar(10) type일 때 기준으로 테스트를 진행 해보자. ALTER command를 통해 title colulm의 타입을 varchar로 변경해준다.

ALTER TABLE test_a ALTER COLUMN title TYPE varchar(10);

Varchar type 역시 지정한 length 이상의 값을 추가할 수 없다.

INSERT INTO test_a (id, title) VALUES (3, '12345678912');
/* 오류발생 */

Title column을 varchar type으로 변경한 다음 데이터를 다시 조회해 보면 결과는 다음과 같다.

title         |  length  |  octet_length
----------------------+--------+--------------
Hello             5            5
안녕하세요          5           15

위의 결과에서 볼 수 있듯이 varchar type은 추가할 수 있는 값의 최대 length를 제한할 뿐 varchar(10)에 설정된 10이라는 length를 맞추기 위해 뒤에 추가 공백을 추가하지는 않는다.

만약 varchar type의 column에 지정한 length에 초과되는 length character가 공백일 때는 오류를 발생 시키지 않고 제한된 length까지만 공백을 추가하고 나머지는 자른다.

INSERT INTO test_a (id, title) VALUES (1, 'Hello          ');

위의 데이터를 추가하고 title length를 조회해보면 결과는 다음과 같다. Length가 5인 Hello string 뒤에 추가 한 10개의 공백 중 5개의 공백만 추가된다. ( 예제에선 length 제한이 10이므로 )

title          |  length  |  octet_length
----------------------+--------+--------------
Hello               5             5
안녕하세요            5            15
Hello/*&공백 5개*/   10           10

반면 text type은 varchar type에 length limit이 없는 것과 같이 동작한다. ALTER command를 통해 title의 type을 text로 변경해보자.

ALTER TABLE test_a ALTER COLUMN title TYPE text;

Text type의 column에는 원하는 length의 값을 자유롭게 추가할 수 있다.

INSERT INTO test_a (id, title) VALUES (3, 'example hello world');

Title column을 text type으로 변경한 다음 데이터를 다시 조회해 보면 결과는 다음과 같다.

title              |  length  |  octet_length
----------------------+--------+--------------
Hello                  5            5
안녕하세요               5           15
example hello world    19           19

Text type은 length에 제한이 없으므로 추가하는 값 뒤에 불필요한 공백을 함께 추가해도 공백 역시 length에 모두 포함한다.

INSERT INTO test_a (id, title) VALUES (1, 'Hello          ');

위의 예제는 Hello라는 값에 공백 10을 함께 추가하는 예제다. 위의 데이터를 조회하면 결과는 다음과 같다.

title           |  length  |  octet_length
----------------------+--------+--------------
Hello                5             5
안녕하세요             5            15
Hello/*&공백 10개*/   15            15

위에서 소개한 세 가지 character type의 performance에 대해서 postgresql documentation은 다음과 같이 말하고 있다.

“ There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead. “

Boolean Types

Boolean data type은 TRUE, FALSE, NULL 세 가지 타입의 값을 가질 수 있다. Boolean type 데이터를 업데이트할 때 TRUE나 FALSE keyword외에 TRUE나 FALSE와 동등하게 취급되는 문자열을 통해 TRUE, FALSE 값을 업데이트 할 수도 있다.

다음은 모두 TRUE로 취급되는 문자열이다. yes, on, 1 즉, 다음 statement는 모두 같은 역할을 한다.

UPDATE library_b
SET available = true
WHERE id = 1;

UPDATE library_b
SET available = 'yes'
WHERE id = 1;

UPDATE library_b
SET available = 'on'
WHERE id = 1;

UPDATE library_b
SET available = '1'
WHERE id = 1;

반대로 FASE로 취급되는 문자열은 다음과 같다 no, off, 0 다음 statement는 모두 같은 역할을 한다.

UPDATE library_b
SET available = false
WHERE id = 1;

UPDATE library_b
SET available = 'no'
WHERE id = 1;

UPDATE library_b
SET available = 'off'
WHERE id = 1;

UPDATE library_b
SET available = '0'
WHERE id = 1;

Date, Time tyeps

PostgreSQL에서 사용할 수 있는 date, time types은 다음과 같다.

• date : 시간을 제외한 연도, 월, 날짜 정보를 나타내는 4 bytes 크기의 data type

• time : 연도, 월, 날짜 정보를 제외한 시간 정보를 나타내는 8 byes 크기의 data type

• timestamp : date와 time 정보 모두 나타내는 8 bytes 크기의 timestamp data type.

시간정보를 제외한 연도, 월, 날짜 정보를 저장하고 표현할 때 date type을 사용할 수 있다. 다음과 같은 테이블이 있다고 가정해보자.

CREATE TABLE test_a (
    id INT PRIMARY KEY, 
    title TEXT, 
    created_at DATE
);

INSERT INTO test_a (id, title, created_at) 
VALUES (1, 'title1', '2024-10-01 12:00:00');

그리고 위의 테이블에 위의 예제와 같이 시간 정보를 모두 포함해서 데이터를 추가해도 database에는 연도, 월, 날짜 정보만 저장된다.

 id | title  | created_at
----+--------+------------
  1 | title1 | 2024-10-01

Date type이 허용하는 input 형식 중 위의 예제처럼 ISO 8601 format이 권장되는 format이지만 다른 형식의 input 역시 허용된다.

INSERT INTO test_a (id, title, created_at) 
VALUES (2, 'title2', 'October 11, 2024');

INSERT INTO test_a (id, title, created_at) 
VALUES (3, 'title3', 'Oct-11-2024');

반대로 연도, 월, 날짜 정보를 제외한 시간 정보만 저장하고 표시하기 위해선 time type을 사용할 수 있다.

CREATE TABLE test_a (
    id INT PRIMARY KEY, 
    title TEXT, 
    created_at TIME
);

INSERT INTO test_a (id, title, created_at) 
VALUES (1, 'title1', '2024-10-01 12:00:00');

위의 예제처럼 데이터를 추가한 뒤 추가된 데이터를 조회 결과는 다음과 같다.

 id | title  | created_at
----+--------+------------
  1 | title1 | 12:00:00

Time type은 다음과 같이 input 값에 PM 옵션을 사용해서 시간을 추가할 수도 있다. 아래 추가한 시간은 둘 다 같은 시간을 나타낸다.

INSERT INTO test_a (id, title, created_at) 
VALUES (1, 'title1', '01:00:00 PM');

INSERT INTO test_a (id, title, created_at) 
VALUES (2, 'title2', '13:00:00');

만약 date와 time 정보를 함께 저장하고 표시하고자 할 때는 timestamp data type을 사용한다.

CREATE TABLE test_a (
id INT PRIMARY KEY, 
title TEXT, 
created_at TIMESTAMP
);

INSERT INTO test_a (id, title, created_at) 
VALUES (1, 'title1', '2024-10-01 12:00:00');

위의 예제처럼 데이터를 추가한 뒤 추가된 데이터를 조회 결과는 다음과 같다.

 id | title  |     created_at
----+--------+---------------------
  1 | title1 | 2024-10-01 12:00:00

Date와 timestamp type은 시간 정보를 저장할 때 다음과 같이 TIMESTAMP 뒤에 (숫자) 형식으로 허용할 범위를 정할 수 있으며 최대 저장 범위는 micro seconds다. 아래 table의 created_at column은 시간 데이터를 저장할 때 million seconds 범위까지 저장한다.

CREATE TABLE test_a (
id INT PRIMARY KEY, 
title TEXT, 
created_at TIMESTAMP(3)
);

INSERT INTO test_a (id, title, created_at) 
VALUES (1, 'title1', '2024-10-01 12:10:05.123456');

위의 테이블에 데이터를 새로 추가해보면 아래의 결과와 같이 million seconds 범위까지만 저장되는 것을 확인할 수 있다.

 id | title  |       created_at
----+--------+-------------------------
  1 | title1 | 2024-10-01 12:10:05.123

만약 다음과 같이 허용 seconds 범위를 명시적으로 설정하지 않으면 최대 허용 범위인 micro seconds 범위 내에서 자유롭게 저장할 수 있다.

CREATE TABLE test_a (
id INT PRIMARY KEY, 
title TEXT, 
created_at TIMESTAMP
);

INSERT INTO test_a (id, title, created_at) 
VALUES (1, 'title1', '2024-10-01 12:10:05.123456');

위의 테이블에 micro seconds 범위를 가진 데이터를 새로 추가해보면 아래의 결과와 같이 micro seconds 범위까지 모두 저장되는 것을 확인할 수 있다.

 id | title  |         created_at
----+--------+----------------------------
  1 | title1 | 2024-10-01 12:10:05.123456

만약 micro seconds 이상의 범위를 가진 데이터를 추가하려고 하면 micro seconds 단위 까지만 저장되고 나머지는 무시된다. 여기서 micro second의 다음 숫자가 5이상이면 저장되는 micro seconds의 숫자가 반올림되어 저장된다.

INSERT INTO test_a (id, title, created_at) 
VALUES (1, 'title1', '2024-10-01 12:10:05.12345678');

/* 6의 다음 수가 7이므로 6이 반올림되어 7으로 저장된다 */
 id | title  |         created_at
----+--------+----------------------------
  1 | title1 | 2024-10-01 12:10:05.123457


/* 6의 다음 수가 3이므로 6이 반올림되지 않고 그대로 저장된다 */
INSERT INTO test_a (id, title, created_at) 
VALUES (1, 'title1', '2024-10-01 12:10:05.12345638');

/* 조회 데이터 결과 */
 id | title  |         created_at
----+--------+----------------------------
  1 | title1 | 2024-10-01 12:10:05.123456

Timestamp 데이터를 위의 예제처럼 일일이 넣지 않고 다른 데이터를 추가할 때 자동으로 현재 시간을 설정하고 싶다면 다음과 같이 DEFAULT 값을 설정할 수 있다.

CREATE TABLE test_a (
id INT PRIMARY KEY, 
title TEXT, 
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
);

위의 예제처럼 DEFAULT 값을 설정 하면 굳이 created_at 데이터를 직접 넣지 않아도 데이터를 추가할 때 현재 시간이 자동으로 추가된다.

INSERT INTO test_a (id, title) 
VALUES (1, 'title1');

Time와 timestamp data type은 WITH TIME ZONE을 통해 데이터를 표시할 때 timezone off 정보를 함께 표시할 지 여부를 설정할 수 있다. 여기서 표시되는 timezone 정보는 PostgreSQL configuration ( postgresql.conf )에 설정된 timezone이다. 다음과 같이 table을 생성했다고 가정해보자.

CREATE TABLE test_a (
id INT PRIMARY KEY, 
title TEXT, 
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
created_at_tz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

위의 테이블을 생성하고 데이터를 추가하면 created_at column은 timezone offset 정보 없이 데이터를 표시하고 created_at_tz column은 timezone offset 정보를 포함해서 결과를 표시한다.

INSERT INTO test_a (id, title) 
VALUES (1, 'title1');

아래는 위의 테이블에 데이터를 추가하고 조회한 결과다. 현재 필자의 configuration 파일 timezone은 Asia/Seoul로 설정되어 있으므로 created_at_tz column의 조회 결과 끝에 +09 offset 정보가 포함되어 나오는 것을 확인할 수 있다. ( Korean Starand Time은 Universal Coordinated Time 보다 9시간 앞선다 )

 id | title  |         created_at         |         created_at_tz
----+--------+----------------------------+-------------------------------
  1 | title1 | 2024-10-16 15:13:51.941976 | 2024-10-16 15:13:51.941976+09

WITH TIME ZONE이 적용된 timestamp type는 내부적으로 UTC timezome으로 저장되고 PostgreSQL congiruation에 설정된 timezone 값에 따라 local time으로 변경되어 표시된다.

반면 WITH TIME ZONE이 적용되지 않은 timestamp type은 PostgreSQL configuration에 설정된 timezone을 기준으로 timestamp 데이터가 저장된다. ( 만약 데이터를 추가하는 session에서 SET timezone을 통해 configuration과 다른 timezone을 설정한 상태라면 SET timezone을 통해 설정된 timezone을 기준으로 timestamp 데이터가 저장된다 )

Time type에도 with time zone을 사용할 수 있지만 PostgreSQL의 documentation에 따르면 time zone을 사용할 때는 data/time 정보를 모두 포함하는 데이터를 대상으로 time zone을 사용하는 것이 권장된다. ( Reference - Time Zones ) 아래는 관련 내용을 documentation에서 발췌한 내용이다.

Although the date type cannot have an associated time zone, the time type can. Time zones in the real world have little meaning unless associated with a date as well as a time …

…To address these difficulties, we recommend using date/time types that contain both date and time when using time zones. We do not recommend using the type time with time zone

Array types

Array data type을 통해 text, ingeter, date등의 데이터를 array 형식으로 관리할 수 있다. 아래는 hobbies column을 text array type으로 설정하는 예시다.

CREATE TABLE test_a(id INT PRIMARY KEY, hobbies text[]);

CREATE TABLE test_a(id INT PRIMARY KEY, hobbies text ARRAY);

특정 data type을 array형식으로 설정할 때는 위의 예제와 같이 ( data type )[] 또는 ( data type ) ARRAY 형식으로 선언해준다.

그리고 array type에 데이터를 추가하는 방식도 다음 두 가지가 있다. 아래의 두 예시는 동일한 역할을 한다.

INSERT INTO test_a (id, hobbies) 
VALUES (1, '{test1-1, test1-2 test1-3}');

INSERT INTO test_a (id, hobbies) 
VALUES (1, ARRAY['test1-1', 'test1-2','test1-3']);

Array data를 업데이트할 때 기존 array data 전체를 새로운 array data로 교체하거나 특정 array item만 업데이트 할 수 있다. 다음 예시는 id가 1인 데이터 중 hobbies array의 3번째 item을 새로운 값으로 업데이트한다. ( PostgreSQL에선 array index가 0이 아닌 1부터 시작한다 )

UPDATE test_a SET hobbies[3] = 'new-test1-3' WHERE id = 1;
/* id가 1인 hobbies array 데이터의 세 번째 item 업데이트 */

반면 기존의 array data를 새로운 array data로 업데이트 하는 예시는 다음과 같다. 다음 두 개의 예제 모두 같은 역할을 한다.

UPDATE test_a SET hobbies = '{new-test1-1, new-test1-2}' 
WHERE id = 1;

UPDATE test_a SET hobbies = ARRAY['new-test1-1', 'new-test1-2'] 
WHERE id = 1;

Array 데이터를 조회할 때 역시 다음과 같이 array의 특정 item만 조회할 수 있다.

SELECT hobbies[2] FROM test_a WHERE id = 1;
/* id가 1인 hobbies array data의 두 번째 item 조회 */

만약 array data의 특정 범위만 조회하고 싶다면 [1:3]와 같은 형식으로 특정 범위만 slice하여 조회할 수 있다.

SELECT hobbies[1:3] FROM test_a WHERE id = 1;
/* id가 1인 hobbies array data의 첫 번째 데이터 부터 
세 번째 데이터까지 조회 */

Enum type

특정한 값으로 구성되는 enum type은 CREATE TYPE keyword를 통해 생성할 수 있다.

CREATE TYPE snack_category AS ENUM ('candy', 'jelly', 'bisket')

위의 예제처럼 생성한 enum type은 table을 생성할 때 data type형식으로 사용할 수 있다.

CREATE TABLE snacks (
  id integer primary key, 
  name varchar(20),
  category snack_category /* snack_category enum type */
)

위의 예제에서 snacks table의 category column의 data type은 snack_category enum type이기에 snack_category enum type을 생성할 때 선언했던 값 이외의 값은 추가할 수 없다. 예를 들어 다음 sql statement를 실행하면 오류가 발생한다.

INSERT INTO snacks (id, name, category) VALUES (
 1,
 'snack 1',
 'ice cream'
)

JSON data type

PostgreSQL을 통해 JSON data type 역시 저장할 수 있다. PostgreSQL에서 JSON data를 저장하기 위해 두 가지 type을 사용할 수 있다. 하나는 json type이고 다른 하나는 jsonb type이다. 이 둘의 type이 받을 수 있는 input data는 거의 동일하지만 데이터를 저장하는 방식에 차이가 있다.

json type은 input으로 전달받은 값을 그대로 저장하고 jsonb type은 input으로 전달받은 값을 내부적으로 binary 형태로 저장하며 indexing 역시 지원하기에 jsonb type이 데이터를 저장할 때는 json type보다 조금 느릴 수 있지만 저장된 data를 대상으로 다양한 작업을 처리할 때는 jsonb type이 json type보다 나은 성능을 기대할 수 있다.

다음은 information column을 jsonb data type으로 설정하여 table을 설정하는 예시다.

CREATE TABLE test_a(
    id INT PRIMARY KEY, 
    information jsonb
);

jsonb type와 json type은 json data format에 위배되지 않는다면 자유로운 형태의 테이터를 저장할 수 있다.

INSERT INTO test_a (id, information) 
VALUES (1, '{"title":"title 1", "category":"science"}');

INSERT INTO test_a (id, information) 
VALUES (2, '{"city":"london", "country":"uk"}');

저장된 json 데이터 중 특정 key:value를 포함하는 data를 조회해야 한다면 다음과 같이 조회할 수 있다. 다음 예제는 city:london이라는 key:value를 포함하고 있는 데이터를 조회한다.

SELECT * FROM test_a 
WHERE information @> '{"city":"london"}';

조회된 결과는 다음과 같다.

 id |             information
----+-------------------------------------
  2 | {"city": "london", "country": "uk"}

위의 같은 방법으로 데이터를 조회할 때 주의할 점이 있다. 만약 다음과 같이 json 데이터에 details과 같이 nested object가 포함되어 있다고 가정해보자.

INSERT INTO test_a (id, information) 
VALUES (3, '{"title":"title 2", "category":"computer", 
"details":{"author":"john"} }');

위의 데이터를 조회할 때 다음과 같이 조회하면 아무런 조회 결과가 나오지 않는다.

SELECT * FROM test_a 
WHERE information @> '{"author":"john"}';

 id | information
----+-------------
(0 rows)

Nested object에 포함된 데이터를 조회하려면 다음과 같이 nested object 자체를 search 조건에 포함시켜 줘야 한다.

SELECT * FROM test_a 
WHERE information @> '{"details":{"author":"john"}}';

 id |                                 information
----+-----------------------------------------------------------------------------
  3 | {"title": "title 2", "details": {"author": "john"}, "category": "computer"}