PostgreSQL adds quotes around number literals.

[rhys-vdw]

Adding an issue here because I don't know what the correct behaviour is, but I noticed that PostgreSQL queries seem to add quotes around numbers.

knex = new Knex({});
pg = new Knex({ client: 'pg' });

knex('table').where('id', 5).toString();
// select * from "table" where "id" = 5

pg('table').where('id', 5).toString();
// select * from "table" where "id" = '5'

Is this to handle a use case I'm not familiar with? Or is it a bug in Knex's postgres code?

[skabbes]

I believe since there is no ambiguity in interpreting numbers as columns vs values, there is no problem with either approach.

[chrisbroome]

I was actually surprised by this, but it looks like the query plans are identical for each case also:

CREATE TABLE test (
  id serial NOT NULL PRIMARY KEY,
  field integer NOT NULL DEFAULT 0
);
INSERT INTO test (field) VALUES (1), (2), (3), (4);

First query:

EXPLAIN SELECT * FROM test WHERE field = '4';

First query results:

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on test  (cost=0.00..36.75 rows=11 width=8)
   Filter: (field = 4)
(2 rows)

Second query:

EXPLAIN SELECT * FROM test WHERE field = 4;

Second query results:

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on test  (cost=0.00..36.75 rows=11 width=8)
   Filter: (field = 4)
(2 rows)

The main thing to note is that the Filter is identical for both cases (field = 4). So it looks like it casts the string to an integer before executing the query (which makes sense), so there's no performance penalty associated with using a string here either.

I think the correct behavior is to not use quotes, but it's good to know that the quotes aren't actually doing any harm.

[rhys-vdw]

@chrisbroome thanks heaps for looking into that.

I think the correct behavior is to not use quotes, but it's good to know that the quotes aren't actually doing any harm.

Agreed. I'm going to leave this open for now in case someone has time to provide a PR or can shed some light on why it's as it is.

[yoonghm]

• MySQL uses ' or " to quote values (i.e. WHERE name = "John"). This is not the ANSI standard for databases.
• MySQL uses ` (accent mark or backtick) to quote system identifiers, which is decidedly non-standard.
• PostgreSQL uses only single quotes for this (i.e. WHERE name = 'John'). Double quotes are used to quote system identifiers; field names, table names, etc. (i.e. WHERE "last name" = 'Smith').
• Numeric constant, in most cases, will be automatically coerced to the most appropriate type depending on context. When necessary, you can force a numeric value to be interpreted as a specific data type by casting it.

# SELECT '1' + 2;
 ?column? 
----------
        3
(1 row)

# select '1' + '2';
ERROR:  operator is not unique: unknown + unknown
LINE 1: select '1' + '2';
                   ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
# select '1' + '2'::REAL;
 ?column? 
----------
        3
(1 row)

# SELECT REAL '1.2' + REAL '1.3'; -- String style to specify real number
 ?column? 
----------
      2.5
(1 row)

# SELECT '1.2'::REAL + '1.3'::REAL; -- PostgreSQL (historical) style
 ?column? 
----------
      2.5
(1 row)

[0] https://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL
[1] http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html

[chainlink]

So this may not be an issue for SELECTS, it IS an issue for INSERTS in Postgres. I believe the same code is used in .raw which should correctly handle integers because which sql being used is unknown.

For Example:

knex.raw('?', [1])

Yields

'1' instead of 1

[niclashoyer]

Just ran into this exact problem, I used knex.raw('VALUES (?,?)', ['foo', 42]) and that did not work. Using knex.raw('VALUES (?,?::numeric)', ['foo', 42]) as a workaround.

[chainlink]

Nice! Thanks

[wubzz]

As far as I can tell, this applies to all ways of running queries via knex. By debugging, I found that it's the pg library doing this js conversion. Reference: https://github.com/brianc/node-postgres/blob/master/lib/utils.js#L46

Correct me if I'm wrong.

[zam6ak]

FWIW, in my case, when using node-postgres directly, the query gets generated properly and numeric columns are not being quoted....

[dlangerenken]

queries that contains arithmetic operations will most likely fail using quotes. At least
generate_series('1','20','1') is throwing an exception ( hint: 'Could not choose a best candidate function. You might need to add explicit type casts.',).

It seems like the last value of generate_series is not accepting a string. generate_series('1','20',1) is working fine

[jurko-gospodnetic]

FWIW, I'd like it to not use the quotes there.

Using quotes is simply not something the developer using that actually expects to happen, and there are always edge-cases where this causes code not to work as intended. The fact that in most cases it does not matter whether we quote the values or not is just not a good enough reason to do the quoting, as I'd very much prefer an infrastructure level lib like knex to stay out of the way if possible and not cause me any grief, especially with such technicalities.

I am ok with libs not being smart enough and doing literally what I tell them, but being too-smart and doing things I did not tell them explicitly to do and ending up breaking or achieving a different effect from the one intended is super annoying. 😆

[wubzz]

I tried this once more - for postgres - by removing knex's number -> string conversion, but again this does not affect the final query since as described above, the pg library runs its own prepareValue which typecasts to string regardless.

So the question remains if this is really a 'bug' and if it's something we should try to handle in knex.

[miewx]

how remove quotes

I find string type id where cause

error: insert or update on table "city" violates foreign key constraint "country_id"

but remove quotes there is no errors

INSERT INTO ur.city ("country_id","name") SELECT '16','test' ;

->

INSERT INTO ur.city ("country_id","name") SELECT 16,'test' ;

[petersg83]

I experience the same issue with
🔴 doesn't work

knex.raw('substring(??, ?)', ['path', 5])

🟢 works

knex.raw('substring(??, ?::integer)', ['path', 5])