postgres

PostgreSQL

Info

• PostgreSQL can store JSON in it.
• Major version released yearly September/October, supported for 5 years.

Syntax

SELECT content -> 'type' AS content_type FROM rich_content;
 
SELECT DINSTINCT CAST (content -> 'type' AS TEXT) -- cast JSON objects to text
AS content_type FROM rich_content;
 
SELECT DINSTINCT content ->> 'type' -- ->> casts as text
AS content_type FROM rich_content;
 
SELECT content -> 'dimensions' AS content_type FROM rich_content
WHERE content ->> 'diemensions' IS NOT NULL
 
SELECT
    content -> 'dimensions' ->> 'height' AS height,
    content -> 'dimensions' ->> 'width' AS width
FROM rich_content WHERE content -> 'dimensions' IS NOT NULL;
 
EXPLAIN ... -- stats

CREATE INDEX ON comments (board_id);
CREATE UNIQUE INDEX username_idx ON users(username);