Postgres Case Sensitivity

by

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.)

More Related Contents:

  1. How to select just the row in the second table?
  2. How do I obtain a Query Execution Plan in SQL Server?
  3. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  4. IN vs ANY operator in PostgreSQL
  5. sql query to return differences between two tables
  6. Generate SQL Create Scripts for existing tables with Query
  7. SQL Server 2005 How Create a Unique Constraint?
  8. SQL Server: Make all UPPER case to Proper Case/Title Case
  9. How do I delete from multiple tables using INNER JOIN in SQL server
  10. Does Foreign Key improve query performance?
  11. SQL : BETWEEN vs =
  12. Export SQL query data to Excel
  13. What is a ‘multi-part identifier’ and why can’t it be bound?
  14. SQL Server convert string to datetime
  15. Record returned from function has columns concatenated
  16. INSERT rows into multiple tables in a single query, selecting from an involved table
  17. Check If the string contains accented characters in SQL?
  18. Count based on condition in SQL Server
  19. How to find a table having a specific column in postgresql
  20. PostgreSQL JOIN with array type with array elements order, how to implement?
  21. Trim trailing spaces with PostgreSQL
  22. What does sp_reset_connection do?
  23. Replace first occurrence of substring in a string in SQL
  24. How to correctly insert newline in nvarchar [duplicate]
  25. Check whether string is a date Postgresql
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. Export table data from one SQL Server to another
  29. Insert data and set foreign keys with Postgres
  30. How to use a ring data structure in window functions

Leave a Comment