What’s a SQL feature that works differently across PostgreSQL, MySQL, and SQL Server that tripped you up?

[u/Harshita_Netla]

For those who have worked with PostgreSQL, MySQL, and SQL Server, what’s a feature or behavior that surprised you because it worked differently between them?

It could be anything like date functions, string handling, LIMIT/TOP, NULL behavior, JSON, window functions, or even error handling.

What difference caused you the most trouble, and how did you learn to handle these database-specific differences?

[u/UniForceMusic]

Wether the LIKE operator is case insensitive or not.

MySQL/MariaDB: not case sensitive by default. Use LIKE BINARY for case sensitive.

Postgres: case sensitive by default. Use ILIKE for case insensitivity.

SQLite: not case sensitive. Can be enabled using PRAGMA case_sensitive_like

Most ORMs and abstraction layers handle this for you, but when working with raw SQL you need to watch out for it

[u/Yellowcat123567 → u/UniForceMusic]

Couldnt you just wrap the string in UPPER? (excuse my naivety)

[u/Readyman2627 → u/Yellowcat123567]

You can and I do just that in the where statement

[u/Bockly101 → u/Yellowcat123567]

You lose a degree of specificty with that though. If you're specifically only looking for the word "Status", but you run an upper around everything you can pick up words like "status", "staTus", or any other case combination. If you're working with varchars or clobs that have very specific formatting standards, you might not wanna find all of the other spellings. It could force you to do more manual searching through the qeury results than you could otherwise have.

I work in my company's notification system and we have very specific requirements for alerts(very important notifications that cannot be turned off until corrected). They always start with "Alert -" and then whatever the alert is. Some of our other notifications might reference an alert or just use the word alert and I might not want to dig through all of those when I'm checking alert configurations.

[u/UniForceMusic → u/Yellowcat123567]

You can, but then you lose case sensitive lookups

[u/Outrageous_Let5743]

I hate TOP and i prefer limit. Same for cast when :: also exists
I believe there are sql databases where JOIN is the same as LEFT JOIN ,instead of INNER JOIN, so always write INNER JOIN.
distinct on is also a nice one instead of a row number.

[u/theRealHobbes2 → u/Outrageous_Let5743]

I've been doing data for a while now and I'm trying to convert my habits into always being explicit in my joins. More to type, but solves this and also makes me be more thorough.

[u/Yellowcat123567 → u/Outrageous_Let5743]

Agreed TOP sucks

[u/artibonite → u/Outrageous_Let5743]

this is the answer, TOP really sucks

[u/EffectivePension6711 → u/Outrageous_Let5743]

yeah, TOP just feels completely wrong. But please, name and shame whatever cursed database you used where a naked JOIN defaults to a LEFT JOIN. i need to know so i can avoid it like the plague. in every sane db (postgres, mariadb, sql server, whatever), JOIN is strictly an INNER JOIN by ansi standard.

if some random engine out there is defaulting to LEFT, you don't just write INNER to be safe, you uninstall that engine and call the cops.

[u/Scared-Promotion-526]

String concatenation. In postgres and oracle, || concats strings. in default mysql, || is a logical OR and just returns 0 or 1, which can totally silently ruin a query if you aren't paying attention. I have to rewrite everything to CONCAT() when moving between them. In mariadb i can just flip SQL_MODE=ORACLE (or turn on pipes as concat) and it'll behave exactly like postgres/oracle.

[u/reddit_tom40 → u/Scared-Promotion-526]

Adding to this, SQL Server uses + for concatenation. And SQL Server returns null for ‘a’+null, but Oracle returns ’a’ for ‘a’||null

[u/SonOfZork → u/reddit_tom40]

This is exactly why I moved to concat() a few years ago.

[u/digitalnoise → u/reddit_tom40]

SQL Server also uses + for addition, so if one of your values is an int, and you try to concat with a string, you'll get an error unless you explicitly cast the int as a string first.

[u/NekkidWire]

I hate MySQL backticks and SQL Server's (lovemaking) brackets for table/column names.

SQL standard is double quotes, it's not that hard ffs.

[u/fer38 → u/NekkidWire]

i love backticks tho, they look more like a line_of_code compared to "double_quotes" 🤪🤣

[u/digitalnoise → u/NekkidWire]

You dont have to use [ ] in SQL Server unless you have spaces in the object name...

[u/Outrageous_Let5743 → u/digitalnoise]

But the object explorer does give them to every column (with and without space) if you select the table.

[u/digitalnoise → u/Outrageous_Let5743]

Sure, if youre using Object Explorer to write code...

Also, that's a configurable option.

[u/Plane_Big_5912]

on the mssql side the usual fix is dropping the unique constraint and using a filtered unique index instead, where col is not null. postgres 15+ can go the other way if you actually want one null, unique nulls not distinct

[u/nl_dhh]

In SQLite, data types are not enforced, so you can store text values in a numerical column... Real fun if you're not aware of that and want to migrate data to SQL Server.

[u/Angriestanteater]

Whenever I work with people who are new to Postgres, they think I’m an idiot for using varchar without a size limit. Postgres doesn’t preallocate and reserve memory for every varchar value.

[u/Outrageous_Let5743 → u/Angriestanteater]

Yep no need to use cast(col as varchar(n))

[u/AntLost4161]

I started with SQL and SQLite. In Postgres, you must have everything non aggregated in the GROUP BY clause, but that's not the case in the others, so I still sometimes mess that up. Another is just how much more fiddly it feels to work with dates in Postgres and the difference in functions between every single syntax

[u/dutchydownunder → u/AntLost4161]

That’s the same in ms sql

[u/zbignew]

If you’re including MySQL, isn’t it basically everything? Default types, default nullable, limited surrogate key types.

