0

We're investigating changing an application from using MSSQL to Postgres.

We have a problem with collations - Postgres defaults to case sensitive whereas MSSQL defaults to case insensitive. If we change the Postgres collation to case insensitive, some feature (like LIKE) are no longer supported.

My colleague's solution to this is to add a case sensitive and case insensitive collation so that we can use the case insensitive one unless we need to do a LIKE, in which case we use the case sensitive collation with ILIKE.

My question is, how does having two collations affect the size of the database?

Is it going to be storing all text fields twice? Or all text in indexes?

2
  • Isnt the feature you need more important than increase in storage? Mar 2 at 19:44
  • It depends, there are multiple ways to solve the problem, so to be able to compare the solutions I need to know the costs
    – ghosttie
    Mar 2 at 20:26

1 Answer 1

1

You can have as many collations as you want: a collation is just a line in the table pg_collation and will take barely any space.

Here are two ideas how to deal with pattern matching:

  1. Use the citext data type:

    CREATE EXTENSION IF NOT EXISTS citext;
    

    That works with LIKE.

  2. Create a view that presents the same column twice:

    CREATE COLLATION english_ci (
       PROVIDER = icu,
       LOCALE = 'en-US@colStrength=secondary',
       DETERMINISTIC = FALSE
    );
    
    CREATE TABLE data (
       id bigint PRIMARY KEY,
       value text COLLATE english_ci
    );
    
    CREATE VIEW v_data AS
    SELECT id, value,
           value COLLATE "C" AS like_value
    FROM data;
    
    INSERT INTO v_data (id, value) VALUES (1, 'string');
    
    SELECT * FROM v_data WHERE like_value ILIKE 'Str%';
    
     id │ value  │ like_value 
    ════╪════════╪════════════
      1 │ string │ string
    (1 row)
    
2
  • But does having two collations store the text twice?
    – ghosttie
    Mar 2 at 19:37
  • No; I misunderstood your question. I have revised the answer. Mar 3 at 6:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.