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?
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.