I have a table with same text column in two different DBMS – SQL Server and PostgreSQL.
I need query this table with exactly same order by this text column (it doesn’t matter if it’s in alphabetical order, it’s important that it’s in the same order). I need this query to compare tables content by this column.
What I need to write in ORDER BY clause?
PostgreSQL 14+, SQL Server 2008R2+
I need a solution independent of collation, OS language, codepage, text column datatype (varchar/nvarchar/text/citext/...)
I would be happy to sort by the order of some hash, but a similar question arises here – how to get the same hash value of some text in PostgreSQL and SQL Server?
8
You do not need to select with the same ordering to compare them; let SQL show you the differences (much more accurate than you trying to compare them). Use the except operation to identify rows that exist in one table but not in the other (also in SQL Server). First (if it does not already exist) in Postgres create a foreign data wrapper FDW. Then use the query:
select <list_of_columns> from <postgres table>
except
select <list_of_columns> from <sql server table>;
The result will be those values that exist in the Postgres table but do not exist in the SQL Server table; reversing the table reference would show those in SQL server not in Postgres. You can then use the known column values to identify the exact rows in each table. (See demo. I do not find a cross DBMS facility so demo simply uses Postgres to simulate).
However, this will most likely not accommodate Collation differences. What you may be asking for is: compare different things and make them be the same.