Is it safe to assume most/all of these best practices are advisable across all flavors of SQL? I can imagine it getting overwhelming when interviewing for jobs that nitpick specific code using specific implementations of SQL
No, many of these are specific to Postgres. I don't know of any other database that has Postgres's rewrite system called Rules. Also other databases handle mixed-case identifiers differently. PostgreSQL is one of the databases that adheres the SQL standard most closely, which makes you think that many of these things would apply to other databases. But that's just it. Databases follow the standard willy nilly, so that may be a reason that in fact Postgres is different from them.
And yet, yes! The best practice, obviously, is to always use Postgres. So --- what other flavors of SQL?
More seriously, I have read that SQLite is often similar to Postgres. Its main author actually has said in a talk that when making decisions he asks, "What would Postgres do?" Also I have heard that Postgres is similar to Oracle, so much that you could move from Oracle to Postgres with a thin layer of compatibility. In fact I think that's what https://www.enterprisedb.com/ is.
The performance characteristics of `char` vs. `varchar` vs. `text` certainly depends on the implementation details of each RDBMS. PostgreSQL recommends that you use `text` for basically every textual column, but other databases can be very different. Some won't even allow you to add an index to a `text` column.
The various timestamp-related data types are also very PostgreSQL-specific.
As a SQL Server developer, a lot of the advice here applies to SQL Server as well (or at least advice that isn't specific to PG tools, such as "Don't use psql -W"). However, the biggest issue with applying these practices to SQL Server is that terms that seem similar may in fact be radically different. For example, the `timestamp` data type in SQL Server isn't at all what you'd think, and `text` is an old deprecated type with poor performance.
So I think there's some good high-level advice here that applies across multiple flavors, but the specifics are really only applicable to PostgreSQL.
> 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.
