Search code examples
c#postgresqlnpgsql

Howto use PostgreSQL table names without double quotes in C# and Npgsql

i'm trying to select data from PostgreSQL 10.5 database with Npgsql2 library - I cannot use Npgsql3 or Npgsql4 because I need to support Windows XP (.NET 4.0 maximum). I use the following code:

var builder = new NpgsqlConnectionStringBuilder();
//setting connection string variables here
var connection = new NpgsqlConnection(builder.Tostring());
var query = "SELECT * FROM \"TableName\" ORDER BY \"ColumnName\"";
var adapter = new NpgsqlDataAdapter(query, connection);
var dataSet = new DataSet();

connection.Open();
adapter.Fill(dataSet);

If I don't use double quotes for TableName or ColumnName in my query - it fails with error:

PostgreSQL ERROR: 42P01: relation “TableName” does not exist

With double quotes it works.

So is it possible to use Npgsql without double quotes? Is there some flag or something?

Solution

  • Without double quotes, PostgreSQL folds all identifiers to lower-case. This is PostgreSQL behavior, and has nothing to do with Npgsql - the latter simply passes along your SQL as you wrote it. You can switch to all-lowercase table names, in which case you no longer need the quotes.