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?
3
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