Insert into Table by getting NextVal of Sequence in PostgreSQL

Question

I have a table called SEQ_TABLE which has two columns, SEQ_NAME and ID

SEQ_NAME        |  ID
----------------------
SEQ_TABLE_10    |   1
SEQ_TABLE_20    |   5

Where ID is the Max of COLUMN_1 of TABLE_10 and TABLE_20

Now, I have to Insert new records into TABLE_10 by obtaining nextvalue of sequence from SEQ_TABLE.

I have written PostgreSQL query as follows:

INSERT INTO TABLE_10 (COLUMN_1, COLUMN_2, COLUMN_3) VALUES  ((SELECT nextval(SEQ_TABLE)),   'Bangalore' ,NULL);

When I execute above Query, It is giving below error: ********** Error **********

ERROR: column "SEQ_TABLE_10" does not exist
SQL state: 42703
Character: 99

But, following Query works fine in MySQL Database:

INSERT INTO TABLE_TABLE(COLUMN_1, COLUMN_2, COLUMN_3) VALUES  ((SELECT nextval('TABLE_172_SEQ','true')),    'Bangalore' ,NULL);

What is the Exact Postgres Query to achieve it in PostgreSQL DB?

MySQL doesn't support sequence unlike Postgres. So this means that nextval function which you are using in MySQL might not be an inbuilt function from MySQL. Can you please check if you have any user defined function named as nextval in your MySQL database? If you get that function, you might just copy over the function to Postgres database and use it or else better go for creating sequence as mentioned in the answer by @apm — Manish
– Manish, Commented Jul 15, 2016 at 7:48
You might have misunderstood my comment. I meant that MySQL doesn't have sequence and thus there is no chance of using nextval fuction in it. But you have a nextval function call being used in MySQL. I suspect the nextval fucntion which you are using in MySQL might be a user-defined function. Can you please check if you have any user-defined function named as "nextVal" in you MySQL database? — Manish
– Manish, Commented Jul 15, 2016 at 9:44
SELECT nextval('TABLE_172_SEQ','true') in your MySQL statement suggests that you have a user-defined nextval function which takes two parameters, the first being the table_name which is a column value in SEQ_TABLE. — Manish
– Manish, Commented Jul 15, 2016 at 9:49
@Manish: You are Right. and is there any way to get max value from column and increment it by 1. if column has no value, i m not getting 1 as a value. — Satish
– Satish, Commented Jul 15, 2016 at 10:41

apm · Accepted Answer · 2016-07-15 06:58:21Z

5

You want to create a sequence, not a table(SEQ_TABLE) Kindly refer this link https://www.postgresql.org/docs/8.1/static/sql-createsequence.html

Eg.

CREATE SEQUENCE serial START 101;
SELECT nextval('serial');
create table MyTable(col1 int,col2 varchar(20));
INSERT INTO MyTable VALUES (nextval('serial'), 'nothing');

answered Jul 15, 2016 at 6:58

apm

5356 silver badges19 bronze badges

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

Comments

Laurenz Albe · Accepted Answer · 2022-11-30 11:51:35Z

In current PostgreSQL versions you would use identity columns:

CREATE TABLE table_10 (
   id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   other text NOT NULL
);

That will implicitly generate a sequence. You are not allowed to insert numbers into into id, but you have to

INSERT INTO table_10 (other) VALUES ('new');

or

INSERT INTO table_10 (id, other) VALUES (DEFAULT, 'new');

and id is automatically filled with the next value from the sequence.

On older PostgreSQL versions, you would use serial or bigserial:

CREATE TABLE table_10 (id serial PRIMARY KEY, other text NOT NULL);

Then id is of type integer, a sequence table_10_id_seq is created and a nextval call is added to the DEFAULT clause of the id column.

You can examine this with \d in psql:

\d table_10
                         Table "laurenz.table_10"
 Column |  Type   |                       Modifiers
--------+---------+-------------------------------------------------------
 id     | integer | not null default nextval('table_10_id_seq'::regclass)
 other  | text    | not null
Indexes:
    "table_10_pkey" PRIMARY KEY, btree (id)

Moreover, the sequence belongs to the table and will be dropped automatically when the table is dropped.

You insert rows omitting the id column like this:

INSERT INTO table_10 (other) VALUES ('something');

Then the DEFAULT values will be used.

If you want to do the same thing “on foot”, for example to use a different name or different starting values for the sequence, that would work like this:

CREATE TABLE table_10 (id integer PRIMARY KEY, other text NOT NULL);
CREATE SEQUENCE table_10_id_seq OWNED BY table_10.id;
ALTER TABLE table_10 ALTER id SET DEFAULT nextval('table_10_id_seq');

Collectives™ on Stack Overflow

Insert into Table by getting NextVal of Sequence in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related