How to store lists in Postgresql?

Question

What is the best way to store lists in postgresql? What kind of field do I have to use? do I have to serialize it?

This is an example of data that I want to store:

id ( number ), name ( text ), events ( list )

1, john connor, ["aaa","bbb","ccc"]

2, jack bush, ["ttt","hhh","lll"]

...

You don't. You create another table and link to the main though a foreign key. A pretty standard parent-child DB relationship. — Alejandro
– Alejandro, Commented Aug 31, 2021 at 13:15
@Alejandro Can't I even serialize the list and store it in the column? — xRobot
– xRobot, Commented Aug 31, 2021 at 13:17
You could, but it violates the best practices for database normalization — user330315
– user330315, Commented Aug 31, 2021 at 13:27
@xRobot You certainly can, you can save a string that contains anything, but doing so will lead to many problems down the road. Use another table and save the hassle. — Alejandro
– Alejandro, Commented Aug 31, 2021 at 14:12
@xRobot All RDBMS are based on the concept on data Normalization (google it - you will find a lot). The first step is called 1st Normal Form (1NF) and part of that is removing all repeating groups. An array or list of any kind violates that. It only leads to maintenance and data issues down the line. As mentioned pull your list out into another table with a column for values. You can always rebuild the list or array with a query but avoid the data abnormalities you will get with a list or array. — Belayer
– Belayer, Commented Aug 31, 2021 at 18:57

bfris · Accepted Answer · 2021-08-31 14:18:18Z

20

How about using arrays? They are declared with [] for any size and [3] for array with 3 cells.

CREATE TABLE test1 (
  id integer,
  name text,
  events text[]);
  
INSERT into test1 (id, name, events)
    values(1, 'john connor', '{"aaa", "bbb","ccc"}'),
          (2, 'jack bush', '{"ttt", "hhh","lll"}');

When inputting arrays, everything has to be enclosed in curly braces AND the curly braces have to be enclosed in single quotes. Finally, strings inside arrays must use double quotes.

Here's what the table looks like:

id  name            events
1   john connor     {aaa,bbb,ccc}
2   jack bush       {ttt,hhh,lll}

answered Aug 31, 2021 at 14:18

bfris

5,9332 gold badges26 silver badges45 bronze badges

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

2 Comments

xRobot Over a year ago

Great, are there some limit on using arrays in postgresql?

bfris Over a year ago

@xrobot, I haven't come across any huge drawbacks for arrays. When you go to retrieve data, you will be limited in the ways you can rearrange the data with array functions.

medvedev_ed · Accepted Answer · 2021-08-31 13:41:39Z

-5

Use json example

select
    json_build_object(
        user_id,
        json_build_object(
            'user_name',
            table.user_name
            'aaa',
            table.aaa,
            'bbb',
            table.bbb,
            'ccc',
            table.ccc
        )
 )
from table

answered Aug 31, 2021 at 13:41

medvedev_ed

192 bronze badges

1 Comment

Community Over a year ago

Please provide additional details in your answer. As it's currently written, it's hard to understand your solution.

Collectives™ on Stack Overflow

How to store lists in Postgresql?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related