How can I add an object to my table with multiple values?

Question

We have a postgres setup, and I'll be honest straight away I know very little about database manipulations, my goal is to add an object to the pre-existing table called websites and then have the following object with properties as below:

"support": {
    "desktop": true,
    "mobile": true
}

I know it's quite easy to just add supportDesktop as a boolean property like so:


ALTER TABLE websites
ADD COLUMN "supportDesktop" boolean DEFAULT true NOT NULL;
ADD COLUMN "supportMobile" boolean DEFAULT true NOT NULL;

COMMIT;

I'm not sure if there's some type of "object" value you can use but any help would be great!

You can always try postgres jsonb datatype and embed the object directly? — Patrick Magee, May 18 '20 at 01:53

score 0 · Accepted Answer · answered May 18 '20 at 02:33

SQL is a relation database, and as far as I know, I'm afraid you can't add columns that are 'object like'. https://www.w3schools.com/sql/sql_datatypes.asp

In case you still want to somehow store those 'object' data to a SQL table, you have to store them inside predefined columns and use XML format to do so. Or in case you want to store them exactly as written above, you can simply store them as text (Varchar), and use line brakes. Here is a detailed explanation: How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

However, my personal advice would be to simply use JSON data format.

How can I add an object to my table with multiple values?

1 Answers1