Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 44E451337B54 for ; Mon, 6 Dec 2010 10:59:37 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 87454-08 for ; Mon, 6 Dec 2010 14:59:31 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f50.google.com (mail-ww0-f50.google.com [74.125.82.50]) by mail.postgresql.org (Postfix) with ESMTP id CD4941337B51 for ; Mon, 6 Dec 2010 10:59:28 -0400 (AST) Received: by wwf26 with SMTP id 26so8434417wwf.19 for ; Mon, 06 Dec 2010 06:59:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:date:message-id :subject:from:to:content-type; bh=eVaGSsTrdVaS7cYQLU/CqYULr/2k1iJJUYXcYOEIKrA=; b=ejDMhdoWV9T8GYBjpXxUt6lmgkMp4nKR/UGXHVVRskId1jprdOXW4TYfbgop34JvwH Jz++INtii52pHkn48UiZcq6EqJxUK3/V4hnZBKX/XTHApgDb+qLr6C+T7Hd+lvYR3LzG u03dc0j/Ian0XICXx/6DLQgqIru2m3wtr0KiE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=BeK2xdxSs809y8cvuR9+blnubektYR7Oa9Fz9qU7wF8wA0F0rqRJuqfwfvu6Vve5cl Hn2kv5opEjqjs82VgvC2ZOn+EqF/yOW+wu+GZXiuR5yVmNuj2jNbRuC63jNfiVi7zD+B 2KKrYh6wP6LpdWKi7DgApLOmniJDHbvJrpYVI= MIME-Version: 1.0 Received: by 10.216.13.207 with SMTP id b57mr5023199web.32.1291647568552; Mon, 06 Dec 2010 06:59:28 -0800 (PST) Received: by 10.216.6.8 with HTTP; Mon, 6 Dec 2010 06:59:28 -0800 (PST) Date: Mon, 6 Dec 2010 15:59:28 +0100 Message-ID: Subject: The best option to insert data with primary id From: - To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016364d26cf7fa4970496bf2224 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.112 tagged_above=-5 required=5 tests=BAYES_50=0.8, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201012/30 X-Sequence-Number: 34866 --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? 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. --0016364d26cf7fa4970496bf2224 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi everyone,
=A0I have a question about how best to insert and manipulate the table with= primary key id for better productivity.=A0I need t= o insert data into the table and get last id.

1.=A0First option to ta= ke counter Postgres SEQUENCE:
INSERT INTO table = (id, ...) VALUES ((SELECT nextval ('seq_table')), ...) RETURNING (S= ELECT currval ('seq_table')) AS id

Only thing I see, that if the row is not insert= ed, the counter is incremented every time when called.=A0Then they will have empty unused id in the table and ID number will g= row much.=A0There will be many records.=A0This id int8 type declared with length 64.
Is there any option to occupy empty sequence record= s.=A0I have to worry about this?

2.=A0Second 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 o= n 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,
=A0tengo una pregunta sobre como mejor hacer insert en la tabla y manipula= r 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 Postgr= es :
INSERT INTO table (id, ...) VALUES ((SELECT nextval('seq_table')),= ...) =A0RETURNING (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.=A0
Hay alguna opcion de ocupar registros de sequence vacias. Tengo que preocu= par por esto?

2. Segunda opcion es coger control de id como
IN= SERT INTO table (id, ...) VALUES ((SELECT MAX(id)+1 FROM table), ...)=A0
RETURNING (SELECT MAX(id)+1 FROM table) AS id

Quero vuestras opiniones sobre como mejor hacer insert de datos para ten= er menor mantenimiento y mejor productividad con usuarios concurrentes.
Muchas gracias.


--0016364d26cf7fa4970496bf2224--