ERROR: missing FROM-clause entry for table
I have composite type "design" consisting of enums (top, drawing, classic, etc.) and I need to somehow get rows from table by value from enum
So, basically, I need to process the following query:
SELECT *
FROM photos
WHERE nails_design.top = 'matte'
The full structure:
-- auto-generated definition
create type design as
(
top tops,
volume_small volume_small,
volume_big volume_big,
drawing drawings,
classic classic
);
alter type design owner to postgres;
-- auto-generated definition
create type tops as enum ('matte', 'glosse');
alter type tops owner to postgres;
CodePudding user response:
You have to do it like this:
SELECT * FROM photos WHERE (photos.nails_design).top = 'matte';
Nesting data types like that is not a good idea. Avoid composite types in table definitions, that violates the first normal form. And, as you see, it doesn't make your queries more intuitive.