Postgres Case Sensitivity

In PostgreSQL unquoted names are case-insensitive. Thus SELECT * FROM hello and SELECT * FROM HELLO are equivalent.

However, quoted names are case-sensitive. SELECT * FROM "hello" is not equivalent to SELECT * FROM "HELLO".

To make a "bridge" between quoted names and unquoted names, unquoted names are implicitly lowercased, thus hello, HELLO and HeLLo are equivalent to "hello", but not to "HELLO" or "HeLLo" (OOPS!).

Thus, when creating entities (tables, views, procedures, etc) in PostgreSQL, you should specify them either unquoted, or quoted-but-lowercased.

To convert existing tables/views/etc you can use something like ALTER TABLE "FOO" RENAME TO "foo".

Or, try to modify dump from MSSQL to make it "PostgreSQL-compatible" (so that it will contain foos or "foo"s but not "FOO"s).

Either by explicitly editing dump file. (If you're using Linux, you can do sed -r 's/"[^"]+"/\L\0/g' dumpfile — however be warned that this command may also modify text in string literals.)
Or by specifying some options when getting dump from MSSQL. (I'm not sure if there are such options in MSSQL, never used it, but probably such options should exist.)

Are PostgreSQL column names case-sensitive?

Identifiers (including column names) that are not double-quoted are folded to lowercase in PostgreSQL. Column names that were created with double-quotes and thereby retained uppercase letters (and/or other syntax violations) have to be double-quoted for the rest of their life:

"first_Name"

Values (string literals / constants) are enclosed in single quotes:

'xyz'

So, yes, PostgreSQL column names are case-sensitive (when double-quoted):

SELECT * FROM persons WHERE "first_Name" = 'xyz';

Read the manual on identifiers here.

My standing advice is to use legal, lower-case names exclusively so double-quoting is not needed.

How to make case-insensitive query in Postgresql?

Use LOWER function to convert the strings to lower case before comparing.

Try this:

SELECT id 
  FROM groups
 WHERE LOWER(name)=LOWER('Administrator')

How to deal with Case Sensitivity in PostgreSQL

You can create a unique index like this:

create unique index unique_color_value on colors(lower(color_value));

That said, it would be much simpler to make your data consistent from the start, by using a constraint that allows only lower case values in the column to start with.

create table colors (
    ...
    color_value text 
        unique 
        check(color_value = lower(color_value))
)

Postgres Case Insensitive in IN operator?

You can try to use ILIKE with ANY

SELECT * 
FROM fruits 
WHERE name ILIKE ANY(array['Orange', 'grape', 'APPLE', 'ManGO']);

sqlfiddle

How to ignore case sensitive rows in PostgreSQL

Use Non deterministic collation (only PostgreSQL version >= 12):

From https://dba.stackexchange.com/questions/101294/how-to-create-postgres-db-with-case-insensitive-collation :

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);
CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

Edit

From https://stackoverflow.com/a/59101567/2928168 :

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

    CREATE TABLE names(
      first_name text,
      /* Example collation used in schema directly */
      last_name text COLLATE "case_insensitive",
    );

    insert into names values
      ('Anton','Egger'),
      ('Berta','egger'),
      ('Conrad','Egger');

    select * from names
      order by
        last_name,
        /* Example collation used only in some query */
        first_name collate case_insensitive;

Case insensitive in Postgres

You can try this out and see if it works for you. I recommend to use this as a temp, until you fix your program.

SELECT loginID 
FROM user 
WHERE (SELECT CHAR_LENGTH(REGEXP_REPLACE(loginId,*loginId from java*,'','ig')) = 0)

This should provide you the loginID if the loginID matches without checking for case sensitivity. Also if you have more than one of the same login but different cases, then this WILL NOT work for you.

Here is a example of the output using one of my test tables in my database. username is all uppercase in my table.

select username from test.person where (select char_length(regexp_replace(username,'jMeRlOs','', 'ig')) = 0)

Result: Sample Image

Are function names in PostgreSQL case insensitive?

Function names are identifiers (like table names, field names), the same rules about case sensitivy apply to all.

In short, identifiers are case insensitive, unless quoted.

More precisely, an unquoted identifier is internally converted to lowercase and then a case sentitive match is attempted.
This can make your life miserable (i.e hidden bugs, hours wasted), typically if you used quoted identifiers when defining the table or function.

That's why you should always define your own naming convention and stick to it.

General advice: use always lowercase for identifiers, and be happy.

db=# select now();
              now
-------------------------------
 2011-06-10 16:33:06.588401-03
(1 row)

db=# select Now();
              now
-------------------------------
 2011-06-10 16:33:08.066818-03
(1 row)

db=# select "now"();
              now
-------------------------------
 2011-06-10 16:33:14.543381-03
(1 row)

db=# select "Now"();
ERROR:  function Now() does not exist
LINE 1: select "Now"();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Case insensitive collation still uses case sensitive comparison

The above sample code works fine with PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit.

It really was dependent on the postgres version even though the instructions were taken straight from the 12.9 documentation.

Postgres Case Sensitivity