Thread: More newbie question: Restructuring a table

Hi again,

Is there a quick and dirty way that I am missing for creating a new
database with a slightly different schema than the old db?

For example, I have a table with a DATETIME field that I really need to
change to a DATE field.

I can create a new table with that field defined as a DATE, but if I do
the following:

INSERT INTO newtable SELECT * FROM oldtable;

I get an data type error.

Is there a "Standard", quick method for doing something like that?

Thanks much.


--
Stand Fast,
    tjg.

Chief Technology Officer              tjg@exceptionalminds.com
Red Hat Certified Engineer            www.exceptionalminds.com
Avalon Technology Group, Inc.                   (503) 246-3630
>>>>>>>>>>>>Linux...Because rebooting isn't normal<<<<<<<<<<<<

On Wed, 1 Mar 2000, Timothy Grant wrote:

> Hi again,
>
> Is there a quick and dirty way that I am missing for creating a new
> database with a slightly different schema than the old db?
>
> For example, I have a table with a DATETIME field that I really need to
> change to a DATE field.
>
> I can create a new table with that field defined as a DATE, but if I do
> the following:
>
> INSERT INTO newtable SELECT * FROM oldtable;
>

 Example:

 SELECT column1, column2::date, column3 INTO newtable FROM oldtable;

 - where column2 is a column with your not wanted datetime and is changed to
date. You must cast to new type from old type.
                        Karel

On Wed, 1 Mar 2000, Timothy Grant wrote:

> Is there a quick and dirty way that I am missing for creating a new
> database with a slightly different schema than the old db?

pg_dump lets you dump out the schema only. Try that, edit it to your taste
and read it back in with psql (into the new database).


--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden