When picking a primary key for a table, you might pick a UUID, serial, or identity column. Laurenz Albe says you should default to using a bigint sequence, and explains why. (Note that identity columns are also a form of sequence, and they’re okay too.) He also explains when it’s fine to use UUIDs instead.
When checking for existence of rows, you can use EXISTS, NOT IN, <> ALL, LEFT JOIN, or EXCEPT. The performance of each option depends on how much of each table matches what you’re looking for, as Francesco Tisiot explains.
Who uses PostgreSQL, where, and how? The State of PostgreSQL 2023 survey results are out with about 900 responses. Interesting notes: 48% of the responses came from EMEA, users with 6+ years experience are more likely to use shell scripts, and psql was the most common tool to connect.
Upcoming local events: Brussels on Feb 2-4, Bengaluru Feb 28-March 1, Oslo March 12, Pasadena March 14-15, Paris March 14-15, Chicago on April 26. Looking in the rear view mirror, Andreas Scherbaum wrote up a review of PGConf.EU.
Technique of the Week: Default Identities
If you’re designing a table where you might need to backfill existing data with its own IDs:
|
1 2 3 |
CREATE TABLE factories ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ); |
The “generated by default” means that if the insert statement doesn’t include the id column, an identity will be generated for you automatically. However, if you DO specify an id, you can insert it.
This way, you can backfill existing data – but of course, it’s up to you to avoid inserting duplicates when you do the inserts. You look smart, though. You got this.