How to append a dynamic string to a text type column in Postgres through Node.js?

Question

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.

Please share suggestions. Thanks :)

If you are sure that none of the strings are null, you can use $1 || searchqueries -- This happends, because CONCAT() uses generic paramaters (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
– pozs, Commented Apr 13, 2017 at 10:43

vitaly-t · Accepted Answer · 2017-04-13 11:38:46Z

1

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

CONCAT($1::text, searchqueries)

answered Apr 13, 2017 at 11:38

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rastalamm · Accepted Answer · 2020-05-06 18:34:04Z

0

UPDATE imagesearchexperiment SET searchqueries = '"+ searchqueries="' WHERE ipAddress = "'+ ip+'"

edited May 6, 2020 at 18:34

Rastalamm

1,7823 gold badges24 silver badges33 bronze badges

answered May 6, 2020 at 15:19

Prateek Pareek

17017 bronze badges

Collectives™ on Stack Overflow

How to append a dynamic string to a text type column in Postgres through Node.js?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related