postgres: inserting array of custom type

Question

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?

Why not simply normalize your data model and use a proper one-to-many relationship with multiple tables instead of arrays — user330315
– user330315, Commented Mar 25, 2020 at 6:44

jjanes · Accepted Answer · 2020-03-25 03:00:12Z

2

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

answered Mar 25, 2020 at 3:00

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

5 Comments

Maths noob Over a year ago

the second option worked except that I am having difficulty reading it later, as I am getting SQL object of class java.lang.String cannot be cast to mapped class org.postgresql.util.PGobject. persumably because it reads the supposed Contributors entry as a string. how can I add the type information to my array types and row types so that I can read them later? I tried to mirror the syntax used in sqlfiddle.com/#!17/88dfc/3 namely ::mytype and ::mytype[] but that is giving me errors. what would the above look like with the type information added?

Maths noob Over a year ago

ie: "{""(Song,Kang-ho)"",""(Omar,Sharif)""}" works, but if I add the type at the end: "{""(Song,Kang-ho)"",""(Omar,Sharif)""}"::person[] it doesn't work. neither does: "{""(Song,Kang-ho)"",""(Omar,Sharif)""}::person[]"

jjanes Over a year ago

The types are already there on the data as stored as dictated by the column types. I don't think that changing how you do the insertion is going to change the retrieval. You have a problem specifically with the retrieval, so that is a different question.

Maths noob Over a year ago

you're right. I am receiving it as a PGobject to then read its value but i am getting SQL object of class java.lang.String cannot be cast to mapped class org.postgresql.util.PGobject. which I don't know what to do with.

jjanes Over a year ago

Alas I can't help with that part.

Collectives™ on Stack Overflow

postgres: inserting array of custom type

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related