composite type insert
I'm trying to understand how to insert a composite type value. I'm
having trouble with the last sql statement below. It errors out with:
ERROR: cannot cast type record to atype
How should I create a composite type value out of columns a and b in
table tt that I can insert into table atable?
CREATE TYPE atype AS (
acol
TEXT,
bcol
TEXT
);
CREATE TABLE atable (
aval
atype
);
CREATE TEMP TABLE
tt
AS
SELECT 'aaa'::text AS a, 'bbb'::text AS b;
INSERT INTO
atable
SELECT
ROW(a, b)
FROM
tt;
Thanks as always.
--
Ron Peterson
https://www.yellowbank.com/
Ron Peterson <ron.peterson@yellowbank.com> writes:
How should I create a composite type value out of columns a and b in
table tt that I can insert into table atable?
Hm, it works for me with an explicit cast:
INSERT INTO
atable
SELECT
ROW(a, b)::atype
FROM
tt;
Perhaps we should allow this in an automatic or even implicit cast
context.
regards, tom lane
On Sun, Nov 19, 2006 at 02:09:11AM -0500, Tom Lane wrote:
Ron Peterson <ron.peterson@yellowbank.com> writes:
How should I create a composite type value out of columns a and b in
table tt that I can insert into table atable?Hm, it works for me with an explicit cast:
INSERT INTO
atable
SELECT
ROW(a, b)::atype
FROM
tt;Perhaps we should allow this in an automatic or even implicit cast
context.
Ah, I can certainly live with a cast. I didn't realize that creating a
composite type with 'create type' also automatically created a cast. I
should have at least tried that. Maybe section 8.11 (Composite Types)
or the 'CREATE TYPE' section of the docs should mention this?
Best.
--
Ron Peterson
https://www.yellowbank.com/