1

I want to create a table that withholds an array of values inside one column in table.

Example in JSON form:

 "trainNumber": 1,
 "departureDate": "2020-01-26",
 "operatorUICCode": 10,
 "operatorShortCode": "vr",
 "trainType": "IC",
 "trainCategory": "Long-distance",
 "commuterLineID": "",
 "runningCurrently": false,
 "cancelled": false,
 "version": 266667265768,
 "timetableType": "REGULAR",
 "timetableAcceptanceDate": "2019-11-08T10:34:22.000Z",
 "timeTableRows": [
   {
     "type": "DEPARTURE",
     "scheduledTime": "2020-01-26T04:57:00.000Z",
     "causes": []
   },
   {
     "type": "ARRIVAL",
     "scheduledTime": "2020-01-26T05:02:00.000Z",
     "causes": []
   },
   {
     "type": "DEPARTURE",
     "scheduledTime": "2020-01-26T05:03:00.000Z",
     "causes": []
   },
   {
     "type": "ARRIVAL",
     "causes": []
   },
   {
     "type": "DEPARTURE",
     "cancelled": false,
     "scheduledTime": "2020-01-26T05:05:00.000Z",
     "causes": []
   },

As show in example "timeTableRows".

How do I create this?

Improve this question
5
  • There is an array data type in Postgres, but you shouldn't really store non-atomic values in columns of a relational database. – mustaccio Jan 25 '20 at 15:14
  • Do you mean; how to insert a json text in a table? – McNets Jan 25 '20 at 15:15
  • @mustaccio Is there a reason for that? I am creating a reservation calendar where I need to enter reserved hours into db. I cant think of another way to do this since it doesn't sound reasonable to create 24 different columns for every hour. – Sampo Jan 25 '20 at 15:52
  • @McNets no. Creating a db for reservation calendar. – Sampo Jan 25 '20 at 15:53
  • 1
    If you're unsure how to model your view of the universe (or its little corner), that would be a different question, and you'll need to describe your universe in greater detail. You should not have non-atomic attributes (which in your case look like proper entities in their own right) in a relational model. – mustaccio Jan 25 '20 at 17:47
0

You could try adding a json array column (json[]), but it depends on your access patterns what the best column type would be.

Example:

ALTER TABLE tableName
ADD COLUMN timeTableRows json[];

And then:

INSERT INTO tableName 
(
    timeTableRows
) 
VALUES
( array['{
   "type": "DEPARTURE",
   "scheduledTime": "2020-01-26T04:57:00.000Z",
   "causes": []
 }',
 '{
   "type": "ARRIVAL",
   "scheduledTime": "2020-01-26T05:02:00.000Z",
   "causes": []
 }']::json[])
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.