| From: | Celia McInnis <celia(dot)mcinnis(at)gmail(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | How do I insert and update into a table of arrays of composite types via a select command? |
| Date: | 2017-10-25 19:34:44 |
| Message-ID: | CAGD6t7+KQP_c2QhVWdi8=0juUfJUo=+ufr+8nD1qN84xhYYB6A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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')}
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2017-10-25 19:47:01 | Re: How do I insert and update into a table of arrays of composite types via a select command? |
| Previous Message | Ben Madin | 2017-10-25 14:22:14 | Re: Installing PostgreSQL 10 on Mac OSX Undefined Symbol _heap_modify_tuple_by_cols |