Postgres Insert JSON Example

Introduction

This tutorial will explain how to use Postgres to insert JSON data using SQL statements in psql. Psql is a front-end to PostgreSQL that allows for inserting record data into a PostgreSQL table in the form of a JSON object using the psql command-line interface. This allows for issuing interactive queries to PostgreSQL and then immediately viewing the results. Inputs can also be made from command-line arguments or from an existing file.

Prerequisites

• Postgres and the command-line interface psql must be installed and working. Ideally, Postgres version 9 or greater should be used to with Postgres to insert JSON data using SQL statements in psql. Execute the postgres -V command to determine the currently installed version of Postgres.

• Some JSON data must be prepared for inserting into a Postgres table. For multiple records, each JSON document must be nested inside of an array. This tutorial will use the following test data in the form of a JSON array:

Access psql

Execute the psql command to enter into the psql command-line interface.

Now connect to a database, that will store the JSON data as a PostgreSQL table, using the following \c command:

A Postgres user and database can also be accessed by using the -U and -d flags, respectively, while the psql command is executed in a terminal. Here is an example:

PostgreSQL ‘CREATE TABLE’

A table must first be created for the JSON data before an INSERT statement can be performed. However, the table’s columns must conform to the keys of the JSON data.

Assume the JSON data uses the following keys in a JavaScript array:

In this case, the CREATE TABLE SQL statement should resemble the following:

Note that the above command should return a response of CREATE TABLE.

Using psql to insert JSON

JSON data can now be inserted into the new Postgres table. Execute the Postgres json_populate_record() function to populate an object with JSON data before inserting it into the table.

Insert a JSON document into Postgres

The following example shows how to insert JSON data as Postgres records into a NULL object so that the data can be inserted into a table:

NOTE: The double colons (::) in the above SQL statement denote a type of data conversion. In this case it converts a NULL object into data that can be digested by the Postgres table using the json_populate_record() function. An example is provided in the following screenshot:

When the above SQL statement is executed, a single record should be inherited into the Postgres table. Use the SELECT keyword to retrieve the data, as shown in the following example:

The above statement should return a response that resembles the following table:

Postgres insert multiple rows of JSON

This section will explain how to insert multiple JSON records using the json_populate_recordset() SQL function. The following example shows how to insert two additional JSON records into the Postgres table:

NOTE: An ERROR: cannot call populate_composite on an array error message will be displayed if the above SQL statement is attempted using the json_populate_record() function explained previously.

However, executing the SELECT id FROM json_table; statement should return three IDs in a table that resemble the following data:

The results are displayed in the following screenshot:

When finished, execute the \q command to quit or exit the psql command-line interface.

Conclusion

This tutorial explained how to use Postgres to insert JSON data into a PostgreSQL table using SQL statements in psql. The tutorial specifically explained how to access psql and execute the psql command to enter into the psql command-line interface. The tutorial also explained how to connect to the database that will store the JSON data as a PostgreSQL table and how to execute the PostgreSQL ‘CREATE TABLE’ command. The article also covered how to use psql to insert a JSON document into Postgres as well as how to insert multiple rows of JSON data into a table. Remember that an ERROR: cannot call populate_composite on an array error message will be displayed if a SQL statement is attempted using the json_populate_record() function when inserting JSON data into a PostgreSQL table. Here the SELECT id FROM json_table; statement must be used instead.