Postgres table partitioning is one of those features that feels like a superpower right up until it isn’t. Just define a partition key, carve up data into manageable chunks, and everything hums along beautifully. And what’s not to love? Partition pruning in query plans, smaller tables, faster maintenance, easy archiving of old data; it’s a smorgasbord of convenience.
Then you try to enforce a unique constraint without including the partition key, and Postgres behaves as if you just asked it to divide by zero. Well… about that.
Click through for an explanation, some workarounds that might work in specific circumstances, and a few closing remarks.
As for SQL Server, the same rule applies. If you want a unique index (which is what a unique key constraint uses under the covers), you must include the partitioning column. If you don’t include it, SQL Server will include it for you rather than giving a hard error.