CODEWITHSUNDEEP
sqldatabasepostgresql
Abhigyan pandey
Abhigyan pandey

Reputation: 41

Generate unique ID to append records in PostgreSQL

I am using one postgreSql query which populates one table with some values and it takes different parameter when i run. I am appending rows each times i run the query with different parameter, Now i am interested in giving one auto generated ID to all rows which are coming as an output for one set of parameter.. So that I can access those rows which belong to certain parameters.

Can anyone help me how to append rows each time with one unique ID column?

Upvotes: 0

Views: 3942

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I sounds like you want to keep track of a "runid" which is based on the parameter. This would be a separate entity. I would suggest a data structure like this:

create table runs (
    runid serial primary key,
    parameter varchar(255),
    createdAt timestamp default current_timestamp
);

Then you would include runid in your table, with a foreign key reference to runs.

Finally you can insert into your table by doing:

with r as (
      insert into runs (v_parameter)
          values (parameter)
          returning *
     )
insert into yourtable ( . . . , runid)
    select . . ., r.runid
    from . . . cross join
         r;

Upvotes: 0

bitifet
bitifet

Reputation: 3669

You can use serial (or bigserial for bigint) as column types.

They're in fact kind of wrappers over integer and biginteger types that automagically configures its default value to the nextval() of an ad-hoc sequence which it also creates for you under the hood.

To add such a field you only need to execute a sentence like this one below:

alter table your_table add column id serial primary key;

(Adjusting the table and column names to whatever you want/need).

Obviously if you didn't yet created the table, you can simply add the field specifying it as serial in table declaration. But don't forget to also declare it as primary key to ensure it is treated as such.

This will create and fill in (because of its inherent default value) the column id with consecutive numbers in a single transaction.

Be aware that, depending on the table size it could take a considerable amount of time.

Also, for huge tables you may need to consider using bigserial instead of serial.

About getting the id assigned to a row you just inserted, you can add to the insert statement a returning id clause which will make it to act like a select returning a row (or multiple rows if inserting multiple ones with single statement) with it (of course you also can use returning * or returning _whatever_columns_you_want_.

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

you can use RETURNING statement to get returning ID

INSERT INTO Table1 (...) VALUES (...)
RETURNING idCol;

syntax

RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Upvotes: 0

Related Questions