I find all caps identifiers wind up just looking like interchangeable blocks, where lowercase have word shapes. So all caps just slows down my reading.

It's really useful to know this when working with SQL interactively.

Specifically, if I'm banging out an ad-hoc query that no one will ever see, and I'm going to throw away, I don't worry about casing.

Otherwise, for me, all SQL that's checked in gets the commands in ALL CAPS.

My understanding is that the caps were syntax highlighting on monochrome screens; no longer needed with colour. Can't provide a reference, it's an old memory.

Note that case handling is a place where postgres (which folds to lowercase) violates the standard (which folds to uppercase).

This is mostly irrelevant since you really shouldn't be mixing quoted with unquoted identifiers, and introspection largely isn't standardized.

Any recommendation for a prettifier / SQL linter?

The uppercase is usually there to show people what's SQL vs what's custom to your database. In books it's usually set in courier.

I thought he was talking about psql's case sensitivity with table names, which is incredibly aggravating.

i agree; but i use caps in my codebase, and lowercase when testing things out manually, just for ease of typing.

> writing queries with keywords in all caps is an effort to increase legibility for visual pattern matching

Considering most programming languages do just fine without ALL CAPS KEYWORDS I'd say it's a strange effort. I wish SQL didn't insist on being different this way.

I agree with you on prettification though. As long as the repository chooses a prettifier you can view it your way then commit it their way. So that's my advice: always demand prettification for pull requests.

For checked-in SQL queries, we follow: https://www.sqlstyle.guide/

The combination of all caps keywords + following "the river" whitespace pattern dramatically improves readability in my opinion

The all caps syntax also helps queries stand out as distinct from typical source languages. It is often helpful, since SQL tends to end up in all sorts of applications.

You can convey more info with color. Any half decent editor can color your SQL.

All caps letters are more similar and harder to read.

Well, as long as you aren't imposing the noisy syntax into everybody by pushing the case-change back into the code...

But getting some editor that highlights the SQL will completely solve your issue.

> no longer needed with colour.

I think the increased legibility for visual pattern matching also makes SQL easier to read for many of the 350 million color blind people in the world.

what is your prettifier of choice for postgres?

Why don't they deprecate some of these features? If they're such easy stumbling blocks, seems like it makes sense to disable things like table inheritance in new schemas, and require some kind of arcane setting to re-enable them.

This reminds me of SQL Anti-patterns, which is a book that everyone who works with databases should read.

That was a fun read, thanks!

Made me reconsider a few habits I picked up from MySQL land

> If you have a user table with a non-nullable email column and a nullable username column, and a uniqueness constraint on something like (email, username), you'll be able to insert multiple identical emails with a null username into that table - because a null isn't equivalent to another null.

FWIW, since 15 postgres you can influence that behaviour with NULLS [NOT] DISTINCT for constraints and unique indexes.

https://www.postgresql.org/docs/devel/sql-createtable.html#S...

EDIT: Added link

I think this is a good pragmatic default. The use case for the alternative is much more rare.

No autovacuum? At reddit's scale I'm surprised you didn't run out of transaction IDs.

But the author did have a paragraph explaining, in general, what they mean.

And they're right! I've had to fix a few issues of this in a project I recently got moved to. There's almost never a reason to duplicate data.

The general rule is to normalize to the max, then denormalize till you get the performance that you need.

I guess this is targeted towards noobs, but the answer is pretty much always 3rd normal form if you are clicking this and are not sure.

Long before databases could even store structured JSON data, junior developers used to bikeshed viciously over the correct degree of normalization.

More experienced developers knew that the correct answer was to duplicate nothing (except for keys obviously) and then to denormalize only with extreme reluctance.

Then databases like mongo came along and encouraged those juniors by giving them something like a database, but where normalization was difficult/irrelevant. The result was a brief flowering of horrible database designs and unmaintainable crap towers.

Now the pendulum has swing back and people have rediscovered the virtues of a normalized database, but JSON columns provide an escape hatch where those bad practices can still flower.

There are two reasons to use a jsonb column:

1. To store JSON. There's a pattern where when your webserver calls into some third-party API, you store the raw API response in a JSONB column, and then process the response from there. This gives you an auditable paper trail if you need to debug issues coming from that 3rd-party API.

2. To store sum types. SQL not supporting sum types is arguably the biggest deficiency when modelling data in SQL databases. There are several workarounds - one of them being "just chuck it in a JSONB column and validate it in the application" - but none of the workarounds is particularly great.

Even if you care about it, you will still often wind up with a junk drawer of JSONB. I don't really see it as a problem unless people are writing bad queries against it instead of lifting values out of the JSONB into their own columns, etc.

Most developers using these kinds of tools these days are actually building their own database management systems, just outsourcing the persistence to another DMBS, so there isn't a strong imperative to think about good design so long as it successfully satisfies the persistence need.

Whether we actually should be building DMBSes on top of DMBSes is questionable, but is the current state of affairs regardless.

This needs working schema migration process, including ability to undo schema change if the new column tanks the performance or breaks stuff.

