Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:

I have a working parameterized query for an insert statement but I now want to add in a 'WHERE NOT EXISTS' clause. The working insert looks like this:

pgClient.query("INSERT INTO social_posts (username, user_image, message, image_url, post_id, post_url, location, network) VALUES ($1,$2,$3,$4,$5,$6,$7,$8)", postArray, 
    function(err, result) {...}

What I'd like to implement is:

pgClient.query("INSERT INTO social_posts(username, user_image, message, image_url, post_id, post_url, location, network) SELECT ($1,$2,$3,$4,$5,$6,$7,$8) WHERE NOT EXISTS (SELECT 1 FROM social_posts WHERE post_id = $9)", postArray,
    function(err, result) {...}

In this case the $9 would actually be equal to postArray[4].

I've tried pushing the value into the array again, that didn't work:

error running query { [error: operator does not exist: character varying = bigint]
name: 'error',
length: 207,
severity: 'ERROR',
code: '42883',
detail: undefined,
hint: 'No operator matches the given name and argument type(s). You might need to add explicit type casts.',
position: '198',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_oper.c',
line: '722',
routine: 'op_error' }

I tried interpolating the value, that didn't work:

error running query { [error: there is no parameter $1]
name: 'error',
length: 88,
severity: 'ERROR',
code: '42P02',
detail: undefined,
hint: undefined,
position: '114',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_expr.c',
line: '823',
routine: 'transformParamRef' }

Does anyone have any ideas? Thanks in advance!

share|improve this question
    
operator does not exist: character varying = bigint :: your argument is a string constant, and the id is an integer type (or the other way round), but either way you cannot compare them. – joop Aug 31 at 17:54
    
they're both strings though – dshamis317 Aug 31 at 18:41
    
Just so, PostgreSQL 9.5 finally brings in support for UPSERT ;) – vitaly-t Sep 1 at 17:43
    
thanks @vitaly-t that's awesome! – dshamis317 Sep 2 at 18:00
up vote 0 down vote accepted

After doing some research I came across something helpful here. I couldn't get the $9 parameter to work the reason why it wouldn't work with interpoloation had to do with an extra set of parentheses around the SELECT items. The correct syntax is:

var postID = postArray[4].toString() // per joop's comment above

pgClient.query("INSERT INTO social_posts(username, user_image, message, image_url, post_id, post_url, location, network) SELECT $1,$2,$3,$4,$5,$6,$7,$8 WHERE NOT EXISTS (SELECT 1 FROM social_posts WHERE post_id = '" + postId + "')", postArray,
function(err, result) {...}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.