Recently, on irc, there were couple of cases where someone wanted to use uuid as datatype for their primary key.
I opposed, and tried to explain, but IRC doesn't really allow for longer texts, so figured I'll write a blogpost.
On 3rd of April 2019, Alvaro Herrera committed patch:
Support foreign keys that reference partitioned tables Previously, while primary keys could be made on partitioned tables, it was not possible to define foreign keys that reference those primary keys. Now it is possible to do that. Author: Álvaro Herrera Discussion: https://firstname.lastname@example.org
Couple of people asked, on irc, about how to generate unique, short, ids for rows.
Since I wrote code for this for explain.depesz.com, I figured that instead of pointing to sources, and letting everyone to extract the functions, I will, instead, write a set of functions that what is needed in reusable way.
Yesterday I wrote about selects on int4 vs. texts.
One of the comments that caught my attention was question about index creation time. So, let's see…
There is this idea that normal form in databases require you to use integer, auto incrementing, primary keys.
One of the points that proponents of surrogate keys (i.e. those based on integer and sequences) raise is that comparing integers is faster than comparing texts. So,
SELECT * FROM users WHERE id = 123
is faster than
SELECT * FROM users WHERE username = 'depesz'
On 25th of January, Tom Lane committed patch:
Implement ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX. This feature allows a UNIQUE OR pkey CONSTRAINT TO be created USING an already-existing UNIQUE INDEX. While the CONSTRAINT isn't very functionally different from the bare index, it's nice TO be able TO do that FOR documentation purposes. The main advantage OVER just issuing a plain ALTER TABLE ADD UNIQUE/PRIMARY KEY IS that the INDEX can be created WITH CREATE INDEX CONCURRENTLY, so that there IS NOT a long INTERVAL WHERE the TABLE IS locked against updates. ON the way, refactor SOME OF the code IN DefineIndex() AND index_create() so that we don't have to pass through those functions in order to create the index constraint's catalog entries. Also, IN parse_utilcmd.c, pass around the ParseState pointer IN struct CreateStmtContext TO save ON notation, AND ADD error location pointers TO SOME error reports that didn't have one before. Gurjeet Singh, reviewed by Steve Singer and Tom Lane
Yesterday (August, 7th), Tom Lane committed:
Log Message: ----------- Recognize functional dependency on primary keys. This allows a table's other columns to be referenced without listing them in GROUP BY, so long as the primary key column(s) are listed in GROUP BY. Eventually we should also allow functional dependency on a UNIQUE constraint when the columns are marked NOT NULL, but that has to wait until NOT NULL constraints are represented in pg_constraint, because we need to have pg_constraint OIDs for all the conditions needed to ensure functional dependency. Peter Eisentraut, reviewed by Alex Hunsaker and Tom Lane
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