Is it possible to cast record to anonymous composite type in PostgreSQL?

Question

I need to create a table with a constraint that no two rows with coinciding value pair exists, the pair consists of an ordinary field value (date) and an expression (procured from the json field), like this:

create table
    test_table
        (
            date   date,
            json  jsonb,
            exclude (((date, json->>'item')) with =)
        )

Trying to create a table using the statement above gives me the following error:

ERROR:  column "row" has pseudo-type record

OK, so I find a test case from postgres repo,

(https://github.com/postgres/postgres/blob/add5cf28d48149459466b9aff374d78aebf17482/src/test/regress/expected/create_index.out#L1337)

(not exactly my case), but the solution is to type my row with a known type, for example like this:

create type my_adhoc_type as
    (
        a date,
        b text
    )
;

create table
    test_table
        (
            date   date,
            json  jsonb,
            exclude (((date, json->>'item')::my_adhoc_type) with =)
        )
;

This works, but seems a little bit cumbersome to define a persistent extra type every time, and unfortunately the type of the table itself does not play the trick as it is a pair of (date, jsonb), not (date, text).

It is possible to use -> instead of ->>, to extract jsonb, not text directly, but this solution does not seem ideal and breaks if I need to extract more than one field from json.

So the question is: is it possible to directly type my row with an appropriate anonymous type, something like:

            exclude (((date, json->>'item')::record(date,text)) with =)

Of course, a better solution to the initial problem will also be much appreciated.

Answer 1

Why use an exclusion constraint here at all? Simple unique btree index is enough:

create unique index on test_table (date, (json->>'item'));