1

I'm generating a dynamic update query based on a list of provided objects for postgres. This is what my query looks like:

update loan_item_assignment as t set id = c.id, dateselectionid = c.dateselectionid, loanitemid = c.loanitemid, active = c.active, type = c.type from (values ( $1, $2, $3, $4, $5 ), ( $6, $7, $8, $9, $10 ), ( $11, $12, $13, $14, $15 ), ( $16, $17, $18, $19, $20 ), ( $21, $22, $23, $24, $25 ), ( $26, $27, $28, $29, $30 ), ( $31, $32, $33, $34, $35 ), ( $36, $37, $38, $39, $40 ) ) as c( id, dateselectionid, loanitemid, active, type ) where c.id = t.id returning *

And here's the values list I'm giving it:

[ 7,
35,
3,
true,
'normal',
8,
35,
4,
true,
'normal',
1,
35,
6,
true,
'normal',
2,
35,
7,
true,
'normal',
3,
35,
8,
true,
'normal',
5,
35,
10,
true,
'normal',
4,
35,
11,
true,
'normal',
6,
35,
12,
true,
'normal' ]

As far as I can tell, the values match up correctly. This is the error I'm seeing:

{ [error: operator does not exist: text = integer]
name: 'error',
length: 195,
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: '448',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_oper.c',
line: '726',
routine: 'op_error' }

And this is the code that's ultimately running the query:

var performQuery = function(text, values, cb) {
   pg.connect(connectionString, function(err, client, done) {
     client.query(text, values, function(err, result) {
       done();
       if (!result) {
         console.log(err);
         cb([], err);
       } else {
         cb(result.rows, err);
       }
     })
   });

}

And here is the table definition:

Table "public.loan_item_assignment"
Column      |  Type   |                             Modifiers                             | Storage  | Stats target | Description 
-----------------+---------+-------------------------------------------------------------------+----------+--------------+-------------
id              | integer | not null default nextval('loan_item_assignment_id_seq'::regclass) | plain    |              | 
dateselectionid | integer |                                                                   | plain    |              | 
loanitemid      | integer |                                                                   | plain    |              | 
active          | boolean |                                                                   | plain    |              | 
type            | text    |                                                                   | extended |              | 
Indexes:
"loan_item_assignment_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"loan_item_assignment_dateselectionid_fkey" FOREIGN KEY (dateselectionid) REFERENCES date_selection(id)
"loan_item_assignment_loanitemid_fkey" FOREIGN KEY (loanitemid) REFERENCES loan_item(id)
7
  • As the error says, you have a type mismatch somewhere. You are assigning an integer to a field that is text. Commented Sep 25, 2016 at 17:10
  • As my examples show, all of the data types in my array match up properly with the columns in data type. Commented Sep 25, 2016 at 17:46
  • can you show definition of your table? Commented Sep 25, 2016 at 17:46
  • Sure, I'll update the main question Commented Sep 25, 2016 at 17:53
  • Proper way of doing it: PostgreSQL multi-row updates in Node.js. Commented Sep 25, 2016 at 18:33

1 Answer 1

1

Vitaly-t's comment on my answer is the solution - to use the pg-promise library to generate the query, and specifically method helpers.update for generating multi-row update queries, as shown in PostgreSQL multi-row updates in Node.js.

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.