Thread: serial column detection

Hi!,how detects PgAdmin when it should show a column as "type" serial? i am trying
to replicate in a existing table the conditions to get shown as serial but i
don't get it.

I had a table like this:

CREATE TABLE test
( test_id integer NOT NULL, CONSTRAINT test_pkey PRIMARY KEY (test_id),
);


that i would like to see as:

CREATE TABLE test
( test_id serial NOT NULL, CONSTRAINT test_pkey PRIMARY KEY (test_id),
);


i have tried with:

CREATE SEQUENCE test_test_id_seq;
ALTER TABLE test ALTER COLUMN test_id SET DEFAULT
nextval('test_test_id_seq'::regclass);
ALTER SEQUENCE test_test_id_seq OWNED BY test.test_id;


and now i have this table:

CREATE TABLE test
( test_id integer NOT NULL DEFAULT nextval('test_test_id_seq'::regclass), CONSTRAINT test_pkey PRIMARY KEY (test_id),
);

What should i do? Thanks!


Regards,
Miguel Angel.

On Thu, 2012-10-25 at 20:35 +0200, Linos wrote:
> Hi!,
>     how detects PgAdmin when it should show a column as "type" serial? i am trying
> to replicate in a existing table the conditions to get shown as serial but i
> don't get it.
> 
> I had a table like this:
> 
> CREATE TABLE test
> (
>   test_id integer NOT NULL,
>   CONSTRAINT test_pkey PRIMARY KEY (test_id),
> );
> 
> 
> that i would like to see as:
> 
> CREATE TABLE test
> (
>   test_id serial NOT NULL,
>   CONSTRAINT test_pkey PRIMARY KEY (test_id),
> );
> 
> 
> i have tried with:
> 
> CREATE SEQUENCE test_test_id_seq;
> ALTER TABLE test ALTER COLUMN test_id SET DEFAULT
> nextval('test_test_id_seq'::regclass);
> ALTER SEQUENCE test_test_id_seq OWNED BY test.test_id;
> 
> 
> and now i have this table:
> 
> CREATE TABLE test
> (
>   test_id integer NOT NULL DEFAULT nextval('test_test_id_seq'::regclass),
>   CONSTRAINT test_pkey PRIMARY KEY (test_id),
> );
> 
> What should i do? Thanks!
> 

If a column is of type integer or biginteger, and that we have a default
value matching the one that would get a serial or bigserial, we declare
it as a serial or bigserial if we are connected to an 8.0 or previous
release. For 8.1 and upwards, we also try to find a dependency between
the sequence and the table. See pgColumn.cpp, lines 345 to 386.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com