How to Create a Postgres JSON Column Data Type

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

PostgreSQL’s JSON support offers users the best of both worlds: a degree of flexibility usually reserved for NoSQL databases along with the many benefits offered by a relational database. In order to harness the full power of using Postgres to store JSON data, it’s important to know how to create a column with the JSON data type. This article will walk you through the steps involved in creating a Postgres JSON column and provide examples to illustrate the process.

Prerequisites

If you’re planning on following along with the examples in this tutorial, you’ll need to make sure that PostgreSQL is installed and running on your device. On Linux systems, you can see which version of PostgreSQL is installed using the command psql -V.

Access the interactive shell for PostgreSQL

Before we can create a table with a JSON column, we’ll need to make sure that we have access to psql, the command-line interface for PostgreSQL:

To enter the psql command-line interface, we’ll need the privileges of the postgres superuser:

1
sudo -u postgres psql

You’ll need to enter the root password to continue.

Create a database and a table in PostgreSQL

Once we’re in the psql console, we can execute queries and perform all kinds of SQL operations. Let’s start by creating a database. We’ll use the command shown below:

1
CREATE DATABASE somedb;

NOTE: Feel free to name your database whatever you like.

If you already have a database created, you can use the command \l to display a list of all available databases in PostgreSQL. You can use the command \c followed by the database name to connect to that database.

Next, we’ll create a table that resides in our newly-created database. Shown below is the basic syntax for creating any table in PostgreSQL:

1
2
3
CREATE TABLE some_table(
first_column DATA_TYPE CONSTRAINT [OPTIONAL],
second_column DATA_TYPE CONSTRAINT [OPTIONAL]);

For our example, we’ll use the statement below:

1
2
3
CREATE TABLE demo(
id SERIAL PRIMARY KEY,
DATA JSON);

NOTE: Again, feel free to use any name you like for your table.

Notice that our table has a column with a JSON data type. Our next task is to insert some values into this table.

PostgreSQL insert JSON data in column

To insert some values, we’ll use the following INSERT INTO statement:

1
2
3
4
5
INSERT INTO demo(DATA)
VALUES('{"title": "PostgreSQL for Beginners", "type": ["Programming", "Computer"], "published": true}'),
('{"title": "Ivanhoe", "type": ["Novel", "Adventure"], "published": true}'),
('{"title": "The Pragmatic Programmer", "type": ["Programming", "Computer"], "published": false}'),
('{"title": "Treasure Island", "type": ["Novel", "Adventure"], "published": true}');

We can verify that this INSERT operation was successful with a quick query. Use the command SELECT * FROM demo; to display the contents of the demo table:

1
2
3
4
5
6
id | data
----+------------------------------------------------------------------------------------------------
1 | {"title": "PostgreSQL for Beginners", "type": ["Programming", "Computer"], "published": true}
2 | {"title": "Ivanhoe", "type": ["Novel", "Adventure"], "published": true}
3 | {"title": "The Pragmatic Programmer", "type": ["Programming", "Computer"], "published": false}
4 | {"title": "Treasure Island", "type": ["Novel", "Adventure"], "published": true}

PostgreSQL json query

In the next example, we’ll use a JSON operator to retrieve the data from the JSON column:

1
SELECT DATA ->> 'title' AS book_title FROM demo;

The result should return the title in text form:

1
2
3
4
5
6
book_title
--------------------------
PostgreSQL for Beginners
Ivanhoe
The Pragmatic Programmer
Treasure Island

Conclusion

If you want to take full advantage of PostgreSQL’s JSON support, you’ll need to know how to create a column with a JSON data type. In this article, we walked you through the process of creating a Postgres JSON column and then showed you how to select data from this column and have it returned in text form. With these instructions and examples, you’ll be ready to create a Postgres JSON column in your own tables. Jump to top

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.