1

I'm trying to update a text type column in Postgres from Node.js. I would like to append a dynamic string to the data in the text type column.

but I get an error

"error: could not determine data type of parameter $1"

var pg = require('pg');

var connectionString = process.env.DATABASE_URL || "pg://admin:guest@localhost:5432/imagesearchexperiment";

var client = new pg.Client(connectionString);
client.connect();

client.query("DROP TABLE IF EXISTS imagesearchexperiment");
client.query("CREATE TABLE IF NOT EXISTS imagesearchexperiment(ipAddress varchar(64), searchqueries text)");

client.query("INSERT INTO imagesearchexperiment(ipAddress, searchqueries) VALUES($1, $2)",["ipAddress1","query1<>query2<>query3"]);
client.query("INSERT INTO imagesearchexperiment(ipAddress, searchqueries) VALUES($1, $2)",["ipAddress2","query1<>query2<>query3"]);
client.query("INSERT INTO imagesearchexperiment(ipAddress, searchqueries) VALUES($1, $2)",["ipAddress3","query1<>query2<>query3"]);

var searchQuery = "query4";
var ip = "ipAddress3";

// The following line is causing an error - error: could not determine data type of parameter $1
client.query("UPDATE imagesearchexperiment SET searchqueries = CONCAT($1, searchqueries) WHERE ipAddress = $2", [searchQuery, ip]);

I've checked out the following links.

Their solution works if I didn't have a dynamic string.

  1. SQL Server - Adding a string to a text column (concat equivalent)
  2. How can I add text to SQL Column
  3. How to prepend a string to a column value in MySQL?
  4. How can I append a string to an existing field in MySQL?

Please share suggestions. Thanks :)

Community
  • 1
  • 1
dsinecos
  • 571
  • 6
  • 13
  • Try `CONCAT($1::text, searchqueries)` ;) – vitaly-t Apr 13 '17 at 10:26
  • If you are sure that none of the strings are `null`, you can use `$1 || searchqueries` -- This happends, because `CONCAT()` uses [generic paramaters](https://www.postgresql.org/docs/current/static/datatype-pseudo.html) (more specifically `any`). So you always have to call with parameters, whose types are known to the query parser. You can use casts to achieve that in SQL (like previously suggested) or (usually) you can set the parameter type via binding (in case of prepared statements): but this highly depends on your client library. – pozs Apr 13 '17 at 10:43
  • 1
    @vitaly-t Thanks :), it works now – dsinecos Apr 13 '17 at 10:46

2 Answers2

1

Function CONCAT isn't sure about the data type it gets. Explicit type casting as ::text will help:

CONCAT($1::text, searchqueries)
vitaly-t
  • 24,279
  • 15
  • 116
  • 138
0
UPDATE imagesearchexperiment SET searchqueries = '"+ searchqueries="' WHERE ipAddress = "'+ ip+'"
Rastalamm
  • 1,712
  • 3
  • 23
  • 32
Prateek Pareek
  • 150
  • 15