If there are CLI tools involved, you also need to ensure you can handle some downtime, or do synchronized version update across company, or support both old and new schemas for a while.

If a database is not part of team's primary product all of this could be missing.

you mean this paper? https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

Right--it makes sense and shouldn't be changed, but it's a bit unintuitive for newcomers.

I got nerd-sniped on this, because I actually hadn't heard that before and would be horrified if it were true. It took some non-trivial digging to even get down into the "well, what does foo->>'bar' even map to in C?" level. I for sure am not claiming certainty, but based merely on "getIthJsonbValueFromContainer" <https://sourcegraph.com/github.com/postgres/postgres@REL_17_...> it does seem that they do palloc copies for at least some of the JSONB calls

You can also use #>> operator for that:

    json_col #>> '{foo,bar,baz}'

With the caveat that you probably shouldn't listen to me (or anyone else on here) since you are the only one who knows how much pain each choice will be ...

I think that given that you are not really dealing with structured data - you've said that different sites have different structures, and I assume even with processing, you may not be able to generate identical metadata structures from each entry.

I think I would go for one column of XML, plus maybe another column that holds a parsed data structure that represents the result of your processing (basically a cache holding the post-processed version of each site). Hopefully that could be re-evaluated by whatever language (Python?) you are using for your application. That way you don't have to do the full parsing each time you want to examine the entry, but you have access to something that can quickly give you whatever metadata is associated with it, but which doesn't tie you to the rigid structure of a table based database.

Once you know what you are really doing with the data, then you could add additional metadata columns that are more rigid, and which can be queried directly in SQL as you identify patterns that are useful for performance.

Just shooting without knowing your real needs - take this with a grain of salt.

Store some parsed representation that makes it easier for you to work with (probably normalized). Keep an archive of raw data somewhere. That may be another column, table or even S3 bucket. Don't worry about schema changes but you need to not lose the original data. There are some pitfalls to schema migrations. But the schema should be the representation that works for you _at the moment_, otherwise it'll slow you down.

If you’re going to be parsing it anyways, and there’s the possibility of actually doing something with that parsed info beyond just reprinting it, then storing the post-parse results is probably better. Especially if you’re only going to need a reduced subset of the information and storage matters.

If you’re just reprinting — you’re parsing only for the sake of rendering logic — then storing the parse-result is probably just extra unnecessary work.

Also if storage isn’t a concern, then I like using the database as intermediate storage for the pipeline. Grab the RSS, stuff it in the DB as-is. Take it out of the DB, parse, store the parse results. Etc. You’ll have to do this anyways if you’re going to end up with a processing queue (and you can use PG as a simple queue.. SELECT…FOR UPDATE), but it’s nice-to-have if your pipeline is going to eventually change as well — and you’re able to reprocess old items

> Store it in multiple columns after processing it and it may require different logic for different websites + changing your overall python processing logic requires a lot of thinking on how it might affect some source.

Don’t you need to deal with this problem regardless? Ideally you’ll find a common subset you actually care about and your app-logic will look like website —> website handler -> extract data subset —> add to common structure —> render common structure

Even if you don’t use a database at all your app-code will need to figure out the data normalization

With all RDBMSes the rule is "normalize to the max, then denormalize till you get the performance that you need".

I love the diagrams from the SQLite documentation: https://www.sqlite.org/syntaxdiagrams.html

I'm looking at the postgres docs now, and they certainly have the arcane formal syntax definitions, but they also have plenty of useful examples https://www.postgresql.org/docs/current/sql-altertable.html#...

FWIW square brackets indicate an optional parameter and the pipe inside indicates all the parameters available; but I agree, I don't particularly care for that notation and prefer actual running SQL examples.

PostgreSQL Administration Cookbook series served me well

https://www.oreilly.com/library/view/postgresql-16-administr...

Sometimes I find it annoying but mostly it works well for me. I've come to embrace the find feature and visually scanning over any parenthetical stuff.

The alternative is they have to break it up into several quasi examples, each with their own optional modifiers.

(author here) hmm it seems to work on my device. Are you scrolling within the code block?

On other hand it's a mere blog post. You should not be bothered by TCP cost. But reliable data storage and reliability/restore (in case of backups) cost is a concern.

(author here) hmm it seems to work on my device. what happens when you try to scroll?

lol this is the first blog post i've written in 4 years and i didn't even post it here so I think you're imagining things

People have too much faith in LLM's at the moment. This might be able to give some insights, but if you're trying to LEARN something in detail, such as the pros and cons on a particular custom query on your unuiqe-ish schema.. The LLM will be prone to making stuff up by design, it wasn't trained on YOUR data..

It will give some good basic results and probably some functional queries, but it misses the mark with the finer points of optimization etc. If you don't know how to properly build the queries, go learn it properly with confidence that what you're learning is actually reliable.

This is similar to what I’ve done with my sass where most of the logic lives in Postgres. I went through much of the logic and refactored it with GPT a while back and improved it significantly. It’s soooo good with SQL. Like insanely good. And these days I'm using Claude for all things code, which is significantly better than GPT.

then have corporate knock on your teams window asking why all it's IP ended up in a Chinese competitor?