Question 306666 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres NodeJs INSERT NULL into INTEGER error

A Postgres table the_table has an INTEGER column the_int_col.

In a NodeJS module, I have the following code:

var values = [ '' ]; // Actual value is derived from other queries

const sql = 'INSERT INTO the_table ( the_int_col )VALUES ( $1::integer ) RETURNING id';

var res = await db.query( sql, values );

Running it, I get this error:

error: invalid input syntax for type integer: ""

I also tried:

const sql = 'INSERT INTO the_table ( the_int_col )VALUES ( NULLIF($1::integer, '') ) RETURNING id';
Tags:

2

Answers


  1. 0

    The NULLIF is almost right, you just have to cast later on:

    const sql = 'INSERT INTO the_table ( the_int_col )VALUES ( CAST(NULLIF($1, '') AS integer) ) RETURNING id';
    Login or Signup to reply.
  2. 0

    The issue you’re facing is due to the way you’re handling the conversion of an empty string (”) to an integer in your SQL query. In PostgreSQL, an empty string cannot be directly cast to an integer, which is why you’re encountering the error invalid input syntax for type integer: "".

    Your second approach using NULLIF($1::integer, ”) is on the right track, but it’s not correctly structured. NULLIF should be used before attempting the type cast to integer. The NULLIF function in PostgreSQL returns NULL if the two arguments are equal; otherwise, it returns the first argument. In your case, you want to return NULL when the input is an empty string, and then cast the result to an integer.

    Here’s how you can modify your SQL query:

    const sql = 'INSERT INTO the_table (the_int_col) VALUES (NULLIF($1, '')::integer) RETURNING id';

    In this revised query, NULLIF($1, ”) will return NULL if $1 is an empty string, and $1 itself otherwise. Then, the ::integer cast will correctly handle NULL values (by keeping them as NULL) and will convert non-empty strings to integers.

    Make sure that the values array in your Node.js code is appropriately populated. If you’re expecting either integers or empty strings, this approach should work as intended. If the array might contain other types of data, you may need additional validation or conversion logic before the database query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search