How to store nested lists in Postgres?

Question

How can I store nested lists in Postgres in way that's easy to use them in my Python program later?

I plan to write the lists to the database once and reuse them many times. I've been able to store the nested lists as a string but it's not optimal, I'm trying to accomplish this with as little post-processing as possible so I'd rather do more up-front work for speed/ease of use on retrieval later.

These nested lists are for layout purposes, not poor data normalization.

Here is what I've tried based off here:

Created table in my database with field that supports ARRAY (I'm using DBeaver, the annotation for ARRAY is the underscore before text: _text)

CREATE TABLE public.layout (
    f_id int8 NULL,
    layout _text NULL
);

When trying this:

insert into mpl_layout (f_id, layout)
values (7, ARRAY[["list_1"],["list_2"],["list_3"],["list_4"]]);

I get an error:

SQL Error [42703]: ERROR: column "list_1" does not exist

Adding parentheses around the ARRAY arguments only changes the error message:

insert into mpl_layout (f_id, layout)
values (7, ARRAY([["list_1"],["list_2"],["list_3"],["list_4"]]));

SQL Error [42601]: ERROR: syntax error at or near "7"

I tried the curly brace '{}' format:

insert into mpl_mosaic_layout (figure_id, layout)
values (7, '{[["list_1"],["list_2"],["list_3"],["list_4"]]}');

And got this error:

SQL Error [22P02]: ERROR: malformed array literal: "{[["list_1"],["list_2"],["list_3"],["list_4"]]}" Detail: Unexpected array element.

What should I try next?

I didn't test it (that's why I didn't post it as an answer), according to the example in the DBeaver URL, the syntax is more like: values (7, ARRAY[ARRAY["list_1"], ARRAY["list_2"], ARRAY["list_3"], ARRAY["list_4"]]); The ARRAY keyword is present before each list. — Ze'ev Ben-Tsvi
– Ze'ev Ben-Tsvi, Commented Jul 20, 2024 at 14:17
@Ze'evBen-Tsvi Your comment is correct, egg on my face -> I should have seen that. Plz post as answer and I will accept. — Python_Learner
– Python_Learner, Commented Jul 20, 2024 at 14:27
1) The reason you are getting the error is this "list_1" , double quotes are for identifiers so Postgres thinks you are referring to a column name. See Arrays 8.15.2. Array Value Input for examples. 2) If you are going to use Python, use the psycopg2 List adaption to have it do the work for you. 3) Your last example should be using all curly braces, see the Array Value Input link in 1). — Adrian Klaver
– Adrian Klaver, Commented Jul 20, 2024 at 14:44

Adrian Klaver · Accepted Answer · 2024-07-20 16:08:32Z

Inserting array using both forms of ARRAY input:

 CREATE TABLE public.layout (
    f_id int8,     
    layout varchar[]
);
insert into layout values (1, ARRAY[['list_1'],['list_2'],['list_3'],['list_4']]);

insert into layout values (2, '{{"list_1"},{"list_2"},{"list_3"},{"list_4"}}');

select * from layout ;
 f_id |                layout                 
------+---------------------------------------
    1 | {{list_1},{list_2},{list_3},{list_4}}
    2 | {{list_1},{list_2},{list_3},{list_4}}

You need to use single quotes with the ARRAY[] form and double quotes with the string('{}') form per here ARRAY VALUE INPUT: The ARRAY constructor syntax can also be used: [...] Notice that the array elements are ordinary SQL constants or expressions; for instance, string literals are single quoted, instead of double quoted as they would be in an array literal.

Also per ARRAY constructors: Multidimensional array values can be built by nesting array constructors. In the inner constructors, the key word ARRAY can be omitted

In Python using psycopg2:

import psycopg2
con = psycopg2.connect(dbname='test', user='postgres', port=5432)
cur = con.cursor()
array_list = [["list_1"],["list_2"],["list_3"],["list_4"]]
cur.execute("insert into layout values(%s, %s)", [3, array_list])
con.commit()

The above uses psycopg2 list/array adaptation as documented here Lists adaptation.

Which results in:

select * from layout ;
 f_id |                layout                 
------+---------------------------------------
    1 | {{list_1},{list_2},{list_3},{list_4}}
    2 | {{list_1},{list_2},{list_3},{list_4}}
    3 | {{list_1},{list_2},{list_3},{list_4}}

Collectives™ on Stack Overflow

How to store nested lists in Postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related