Issues with UniqueColumns constraints with nullable fields

[igormorgado]

Maybe I have understood something wrong.

I have added the following constraints into my model:

class Meta(BaseMeta):
        tablename = "event"
        constraints = [orm.UniqueColumns(
            "event_type",
            "asset",
            "asset_contract",
            "from_account",
            "value"
        )]

In my understand, it will restrict me to have two entries where ALL 5 columns are equal. But to my surprise:

SELECT event_type, asset, asset_contract, from_account, value from events;
 event_type | asset | asset_contract | from_account |         value          
------------+-------+----------------+--------------+------------------------
          4 |       |              3 |            3 | 123.000000000000000000
          4 |       |              3 |            3 | 123.000000000000000000

Both are exactly the same.

And these are the restriction in Postgres

nftsport=# SELECT con.*
       FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class rel
                       ON rel.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
                       ON nsp.oid = connamespace
       WHERE nsp.nspname = 'public'
             AND rel.relname = 'events';
  oid  |                           conname                            | connamespace | contype | condeferrable | condeferred | convalidated | conrelid | contypid | conindid | conparentid | confrelid | confupdtype | confdelty
pe | confmatchtype | conislocal | coninhcount | connoinherit |   conkey    | confkey | conpfeqop | conppeqop | conffeqop | conexclop | conbin
-------+--------------------------------------------------------------+--------------+---------+---------------+-------------+--------------+----------+----------+----------+-------------+-----------+-------------+----------
---+---------------+------------+-------------+--------------+-------------+---------+-----------+-----------+-----------+-----------+--------
 16493 | events_pkey                                                  |         2200 | p       | f             | f           | t            |    16488 |        0 |    16492 |           0 |         0 |             |
   |               | t          |           0 | t            | {1}         |         |           |           |           |           |
 16495 | uc_events_event_type_asset_asset_contract_from_account_value |         2200 | u       | f             | f           | t            |    16488 |        0 |    16494 |           0 |         0 |             |
   |               | t          |           0 | t            | {2,3,4,6,9} |         |           |           |           |           |
 16496 | fk_events_assets_id_asset                                    |         2200 | f       | f             | f           | t            |    16488 |        0 |    16453 |           0 |     16447 | a           | a
   | s             | t          |           0 | t            | {3}         | {1}     | {96}      | {96}      | {96}      |           |
 16501 | fk_events_asset_contracts_id_asset_contract                  |         2200 | f       | f             | f           | t            |    16488 |        0 |    16429 |           0 |     16423 | a           | a
   | s             | t          |           0 | t            | {4}         | {1}     | {96}      | {96}      | {96}      |           |
 16506 | fk_events_accounts_id_from_account                           |         2200 | f       | f             | f           | t            |    16488 |        0 |    16415 |           0 |     16409 | a           | a
   | s             | t          |           0 | t            | {6}         | {1}     | {96}      | {96}      | {96}      |           |
 16511 | fk_events_accounts_id_to_account                             |         2200 | f       | f             | f           | t            |    16488 |        0 |    16415 |           0 |     16409 | a           | a
   | s             | t          |           0 | t            | {7}         | {1}     | {96}      | {96}      | {96}      |           |
(6 rows)

Created by these alembic lines

    sa.ForeignKeyConstraint(['asset'], ['assets.id'], name='fk_events_assets_id_asset'),
    sa.ForeignKeyConstraint(['asset_contract'], ['asset_contracts.id'], name='fk_events_asset_contracts_id_asset_contract'),
    sa.ForeignKeyConstraint(['from_account'], ['accounts.id'], name='fk_events_accounts_id_from_account'),
    sa.ForeignKeyConstraint(['to_account'], ['accounts.id'], name='fk_events_accounts_id_to_account'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('event_type', 'asset', 'asset_contract', 'from_account', 'value', name='uc_events_event_type_asset_asset_contract_from_account_value')

My questions are:

1. If some of the fields are nullable (in this case, all of them are nullable) the constraint breaks?
2. Is there any issues with constraints in Foreign Keys? Because 3 of them are FKs.
3. Any issues with Decimals and constraints? (Value is decimal).
4. Any issues with 5 fields in same constraint?

[igormorgado]

I found out the solution:

Postgres cannot handle unique constrains with Nullable fields since Null != Null. As explained ins this SO post.

A possible solution is to create partial indexes, without the nullable fields, but that is impractical with 4 them because I would need a 4! - 4 constraints.

Another alternative is to use COALESCE. I think I will find out another solution.