Changing the type of a column in an already populated database.

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.0 system. (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 such a 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.0 system. (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

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.0 system. (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

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 nothing about 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 nothing about 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:

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