Inserting JSON data in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the correct SQL command to insert JSON data into a PostgreSQL table?

Use the INSERT INTO statement with a column of type json or jsonb. The JSON data should be provided as a string literal enclosed in single quotes.

beginner

What data types in PostgreSQL are used to store JSON data?

PostgreSQL provides two data types for JSON: json which stores JSON as text, and jsonb which stores JSON in a binary format for faster processing.

beginner

How do you insert a JSON object into a table column of type jsonb?

You insert JSON data as a string literal. For example: <br>INSERT INTO table_name (json_column) VALUES ('{"key": "value"}');

beginner

Can you insert JSON arrays into PostgreSQL JSON columns?

Yes, you can insert JSON arrays as string literals just like JSON objects. For example: <br>INSERT INTO table_name (json_column) VALUES ('[1, 2, 3]');

beginner

What happens if you try to insert invalid JSON into a JSON or JSONB column?

PostgreSQL will return an error because the data must be valid JSON format to be stored in json or jsonb columns.

Which PostgreSQL data type is optimized for faster JSON processing?

A jsonb

B json

C text

D varchar

The jsonb type stores JSON in a binary format, making it faster for processing than json which stores JSON as plain text.

How should JSON data be formatted when inserting into a PostgreSQL JSON column?

A As a number

B As a string literal enclosed in single quotes

C As a plain JSON object without quotes

D As a SQL function

JSON data must be inserted as a string literal enclosed in single quotes, for example: '{"key": "value"}'.

What will happen if you insert invalid JSON text into a JSON column?

A PostgreSQL will convert it automatically

B PostgreSQL will accept it as text

C PostgreSQL will throw an error

D PostgreSQL will ignore the invalid parts

PostgreSQL requires valid JSON format for JSON columns and will throw an error if the data is invalid.

Which SQL command is used to add JSON data to a table?

A INSERT INTO

B SELECT

C UPDATE

D DELETE

The INSERT INTO command is used to add new rows, including JSON data, into a table.

Can you insert a JSON array into a PostgreSQL JSON column?

A Only with special functions

B No, only JSON objects are allowed

C Only if converted to text first

D Yes, as a string literal

JSON arrays can be inserted as string literals just like JSON objects.

Explain how to insert JSON data into a PostgreSQL table column.

Think about the SQL command and how JSON is represented as text.

What are the differences between json and jsonb data types in PostgreSQL?

Consider storage format and performance.