> Is it safe to assume most/all of these best practices are advisable across all flavors of SQL?

No, anything like this is pretty implementation specific.

In "Table Inheritance" the article is referring to a postgres specific feature which implements a form of the data pattern by the same name. The pattern is not unique to circumstances where you are working with an ORM. So if your data is conveniently modelled using such a pattern by all means use it, in whatever DBMS you are using, but perhaps not with this postgres specific feature.

In "Not In" the fragment "does not optimize very well" is dependent on the query planner. Other DBMS engines may not optimise this well, but you might want to run performance tests on realistic data to check. The possible confusion when the list contains one or more NULLs is present in anything standard because that is a property of NULL handling.

The comments about case is implementation specific too. In SQL Server for instance it depends upon the collation set for the database, with names being consistently case-sensitive or consistently not (usually the latter) depending on the collation setting and irrespective of any name escaping.

The point about using BETWEEN with date+time values is correct generally, and I have seen it cause confusion a number of times. Though I've seen similar confusion with other comparisons based on date for date+time types too so this isn't specific to BETWEEN.

The comments about time types with timezone information are general to databases that support such types (though not all common DBs do). My advice would instead be to always store times in UTC and translate to the local timezone elsewhere (though this can have its own problems so others may disagree).

String data types and their performance characteristics vary a lot between databases. In SQL Server VARCHAR(MAX) value (the equivalent to postgres' VARCHAR-with-no-length or TEXT) will usually be stored off-page which can have negative performance implications, and can't be indexed, so you wan to use limited length text columns unless you definitely need long values. What SQL Server calls TEXT is essentially a BLOB type and long-since deprecated anyway.

> I can imagine it getting overwhelming when interviewing for jobs that nitpick specific code using specific implementations of SQL

If the job is specifically working with that flavour of database then this sort of detail might come up, there is little-to-nothing you as an interviewee can do about that other than swot-up beforehand if you are used to a different engine. Though such detailed nitpicking probably doesn't belong in a good interview process anyway, at least not until late stages when there are too many otherwise excellent candidates and they need something/anything to differentiate between them to make the choice easier!

If you are interviewing for a job that involves coding for multiple SQL database flavours then you will need to at least appreciate that the characteristics and behaviours of data types vary between engines, even if you don't know all the specifics off the top of your head. Other oddities you might come across include Oracle storing empty strings as NULL (with the possible problems at retrieval time this implies), SQL server ignoring trailing spaces in strings most of the time, and so forth.