Bug?

Indira Muthuswamy wrote:

Hai,

I have encountered a problem with PostgreSQL.I have created a table
'tab1' with a column 'a' with serial type.I entered 20 records into
the table.So the query
select max(a) from tab1;
returned 20.When I tried the same query after the command
truncate table tab1;
I found that the output of the first query as

max
-----

(1 row)

When I tried to insert a new row into the table tab1 I found that the
value at column a incremented to the value 21.
But I heard from my friends that the value of the serial column gets
decremented whenever we issue the 'truncate table' command (in MS SQL
SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify
me on this?

Sure. You heard wrong about postgres.

Please do not ask questions like this on the -hackers list - this should
have gone to pgsql-general. The hackers list is for postgresql
development discussions only.

cheers

andrew

Moving to -sql.

On Wed, Oct 18, 2006 at 06:53:46PM +0530, Indira Muthuswamy wrote:

Hai,

I have encountered a problem with PostgreSQL.I have created a table
'tab1' with a column 'a' with serial type.I entered 20 records into the
table.So the query
select max(a) from tab1;
returned 20.When I tried the same query after the command
truncate table tab1;
I found that the output of the first query as

max
-----

(1 row)
When I tried to insert a new row into the table tab1 I found that the
value at column a incremented to the value 21.
But I heard from my friends that the value of the serial column gets
decremented whenever we issue the 'truncate table' command (in MS SQL
SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me on
this?

Your friends are mistaken. TRUNCATE does nothing to affect sequences.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Then how do we clear the values of a serial column(is it done only by
dropping the column?)?

Regards,
M.Indira

See section 9.12 of the docs.

On Thu, Oct 19, 2006 at 12:28:58PM +0530, Indira Muthuswamy wrote:

Then how do we clear the values of a serial column(is it done only by
dropping the column?)?

Regards,
M.Indira

On 10/19/06, Jim C. Nasby <jim@nasby.net> wrote:

Moving to -sql.

On Wed, Oct 18, 2006 at 06:53:46PM +0530, Indira Muthuswamy wrote:

Hai,

I have encountered a problem with PostgreSQL.I have created a table
'tab1' with a column 'a' with serial type.I entered 20 records into the
table.So the query
select max(a) from tab1;
returned 20.When I tried the same query after the command
truncate table tab1;
I found that the output of the first query as

max
-----

(1 row)
When I tried to insert a new row into the table tab1 I found that the
value at column a incremented to the value 21.
But I heard from my friends that the value of the serial column gets
decremented whenever we issue the 'truncate table' command (in MS SQL
SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me

on

this?

Your friends are mistaken. TRUNCATE does nothing to affect sequences.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Then how do we clear the values of a serial column(is it done only by
dropping the column?)?

If you look at the following link, you will see that serial is not really a true data-type. It is
merely a short-cut to get the desired results:

http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL

Also, if you are interested in resetting your columns "serial" value back to 1 or 0 or -10000, you
can do it using the setval() function for the following link:

http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html

Regards,

Richard Broersma Jr.