Thread: integer column to serial "type"

Hello,
    there is some way to get and old integer primary key column to show the "type"
serial at table description?

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),
);


i know this works the same as serial but do i have some way to get this table to
show me test_id column as "serial" type without have to recreate the table and
restore original rows?

Regards,
Miguel Angel.

Linos <info@linos.es> writes:
>     there is some way to get and old integer primary key column to show the "type"
> serial at table description?

No, because there is no such type.  "serial" is just a convenient macro
that you can use in CREATE TABLE; it's not a real type.

            regards, tom lane

On 25/10/12 17:39, Tom Lane wrote:
> Linos <info@linos.es> writes:
>>     there is some way to get and old integer primary key column to show the "type"
>> serial at table description?
>
> No, because there is no such type.  "serial" is just a convenient macro
> that you can use in CREATE TABLE; it's not a real type.
>
>             regards, tom lane
>
>

Yeah, i knew it's not a real type and supposed the outcome, i only wanted to be
sure, i find much more pleasant to see:

test_id serial NOT NULL,
than
test_id integer NOT NULL DEFAULT nextval('test_test_id_seq'::regclass),

but i can live with it, thanks!

Regards,
Miguel Angel.

On 25/10/2012 16:32, Linos wrote:
> Hello,
>     there is some way to get and old integer primary key column to show the "type"
> serial at table description?
>
> 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),
> );


PgAdmin manages it somehow, because it's able to show columns as
"serial"... maybe it infers it from the ownership dependency between the
sequence and the column.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 25/10/12 19:45, Raymond O'Donnell wrote:
> On 25/10/2012 16:32, Linos wrote:
>> Hello,
>>     there is some way to get and old integer primary key column to show the "type"
>> serial at table description?
>>
>> 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),
>> );
>
>
> PgAdmin manages it somehow, because it's able to show columns as
> "serial"... maybe it infers it from the ownership dependency between the
> sequence and the column.
>
> Ray.
>
>

Yeah i was referring to PgAdmin, didn't notice that columns shown as serial in
PgAdmin are shown like integer nextval(sequence) in \d from psql, i will try to
ask in PgAdmin mailing list how they detect a "serial" column because i have
tried to replicate the exact setup (ownership included) of one and it doesn't works.

Regards,
Miguel Angel.