big_whack

Does email work on iOS?

This is one of the ostensible guiding principles of the Washington Post: "The newspaper’s duty is to its readers and to the public at large, and not to the private interests of its owners."

Sorry, but someone who is averse to joins is not a non-expert in SQL, they are a total novice. The answer is like any other programming language. You simply must learn the language fundamentals in order to use it.

It's not really a problem of there being combinatorially many ways to join table A to table B, but rather that unless the join is fully-specified those ways will mostly produce different results. Your tool would need to sniff out these ambiguous cases and either fail or prompt the user to specify what they mean. In either case the user isn't saved from understanding joins.

Once you have SQL, you have datalog. Once you have datalog, you have SQL. The problem isn't the target, it is getting sufficiently rigorous and structured output from the LLM to target anything.

I think the problem is the quirkiness on the English side, not the SQL side. You could translate datalog to SQL or vice versa, but understanding intention from arbitrary english is much harder. And often query results must be 100% accurate and reliable.

You seem to think you're disagreeing with me but afaict you're just demonstrating my point, unless your point is just about how (int, int) will get packed. That's what I meant about the column order of indexes. If you have two ints and a bigint, but you need to index it like (int, bigint, int), then you aren't gaining anything there either.

As your example shows, there is no benefit in index size (e.g for supporting FKs) in going from int to bigint for a single key. You end up with the same index size no matter what, not twice the size which was what I took your original post to mean.

Postgres pads tuples to 8 bytes alignment so an indexed single-column int takes the same space as an indexed bigint. That's the usual case for indexed foreign keys.

Differences can appear in multicolumn indexes because two ints takes 8 bytes while two bigints takes 16, however the right layout of columns for an index is not always the layout that minimizes padding.

Reconfiguring tables to use a different kind of unique ID (primary key in this context) can be a much bigger pain than an ordinary column rename if it is in use by foreign key constraints.

If you use a surrogate key, you still need a unique constraint in the table (probably the same columns you would otherwise call your natural PK). If your unique constraint isn't sufficient to capture the difference you mention, you need to add more columns.

However, that's strictly better than the natural PK situation, where you would need to not only add new columns to the key, but also add those columns to all referencing tables.

My reading dropped off for years until I got a kindle. The kindle made reading way more convenient and portable. Totally changed my life.