[SOLVED] Postgresql return variable in type stored in a table

Postgresql return variable in type stored in a table

I'm currently developing an app and encountered a problem when trying to create a query on a table that uses a custom type.

Here is an example table and type,

CREATE TYPE address AS (
  street varchar(40),
  city   varchar(25),
  zip    varchar(5)
);
CREATE TABLE houses (
  id SERIAL PRIMARY KEY,
  address address
);

I need to retrieve a list with all the cities stored in the table houses. Here's what I tried to do:

SELECT address.city
FROM houses
GROUP BY address.city

It detects address.city as a table. Is there any way to do what I want to do in PostgreSQL?

Solution

I would not use a compound type like that in a table. But you can still do this, use parenthesis.

SELECT (address).city
FROM houses
GROUP BY (address).city

From the docs

To access a field of a composite column, one writes a dot and the field name, much like selecting a field from a table name. In fact, it's so much like selecting from a table name that you often have to use parentheses to keep from confusing the parser.

Instead, I would use a jsonb. Or, if you're going to use a compound type you should use stdaddr from PostGIS.