Thread: Populating array of composite datatype
Hello,
I have a composite datatype abc which has two integer fields x,y.
I have a table Test which has an array of abc.
I am trying to populate Test. Tried
insert into test values (ARRAY[abc(1,2)]); but got error
ERROR: function abc(integer, integer) does not exist
Is there anyway for doing this?
regards
Sameer
On Wed, Aug 7, 2013 at 4:04 PM, Sameer Thakur <samthakur74@gmail.com> wrote:
Hello,I have a composite datatype abc which has two integer fields x,y.I have a table Test which has an array of abc.I am trying to populate Test. Triedinsert into test values (ARRAY[abc(1,2)]); but got errorERROR: function abc(integer, integer) does not existIs there anyway for doing this?
I think you need to use row() and explicit type cast.
postgres=# create type abc as (x integer, y integer);
CREATE TYPE
postgres=# create table foo(val abc[]);
CREATE TABLE
postgres=# insert into foo values (array[row(1,2)::abc]);
INSERT 0 1
postgres=# insert into foo values (array[row('1','2')::abc]);
INSERT 0 1
postgres=# select * from foo ;
val
-----------
{"(1,2)"}
{"(1,2)"}
(2 rows)
On Wed, Aug 7, 2013 at 3:53 AM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
postgres=# insert into foo values (array[row(1,2)::abc]);
Also because all array members must be of the same db type, you can:
insert into foo values (array[row(1,2)]::abc[]). This can be helpful if you have more than one array member.
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
On Wed, Aug 7, 2013 at 3:53 AM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:postgres=# insert into foo values (array[row(1,2)::abc]);Also because all array members must be of the same db type, you can:insert into foo values (array[row(1,2)]::abc[]). This can be helpful if you have more than one array member.
Agreed. Thank you Chris.
--Raghav
Thank you Raghavendra and Chris, both approaches work.
Sameer Thakur wrote
> insert into test values (ARRAY[abc(1,2)]); but got error
When you use a function form of casting like this you are basically
short-circuiting the type conversion mechanisms built into PostgreSQL by
directly calling the conversion function instead of actually telling the
system to perform a cast where the input is of one type and the output is of
another. While direct function invocation will work in some instances it is
better not to rely on it. If you really do mean to convert from one type to
another you should - almost always - use either:
the SQL standard
CAST('val' AS type)
or the PostgreSQL short-hand
'val'::type
[It seriously sucks that the standard syntax for casting is so verbose that
it is almost unusable unless you must - immediately - have cross-database
compatibility and thus can withstand the pain using it inflicts.]
There are a bunch more comments, rules, and warnings on this topic somewhere
in the documentation if you are really curious but, really, just use the
explicit casting mechanism provided by the database instead of calling the
casting functions directly.
Note I am not positive that abc(1,2) is truly an instance of this behavior
but I'm guessing you tried doing this because similar syntax has worked for
you before due to the behavior I've referenced above.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Populating-array-of-composite-datatype-tp5766628p5766656.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Just wanted to put a short thank you note in here. I've been using PostgreSQL for a while, but only using basic SQL. The information in this thread pointed me to the information that I needed to read about to solve a problem which has been tormenting me, off and on, for a couple of months. I just couldn't solve the problem with ANSI standard SQL. But finally did with an array of my own composite data type. This allowed me to _easily_ find and report on some things which should have been identically defined on multiple systems, but were not.
--
--
As of next week, passwords will be entered in Morse code.
Maranatha! <><
John McKown
Maranatha! <><
John McKown