PostgreSQL insert into and select from an array of user defined objects

Question

I've been having some issues while trying to learn PostgreSQL. I've created a relational object called person and then a table consisting of a primary integer key and an array of person objects. I have a feeling it's the way I am inserting rows into this array however, I am unsure of how to access specific columns of the object as well (Ex. person.name).

Currently the only way I was able to insert a row is as follows however, I think it may just be making a string object instead of the proper person object.

INSERT INTO towns VALUES (0, '{"(bob,blue,springfield,33)"}');

For reference the schema I created is:

CREATE TYPE person AS (
    name text,
    favorite_color text,
    hometown text,
    age integer
);

CREATE TABLE towns (
    town_id integer PRIMARY KEY,
    people person[]
);

score 1 · Accepted Answer · 2016-11-26 07:49:45Z

1

That's one of the reasons I prefer the array[...] syntax over '{...}'. You don't need to think about nesting quotes:

INSERT INTO towns  (town_id, people)
  VALUES 
(0, array[('bob','blue','springfield',33)::person]);

('bob','blue','springfield',33)::person creates a record of type person and array[...] that makes that a single element array. You have to cast the anonymous record created with (...) to person to make this work. If you want to insert multiple person records into the array it's a bit less typing to case the whole array at the end:

array[('bob','blue','springfield',33), ('arthur','red','sleepytown',42)]::person[]

To select a specific element of a record in an array, you can use e.g:

select town_id, people[1].name
from towns

edited Nov 26, 2016 at 7:49

answered Nov 26, 2016 at 7:43

user330315

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

Comments

phobia82 · Accepted Answer · 2016-11-26 05:58:42Z

0

You are combining 2 things here, a field type as array of a composite type person.

To insert a composite type you should do something like this ROW("bob","blue","springfield",33), note that ROW keyword is optional

For array types you should use brackets inside single quotes, like '{val1,val2}', in your case, you are adding only one element of the array, which result is a person type.

Your example should look like:

INSERT INTO towns VALUES (0, '{ROW("bob","blue","springfield",33)}');

Note that the double quotes are for values inside the person type, not for the whole object.

References: Composite Types, Arrays

To access the person value, you have to wrap the object in (), so (person).name

edited Nov 26, 2016 at 5:58

answered Nov 26, 2016 at 5:52

phobia82

1,2578 silver badges10 bronze badges

2 Comments

Jdeggs Over a year ago

Gotcha, I appreciate the demystification of quote and single quote! I tried executing the example given however, the error malformed array literal: "{ROW("bob","blue","springfield",33)}" is raised. I did some testing after reading the docs you posted more thoroughly, using a table with just an instance of the composite type. With the structure you mentioned for inserting into a composite type and wrapping the fields to access composite fields works perfectly!

phobia82 Over a year ago

Note that the array should be wrapped in single quotes, not double

Winter Faulk · Accepted Answer · 2016-11-26 06:19:58Z

0

The only way I could get this to work was by inserting in the following way:

INSERT INTO towns VALUES (0, (array[ROW('bob','blue','springfield',33)::person]));

And to select you would have to do this:

select (people[1]).age from towns;

answered Nov 26, 2016 at 6:19

Winter Faulk

4134 silver badges9 bronze badges

Collectives™ on Stack Overflow

PostgreSQL insert into and select from an array of user defined objects

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related