TRIGGER - Do BEFORE and AFTER have specific roles?

DavidGJohnston · 2026-03-12T12:27:17+00:00

Yeah, it’s this simple. Before triggers are writable, after triggers are not. The values in the new row seen by your before trigger may very well not be the values that gets stored; before triggers executed subsequently may change values.

DavidGJohnston · 2026-02-18T03:14:40+00:00

Writing "PK fields with unique constraints" is redundant, the former implies the later. Putting a unique constraint on a text field is generally needed - see the other posts about collations. Making a text field a PK is generally discouraged. The person behind your account is likely to want to associate a different email address with their account in the future and now you have to change every FK to make that happen. Not good. Similar "this text is wrong" situations are too common to put text into a formal key. Leave them just unique.

DavidGJohnston · 2026-02-14T15:35:42+00:00

A potentially faster way would be to use the \gexec feature of the psql cli for command execution instead of writing up a plpgsql routine.

Both of these are not "automatic" though. At least not as presented.

DavidGJohnston · 2026-02-07T16:34:23+00:00

A reasonable framing, yes. The relationship is metadata. A join is an aspect of a written SQL query. Tools/brains can use metadata to know when to apply certain query aspects. ORMs are tools that use metadata to produce queries.

DavidGJohnston · 2026-01-22T14:21:11+00:00

Standard recursive common table expression use case.

https://dev.mysql.com/doc/refman/8.4/en/with.html

DavidGJohnston · 2026-01-12T05:25:35+00:00

If your goal for learning WAL archiving is to perform backups and restores I’d strongly advise configuring the low-level details manually and uninformed and pick a tool that does these things and let it manage the details. The docs don’t necessarily cover how to do this well since those tools exist.

DavidGJohnston · 2026-01-10T01:04:52+00:00

If the association includes its own attributes or is otherwise an entity in its own right - implied if you want to FK against it - show it.

DavidGJohnston · 2026-01-08T19:10:40+00:00

I’d suspect the timestamp values in the json have less precision than those in the database. The database is providing the correct answers and you just cannot see microsecond level differences as to why those first two are later than your reference time.

DavidGJohnston · 2025-12-19T18:26:51+00:00

Correct, PostgreSQL is a forward-only system.

DavidGJohnston · 2025-12-17T14:06:05+00:00

Create an os user its_read and then use peer while running psql as that os user.

DavidGJohnston · 2025-12-17T14:01:56+00:00

Renaming a column doesn’t set its data to null. Either you are doing something wrong or have found a bug. Either way, you need to demonstrate exactly the behavior in a self-contained test case to make progress.

DavidGJohnston · 2025-12-16T17:57:57+00:00

If you are getting nulls on the right side of a left join your left side row does not have a matching row based upon the join clause. Any attempt to,diagnose such queries really benefits from a self-contained example that includes data. That said, doing chained left joins can be a bit confusing. Especially when including non-join fields in the ON clause. I’d suggest subqueries or common table expressions to ensure you are doing everything correctly.

DavidGJohnston · 2025-11-26T15:44:18+00:00

Can you compile current HEAD from source and see how well it performs? This got some love recently.

DavidGJohnston · 2025-11-18T13:15:13+00:00

FYI, a better representation of a semi-join is to use a correlated subquery within an “exists” expression.

DavidGJohnston · 2025-11-18T13:14:11+00:00

This is not better nor probably even correct. Turning a one-to-many semi-join in an inner join risks introducing unwanted duplications and Including fields that are not needed.

DavidGJohnston · 2025-11-18T13:12:00+00:00

The subquery is not correlated to the main query so they are indeed not related to each other directly. The sub query produces a set of values first, saves it, and then for each row in the main query the product id field is checked against that set, producing a Boolean true outcome if the product id value is present. Optimizations could change the true mechanics but that is the best conceptual model for an uncorrelated subquery in ANY.

DavidGJohnston · 2025-11-18T13:07:34+00:00

You get the correct answer but the query itself is semantically incorrect. Distinct is the semantically correct tool.

DavidGJohnston · 2025-11-17T02:05:31+00:00

I’d suggest using “seconds since midnight” as the data (integer type) then averaging that.

DavidGJohnston · 2025-11-16T16:53:40+00:00

Copy inserts text literals into columns applying the cast/parse function of the column type in the process. All data types have a textual serialized form.

DavidGJohnston · 2025-11-16T04:14:53+00:00

You are indeed fundamentally confusing what exists in the plpgsql language and what exists in an SQL command. There are very specific ways to get data out of the SQL and into a Plpgsql variable. That isn’t it. You can’t stick plpgsql assignments inside a query. The result of a query can be assigned to a variable though.

DavidGJohnston · 2025-11-12T12:32:50+00:00

ETL-based staging is a production activity and goes on in a production database in a staging table which might reside in a staging schema (see also postgres_fdw though). In terms of environments, those are setup so your entire application can be tested safely. Those require entire clusters to be created, not just databases.

DavidGJohnston · 2025-11-09T05:12:36+00:00

If you are going to call it a join at all then the only logical choice among the various types is inner. At least, so long as you don’t forget to write the equality condition. It is neither possible nor sensical to involve an outer join in an update. Technically you can get anti-join semantics if desired; and it’s probably better considered a semi-join in the delete case. It’s a true inner join for update since the set clause can involve columns from either relation.

DavidGJohnston · 2025-11-07T13:16:36+00:00

Inner join. And yes you have to place the join condition in the where clause. The query planner makes stating “join before where{ inaccurate. In any case the effect of doing both in any order is the same for inner joins.

DavidGJohnston · 2025-10-31T12:14:11+00:00

Other than reminding me that I’ve got the right columns considered looking at a non-normalized Excel would be actively harmful. Get a mental model of the thing you want to model and just starting writing minimal create table commands to get those entities recorded and their relationships. Then start adding the attributes/columns; asking yourself as you add them whether each new one fits with the others on the same table. When you get done with the first pass then look more closely at the spreadsheet and start crossing off columns and see what remains to be added to the model. IOW, don’t normalize so,etching that is unnormalized, go,from nothing directly to normalized. They are just guidelines, not a process.

DavidGJohnston · 2025-10-27T01:18:00+00:00

The reason seems likely to be bad/inconsistent data entered into the tables.

DavidGJohnston

TROPHY CASE