How do I insert and update into a table of arrays of composite types via a select command?
I have a composite type like this, for example:
create type mytype(a text, b text, c text);
and a table
create table t_cols(a text,b text,c text);
containing some data. I have a table with my composite type:
create table t_composite(data_comp mytype);
and loaded data into it like so:
insert into t_composite select ROW(a,b,c)::mytype from t_columns;
I will want to construct a table containing arrays of this composite type
{(a1,b1,c1), ...,(an,bn,cn) } - the arrays can be of variable lengths (the
arrays will be formed to meet some constraints).
I formed the table for the arrays:
create table t_array(data_array mytype[]);
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.
In case it's not clear what I want to do, say, for example that I have a
table containing:
('a','b','c')
('d','e','f')
('h'.'i'.'j')
('k','l','m')
1. First of all I'd like to form (and I hope that this is easy!) a table
with:
{('a','b','c')}
{('d','e','f')}
{('h','i','j')}
{('k','l','m')}
2. After this is done, I will then want to add some other arrays to the
table which contain some combination of the initial elements, to perhaps
(subject to some constraints) eventually getting something like:
{('a','b','c')}
{('d','e','f')}
{('h','i','j')}
{('k','l','m')}
{('a','b','c'),('h','i','j')}
{('d','e','f'),('h','i','j'),('k','l','m')}
Celia McInnis <celia.mcinnis@gmail.com> 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
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 <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Celia McInnis <celia.mcinnis@gmail.com> 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 1or just cast the whole ARRAY[] construct:
regression=# insert into t_array select array[row('a','b','c'),row('d'
,'e','f')]::mytype[];
INSERT 0 1although I think the latter only works in relatively late-model
Postgres, and it might make parsing a bit slower too.regards, tom lane
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 <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Celia McInnis <celia.mcinnis@gmail.com> 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 1or just cast the whole ARRAY[] construct:
regression=# insert into t_array select array[row('a','b','c'),row('d'
,'e','f')]::mytype[];
INSERT 0 1although I think the latter only works in relatively late-model
Postgres, and it might make parsing a bit slower too.regards, tom lane
On Wed, Oct 25, 2017 at 2:16 PM, Celia McInnis <celia.mcinnis@gmail.com>
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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Wed, Oct 25, 2017 at 2:16 PM, Celia McInnis <celia.mcinnis@gmail.com>
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
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 <tgl@sss.pgh.pa.us> wrote:
Show quoted text
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Wed, Oct 25, 2017 at 2:16 PM, Celia McInnis <celia.mcinnis@gmail.com>
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