0

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.

0

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

create unique index on test_table (date, (json->>'item'));
2
  • Tried to create unique index, but in the CREATE TABLE statement, got syntax error and stopped digging in this direction. It turns out that postgres has different syntax for unique table constraint in CREATE TABLE and for unique index in CREATE INDEX statement. First case, you can use only column_names, not expressions, so that was the reason I got an error. Example: create table test_table (date date, json jsonb, unique (date, (json->>'item'))). Documentation on the statements corroborates this. So my problem is solved, thank you!
    – atmc
    Nov 21 at 18:12
  • Unique constraint definition in create/alter table follow SQL spec rules. But in the same time is limited by SQL spec. Some advanced features only exist for unique indexes - partial and expression indexes (there are no CREATE INDEX in the SQL standard).
    – Melkij
    Nov 21 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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