Tag: unique

Waiting for PostgreSQL 15 – Add UNIQUE null treatment option

On 3rd of February 2022, Peter Eisentraut committed patch:

Add UNIQUE null treatment option 
 
The SQL standard has been ambiguous about whether null values in
unique constraints should be considered equal or not.  Different
implementations have different behaviors.  In the SQL:202x draft, this
has been formalized by making this implementation-defined and adding
an option on unique constraint definitions UNIQUE [ NULLS [NOT]
DISTINCT ] to choose a behavior explicitly.
 
This patch adds this option to PostgreSQL.  The default behavior
remains UNIQUE NULLS DISTINCT.  Making this happen in the btree code
is pretty easy; most of the patch is just to carry the flag around to
all the places that need it.
 
The CREATE UNIQUE INDEX syntax extension is not from the standard,
it's my own invention.
 
I named all the internal flags, catalog columns, etc. in the negative
("nulls not distinct") so that the default PostgreSQL behavior is the
default if the flag is false.
 
Reviewed-by: Maxim Orlov <orlovmg@gmail.com>
Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com

Continue reading Waiting for PostgreSQL 15 – Add UNIQUE null treatment option

Getting unique elements

Let's assume you have some simple database with “articles" – each article can be in many “categories". And now you want to get list of all articles in given set of categories.

Standard approach:

SELECT
    a.*
FROM
    articles AS a
    JOIN articles_in_categories AS aic ON a.id = aic.article_id
WHERE
    aic.category_id IN (14,62,70,53,138)

Will return duplicated article data if given article is in more than one from listed categories. How to remove redundant rows?

Continue reading Getting unique elements

Waiting for 8.5 – better messages for unique violation

On 1st of August Tom Lane committed patch from Itagaki Takahiro:

Improve unique-constraint-violation error messages to include the exact
values being complained of.
 
In passing, also remove the arbitrary length limitation in the similar
error detail message for foreign key violations.
 
Itagaki Takahiro

and later added an extension to it:

Department of second thoughts: let's show the exact key during unique index
build failures, too.  Refactor a bit more since that error message isn't
spelled the same.

Continue reading Waiting for 8.5 – better messages for unique violation

Waiting for 8.5 – deferrable uniqueness

On 29th of July ( I know, there is a long backlog in waiting for 8.5 series. I'm working on it :), Tom Lane committed patch by Dean Rasheed:

Support deferrable uniqueness constraints.
 
The current implementation fires an AFTER ROW trigger for each tuple that
looks like it might be non-unique according to the index contents at the
time of insertion.  This works well as long as there aren't many conflicts,
but won't scale to massive unique-key reassignments.  Improving that case
is a TODO item.
 
Dean Rasheed

Continue reading Waiting for 8.5 – deferrable uniqueness