How use nextval in sequence using node-postgres

Question

my table postgres database has an auto increment key.

In convencional postgres sql I can:

INSERT INTO CITY(ID,NAME) VALUES(nextval('city_sequence_name'),'my first city');

How can I do this using node-postgres ? Until now, I have tried:

myConnection.query('INSERT INTO city(id,name) VALUES ($1,$2)', ['nextval("city_sequence_name")','my first city'],callback);

But the error rules:

erro { error: sintaxe de entrada é inválida para integer: "nextval("city_sequence_name")"

try changing 'nextval("city_sequence_name")' to"nextval('city_sequence_name')" — Vao Tsun
– Vao Tsun, Commented Apr 9, 2017 at 15:48
Why not simply skip the column then? - INSERT INTO CITY(NAME) VALUES('my first city');. This is how one normally lets auto-generated columns generate a new value. Alternatively, one can do INSERT INTO CITY(ID, NAME) VALUES(DEFAULT, 'my first city');. — vitaly-t
– vitaly-t, Commented Apr 9, 2017 at 22:29

joelton · Accepted Answer · 2017-04-09 16:13:10Z

2

So, I was able to identify the solution to this case:

connection.query("INSERT INTO city(id,name) VALUES(nextval('sequence_name'),$1)", ['first city'],callback);

answered Apr 9, 2017 at 16:13

joelton

1632 silver badges11 bronze badges

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

Comments

Evan Carroll · Accepted Answer · 2017-04-09 16:50:46Z

0

This is a bad practice. Instead, just set the column to DEFAULT nextval() or use the serial type.

# CREATE TABLE city ( id serial PRIMARY KEY, name text );
CREATE TABLE
# \d city
                         Table "public.city"
 Column |  Type   |                     Modifiers                     
--------+---------+---------------------------------------------------
 id     | integer | not null default nextval('city_id_seq'::regclass)
 name   | text    | 
Indexes:
    "city_pkey" PRIMARY KEY, btree (id)

# INSERT INTO city (name) VALUES ('Houston'), ('Austin');
INSERT 0 2
test=# TABLE city;
 id |  name   
----+---------
  1 | Houston
  2 | Austin
(2 rows)

answered Apr 9, 2017 at 16:50

Evan Carroll

1

3 Comments

Gaetano Piazzolla Over a year ago

why is it a bad practice? is not good to handle sequence insertion in application code?

Evan Carroll Over a year ago

@GaetanoPiazzolla because how often do you want a column that represents a sequence to default to anything but the next value in that sequence? And if it defaults to the next value, why would you want to explicitly write the default in the app code? Moreover, the newer way to do this does not even expose a nextval() to the user as it uses IDENTITY COLUMNs, which is standardized.

Gaetano Piazzolla Over a year ago

One thing that maybe you haven't considered: If you do use java and JPA - the library does use a pre- allocation of next val for the sequences, so that it does not need to run a new "nextVal" each time it inserts a new row :)

Collectives™ on Stack Overflow

How use nextval in sequence using node-postgres

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related