Home > mailing lists

Thread: [GENERAL] How do I insert and update into a table of arrays of composite typesvia a select command?

[GENERAL] How do I insert and update into a table of arrays of composite typesvia a select command?

From

Celia McInnis

Date:

26 October 2017, 01:34:44

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

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

From

Tom Lane

Date:

26 October 2017, 01:47:01

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

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

From

Celia McInnis

Date:

26 October 2017, 02:59:41

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:

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

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

From

Celia McInnis

Date:

26 October 2017, 03:16:09

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:

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

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

From

"David G. Johnston"

Date:

26 October 2017, 03:21:55

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.

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

From

Tom Lane

Date:

26 October 2017, 03:25:20

"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

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

From

Celia McInnis

Date:

26 October 2017, 03:33:53

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:

"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