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();
}

Uppercase <> lowercase. Either use lowercase only or quote your identifiers (using double quotes). — wildplasser
– wildplasser, Commented Aug 22, 2015 at 15:58
I just tried all lower case parameter names and I am still getting the same error. — Greg Finzer
– Greg Finzer, Commented Aug 22, 2015 at 16:03
in psql (or pgadmin) inspect what the actual names of your columns are. — wildplasser
– wildplasser, Commented Aug 22, 2015 at 16:05
As far as I know, most drivers including the postgresql driver follow the microsoft convention to identify parameters with an @ prefix, not an underscore (_) — Mark Rotteveel
– Mark Rotteveel, Commented Aug 22, 2015 at 16:06
It looks like it has to be an @ sign. It is strange because the @ is invalid for function parameters. — Greg Finzer
– Greg Finzer, Commented Aug 22, 2015 at 16:08

Shay Rojansky · Accepted Answer · 2015-08-23 12:23:54Z

14

The responses in the comments are correct:

Npgsql doesn't support _ as a parameter placeholder notation. You should be using @ or : (so @FirstName or :FirstName, not _FirstName).
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);

answered Aug 23, 2015 at 12:23

Shay Rojansky

17k3 gold badges44 silver badges73 bronze badges

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

5 Comments

Kasey Speakman Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

PostgreSQL Parameterized Insert with ADO.NET

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related