From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: The best option to insert data with primary id
Date: 11 Dec 2010 10:17:58 GMT
Message-ID: <idvj4m$8e4$1@reversiblemaps.ath.cx> (raw)
References: <AANLkTint7hvf03OXc8ujqWE+a2ETWUXu7NtHysKqZjUG@mail.gmail.com>
On 2010-12-06, - <grandebuzon@gmail.com> wrote:
> --0016364d26cf7fa4970496bf2224
> Content-Type: text/plain; charset=ISO-8859-1
>
> Hi everyone,
> I have a question about how best to insert and manipulate the table with
> primary key id for better productivity. I need to insert data into the table
> and get last id.
>
> 1. First option to take counter Postgres SEQUENCE:
> INSERT INTO table (id, ...) VALUES ((SELECT nextval ('seq_table')), ...)
> RETURNING (SELECT currval ('seq_table')) AS id
>
> Only thing I see, that if the row is not inserted, the counter is
> incremented every time when called. Then they will have empty unused id in
> the table and ID number will grow much. There will be many records. This id
> int8 type declared with length 64.
> Is there any option to occupy empty sequence records. I have to worry about
> this?
(assuming the default for id is nextval ('seq_table'))
INSERT INTO table ( id, ...) VALUES ( default, ...) RETURNING id;
or you can leave id and default out of the left half:
INSERT INTO table ( ...) VALUES ( ...) RETURNING id;
> 2. Second option is to take control of id and
> INSERT INTO table (id, ...) VALUES ((SELECT MAX (id) +1 FROM table), ...)
> RETURNING (SELECT MAX (id) +1 FROM table) AS id
you run into concurrency issues that way. (two concurrent inserts
could pick the same ID, one will fail with an error)
> Quero your opinions on how best to insert data to have less maintenance and
> better productivity with concurrent users.
> Thank you very much.
INSERT INTO table ( ...) VALUES ( ...) RETURNING id;
Use the sequence, that's what they were designed for.
Let id get the default value and pull that from the returning.
you will get gaps in the serquence due to failed or cancelled
transactions but there will probably not be many gaps.
--
⚂⚃ 100% natural
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: The best option to insert data with primary id
In-Reply-To: <idvj4m$8e4$1@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox