CODEWITHSUNDEEP
javapostgresqlliterals
Maths noob
Maths noob

Reputation: 1802

postgres: inserting array of custom type

I have: 


CREATE TYPE Person AS (given_name VARCHAR,
                       family_name VARCHAR);
CREATE TYPE Contributors AS (directors person[],
                             actors person[]) ;
CREATE TABLE Catalogue (id serial, contributors Contributors)

And I want to insert the following array literal into the catalogue table's contributors field:

({},{(Song,Kang-ho),(Omar,Sharif)})

through the JDBC Driver's PGobject value but im having difficulty doing so.

I have come across this question and I kinda understand the problem.

I tried the following:

({}, "{(Song,Kang-ho),(Omar,Sharif)}")

but i got:

ERROR: malformed record literal: "(Song" Detail: Unexpected end of input.

so i tried:

'({},"{(Song,Kang-ho),(Omar,Sharif)}")'

and also:

'({},"{'(Song,Kang-ho)','(Omar,Sharif)'}")'

but I get this error:

ERROR: malformed record literal: .... Detail: Missing left parenthesis.

what's the syntax to use for nested row-fields inside an array literal?

Upvotes: 1

Views: 661

Answers (1)

jjanes
jjanes

Reputation: 44137

It is hard to figure out what you are actually trying, as you are only showing part of a statement and it is not clear where the part you show fits into the whole.

This works:

insert into catalogue values  (1,('{}', '{"(Song,Kang-ho)","(Omar,Sharif)"}'));

So does this:

insert into catalogue values  (1,'({},"{""(Song,Kang-ho)"",""(Omar,Sharif)""}")');

Upvotes: 2

Related Questions