Why do I need to quote columns and tables using Npgsql for C# and PostgreSQL?

Question

So I'm switching from SQL Server 2008 R2 (on Windows Server 2012) to PostgreSQL 9.5 (again on Windows) and all of my existing SQL statements are written in this format:

INSERT INTO Address (Address1, Address4, Address5, Postcode, IsoCountryCode) 
VALUES(@P0, @P1, @P2, @P3, @P4)

I'm using Ngpsql v3.1.3 as my .NET library to talk to PostgreSQL with C#. Executing the same statement results in an error that the table Address cannot be found. To get it to work I have to wrap all column names and table names in double quotes like this:

INSERT INTO \"Address\" (\"Address1\", \"Address4\", \"Address5\", \"Postcode\", \"IsoCountryCode\") 
VALUES(@P0, @P1, @P2, @P3, @P4)

I have over 600 SQL queries in my application and I don't want to have to go through them all injecting quotes everywhere.

The Npgsql documentation sample does not use quotes so am I missing something? See here Npgsql Documentation Example

Specifically their example has this:

INSERT INTO data (some_field) VALUES ('Hello world')

So I presumed I wouldn't have to double quote things. Can anyone enlighten me?

Object names by default are case insensitive, however if you create case sensitive object names (that is they are also quoted in the DDL), then you also need to quote them in queries (unless the name matches the storage format of case insensitive object names). — Mark Rotteveel
– Mark Rotteveel, Commented Jun 5, 2016 at 10:20
Ok so I presume there should be a setting in PostgreSQL to change this. I have literally installed PostgreSQL v9.5 and changed no configuration parameters. — Gareth
– Gareth, Commented Jun 5, 2016 at 10:30

Shay Rojansky · Accepted Answer · 2016-06-05 13:02:54Z

4

As mentioned in the comments, this is simply how PostgreSQL works - PostgreSQL implicitly converts unquoted identifiers to lowercase. There is no settings AFAIK to change this behavior. The docs on lexical structure state that:

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

answered Jun 5, 2016 at 13:02

Shay Rojansky

17k3 gold badges44 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gareth Over a year ago

Okay, I understand it now. Thank you for making it clearer. My table definitions and column names were all in Pascal case. e.g. Table = Address, Columns = Address1, Address2, etc. Converting them to lowercase allowed my queries to run unquoted.

Gareth Over a year ago

Additional thanks to Mark Rotteveel too as you say he mentioned that originally but it didn't click for me until Shay elaborated.

Collectives™ on Stack Overflow

Why do I need to quote columns and tables using Npgsql for C# and PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related