Thread: Changing the type of a column in an already populated database.
Hi! I'd like to know if it is possible to change the type of a column to a compatible one, in an already populated database. For example, changing a column from varchar(20) to varchar(25) ? I'm using postgresql 7.3rc1 Thanks for your help. Best regards, David -- dpradier@apartia.fr
I want to use PostgreSQL with this CMS system EZ Publish But installation manual of EZ Publish says that if I want to use PostgreSQL with EZ, Postgre has to be compiled with: NAMEDATALEN 64 (instead of 32) in the source file : src/include/postgres_ext.h When I compile postgre 7.2.3 with that change, it compiles ok, but it can't create databases/database tables on my RedHat8.0system. (I can create tables without that change) 'initdb' crashes. If they suggests to do that, I guess it should be possible, but with which version of postgre and/or on which system is sucha change possible?
Henry Pedask <henry@sekretar.ee> writes:
> I want to use PostgreSQL with this CMS system EZ Publish
> But installation manual of EZ Publish says that if I want to use PostgreSQL with EZ, Postgre has to be compiled with:
> NAMEDATALEN 64 (instead of 32)
> in the source file : src/include/postgres_ext.h
> When I compile postgre 7.2.3 with that change, it compiles ok, but it can't create databases/database tables on my
RedHat8.0system. (I can create tables without that change)
> 'initdb' crashes.
You probably skipped doing "make clean" before "make all"; you need to
be sure that everything gets recompiled after making such a change.
Or you could move to 7.3rc1 --- NAMEDATALEN is 64 by default in 7.3.
regards, tom lane
> Or you could move to 7.3rc1 --- NAMEDATALEN is 64 by default in 7.3. Does this mean that serial sequence names will change? For example, instead of: test=# create table reallylongtablename (reallylongserialname serial); NOTICE: CREATE TABLE will create implicit sequence 'reallylongtab_reallylongser_seq' for SERIAL column 'reallylongtablename.reallylongserialname' It will be: test=# create table reallylongtablename (reallylongserialname serial); NOTICE: CREATE TABLE will create implicit sequence 'reallylongtablename_reallylongserialname_seq' for SERIAL column 'reallylongtablename.reallylongserialname' Greg
"Gregory Wood" <gregw@com-stock.com> writes:
>> Or you could move to 7.3rc1 --- NAMEDATALEN is 64 by default in 7.3.
> Does this mean that serial sequence names will change?
Yes, the serial-sequence name compaction algorithm has always been
affected by NAMEDATALEN ... the algorithm is still the same, but where
it kicks in is different ...
regards, tom lane
David Pradier wrote: > Hi! > > I'd like to know if it is possible to change the type of a column to a > compatible one, in an already populated database. > For example, changing a column from varchar(20) to varchar(25) ? > I'm using postgresql 7.3rc1 > > Thanks for your help. > > Best regards, > David dump the database, edit the dump file, and reload. of course, this is annonying on live data, but it works
>You probably skipped doing "make clean" before "make all"; you need to Thank you so much! Your comment really helped Why I couldn't see it myself? Stupid! Maybe it's because about a week ago I knew nothing about compiling linux software, and a month ago I knew almost nothingabout linux. So I'm total newbie. Thanks, Henry Pedask
On 22 Nov 2002 at 10:04, Henry Pedask wrote: > Why I couldn't see it myself? Stupid! > Maybe it's because about a week ago I knew nothing about compiling linux software, and a month ago I knew almost nothingabout linux. > So I'm total newbie. Well, this might save you some reading.. Somethings you may know some may not.. Following are frequently used make targets for software packaegs that use autoconf/automake for package builiding 1) clean: Removes all object files and any compiled binaries. It makes sure that any build afterwards is "from the scratch" and without any stale objects in it. Always recommended before installing on production machine 2)distclean: Just like clean but it removes makefiles too. You have to do a ./configure again. If you have changed the Makefile.am around, this is the way to go 3install: Installs the compiled software. If no, software is compiled, it will compile first and install later. so a make clean;make install would work as expected. 4)uninstall: Uninstalls the package previously installed by "make install" Note that you shouldn't have done ./configure in between install and uninstall. That might mess up with installation target.. If you plan to use uninstall, check chekinstall. It creates debs/rpms/tgzs before actually installing. So package maintenance becomes pretty easy. And lastly, just issuing "make" would compile the software to bring it upto date.. HTH Bye Shridhar -- "[In 'Doctor' mode], I spent a good ten minutes telling Emacs what Ithought of it. (The response was, 'Perhaps you could try to be lessabusive.')"(By Matt Welsh)
Hi, --On Donnerstag, 21. November 2002 23:45 -0500 Brian Minton <bminton@efn.org> wrote: > David Pradier wrote: >> Hi! >> >> I'd like to know if it is possible to change the type of a column to a >> compatible one, in an already populated database. >> For example, changing a column from varchar(20) to varchar(25) ? >> I'm using postgresql 7.3rc1 >> >> Thanks for your help. >> >> Best regards, >> David > > dump the database, edit the dump file, and reload. of course, this is > annonying on live data, but it works Its more easy with a temporary or short lifetime table: create table temp_table as select * from oldtable; drop oldtable; create oldtable ( ...); insert into oldtable select * from temp_table; You can cover this in a transcation and with luck nobody will even notice it for the whole process :) (Take care of referential integrity or stored functions as they might reference a cached, e.g. OID based reference to the old table) Regards Tino
On Thu, 21 Nov 2002, Brian Minton wrote: > David Pradier wrote: > > Hi! > > > > I'd like to know if it is possible to change the type of a column to a > > compatible one, in an already populated database. > > For example, changing a column from varchar(20) to varchar(25) ? > > I'm using postgresql 7.3rc1 > > > > Thanks for your help. > > > > Best regards, > > David > > dump the database, edit the dump file, and reload. of course, this is > annonying on live data, but it works The other method, which works well on tables with no triggers or fks or anything, which is common when you're first building tables and such: create table test (name varchar(20), id int, bucks numeric(12,2)); (insert some data, play around...) begin; select name::varchar(32), id, bucks into test2 from test; drop table test; alter table test2 rename to test; commit;
I wonder if there are any intentions of supporting this feature via alter table. That woud be so useful... If I'm not mistaking.....Informix supports that....anyone know if Oracle or sybase support this... FYI, I'm talking about create table test ( id serial, name text) ; alter table test alter name varchar(20); In phase one PG will just do the job....hopefully you the user know what you are doing. in phase two PG can run a check to see if any integrity or constraint will yield invalid or ambiguous, fix if you can, error out if you can'nt. Brian Minton wrote: > David Pradier wrote: > >> Hi! >> >> I'd like to know if it is possible to change the type of a column to a >> compatible one, in an already populated database. >> For example, changing a column from varchar(20) to varchar(25) ? >> I'm using postgresql 7.3rc1 >> >> Thanks for your help. >> >> Best regards, >> David > > > dump the database, edit the dump file, and reload. of course, this is > annonying on live data, but it works > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
I've been checking the news (Cda) and download (US) sections of a few postgresql mirrors, anxiously waiting for 7.3 RC1. I see that Tom Lane mentions it in his response below. Will it be out soon? (I ask rhetorically). Is there a release date? I don't mean to badger, but . . . I am looking forward to using the latest jdbc driver which supports java 2 connection pooling. Tom Lane wrote: >Henry Pedask <henry@sekretar.ee> writes: > > >>I want to use PostgreSQL with this CMS system EZ Publish >>But installation manual of EZ Publish says that if I want to use PostgreSQL with EZ, Postgre has to be compiled with: >>NAMEDATALEN 64 (instead of 32) >>in the source file : src/include/postgres_ext.h >>When I compile postgre 7.2.3 with that change, it compiles ok, but it can't create databases/database tables on my RedHat8.0system. (I can create tables without that change) >>'initdb' crashes. >> >> > >You probably skipped doing "make clean" before "make all"; you need to >be sure that everything gets recompiled after making such a change. > >Or you could move to 7.3rc1 --- NAMEDATALEN is 64 by default in 7.3. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > > -- Ron St.Pierre Syscor R&D tel: 250-361-1681 email: rstpierre@syscor.com
Ron St.Pierre wrote: > I've been checking the news (Cda) and download (US) sections of a few > postgresql mirrors, anxiously waiting for 7.3 RC1. I see that Tom Lane > mentions it in his response below. Will it be out soon? (I ask > rhetorically). Is there a release date? RC2 is already out ... go to the developpers site if you would like to download it. Jc