Re: 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=> \d whiteboards

Table "public.whiteboards"
Column | Type | Modifiers
---------------+-----------------------------+--------------------------------------------------------------
rec_id | integer | not null default nextval('whiteboards_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=> \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(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Josh Berkus
Sent: Monday, March 20, 2017 6:43 AM
To: Dinesh Chandra 12108; pgsql-performance-owner(at)postgresql(dot)org
Cc: pgsql-performance(at)postgresql(dot)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
trigger to the table.

--
Josh Berkus
Containers & Databases Oh My!

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