Postgres vs MS SQL Server

[jeffshumphreys]

Enumerated types

[jeffshumphreys]

Throws error if a string value would be truncated

[jeffshumphreys]

Supports easy durations as strings

[jeffshumphreys]

Everything is just UTF8.

[jeffshumphreys]

Everything is case sensitive

[jeffshumphreys]

Support for regex, match, like

[jeffshumphreys]

True is a value, Boolean is a data type

[jeffshumphreys]

DISTINCT FROM

[jeffshumphreys]

Bit arrays

[jeffshumphreys]

Array data types

[jeffshumphreys]

JSON data type

[jeffshumphreys]

Array literals

[jeffshumphreys]

Supports slices

[jeffshumphreys]

The "~" operator to match to regex strings.

[jeffshumphreys]

SELECT * FROM file_extensions fe WHERE file_extension ~ '[.+!#-]';

[jeffshumphreys]

The boolean type. UPDATE files SET file_name_broken_cant_change_ro = TRUE WHERE file_id = 38873; BIT is an interesting type in SQL Server, but not the same. That and it's not aggreble.

[jeffshumphreys]

PostgreSQL allows function overloading; that is, the same name can be used for several different functions so long as they have distinct argument types. I don't think MS SQL Server supports this, even in SQLCLR.

So I'm creating a function, RTRIM(string, int), that trims off n characters from the right. Why? I created a table that had columns like "amountleft" and "amountleft_original", updated by a MERGE command. To generate the following SQL:

WHEN MATCHED THEN 
    UPDATE SET
        amountleft_original = tgt.amountleft,
        amountleft = src.amountleft

and not miss any columns I can use the following:

SELECT RTRIM(column_name, LENGTH('_original')) AS column_name FROM information_schema.COLUMNS WHERE table_name = 'torrents' AND table_schema = 'simplified' AND column_name LIKE '%original';

I know this is anathema to people I've worked with, like Jamie or crap Simplot people "why not just cut and paste??", but, hey, I have a brain.