node.js + postgres: Syntax error at or near "$1"

node.js, postgresql

Solution

Databases generally don't let you use placeholders for identifiers (table names, column names, ...). PostgreSQL is complaining because you're trying to use the first three placeholders as column names:

INSERT INTO users($1, $2, $3) ...

You can think of identifiers like variable names in JavaScript: you can't put a variable name into another variable without some sort of `eval` chicanery. Similarly, you can't put a column name in a placeholder.

You should specify the column names without placeholders:

var queryConfig = {
  text: 'INSERT INTO users(username, firstname, lastname) VALUES($1, $2, $3);',
  values: ['ab', 'A', 'B']
};

Problem

I am having trouble understanding the npm package pg and its "dynamic statements". I want to be able to generate INSERT INTO statements but I only get syntax error in return ``` var pg = require('pg'); var connectionString = 'postgres://postgres@localhost/db'; pg.connect(connectionString, function(err, client, done) { if (err) { return console.error(err); } var queryConfig = { text: 'INSERT INTO users($1, $2, $3) VALUES($4, $5, $6);', values: ['username', 'firstname', 'lastname', 'ab', 'A', 'B'] }; client.query(queryConfig, function (err, result) { if (err) { return console.error(err); } done(); }); }); ``` The error I get is: ``` { [error: syntax error at or near "$1"] name: 'error', length: 84, severity: 'ERROR', code: '42601', detail: undefined, hint: undefined, position: '19', internalPosition: undefined, internalQuery: undefined, where: undefined, file: 'scan.l', line: '1053', routine: 'scanner_yyerror' } ``` Can anybody help me to understand what I'm doing wrong?

Original source