CODEWITHSUNDEEP
sqlpostgresql
mbcrute
mbcrute

Reputation: 1127

How do you SELECT attributes of a custom type via a table alias?

Is there any way to select the individual attributes of a custom typed column in PostgreSQL via a table alias? Without using a table alias, wrapping the name of the column in parens works fine. As soon as a table alias is introduced my query fails with a syntax error.

I've scoured the Postgres docs and haven't been able to find anything illustrating how to accomplish this. Surely this is possible?

CREATE TYPE test_type AS (
    some_text TEXT,
    some_number INTEGER
);

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    some_test test_type NOT NULL
);

INSERT INTO test_table (some_test)
VALUES (ROW('SOME TEXT', 42));

-- This works great

SELECT id, (some_test).some_text, (some_test).some_number
FROM test_table;

┌────┬───────────┬─────────────┐
│ id │ some_text │ some_number │
├────┼───────────┼─────────────┤
│  1 │ SOME TEXT │          42 │
└────┴───────────┴─────────────┘

As soon as the table alias is introduced you can't SELECT the individual attributes from the custom typed column

SELECT id, x.(some_test).some_text, x.(some_test).some_number
FROM test_table AS x;

ERROR:  syntax error at or near "("
LINE 1: select id, x.(some_test).some_text, x.(some_test).some_numbe...
                     ^

Upvotes: 4

Views: 714

Answers (1)

user330315
user330315

Reputation:

You need to put the parentheses around the combination of alias and column:

SELECT id, (x.some_test).some_text, (x.some_test).some_number
FROM test_table x;

Upvotes: 4

Related Questions