Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005

    Unanswered: Problem changing data type to serial

    Hello all,

    I am trying to change a datatype of a column
    from integer to serial. There is some data already
    in the table. I am using PostgreSQL v8..

    This is the simple table..
    CREATE TABLE public.changedatatypetest
    name varchar(10),
    id integer,
    However, when I try and change the data type
    all I get is the following error message:

    ERROR: type "serial" does not exist.

    I get this error either if I am doing it with PG Lightning Admin
    of "Manually" using the following SQL command:

    ALTER TABLE changedatatypetest ALTER COLUMN  TYPE serial;
    Now, if I add a new column there is no problem setting
    the data type to serial. If I try and change the data type
    to varchar, there is no problem.

    I have soon pretty much done everything, Removing all
    constraints, removing all data in the table etc. But nothing works.

    Is it possible to change data type to serial, or should i tackle this
    another way?

    Kind Regards!

  2. #2
    Join Date
    Sep 2005
    To respond to my own question, as far as I
    know the reason for the above not to work
    is because "serial" is not a true data type.

    And using serial on a column involves several
    steps, which is not supported by ALTER as of

    Perhaps in the future....


  3. #3
    Join Date
    Feb 2005
    Colorado Springs
    I've not tried this with PG Lightning Admin, but if you post a question on the AM Software forum, I suspect Tony Caduto will respond - he has been very willing to communicate with me.

    A "work around" is possible with PG Admin III. First, create a sequence (simply right-click sequences, select New Sequence, and fill in the pop-up box, making sure to set the Next Val to the next available value for your table). Next, make sure your table variable is set to Not Null and Unique (i.e., a primary key). Then set the default value for this variable to nextval('public.your_seq'::text). It is my understanding from the PG Admin III documentation that this recreates the internal workings of a sequence. Works fine in my app.

  4. #4
    Join Date
    Sep 2005
    This worked for PG Lightning Admin as well, I used
    the same steps as you described and the data type
    is now changed.

    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts