`query()` throwing column does not exist error, despite it existing

[lukasjenks]

trafficstars

In pgAdmin/the cli, the following query:

UPDATE wq SET l_id = NULL, v_id = NULL WHERE w_id = 'cf93bc71-88c1-4bba-9e5c-fdc58d0ed14e';

works fine. However, when calling the same with the pg package in node:

const w_id_val = 'cf93bc71-88c1-4bba-9e5c-fdc58d0ed14e';
// (here client is the result of calling `const pool =  pg.Pool({...})` , 
// then `let client = await pool.connect()`);
const result = await client.query(
  `UPDATE wq
   SET l_id = null,
       v_id = null
   WHERE w_id = $1`,
  [w_id_val]
);

I get the following error:

{
   "message":"column \"w_id\" does not exist",
   "stack":"error: column \"w_id\" does not exist\n    at Connection.parseE (/Users/lukasjenks/Documents/Work/socrative-nodejs/node_modules/pg/lib/connection.js:569:11)\n    at Connection.parseMessage (/Users/lukasjenks/Documents/Work/socrative-nodejs/node_modules/pg/lib/connection.js:396:17)\n    at Socket.<anonymous> (/Users/lukasjenks/Documents/Work/socrative-nodejs/node_modules/pg/lib/connection.js:132:22)\n    at Socket.emit (events.js:314:20)\n    at Socket.EventEmitter.emit (domain.js:483:12)\n    at addChunk (_stream_readable.js:297:12)\n    at readableAddChunk (_stream_readable.js:272:9)\n    at Socket.Readable.push (_stream_readable.js:213:10)\n    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)\n    at TCP.callbackTrampoline (internal/async_hooks.js:126:14)",
   "name":"error",
   "length":112,
   "severity":"ERROR",
   "code":"42703",
   "position":"68",
   "file":"parse_relation.c",
   "line":"3514",
   "routine":"errMissingColumn"
}

I can confirm the column exists with this query:

SELECT table_schema, table_name, column_name, data_type
  FROM information_schema.columns
 WHERE table_name = 'wq';

public	wq	id	uuid
public	wq	w_id	uuid
public	wq	l_id	uuid
public	wq	v_id	uuid

I can also confirm that the column (w_id) should be recognized by pg as when using pg to query the table with a SELECT statement, I get this back in the fields property in the result object returned:

  fields: [
    Field {
      name: 'id',
      tableID: 26611,
      columnID: 1,
      dataTypeID: 2950,
      dataTypeSize: 16,
      dataTypeModifier: -1,
      format: 'text'
    },
    Field {
      name: 'w_id',
      tableID: 26611,
      columnID: 3,
      dataTypeID: 2950,
      dataTypeSize: 16,
      dataTypeModifier: -1,
      format: 'text'
    },
    ...

I've also confirmed this isn't a case issue; i.e. the column name is all lowercase and using double quotes around the column name has no effect.

[brianc]

not sure what to tell you here, but this library does absolutely 0 parsing or manipulating with your query text in any way. Can you make a script that's self-contained I could run over here? Pretty sure this would be a very extremely show stopping bug for a large part of the install base if the library did something to column names...but 100% always down to take a look if you have a self-contained thing that reproduces the issue! The only other thought is the library is using environment variables to connect and is connecting to a different database than your psql command? e.g. what happens if you run the information schema introspection query from the library?

[lukasjenks]

I would normally agree the most likely scenario would be the library is connecting to a different database, however to answer your question when I run the info schema introspection query from the lib this is the result (the column on the table is returned by the library itself):

Code:

let columns = await client.query(
  `SELECT table_schema, table_name, column_name, data_type
  FROM information_schema.columns
 WHERE table_name = 'wq'`
);
console.log(columns);

First few lines of output:

Result {
  command: 'SELECT',
  rowCount: 6,
  oid: NaN,
  rows: [
    anonymous {
      table_schema: 'public',
      table_name: 'wq',
      column_name: 'l_id',
      data_type: 'integer'
    },
    anonymous {
      table_schema: 'public',
      table_name: 'wq',
      column_name: 'w_id', // column in question is returned
      data_type: 'uuid'
    },
    ...

Has something similar to this ever came up before to your knowledge? I also wonder if the "message":"column \"w_id\" does not exist" message is indicating that somehow double quotes are being substituted in around the column name, though as you said the library shouldn't manipulate the query text/do anything to the column names, hence my confusion there.

Edit: From what I can tell, I'm also able to execute SELECT w_id FROM wq from pg-node, but not SELECT w_id FROM wq WHERE w_id = $1 (returns the same column does not exist error). It seems its the addition of the where statement could be causing the issue, but again, no idea why as using WHERE with other columns on the same table with the same data type in the same statement only edited such that the w_id WHERE clause is removed succeeds (does not return the column does not exist error).

[brianc]

yeah that is very very bizarre and I have legit never seen this before in 12 years of maintaining this library.... 🤔

If you have a script that can reproduce this I'd love to take a look at it, absolutely. Also, out of curiosity what version of postgres are you using?

[cekvenich2]

If you use " as outer and ' as inner it works for me.

[charmander]

How about a non-parameterized query, i.e. a hardcoded literal instead of $1?