I'm using postgres with node.js via the pg package, I'm only starting off so I'm aiming for a very simple table of the form shown below, created in the psql shell by the command
CREATE TABLE people(id int, name text);
Column | Type | Modifiers
------------+---------+-----------
id | int |
name | text |
The relevant parts of the node file are shown below. All I'm trying to do is get a count of the current number of people (which is fine), increment it by 1 and use this new value as the id of the next person I add to the table
pg.connect(conString, function(err, client) {
var count;
var next;
client.query('SELECT COUNT(*) FROM people', function(err, result) {
count = result.rows[0].count;
next = Number(count)+1;
});
client.query('INSERT INTO people (id, name) VALUES ($1, $2)', [next, req.body.personName],function(err, result) {
});
});
The req.body.personName is being set elsewhere in the express app, there is no problem with that part of it, the name gets stored fine in postgres but the id column remains blank.
I have
numeric and int types when creating the field in postgres but it made no differenceVALUES ($1::int, $2) , again to no avail[node-pg-types][1] project might be a solution, but from what I understand of it, it isn't (I could well be wrong)next variable in the client.query call with either a 1 or '1' (or any other number/string) then 1 will appear as the id in postgres
It looks like you're doing two things at once (asynchronously). Try the INSERT after the query finishes (in the callback):
pg.connect(conString, function(err, client) {
var count;
var next;
client.query('SELECT COUNT(*) FROM people', function(err, result) {
count = result.rows[0].count;
next = Number(count)+1;
client.query('INSERT INTO people (id, name) VALUES ($1, $2)', [next,
req.body.personName],function(err, result) {
});
});
});