How do you CREATE TABLE AS SELECT (CTAS) with a composite type?

Question

PostgreSQL supports CREATE TABLE AS SELECT (CTAS). It also supports composite types that can represent whole rows.

Here is an example of a CTAS,

CREATE TABLE foo AS
SELECT * FROM ( VALUES (1), (2) );

Here is a few examples of SELECTing a row.

SELECT (1,2);
SELECT ROW(1,2);
SELECT t FROM ( VALUES (1,2), (2,100) ) AS t;
SELECT x FROM ( VALUES ((1,2)), ((2,100)) ) AS f(x);
SELECT (x,y) FROM ( VALUES (1,2), (2,100) ) AS t(x,y);

However, none of them seem to work with CTAS.

CREATE TABLE foo AS SELECT f FROM ( VALUES (1,2), (2,100) ) AS f(x,y);
ERROR:  column "f" has pseudo-type record

CREATE TABLE foo AS SELECT (x,y) FROM ( VALUES (1,2), (2,100) ) AS f(x,y);
ERROR:  column "row" has pseudo-type record

I get that this is a typing-error, but what syntax does CTAS want?

score 3 · Answer 1 · answered Dec 20 '16 at 00:24

I don't think you can do it the way you are trying to... if my understanding of "A pseudo-type cannot be used as a column data type" is correct.

However, you can do something similar.

First, you define your composite type (for instance, we have a "point in two dimensions", with fields x and y):

-- Need to define the composite type
CREATE TYPE point_2d AS
(
    x real,
    y real
) ;

Then, you can write 'literals' that need to be cast to point_2d so that PostgreSQL know which exact type they have. So, this works:

CREATE TABLE 
    ttt AS
SELECT
    *
FROM 
(
    VALUES 
    ('(1.5, 2.5)'::point_2d, '(-1.3, -2.2)'::point_2d),
    ('(2.3, 4.4)'::point_2d, '(-2.2, -3.3)'::point_2d)
) AS lines (start_point, end_point) ;

And then, you can perform queries like:

SELECT
    (start_point).x AS x_start,
    (start_point).y AS y_start,
    (end_point).x AS x_end,
    (end_point).y AS y_end
FROM
    ttt ;

How do you CREATE TABLE AS SELECT (CTAS) with a composite type?

1 Answers1