PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to store nested lists in Postgres?

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?

Tags:

2

Answers


  1. 0

    The syntax should be:

    values (7, ARRAY[ARRAY["list_1"], ARRAY["list_2"], ARRAY["list_3"], ARRAY["list_4"]]);

    The ARRAY keyword is present before each list

    Login or Signup to reply.
  2. 0

    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}}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search