How to Create a Postgres JSON Column Data Type
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