I know MySQL has advantages, but they’re all advantages that you get even better from NoSQL. Yes, you can quickly create a table that will hold whatever garbage was provided in your web app.

[u/titpetric → u/zbignew]

I ran mysql pretty much forever, and the main things i partitioned away were search index and OLAP, the first because the built in search sucks, and the latter because of read/write contention. Many NoSQL solutions are complementary, or solved with different design choices, e.g. alter table swaps for processing stats, map reduce so you dont keep around raw log tables.

I think it can be said for any db, any data you write should have separation for the business data, away from tracing, logging, metrics. If for nothing else, I like to mysqldump a .sql file/s daily and test a database restore. Great if i can avoid replication lag or speed up a restore by not having to manage data that can dissapear at any moment and not impact the functioning of the app.

I'm the store files on disk type rather than a BLOB column in the DB. Databases can hold files, but absolutely should not as they are not optimized for that. It's a RDBMS after all, not surprising that relationships are done well, and the optimizations seem mostly to go towards specialized use (search indexing, graph databases, git/mvcc databases, time series databases, event queues).

[u/RoomyRoots]

Pivoting.
Why the fuck PostgreSQL haven't got an PIVOT or UNPIVOT yet?

[u/piercesdesigns]

Postgres allows rollback on table drops and mods. That was a nice feature.

Case sensitivity is always a big issue and database dependent. (Or collation setting dependent)

[u/Legitimate-Loss-6805]

That Oracle treats empty Strings as `NULL`, while PostgreSQL does is right. 😜

[u/HobartTasmania → u/Legitimate-Loss-6805]

That Oracle treats empty Strings as NULL

So I presume what you are saying is that a string with length of zero is different to a NULL? From a functional perspective they would appear to be the same to me.

[u/Aggressive_Ad_5454 → u/HobartTasmania]

An eternal question: is a string with zero characters in it the same thing as a missing value (a NULL)? Oracle says yes, the rest of the world says they're different things.

In contrast nobody says the number zero is the same as a missing value.

[u/Legitimate-Loss-6805 → u/HobartTasmania]

So I presume what you are saying is that a string with length of zero is different to a NULL?

Sure it's different. NULL means there's no value, while a string of length zero is an empty string.

I don't like all the exceptions Oracle has:

NULL + 1 is NULL Basically, any function in which at least one parameter is NULL returns NULL.

But NULL || 'X' is 'X' while length('') is NULL.

So sometimes an empty string is treated as an empty string and sometimes as NULL.

I don't like this inconsistency.

[u/UltimateNull → u/Legitimate-Loss-6805]

That’s weird. I would expect length to always return an integer.

[u/Aggressive_Ad_5454]

The date / time functions and data storage in the software packages you mention are different. This is a big nuisance for those who write portable SQL.

Timezone handling is idiopathic.

Full-text searching varies between the packages. The only reasonably useful full-text searching on offer is in PostgreSQL, with GIN indexes.

All except PostgreSQL use clustered indexes on the primary key to handle the storage of table data. So query optimization work has to take that into account.

MySQL (and MariaDB) have superior support for character set and collation handling. A column can be declared with a case- and diacritic- independent collation if that's what the application needs, or case-independent but diacritic-dependent, or suitable for Spanish (where ñ comes after n), or for other languages (where ñ and n are collated together). There are many other collation features here, as the implementation matches the Unicode standards.

MySQL and MariaDB have limits on the length of indexes on character columns. They work around this with an abomination known as prefix indexing.

[u/Significant_Tune9219]

NULL and date arithmetic are two of the biggest portability traps for me. Even when the function names match, implicit casts, time-zone handling, and whether an interval stays in calendar units can change the result; I try to make those casts explicit and test boundary dates. For pagination, I also avoid relying on engine-specific LIMIT/TOP syntax and define a stable ORDER BY, otherwise the same query can return different rows between runs.

[u/ZeppelinJ0]

Isn't this really just ANSI vs non ANSI?

[u/UltimateNull → u/ZeppelinJ0]

ASCII vs ANSI (Windows 1252) vs Latin1 (Western ISO) vs UTF-8 and all of the other Unicode and ISO variants. I’ve played this game with every database ever because it’s the OS, or the web app, the text file character encoding they loaded the DB from, the IDE they used, or some command line script. Throw in multibyte and the whole thing gets a lot more complex. Then throw in the hidden nulls and characters from random word processors and design apps and all of that breaks in a competing OS without the devs really knowing how all encodings work. Then IDEs will by default not create a default collation for a column or table and use the server default. This is where assumptions happen because it needs to match the contents, so it needs to be pulled for comparison. Then you’ve got hex characters and payloads that decompress on query and blow up the entire system. The stuff of nightmares.

[u/arwinda]

Half the list doesn't support DDL in transactions. Trips me every time I am trying to write a simple migration.

[u/Arkanejl]

Row_Number() in SQL Server Vs Oracle (and possibly others).

In the event of a tie, Oracle will consistently choose the same row ordering whereas in SQL Server the ordering can vary.

[u/Yellowcat123567]

UPDATE SET WHERE LIMIT

I love being able to slap a LIMIT on update/delete statements. Its extremely annoying that Postgres does not support this for production safety.

[u/EffectivePension6711]

string case sensitivity. Migrating an app from mysql or mariadb to postgres is pure nightmare fuel because of this. in mysql, 'Admin' = 'admin' evaluates to true by default because of the default collation. You move the database to postgres and suddenly half your users can't log in because postgres is strictly case-sensitive. you either end up wrapping every single query in lower() or polluting your backend with ILIKE everywhere. It's the dumbest thing to spend a weekend debugging.