Unable to get lines from database table using composite type

November 25, 2022

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;

>Solution :

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.