How to create a column that holds an array in Postgres?

Question

Background:

I am making a db for a reservartions calendar. The reservations are hourly based, so I need to insert many items to one column called "hours_reserved".

Example tables of what I need:

        Table "Space"
  Column        /          Values
 id             /             1
 date           /          5.2.2020
 hours          /      {  8-10, 10-12 }

        Table "reservation"
  Column        /          Values
 id             /             1
 space_id       /             1
 date           /          5.2.2020
 reserved_hours /            8-10

        Table "reservation"
  Column        /          Values
 id             /             2
 space_id       /             1
 date           /          5.2.2020
 hours          /           10-12

So I need to have multiple items inserted into "space" table "hours" column. How do I do this in Postgres? Also is there a better way to accomplish this?

Read up on on database normalization and creating one-to-many relationships. While possible in Postgres, storing things in arrays is rarely the best choice — user330315
– user330315, Commented Feb 5, 2020 at 11:35

ffflabs · Accepted Answer · 2020-02-05 11:35:28Z

2

There is more way to do this, depending on the type of the hours field (i.e. text[], json or jsonb) I'd go with jsonb just because you can do a lot of things with it and you'll find this experience to be useful in the short term.

CREATE TABLE "public"."space" 
("id" SERIAL, "date_schedule" date, "hours" jsonb, PRIMARY KEY ("id"))

Whenever you insert a record in this table that's manually crafted, write it as text (single quoted json object) and cast it to jsonb

insert into "space" 
  (date_schedule,hours) 
values 
  ('05-02-2020'::date, '["8-10", "10-12"]'::jsonb);

There is more than one way to match these available hours against the reservations and you can take a look at the docs, on the json and jsonb operations. For example, doing:

 SELECT id,date_schedule, jsonb_array_elements(hours) hours FROM "public"."space"

would yield

Which has these ugly double quotes (which is correct, since json can hold several kinds of scalars, that column is polimorfic :D)

However, you can perform a little transformation to remove them and be able to perform a join with reservations

with unnested as (
  SELECT id,date_schedule, jsonb_array_elements(hours) hours FROM "public"."space"
)
select id,date_schedule,replace(hours::text, '"','') from unnested

The same can be achieved defining the field as text[] (the insertion syntax is different but trivial)

in that scenario your data will look like:

Which you can unwrap as:

SELECT id,date_schedule, unnest(hours) FROM "public"."space"

answered Feb 5, 2020 at 11:35

ffflabs

17.6k5 gold badges57 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sampo Kaarenmaa Over a year ago

Very good. Do I need to specify anything when creating a node endpoint for this table?

ffflabs Over a year ago

most ORMs will deal seamlessly with javascript objects. IF you plan tu use just a driver, go for PG Promise

Sampo Kaarenmaa Over a year ago

I decided to test with text[]. Any Idea why using put with postman returns "message": "error: invalid input syntax for type integer: \"{\"hours\":[\"8-10\"]}\""?

ffflabs Over a year ago

Don't know, but if you're using say, express to handle the request, console.log them to see what's coming. Perhaps you're not using the json parsing middleware?

Sampo Kaarenmaa Over a year ago

Yes content type is JSON. My endpoint is like this

// UPDATE exports.updateVaraus = async(id, myarray) => {     try {         const result = await client.query('UPDATE varaus SET id =1$, myarray = $2')          [id, myarray]         );         return result.rows     } catch (err) {         throw new Error(err);     } }

Sampo Kaarenmaa · Accepted Answer · 2020-02-05 11:26:06Z

0

Apparently

ALTER TABLE mytable
ADD COLUMN myarray text[];

Works fine.

I got a following problem when trying to put(update) into that column using postman(create works fine):

{
    "myarray": ["8-10"]
}

Results into:

"message": "error: invalid input syntax for type integer: \"{\"myarray\":[\"8-10\"]}\""

answered Feb 5, 2020 at 11:26

Sampo Kaarenmaa

1473 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to create a column that holds an array in Postgres?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related