How to Insert JSON Data in PostgreSQL: Syntax and Examples

To insert JSON data in PostgreSQL, use a column with the json or jsonb data type and insert the JSON string using an INSERT INTO statement. Wrap the JSON data in single quotes and cast it if needed, for example: INSERT INTO table_name (json_column) VALUES ('{"key": "value"}'::jsonb);.

Syntax

PostgreSQL supports two JSON data types: json and jsonb. You insert JSON data by providing a JSON string in single quotes. You can cast the string explicitly to json or jsonb to ensure correct storage.

• json_column: The column with JSON or JSONB type.
• VALUES: The JSON data as a string.
• Cast (::json or ::jsonb): Optional but recommended for clarity.

sql

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

Example

This example creates a table with a jsonb column and inserts a JSON object into it. Then it selects the data to show the stored JSON.

sql

CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);

INSERT INTO products (data) VALUES ('{"name": "Apple", "price": 1.25, "in_stock": true}'::jsonb);

SELECT * FROM products;

Output

 id |                      data                      
----+----------------------------------------------
  1 | {"name": "Apple", "price": 1.25, "in_stock": true}
(1 row)

Common Pitfalls

Common mistakes when inserting JSON data include:

• Not using single quotes around the JSON string, which causes syntax errors.
• Using double quotes incorrectly inside JSON; JSON keys and string values must be double-quoted, but the whole JSON must be single-quoted in SQL.
• Forgetting to cast the string to json or jsonb, which can lead to errors or unexpected behavior.

sql

/* Wrong: Missing single quotes around JSON */
INSERT INTO products (data) VALUES ({"name": "Apple"});

/* Right: JSON string in single quotes and cast */
INSERT INTO products (data) VALUES ('{"name": "Apple"}'::jsonb);

Quick Reference

Step	Description	Example
1	Create a table with a JSON or JSONB column	CREATE TABLE t (data JSONB);
2	Insert JSON data as a string with single quotes	INSERT INTO t (data) VALUES ('{"key": "value"}');
3	Cast the string to JSON or JSONB for clarity	INSERT INTO t (data) VALUES ('{"key": "value"}'::jsonb);
4	Query the table to see stored JSON	SELECT * FROM t;

Key Takeaways

Use JSON or JSONB column types to store JSON data in PostgreSQL.

Always wrap JSON data in single quotes and use double quotes inside JSON keys and strings.

Cast JSON strings explicitly to json or jsonb to avoid errors.

Check your JSON syntax carefully to prevent insertion failures.

Use JSONB for better performance with indexing and querying JSON data.