PostgreSQL Parameterized Insert with ADO.NET

Question

I am using NpgSQL with PostgreSQL and ADO.NET. Forgive the simplicity of the question as I just started using PostgreSQL and NpgSQL this week.

Something like this works fine:

[Test]
public void InsertNoParameters()
{
    NpgsqlConnection conn = new NpgsqlConnection("Host=localhost; Database=postgres; User ID=postgres; Password=password");
    conn.Open();

    IDbCommand command = conn.CreateCommand();
    string sql = "INSERT INTO Customers (FirstName,LastName) VALUES ('Test','Tube')";
    command.CommandText = sql;
    command.ExecuteNonQuery();
    conn.Close();
}

When I put in parameters I get the error message: Npgsql.NpgsqlException : ERROR: 42703: column "_firstname" does not exist

[Test]
public void InsertWithParameters()
{
NpgsqlConnection conn = new NpgsqlConnection("Host=localhost; Database=postgres; User ID=postgres; Password=password");
conn.Open();

IDbCommand command = conn.CreateCommand();
string sql = "INSERT INTO Customers (FirstName,LastName) VALUES (_FirstName,_LastName)";
command.CommandText = sql;

var parameter = command.CreateParameter();
parameter.ParameterName = "_FirstName";
parameter.Value = "Test";
command.Parameters.Add(parameter);

parameter = command.CreateParameter();
parameter.ParameterName = "_LastName";
parameter.Value = "Tube";
command.Parameters.Add(parameter);

command.ExecuteNonQuery();
conn.Close();
}

Solution

The responses in the comments are correct:

1. Npgsql doesn't support _ as a parameter placeholder notation. You should be using @ or : (so @FirstName or :FirstName, not _FirstName).
2. PostgreSQL will automatically lower-case your table and column names unless they are double-quoted. Either use lower-case names for everything (simpler) or quote identifiers in your SQL queries.

So your code should look more or less like this:

IDbCommand command = conn.CreateCommand();
string sql = "INSERT INTO Customers (first_name, last_name) VALUES (@FirstName,@LastName)";
command.CommandText = sql;

var parameter = command.CreateParameter();
parameter.ParameterName = "FirstName";
parameter.Value = "Test";
command.Parameters.Add(parameter);

Comments

[wildplasser]

Uppercase <> lowercase. Either use lowercase only or quote your identifiers (using double quotes).

[Greg Finzer]

I just tried all lower case parameter names and I am still getting the same error.

[wildplasser]

in psql (or pgadmin) inspect what the actual names of your columns are.

[Mark Rotteveel]

As far as I know, most drivers including the postgresql driver follow the microsoft convention to identify parameters with an @ prefix, not an underscore (_)

[Greg Finzer]

It looks like it has to be an @ sign. It is strange because the @ is invalid for function parameters.

[Mark Rotteveel]

@Greg''Wildman''Finzer That's irrelevant, it is handled in the driver, not in the server.

[Kasey Speakman]

I disagree with #2 recommendations. Quoted identifiers should not be used generally. If you define a field with quotes you also have to use them every time you reference that field. So it better be worth it (but it won't be). Casing vs underscore is debatable. Using cased names does work just fine without quoted identifiers, and it will be preferable sometimes... depends on the project.

[Shay Rojansky]

Using cased names does not work just fine without quoted identifiers, in the sense that PostgreSQL will fold the name to lowercase. This means that Ident1 and ident1 will be in conflict. There's nothing particularly wrong with quoting your identifiers - this is a standard PostgreSQL practice and a necessity where mixed case is desired. For example, an O/RM which maps C# property names to database columns (e.g. Entity Framework) will typically quote all identifiers.

[Kasey Speakman]

You can issue definition queries and data queries with cased names, and it will work just fine. Postgres will handle them with it's version of "case insensitivity", meaning they will be lower cased. ORMs not able to map a lower-cased names to a case-named object properties will probably also have problem with your other suggestion of using underscores. ORMs that create columns with quoted identifiers are creating a debt that it's users will have to pay in the future.

[Shay Rojansky]

Everything will work "just fine", until you happen to have two identifiers that are identical if folded to lowercase. This is why any O/RM that maps identifier from a case-sensitive language (such as C#) to PostgreSQL absolutely must quote, otherwise sooner or later two properties will fold into the same lowercase and boom.

[Shay Rojansky]

If you control all applications accessing your database and can make sure all identifiers are distinct, it's fine not to quote. If you're writing a general-purpose component such as an O/RM there's a very good chance you must quote. It all depends on the context.