Inserting JSON data in PostgreSQL

Introduction

We use JSON data to store flexible information in a single column. It helps when data has different shapes or details that change often.

You want to save user preferences that vary a lot between users.

You need to store product details that can have different attributes.

You want to keep logs or events with different fields in one table.

You are building a quick prototype and want to avoid many columns.

You want to store data from an API that returns JSON format.

Syntax

PostgreSQL

INSERT INTO table_name (json_column) VALUES ('{"key": "value"}');

The JSON data must be a valid JSON string inside single quotes.

Use double quotes inside JSON keys and values as per JSON rules.

Examples

Insert a JSON object with name and age into the info column.

PostgreSQL

INSERT INTO users (info) VALUES ('{"name": "Alice", "age": 30}');

Insert a JSON object with an array inside it.

PostgreSQL

INSERT INTO products (details) VALUES ('{"id": 101, "colors": ["red", "blue"]}');

Insert a JSON object with boolean value.

PostgreSQL

INSERT INTO logs (event) VALUES ('{"type": "login", "success": true}');

Sample Program

This creates a table with a JSON column, inserts one employee record as JSON, then selects all rows.

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, data JSON);
INSERT INTO employees (data) VALUES ('{"name": "John", "department": "Sales", "full_time": true}');
SELECT * FROM employees;

Important Notes

PostgreSQL has two JSON types: JSON and JSONB. JSONB stores data in a binary format and is faster for queries.

Always validate your JSON data before inserting to avoid errors.

You can insert JSON arrays or nested objects as long as they are valid JSON.

Summary

Use INSERT with a JSON string to add JSON data into a table.

JSON data must be valid and enclosed in single quotes in SQL.

JSON columns let you store flexible, complex data in one place.