From: - <grandebuzon@gmail.com>
To: pgsql-sql@postgresql.org
Subject: The best option to insert data with primary id
Date: Mon, 6 Dec 2010 15:59:28 +0100
Message-ID: <AANLkTint7hvf03OXc8ujqWE+a2ETWUXu7NtHysKqZjUG@mail.gmail.com> (raw)
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?
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
Quero your opinions on how best to insert data to have less maintenance and
better productivity with concurrent users.
Thank you very much.
Same question in Spanish.
Hola a todos,
tengo una pregunta sobre como mejor hacer insert en la tabla y manipular id
con primary key para mejor productividad. Necesito Insertar datos a la tabla
y obtener ultima id.
1. Primera opcion llevar contador con SEQUENCE de Postgres :
INSERT INTO table (id, ...) VALUES ((SELECT nextval('seq_table')), ...)
RETURNING (SELECT currval('seq_table')) AS id
Unica cosa que veo, que si no se inserta la fila, el contador se incrementa
cada ves cuando se llama. Entonces habran id vacias sin usar en la tabla y
numero de id se va a crecer mucho. Habran muchos registros. Esta id
declarada como type int8 con longitud 64.
Hay alguna opcion de ocupar registros de sequence vacias. Tengo que
preocupar por esto?
2. Segunda opcion es coger control de id como
INSERT INTO table (id, ...) VALUES ((SELECT MAX(id)+1 FROM table), ...)
RETURNING (SELECT MAX(id)+1 FROM table) AS id
Quero vuestras opiniones sobre como mejor hacer insert de datos para tener
menor mantenimiento y mejor productividad con usuarios concurrentes.
Muchas gracias.
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: grandebuzon@gmail.com
Subject: Re: The best option to insert data with primary id
In-Reply-To: <AANLkTint7hvf03OXc8ujqWE+a2ETWUXu7NtHysKqZjUG@mail.gmail.com>
* 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