Suggestion: Unique should allow empty values

[benallfree]

I noticed that if Unique is enabled, multiple blank values are disallowed. This essentially means Unique implies Required. I think it should work like this:

Required: no blanks allowed
Unique: Unique values OR blanks allowed
Unique + Required: unique values only (no blanks)

In my use case, I want each record in my collection to be optionally associated with one unique uid. Is there a way to express this type of constraint?

[ganigeorgiev]

This is a little tricky because we don't store null values and use zero-defaults. I'll consider updating the "Unique" constraint behavior at least for string based values and exclude empty strings from the validation while working on the refactoring. But I'll think more on it with a clear head tomorrow.

A somewhat hacky workaround would be to create a partial unique index via sql for your column manually (or via migration):

app.Dao().DB().NewQuery(`
    CREATE UNIQUE INDEX your_unique_index_name ON YOUR_COLLECTION_NAME (uid) WHERE uid != '';
`).Execute();

[benallfree]

Hmm I see what you mean. I ran into the same issue with storing port numbers for PocketHost. I wanted it to be unique but integer fields appear to default to 0, which introduces duplicates. There is currently no way to express "unique except this magic value" except using the migration you show above. Maybe allowing a magic value exclusion in the UI would work?

[benallfree]

Ran into this again, needing unique or NULL/blank. Any new thoughts?

[ganigeorgiev]

I'm not sure I follow. I thought this was already resolved with the recent Indexes UI.
Note that PocketBase doesn't store null values (with exception of the json type field). If you want the unique constraint to ignore empty values use a partial index as shown above, aka. specifying a WHERE clause that will skip the empty values from the unique check. The index can be specified from the Admin UI > Edit collections > "Unique constraints and indexes" section.

[benallfree]

Whoa lol I never realized that was editable, I thought it was just showing a preview 😆

[aleBranching]

Leaving this here in case anyone runs into this problem

CREATE UNIQUE INDEX `idx_vXcPPha` ON `main_table` (`collumn_name`) WHERE `collumn_name` != ''

This works in the Unique constraints and indexes section of the UI. In case anyone runs into the problem make sure that the collumn_name in the where clause is surrounded by backticks and not something else