Postgres (SQL) - Insert into a JSONB column

by
Jeremy Canfield |
Updated: December 21 2024
| Postgres (SQL) articles
The psql --list command can be used to display the Postgres databases. Something like this should be returned.
~]# psql --username johndoe --dbname db001 --list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
db001 | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
db002 | john.doe | UTF8 | en_US.utf8 | en_US.utf8 |
The psql command with the \dt (describe table) command can be used to list the tables in a database. Something like this should be returned.
~]# psql --username johndoe --dbname db001 --command "\dt"
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+-------+-------
public | table001 | table | johndoe
public | table002 | table | johndoe
public | table003 | table | johndo
The psql command with the \d <table name> command can be used to list the columns in a table.
psql --username johndoe --dbname mydb --command "\d mytable"
Something like this should be returned. Notice in this example that there is a JSONB column.
Table "public.mytable"
Column | Type | Collation | Nullable | Default
-----------------------+--------------------------+-----------+----------+-----------------------------------------------------
id | integer | | not null |
name | varchar(30) | | |
json | jsonb | | |
Here is an example of how you could insert into the JSONB column.
update mytable set json='{\"data\": [{\"date\": \"11/18/2024 @ 00:00:00 AM\", \"message\": \"test\"}]}' where id='1'
Did you find this article helpful?
If so, consider buying me a coffee over at