Re: [GENERAL] How do I insert and update into a table of arrays of composite types via a select command?

[Celia McInnis]

Yes, thanks - that's nicer - I am now using:

insert into t_array select array[data_comp]::mytype[] from t_composite;



On Wed, Oct 25, 2017 at 5:25 PM, Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Wed, Oct 25, 2017 at 2:16 PM, Celia McInnis 
> >> Got it, finally...
> >> insert into t_array select array[row((data_comp).*)::mytype[] from
> >> t_composite;
> >>
> >> I'm not sure why I need (data_comp).* rather than some of the other
> things
> >> that I tried and failed with...
>
> > ​The ​unusual set of parentheses are so the parser interprets data_comp
> is
> > a column and not a table. Usually one write SELECT tbl.* FROM tbl so that
> > is the assumed meaning of "name".*
>
> If data_comp is a column of a composite type, you probably don't need all
> that notation anyway --- seems like array[data_comp::my_type] or
> array[data_comp]::my_type[] ought to work.
>
> regards, tom lane
>

Re: [GENERAL] How do I insert and update into a table of arrays of composite types via a select command?

[Tom Lane]

"David G. Johnston"  writes:
> On Wed, Oct 25, 2017 at 2:16 PM, Celia McInnis 
>> Got it, finally...
>> insert into t_array select array[row((data_comp).*)::mytype[] from
>> t_composite;
>> 
>> I'm not sure why I need (data_comp).* rather than some of the other things
>> that I tried and failed with...

> ​The ​unusual set of parentheses are so the parser interprets data_comp is
> a column and not a table. Usually one write SELECT tbl.* FROM tbl so that
> is the assumed meaning of "name".*

If data_comp is a column of a composite type, you probably don't need all
that notation anyway --- seems like array[data_comp::my_type] or
array[data_comp]::my_type[] ought to work.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How do I insert and update into a table of arrays of composite types via a select command?

[David G. Johnston]

On Wed, Oct 25, 2017 at 2:16 PM, Celia McInnis 
wrote:

> Got it, finally...
>
> insert into t_array select array[row((data_comp).*)::mytype[] from
> t_composite;
>
> I'm not sure why I need (data_comp).* rather than some of the other things
> that I tried and failed with...
>
>
​The ​unusual set of parentheses are so the parser interprets data_comp is
a column and not a table. Usually one write SELECT tbl.* FROM tbl so that
is the assumed meaning of "name".*

David J.

Re: [GENERAL] How do I insert and update into a table of arrays of composite types via a select command?

[Celia McInnis]

Got it, finally...

insert into t_array select array[row((data_comp).*)::mytype[] from
t_composite;

I'm not sure why I need (data_comp).* rather than some of the other things
that I tried and failed with...

On Wed, Oct 25, 2017 at 3:47 PM, Tom Lane  wrote:

> Celia McInnis  writes:
> > My first question is: HOW do I populate this table, at first with arrays
> > containing one element from the t_composite table?
> > I have tried various things with no success.
>
> You didn't say what you tried, but I imagine it was something like
>
> regression=# insert into t_array select array[row('a','b','c'),row('d'
> ,'e','f')];
> ERROR:  column "data_array" is of type mytype[] but expression is of type
> record[]
> LINE 1: insert into t_array select array[row('a','b','c'),row('d','e...
>^
> HINT:  You will need to rewrite or cast the expression.
>
> Like it says, you need a cast.  You can either cast the array elements
> individually:
>
> regression=# insert into t_array select array[row('a','b','c')::
> mytype,row('d','e','f')::mytype];
> INSERT 0 1
>
> or just cast the whole ARRAY[] construct:
>
> regression=# insert into t_array select array[row('a','b','c'),row('d'
> ,'e','f')]::mytype[];
> INSERT 0 1
>
> although I think the latter only works in relatively late-model
> Postgres, and it might make parsing a bit slower too.
>
> regards, tom lane
>

Re: [GENERAL] How do I insert and update into a table of arrays of composite types via a select command?

[Celia McInnis]

Thanks Tom - I saw how to do that for specified data, but I want to select
the elements from a table of composite and don't know how to do that. I
can't do insertions manually of each row separately! So how do I get
material from my table of composite values into my table of array values?

I am running postgresql 9.6.2.



On Wed, Oct 25, 2017 at 3:47 PM, Tom Lane  wrote:

> Celia McInnis  writes:
> > My first question is: HOW do I populate this table, at first with arrays
> > containing one element from the t_composite table?
> > I have tried various things with no success.
>
> You didn't say what you tried, but I imagine it was something like
>
> regression=# insert into t_array select array[row('a','b','c'),row('d'
> ,'e','f')];
> ERROR:  column "data_array" is of type mytype[] but expression is of type
> record[]
> LINE 1: insert into t_array select array[row('a','b','c'),row('d','e...
>^
> HINT:  You will need to rewrite or cast the expression.
>
> Like it says, you need a cast.  You can either cast the array elements
> individually:
>
> regression=# insert into t_array select array[row('a','b','c')::
> mytype,row('d','e','f')::mytype];
> INSERT 0 1
>
> or just cast the whole ARRAY[] construct:
>
> regression=# insert into t_array select array[row('a','b','c'),row('d'
> ,'e','f')]::mytype[];
> INSERT 0 1
>
> although I think the latter only works in relatively late-model
> Postgres, and it might make parsing a bit slower too.
>
> regards, tom lane
>

Re: [GENERAL] How do I insert and update into a table of arrays of composite types via a select command?

[Tom Lane]

Celia McInnis  writes:
> My first question is: HOW do I populate this table, at first with arrays
> containing one element from the t_composite table?
> I have tried various things with no success.

You didn't say what you tried, but I imagine it was something like

regression=# insert into t_array select 
array[row('a','b','c'),row('d','e','f')];
ERROR:  column "data_array" is of type mytype[] but expression is of type 
record[]
LINE 1: insert into t_array select array[row('a','b','c'),row('d','e...
   ^
HINT:  You will need to rewrite or cast the expression.

Like it says, you need a cast.  You can either cast the array elements
individually:

regression=# insert into t_array select 
array[row('a','b','c')::mytype,row('d','e','f')::mytype];
INSERT 0 1

or just cast the whole ARRAY[] construct:

regression=# insert into t_array select 
array[row('a','b','c'),row('d','e','f')]::mytype[];
INSERT 0 1

although I think the latter only works in relatively late-model
Postgres, and it might make parsing a bit slower too.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general