ERROR: invalid input syntax for type integer: "abc" — PostgreSQL was asked to read the text between the quotes as an integer and could not. Check the value before it reaches SQL (int.TryParse, then a typed parameter), and when you load text columns, find the bad rows with pg_input_is_valid before you cast.

The quoted text in the message is the exact value that failed, so "" means an empty string, the most common culprit in imported files. Comparing an integer column with a quoted number works (id = '1' found Maria Garcia); anything that is not a whole number fails the statement.

The error

SELECT name FROM customers WHERE id = 'abc';
ERROR:  invalid input syntax for type integer: "abc"
LINE 1: SELECT name FROM customers WHERE id = 'abc';
                                              ^

From Npgsql 10.0.3, when the SQL text carries the value:

Unhandled exception. Npgsql.PostgresException (0x80004005): 22P02: invalid input syntax for type integer: "abc"

POSITION: 39

Why it happens

A quoted literal has no type of its own until PostgreSQL decides one; next to an integer column it is read as an integer, and the integer parser rejects text that is not a whole number, though it forgives surrounding spaces (' 4 ' loaded as 4). The same happens with 'abc'::integer and with CAST(qty AS integer) over a text column, where the first bad row stops the whole statement. From .NET, what you get depends on how the value travels:

  • SQL built by string concatenation: 22P02 from the server, as above.
  • AddWithValue("id", "abc"): the string is sent as text, and the server answers 42883: operator does not exist: integer = text instead.
  • CAST(@id AS integer) around that string parameter: 22P02 again.
  • A parameter declared NpgsqlDbType.Integer holding a string: Npgsql refuses before sending anything, with System.InvalidCastException: Writing values of 'System.String' is not supported for parameters having NpgsqlDbType 'Integer'.

The fix

if (!int.TryParse(input, out var id))
{
    Console.WriteLine($"'{input}' is not a customer id");
    return;
}
await using var conn = new NpgsqlConnection(Shop);
await conn.OpenAsync();
await using var cmd = new NpgsqlCommand("SELECT name FROM customers WHERE id = @id", conn);
cmd.Parameters.Add(new NpgsqlParameter<int>("id", id));
Console.WriteLine(await cmd.ExecuteScalarAsync() ?? "(no customer)");

With abc the app printed 'abc' is not a customer id and sent nothing; with 1 it printed Maria Garcia. The bad value is caught where it enters, and the typed parameter means the server never parses text at all.

For text data already in a table, list the rows that will not cast, then load:

SELECT order_no, qty FROM orders_import WHERE NOT pg_input_is_valid(qty, 'integer');
 order_no |  qty
----------+-------
 A-1002   |
 A-1003   | three
(2 rows)

INSERT INTO orders SELECT order_no, CASE WHEN pg_input_is_valid(qty, 'integer') THEN CAST(qty AS integer) END FROM orders_import;
INSERT 0 4

pg_input_is_valid exists from PostgreSQL 16. On older servers a pattern such as qty !~ '^\s*-?[0-9]+\s*$' does the job; here it returned the same two rows, but it is stricter than the server, which also accepted 0x1A and 1_000 as integers. The CASE loads the bad values as NULL; fix them at the source if they matter. The popular CAST(NULLIF(trim(qty), '') AS integer) handles only the empty string: here it failed on "three" instead.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4) on Docker 29.9.2 under Windows 11, with psql 18.4 inside it, a customers table keyed by an integer id, and an orders_import staging table of text columns holding 2, an empty string, three and 4 . The .NET side was a console app on SDK 10.0.403 with Npgsql 10.0.3, one method per way of passing the value. The names fix7-pg and shop are this test's own, and the container was removed afterwards.

Frequently asked

What does invalid input syntax for type integer mean in PostgreSQL?
PostgreSQL tried to convert the quoted text in the message into an integer and the text is not a whole number. An empty pair of quotes means the value was an empty string. Find where that value comes from, a literal, a parameter or a text column, and validate or clean it before the conversion.
Why does Npgsql say operator does not exist: integer = text?
Because AddWithValue with a C# string sends the parameter as text, and PostgreSQL has no operator comparing an integer column with text. Parse the string into an int in C# and pass it as an int parameter, for example new NpgsqlParameter<int>("id", id).
How do I find rows that are not valid integers in PostgreSQL?
On PostgreSQL 16 or later use WHERE NOT pg_input_is_valid(column, 'integer'). On older versions a regular expression such as column !~ '^\s*-?[0-9]+\s*$' finds the same rows. Both run without raising an error.

More decoded errors in the Fixes category. The SQL Server counterpart of this error is Msg 245, conversion failed when converting the varchar value.