Correct Way To Prepare pg Query

Question

I have been trying to prepare a query to update rows in my table for certain user(s) I am using Node js and the npm package pg. The problem is arising when I run the query my error message will return error:

syntax error at or near "VALUES", or "WHERE"

but never giving more context of the error making it hard to pinpoint. I have tried re-arranging the values and trying different approaches but have been unsuccessful.

populateProfileData(username, gender, dob, country, province, city, date, user_id) {
    console.log('================' + username + gender + dob + country + province + city + date + user_id);
    return new Promise(function (resolve, reject) {
        const client = new pg.Client(connectionString);
        client.connect()
            .then(() => console.log('connected'))
            .catch(err => console.error('connection error', err.stack))
        // This Works ---> UPDATE users SET dob = '032018' WHERE USER_id = 15
        const text = 'UPDATE users SET(username, dob, country, province, city, date)  WHERE user_id = (user_id) VALUES($1, $2, $3, $4, $5, $6, $7)';
        const values = [username, dob, country, province, city, date, user_id]
        const query = client.query(text, values)
            .then(success => {
                console.log('ran query')
                resolve(success);
            })
            .catch(error => {
                console.log('--------' + error)
                reject(error);
            })
    });
}
}

Answer 1

1. For UPDATE a some records you must have indexed identification column[s] to use its for search existing rows:

INSERT INTO users (id, username, dob, country, province, city, date) 
VALUES 
  (1, 'Joe', 1, 'usa', 'utah', 'odessa', '12-21-2015'),
  (2, 'Doe', 2, 'usa', 'utah', 'odessa', '12-21-2015'),
  ...
ON CONFLICT (id)
DO UPDATE SET 
  username=EXCLUDED.username,
  dob=EXCLUDED.dob,
  country=EXCLUDED.country,
  province=EXCLUDED.province,
  city=EXCLUDED.city,
  date=EXCLUDED.date;

2. instead update existing rows you can simle ignore its:

INSERT INTO users (id, username, dob, country, province, city, date) 
VALUES 
  (1, 'Joe', 1, 'usa', 'utah', 'odessa', '12-21-2015'),
  (2, 'Doe', 2, 'usa', 'utah', 'odessa', '12-21-2015'),
  ...
ON CONFLICT (id) 
DO NOTHING

3. when your table have a constraints, you must disable its before update and turn on after all update ended:

ALTER "country" DROP NOT NULL;
INSERT INTO users (id, username, dob, country, province, city, date) 
VALUES 
  (1, 'Joe', 1, 'usa', 'utah', 'odessa', '12-21-2015'),
  (2, 'Doe', 2, 'usa', 'utah', 'odessa', '12-21-2015'),
  ...
ON CONFLICT (id)
DO UPDATE SET 
  username=EXCLUDED.username,
  dob=EXCLUDED.dob,
  country=EXCLUDED.country,
  province=EXCLUDED.province,
  city=EXCLUDED.city,
  date=EXCLUDED.date;
ALTER "country" SET NOT NULL;