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 ...



Now, how can I do the above in PostgreSQL ??

Do help.

Thanks & Regards.

Look at http://www.postgresql.org/idocs/ind...altertable.html in the comments, there is something that says:

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

Hi

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



PostgreSQL is open source, has more features compared to MySQL. .... some of the reasons...

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

Regards.

Sheesh, are we supposed to GUESS what's happening? What error message do you get?

Is this the actual query you are using? What's a varchar2? Do you mean varchar(23), varchar(3)?

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?

Ooops, didn't realize that had happened.

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 .

Hi All

Thanks for the input.




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=#




The VARCHAR datatype is currently synonymous with the VARCHAR2 datatype ... ref...

http://technet.oracle.com/docs/prod..._elem.htm#45443

Regards.

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;

You are using Postgres not Oracle. varchar2 has no meaning in postgres.

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/ind...altertable.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:


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.

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!

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.

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.