#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    69
    Rep Power
    12

    Modify Data Type Of A Column


    Hi

    Is it possible to change the data type of a column in a table ?

    As per docs, it does not seems to be there ...??

    However, I want to make sure...

    For eg., in Oracle, we can do this ...

    Code:
     alter table xyz modify name varchar2(3);
    Now, how can I do the above in PostgreSQL ??

    Do help.

    Thanks & Regards.
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Look at http://www.postgresql.org/idocs/inde...ltertable.html in the comments, there is something that says:
    "How do I alter the type of a column?"

    I had this problem too and finally found it!
    use the following syntax:

    ALTER TABLE tablename MODIFY columnname new type;
    anyway it seems to be unclear

    A question:
    Why did you choose PostgreSQL? There are also SAPDB and Firebird, which have similar characteristics, SAPDB has even an Oracle 7 compatibility mode, or something like that
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    69
    Rep Power
    12
    Hi

    Thanks for your reply...But it is not working...

    Originally posted by pabloj

    A question:
    Why did you choose PostgreSQL? There are also SAPDB and Firebird, which have similar characteristics, SAPDB has even an Oracle 7 compatibility mode, or something like that
    PostgreSQL is open source, has more features compared to MySQL. .... some of the reasons...

    BTW, what about SAPDB, Firebird ?? comparable to PostgreSQL ??

    Regards.
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Thanks for your reply...But it is not working...
    Sheesh, are we supposed to GUESS what's happening? What error message do you get?

    alter table xyz modify name varchar2(3);
    Is this the actual query you are using? What's a varchar2? Do you mean varchar(23), varchar(3)?
  8. #5
  9. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    SAPDB (www.sapdb.org) and Firebird (http://firebird.sourceforge.net) are two enterprise level dbs both opensource and free, available for many platforms, with good management tools, odbc and jdbc drivers.
    They are well worth a try.

    BTW Rod, could you please delete duplicated posts?
    Last edited by pabloj; August 20th, 2002 at 07:35 AM.
  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Ooops, didn't realize that had happened.
  12. #7
  13. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Speaking of management tools, there is a new alpha release of PGAdminII, which will make a lot of PostgreSQL work a little less painful. It looks like it is going to be stable fairly soon.

    As to why people choose the 9.5 MB PostgreSQL download, instead of the 41 MB SAPDB, that is one answer. PostgreSQL is very lightweight and simple to deploy, and for the quality and scope of features you get, it runs on amazingly low-end hardware. On my FreeBSD servers, most of the time, PostgreSQL takes up less memory than MySQL. I don't really know anything about Firebird, but I haven't heard any real case histories of companies using it, yet. Heard any? I would like to know. It only recently was released as version 1.

    One other great reason to choose PostgreSQL is that it is the only enterprise-level DBMS that runs on FreeBSD (natively). In fact, it seems PostgreSQL's C source is so clean that it just...compiles, on almost any Unix platform .
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    69
    Rep Power
    12
    Hi All

    Thanks for the input.


    Originally posted by rod k
    What error message do you get?
    hris=# \d con10
    Table "con10"
    Attribute | Type | Modifier
    -----------+-----------------------+----------
    did | integer | not null
    name | character varying(40) | not null
    Index: con10_pkey

    hris=# ALTER TABLE con10 MODIFY did decimal;
    ERROR: parser: parse error at or near "modify"
    hris=# ALTER TABLE con10 MODIFY name int;
    ERROR: parser: parse error at or near "modify"
    hris=# select count(*) from con10;
    count
    -------
    0
    (1 row)

    hris=# ALTER TABLE con10 MODIFY name text;
    ERROR: parser: parse error at or near "modify"
    hris=# ALTER TABLE con10 MODIFY name new text;
    ERROR: parser: parse error at or near "modify"
    hris=#


    Originally posted by rod k
    Is this the actual query you are using? What's a varchar2? Do you mean varchar(23), varchar(3)?
    The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype ... ref...

    http://technet.oracle.com/docs/produ...elem.htm#45443

    Regards.
  16. #9
  17. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    OK, didn't see this the first time: Modify is NOT valid postgres. You need to use ALTER as in:

    alter table con10 alter name int;

    The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype ... ref...http://technet.oracle.com/docs/prod..._elem.htm#45443
    You are using Postgres not Oracle. varchar2 has no meaning in postgres.
  18. #10
  19. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    1. Why refer to Oracle tech docs for PostgreSQL database? There is no varchar2 in PostgreSQL. (Oops... I see Rod beat me to it)

    2. See http://www.postgresql.org/idocs/inde...ltertable.html . PostgreSQL does not yet support changing column types or even dropping columns from a table. The simplest thing is just to dump the table definition and data, alter the SQL create statements and run the dump script. It's really not that hard.

    3. Or you can run a query that copies the data to a temporary table, drops and recreates your main table, and then re-imports your data. This can all be done with one atomic operation, which is no problem unless your table is very large and consistently busy:
    Code:
    BEGIN;
    CREATE TABLE "my_temp" AS SELECT * FROM "mytable";
    DROP TABLE "mytable";
    CREATE TABLE "mytable" (
    -- your new table definition here --
    );
    INSERT INTO "mytable" SELECT * FROM "my_temp";
    DROP TABLE "my_temp";
    COMMIT;
    4. If you really must do this on a live database, without any possibility of dumping, there is another trick you can consider: RENAME the existing column, add a new column with the original column name, using your desired datatype, and then run a quick query which copies all values from the old column to the new one. (of course, keep all this in a transaction). You will be left with an extra empty column, but you can find a convenient time to drop that column with a script like the one above.

    Tools such as phpPgAdmin and PGAdminII handle some of this stuff for you.
    Last edited by rycamor; August 21st, 2002 at 02:39 AM.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  20. #11
  21. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    rycamor

    Firebird is derived from Interbase 6 released as opensource from Borland, so it's a 1 version but really could be called at least 6.5.

    I know that PostgreSQL is an excellent db, but I've never considered the small download a feature, and the ability to run on old hardware is not a feature for an enterprise level db IMHO, as no critical/stressed application would be put on outdated harware. Of course it is if you are offering some cheap hosting service or something like that.
    Also I don't understand why PostgreSQL is so allergic to windows, other dbs are really cross-platform!
  22. #12
  23. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    I'm aware of Firebird's roots, but there were changes made, which is why it's not Interbase 6.5 (as I recall, there was a bit of a fight over certain source and functionality for awhile). But still, where are the Firebird users? I still haven't heard any real-world cases. I am just curious.

    My point about the small download (non-feature) of PostgreSQL is that it is a very efficiently coded, and very efficiently performing DBMS. There is no other DBMS out there that puts as much functionality into that concise amount of code. I'm not advocating putting it on outdated hardware by any means, although that does make for a cheap development station. And it runs on an ultra-stable operating system that most other DBMS vendors have chosen to ignore.

    PostgreSQL may be Windows-unfriendly, (although even that is changing), but that is probably a function of the strength of it's Unix C code: no fluff. I have heard that the C in PostgreSQL source is considered textbook material for good form, etc... I pose the same argument you did: why would anyone want to run an enterprise-class database on a Windows machine ?

    Really, I would like to have the time to check out Firebird, as I have heard some good things about it, as far as scaleability, etc.., but I suspect it doesn't have anywhere near the advanced logical functionality PostgreSQL has. Whatever it may lack in raw power, PostgreSQL is a dream for data manipulation, and I think in some ways is the most advanced SQL DBMS system available, period. Yes, it needs more scaleability, replication, etc... but those are quantitative, not qualitative.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    69
    Rep Power
    12
    Hi

    I was only refering to a parallel in Oracle when posting my question, i.e., this is available in oracle , now what is the equivalent in PostgreSQL. This was my intention.

    That clears it, I hope.

    Many thanks for all the valuable information.

    Best Regards.




IMN logo majestic logo threadwatch logo seochat tools logo