1

Let's say I've got a table which has columns of user defined types:

create type my_udt as (
   x varchar(10),
   y numeric(5,3),
   z varchar(10)
);

create table udt_tab (
   a varchar(10),
   udt_col my_udt
);

then what I want to do is query it like:

select * from udt_tab t
where t.udt_col.x = 'Foo';

which would work fine in Oracle, but in Postgres it gives me:

SQL Error [42P01]: ERROR: missing FROM-clause entry for table "b" Position: 36

I've tried a few variations such as

select * from udt_tab
where udt_col_tab.udt_col.x = 'Foo';

select * from udt_tab t
where t.udt_col.x = 'Foo';

but nothing seems to work and I can't find anything in the manual about this.

We are migrating an existing Oracle database with lots of application code, so restructuring the table to avoid using the UDTs would add a lot of extra effort.

2
  • 2
    You created udt_tab and are referencing udt_col_tab; is that a typo?
    – gwaigh
    Commented Oct 4, 2021 at 17:57
  • yes, fixed now thanks.
    – Neil
    Commented Oct 4, 2021 at 21:18

1 Answer 1

3

The manual explains how to access attributes of composite types:

You must write it like this:

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

or if you need to use the table name as well (for instance in a multitable query), like this:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Fiddle

2
  • It's also documented here: postgresql.org/docs/current/…
    – user1822
    Commented Oct 4, 2021 at 19:37
  • Thanks for that. I was probably not entering the right search phrases to have found it. It seems an odd syntax, but I'm sure I'll get used to it :)
    – Neil
    Commented Oct 4, 2021 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.