Thread: Nu-B\Column:Alter type

 Hello List...

 How do I change a column from date type to text?

 thanks
 /j-p.


----------------------
 JUSTATEST Art Online
  www.justatest.com

John-paul,

>  How do I change a column from date type to text?

This functionality is not supported in version 7.2 or earlier.  You
 will have to drop and re-create the table:

1. CREATE TABLE temp1 AS
    SELECT * FROM bad_table;
2. DROP TABLE bad_table;
3. CREATE TABLE bad_table;
4. INSERT INTO bad_table SELECT * FROM temp1;
5. DROP TABLE temp1

-Josh Berkus

Thanks for your support Josh...

In your sequence below - where does the column (e.g. col 8 of 25) get changed?

Would I list the good column names to insert into bad_table in point 4., then insert my new column - or is there a way
to"select * except one" (I guess that's pretty lax SQL)? 

Am I making sense?

/j-p.


On Fri, 22 Feb 2002, Josh Berkus wrote:

> John-paul,
>
> >  How do I change a column from date type to text?
>
> This functionality is not supported in version 7.2 or earlier.  You
>  will have to drop and re-create the table:
>
> 1. CREATE TABLE temp1 AS
>     SELECT * FROM bad_table;
> 2. DROP TABLE bad_table;
> 3. CREATE TABLE bad_table;
> 4. INSERT INTO bad_table SELECT * FROM temp1;
> 5. DROP TABLE temp1
>
> -Josh Berkus
>


-----------------------
 JUSTATEST Art Online
  www.justatest.com

> In your sequence below - where does the column (e.g. col 8 of 25) get
>  changed?

Ooops.
Step 0.1:  pg_dump -s -t bad_table > bad_table.sql
Step 0.2:  Edit bad_table.sql to the table definition you want.
Step 0.3: Use the file bad_table.sql as your CREATE TABLE statement in
 step 3.

> Would I list the good column names to insert into bad_table in point
>  4., then insert my new column - or is there a way to "select *
>  except one" (I guess that's pretty lax SQL)?

Hmmm ... I thought that you wanted to change an existing column from
 DATE to VARCHAR.

> > 1. CREATE TABLE temp1 AS
> >  SELECT * FROM bad_table;
> > 2. DROP TABLE bad_table;
> > 3. CREATE TABLE bad_table;
> > 4. INSERT INTO bad_table SELECT * FROM temp1;
> > 5. DROP TABLE temp1

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

On Fri, 22 Feb 2002, Josh Berkus wrote:


How is this done? e.g. I want to change column 8's type.

> Step 0.2:  Edit bad_table.sql to the table definition you want.


> Hmmm ... I thought that you wanted to change an existing column from
>  DATE to VARCHAR.

Yes you're right, I'm just musing on how a change would be done i.e. do I have to practically rebuild the table or can
thechange be done more simply... inexperience showing.. thanks for your patience. 

/j-p.


-----------------------
 JUSTATEST Art Online
  www.justatest.com