Bootstrap FreeKB - Postgres (SQL) - Insert into a JSONB column
Postgres (SQL) - Insert into a JSONB column

Updated:   |  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 Buy Me A Coffee



Comments


Add a Comment


Please enter 71d6c2 in the box below so that we can be sure you are a human.