Convert string back to array of tuples

Question

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?

1) Do not use images for textual data, copy and paste as text. 2) There are two versions of SELECT INTO one inside plpgsql and one outside. Which one are you using and what is the issue when using it? — Adrian Klaver
– Adrian Klaver, Commented Jan 21 at 21:25
SELECT ARRAY[(1,2)] AS a INTO temp_table ERROR: column "a" has pseudo-type record[] SQL state: 42P16 — Ludovic Aubert
– Ludovic Aubert, Commented Jan 21 at 21:35
that was the issue. ARRAY[(1,2)]::text works it around, but then... — Ludovic Aubert
– Ludovic Aubert, Commented Jan 21 at 21:37

Zegarek · Accepted Answer · 2025-01-21 21:35:57Z

1

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)"}

answered Jan 21 at 21:25

Zegarek

29.3k5 gold badges27 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

Convert string back to array of tuples

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related