PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use EXCEPT operator in PostgresSQL with case insensitivity?

I have two tables, Table_1 and Table_2. I want to compare one of the columns between the tables and have an output of the difference.
Specifically, I need to know which entries (of a column) of Table_1 are not present in table 2. I am using the EXCEPT operator but the numbers aren’t correct so I’d like to know how to make it case insensitive.
Here is my basic approach (without the case insensitive stuff):

SELECT "Address" FROM Table_1
EXCEPT
SELECT "People Address" FROM Table_2

So I thought I’d use:

SELECT "Address" FROM Table_1
EXCEPT
SELECT "People Address" FROM Table_2

To make it case insensitive, I’ve tried:

SELECT "Address" COLLATE Latin1_General_CI_AS FROM Table_1
EXCEPT
SELECT "People Address" COLLATE Latin1_General_CI_AS FROM Table_2

With the approoach above, I am getting the following error:

db query error: pq: collation "Latin1_General_CI_AS" for encoding "UTF8" does not exist.

How can I include case insensitivity? This is in PostgresSQL

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    COLLATE Latin1_General_CI_AS is MS SQL server syntax. So I ended up using the UPPER keyword like this:

    SELECT UPPER("Address") FROM Table_1
EXCEPT
SELECT UPPER("People Address") FROM Table_2

    Thanks to the comments posted in the question by @sergey


  2. 0

    The error you specified in PostgreSQL is due to the fact that the Latin1_General_CI_AS statement specific to SQL Server is not recognized in PostgreSQL. To correct this error, you can use the LOWER or UPPER functions to make a case-insensitive comparison in PostgreSQL.

    Below I show you how you can write a query to compare address columns and find case-insensitive differences:

    SELECT LOWER("Address") AS Address FROM Table_1
EXCEPT
SELECT LOWER("People Address") AS Address FROM Table_2;
    Login or Signup to reply.
  3. 0

    You can create a case insensitive collation:

    CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

    https://www.postgresql.org/docs/current/collation.html

    And use it as expected:

    SELECT "Address" COLLATE case_insensitive FROM Table_1
EXCEPT
SELECT "People Address" COLLATE case_insensitive FROM Table_2

    Demo: https://dbfiddle.uk/lFOBATce

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search