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 <firstname.lastname@example.org>
Reviewed-by: Pavel Borisov <email@example.com>
Continue reading Waiting for PostgreSQL 15 – Add UNIQUE null treatment option
In this, hopefully 2nd to last, post in the series, I will cover the rest of usually happening operations that you can see in your explain outputs.
Continue reading Explaining the unexplainable – part 4
On 3rd of November, Heikki Linnakangas committed patch:
Support range DATA types.
Selectivity estimation functions are missing FOR SOME range TYPE operators,
which IS a TODO.
Continue reading Waiting for 9.2 – Range data types
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.
articles AS a
JOIN articles_in_categories AS aic ON a.id = aic.article_id
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
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.
Continue reading Waiting for 8.5 – deferrable uniqueness
Every so often you need to get list of unique elements in some column. The standard way to do it is:
SELECT DISTINCT COLUMN FROM TABLE;
SELECT COLUMN FROM TABLE GROUP BY COLUMN;
The only problem is that it's slow – as it has to seq scan whole table. Can it be done faster?
Continue reading Getting list of unique elements