2

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?

1

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.