1. Summarize the problem
I need to insert data into Postgres from node-pg. There is a primary key column created with 'serial'.
The node-pg documentation is sorely lacking, most tables I see in production have an auto-increment primary key, and a date field somewhere, the example in the documentation provides no examples beyond two text fields (name, email address).
Something different needs to be done, beyond what is shown in any example I can find anywhere to insert a record/row with an auto-increment id, date field and a geo data type (latitude, longitude).
If I can simply get a record with the auto-increment id working, will be happy and move to the next step of trying to get a TIMESTAMP field inserted, then a geo code.
2. Provide background including what you've already tried
I have read the node-pg documentation, I have posted an issue in the github area with no response, I have read a dozen or so articles and author's are usually lazy and do not provide an example for the most common real world scenario of inserting a row with an auto-increment primary key/id and at least one date field.
All examples insert two text fields only. Maybe node-pg is not used in an enterprise production environment where relational integrity is highly relevant, I don't know, but it's frustrating.
3. Show some code
Here is the create table .sql
CREATE TABLE address (
id serial PRIMARY KEY,
client_id integer not null,
property_id integer not null,
street1 varchar(40),
street2 varchar(40),
city varchar(30),
state_ab char(2),
zip varchar(10)
);
// verified_date TIMESTAMP, //no example on how to with node-pg
proc_db=> \d address
Table "proc.address"
Column | Type | Modifiers
--------------------+-----------------------+------------------------------------------------------
id | integer | not null default nextval('address_id_seq'::regclass)
client_id | integer | not null
client_property_id | integer | not null
street1 | character varying(40) |
street2 | character varying(40) |
city | character varying(30) |
state_ab | character(2) |
zip | character varying(10) |
Here is the node-pg code that runs without error, no error is displayed, but it is not inserting anything either.
function test_insert() {
const text = 'INSERT INTO address ('
+ ' id,'
+ ' client_id,'
+ ' client_property_id,'
+ ' street1,'
+ ' street2,'
+ ' city,'
+ ' state,'
+ ' zip)'
+ ' VALUES(DEFAULT, $1, $2, $3, $4, $5, $6, $7) RETURNING *';
//+ ' verified_date) VALUES(DEFAULT, $1, $2, $3, $4, $5, $6, $7, $8) RETURNING *'; //inserting TIMESTAMP not working either, one step at a time.
//const now = Date();
const values = [
101,
1001,
'1600 Amphitheatre Parkway',
'',
'Mountain View',
'CA',
'94043'];
client
.query(text, values)
.then(res => {
console.log(res.rows[0])
})
.catch(e => console.error(e.stack))
}
test_insert()
4. Describe expected and actual results including any error messages
The data is not inserted, I would expect an error message, but there is none. The example provided is taken directly from the node-pg documentation, but does not work.
For starters I need to insert a row that is auto-incremented, and as a follow up, if anyone wants to chime in, I need to add a field that is a TIMESTAMP
I have no clue why an error message is not being printed or why the insert fails and does not work.