Database Administration
postgresql select user-defined-type composite-types
Updated Fri, 08 Jul 2022 20:02:05 GMT

PostgresSQL: Get single attribute of UDT in SELECT statement


I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work.

Creation of type and table:

CREATE TYPE  ttp AS (f1 int, f2 int);
CREATE TABLE tbl (ctyp ttp);

The SELECT statement:

-- does not work
SELECT ctyp.f1 FROM tbl
SELECT ctyp(f1) FROM testtable

Both times I get an error. Is it possible to access a single attribute defined in a UDT in a SELECT?

Solution

Use:

SELECT (ctyp).f1 FROM tbl;

The parentheses are necessary to disambiguate tables from composite types as detailed in the manual on composite types.

Comments (1)

  • +0 – Thanks! The difference is very subtle. Btw, I thought it might be somewhere written in the manual, but I really couldn't find it. Thanks again! — Mar 20, 2013 at 22:35  

External Links

External links referenced by this document:

References