From: Dinesh Chandra 12108 <Dinesh.Chandra@cyient.com>
To: John Gorman <jgorman@eldocomp.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
	"pgsql-performance-owner@postgresql.org"
	<pgsql-performance-owner@postgresql.org>
Subject: Re: Auto generate number in Postgres-9.1.
Thread-Topic: [PERFORM] Auto generate number in Postgres-9.1.
Thread-Index: AdKhfwAfZEZINCnoSxGDchgEFLEA3f//paCAgAAB7wD//5+kgA==
Date: Mon, 20 Mar 2017 14:08:07 +0000
Message-ID: <d932f1ffb7b84318b96477f3cfc84e65@cyient.com>
References: <ab77f3e1c16540a09002740e283529c5@cyient.com>
 <97da050c-395c-7205-c5e9-95e7fdf13fde@berkus.org>
 <0A1B0B276DEE6441A2E68EB66D4540DAE6869132@winex2.eldocomp.com>
In-Reply-To: <0A1B0B276DEE6441A2E68EB66D4540DAE6869132@winex2.eldocomp.com>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

Thanks for your immediate response!!!!

Its working fine when we insert a new row.

But on deletion it's not automatically re-adjusting the id's.

Do I need to create trigger for this??

Regards,
Dinesh Chandra
|Database administrator (Oracle/PostgreSQL)| Cyient Ltd. Noida.


-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owne=
r@postgresql.org] On Behalf Of John Gorman
Sent: 20 March, 2017 7:20 PM
To: pgsql-performance@postgresql.org; pgsql-performance-owner@postgresql.org
Subject: Re: [PERFORM] Auto generate number in Postgres-9.1.

Sequences are stored as a separate object in PostgreSQL.

Here in this example table and you can see that rec_id is a sequence number=
 and that the object name is: whiteboards_rec_id_seq

mydb=3D> \d whiteboards

                                         Table "public.whiteboards"
    Column     |            Type             |                          Mod=
ifiers
---------------+-----------------------------+--------------------------
---------------+-----------------------------+--------------------------
---------------+-----------------------------+----------
 rec_id        | integer                     | not null default nextval('wh=
iteboards_rec_id_seq'::regclass)
 board_name    | character varying(24)       | not null
 board_content | text                        | not null
 updatets      | timestamp without time zone | default now()
Indexes:
    "whiteboards_pkey" PRIMARY KEY, btree (rec_id)

Now I can display the whiteboards_rec_id_seq object

mydb=3D> \dS whiteboards_rec_id_seq
     Sequence "public.whiteboards_rec_id_seq"
    Column     |  Type   |         Value
---------------+---------+------------------------
 sequence_name | name    | whiteboards_rec_id_seq
 last_value    | bigint  | 12
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 31
 is_cycled     | boolean | f
 is_called     | boolean | t

-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owne=
r@postgresql.org] On Behalf Of Josh Berkus
Sent: Monday, March 20, 2017 6:43 AM
To: Dinesh Chandra 12108; pgsql-performance-owner@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Auto generate number in Postgres-9.1.

Dinesh,

> I have to add one column  "ID" in postgres table which will generate
> Auto Increment
> <http://www.davidghedini.com/pg/entry/postgresql_auto_increment>ed number=
 .
>
>
>
> Example:
>
> Suppose I have five records and if I insert 1 new record It should
> auto generate 6.

https://www.postgresql.org/docs/9.6/static/sql-createsequence.html
also SERIAL on this page:
https://www.postgresql.org/docs/9.6/static/datatype-numeric.html


>
> If I truncate the same table and then again insert rows should start
> with 1 in "ID" column.

That's not how it works, normally.  I'd suggest adding an ON TRUNCATE trigg=
er to the table.


--
Josh Berkus
Containers & Databases Oh My!


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

________________________________

DISCLAIMER:

This email message is for the sole use of the intended recipient(s) and may=
 contain confidential and privileged information. Any unauthorized review, =
use, disclosure or distribution is prohibited. If you are not the intended =
recipient, please contact the sender by reply email and destroy all copies =
of the original message. Check all attachments for viruses before opening t=
hem. All views or opinions presented in this e-mail are those of the author=
 and may not reflect the opinion of Cyient or those of our affiliates.


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance