arrayspostgresqltype-conversiontuplespostgresql-17

Convert string back to array of tuples

I have an array of tuples in PostgreSQL 17.

I couldn't get SELECT...INTO...FROM to work for some reason:

SELECT ARRAY[(1,2)] AS a INTO temp_table;

ERROR: column "a" has pseudo-type record[] 
SQL state: 42P16

To solve it, I had to convert it to text:

SELECT ARRAY[(1,2)] AS a, ARRAY[(1,2)]::text AS b
a
type record[]		 b
type text
{"(1,2)"} {"(1,2)"}

Now I need to convert column b back to array of tuples, like column a. How can it be done?

Solution

  • What you're calling a tuple is technically type record - the parenthesized thing is row composite value literal syntax, with the row keyword skipped. Since ::record[] syntax won't work, you can create a dummy table or type to work around that:
    demo at db<>fiddle

    create type dummy_type as(x int, y int);
create temp table temp_dummy_table(x int, y int);
SELECT ARRAY[(1,2)] AS a
     , ARRAY[(1,2)]::text AS b
     , '{"(1,2)"}'::dummy_type[]
     , '{"(1,2)"}'::temp_dummy_table[];
    a b dummy_type temp_dummy_table
    {"(1,2)"} {"(1,2)"} {"(1,2)"} {"(1,2)"}

    Related: