A collation is an SQL schema object that maps an SQL name to operating system locales. Whatever you shall setup as a default collation settings for the database. By default implicitly that collation will choose your database.
In PostgreSQL nevertheless, the initial set of collation names is
platform-dependent.
There are no case insensitive collations, but there is the citext extension.
The standard approach to doing case-insensitive matches in PostgreSQL has been to use the lower function when comparing values, for example
SELECT * FROM tab WHERE lower(col) = LOWER(?);
This works reasonably well, but has a number of drawbacks:
It makes your SQL statements verbose, and you always have to remember to use lower on both the column and the query value.
It won't use an index, unless you create a functional index using lower.
If you declare a column as UNIQUE or PRIMARY KEY, the implicitly generated index is case-sensitive. So it's useless for case-insensitive searches, and it won't enforce uniqueness case-insensitively.
From the documentation:
The citext data type allows you to eliminate calls to lower in SQL queries, and allows a primary key to be case-insensitive. citext is locale-aware, just like text, which means that the matching of upper case and lower case characters is dependent on the rules of the database's LC_CTYPE setting. Again, this behavior is identical to the use of lower in queries. But because it's done transparently by the data type, you don't have to remember to do anything special in your queries.
For your reference: PostgreSQL Documentation & StackOverFlow Ref Here
Best Answer
You can get some info by inspecting a backup file using the RESTORE HEADERONLY command. The linked documentation explains what all of the result set fields are and mean, but the ones you are looking for should be:
[SoftwareVersionMajor][SoftwareVersionMinor]for distinction)[SoftwareVersionMinor]as this value should be "50" for that version, else "00" for all others (so far)[Collation]For example:
That will return a result set. If you are executing this from app code, it should be fairly easy to grab those specific fields. If you are executing this from T-SQL, and you need to act on it programmatically, then you will need to:
RESTORE HEADERONLYcommand within anINSERT INTO ... EXEC(...)construct: