Forum OpenACS Q&A: alter column type problem.

Posted by David Kuczek on 06/20/01 04:29 PM

I tried to alter the type of a table following the instructions on

http://odbc.postgresql.org/docs/index.php?sql-altertable.html

which tell you to do this:

CREATE TABLE temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors (
did DECIMAL(3) DEFAULT 1,
name VARCHAR(40) NOT NULL,
city VARCHAR(30)
);
INSERT INTO distributors SELECT * FROM temp;
DROP TABLE temp;

It didn't quite work. Postgres told me something like :

"Attribute 'note' is of type 'varchar' but expression is of type
'numeric'

You will need to rewrite or cast the expression

AND

Attribute 'date' is of type 'timestamp' but expression is of type
'varchar'

You will need to rewrite or cast the expression

So I set all date and note = null. But he still delivered my the same
ERROR.

What could I do differently?

Thanks

2: Response to alter column type problem. (response to 1)

Posted by Janine Ohmer on 06/20/01 04:45 PM

What could I do differently?

Wrap your code in pre tags? :)

Seriously, I've had similar problems with Oracle so I think I know what the problem is. This technique does the insert by position rather than by column name. So if you create the new table with columns in different order than the source table, you'll get mismatches like this.

It's hard to tell exactly what went wrong here since the columns it's complaining about don't appear in your new table. But, I'm guessing that you're trying to drop columns (the last example on the page you mentioned). In that case your first select should only include the columns you want to keep. You're doing a "select *" and then (I think) trying to stuff the result into a table that doesn't contain all the columns you selected. But it doesn't know that, since it doesn't look at the column names at all, and just ends up trying to stuff data into columns that are not the right type.

3: Response to alter column type problem. (response to 1)

Posted by David Kuczek on 06/20/01 07:15 PM

Hello Janine,

what I do is:

1.) I create a new table called students_new with mark_1 mark_2 mark_3 being changed from numeric to varchar(20).

2.) CREATE TABLE temp AS SELECT * FROM students.

3.) INSERT INTO students_new SELECT * FROM temp;

This is where the error appears:

ERROR: Attribute 'mark_1' is of type 'varchar' but expression is of type 'numeric'

You will need to rewrite or cast the expression

I don't drop columns, but just alter their data types...

4: Response to alter column type problem. (response to 1)

Posted by Janine Ohmer on 06/20/01 07:39 PM

Ah, well you can't do what you're trying to do this way. Postgres
doesn't know you want to do a conversion, it thinks you're making
a mistake.

AFAIK you'll have to write a script which loops through the
source table and inserts into the destination table. Maybe the
Postgres experts here know of a shortcut, but that's the only way I
know to do it.

5: Response to alter column type problem. (response to 1)

Posted by Gaizka Villate on 06/20/01 07:43 PM

Shouldn't this be enough to solve the problem?

 
$ create table temp as select * from students;
$ insert into students_new (col1, col2, ..., mark_1, mark_2, mark_3)
    select (col1, col2, ..., cast(mark_1 as varchar), cast(mark_2 as varchar), cast(mark_3 as varchar)) from temp;

I think that's what David wants to do.

6: Response to alter column type problem. (response to 1)

Posted by Janine Ohmer on 06/20/01 08:02 PM

Ok, there you have it - a Postgres expert *does* know a better
way. :)

7: Response to alter column type problem. (response to 1)

Posted by David Kuczek on 06/20/01 08:06 PM

Hello Gaizka,

your proposal doesn't seem to work on my database (PG 7.1.2).

For one it doesn't accept the brakets:

ERROR: parser: parse error at or near "FROM"

When I remove the four brakets, it is telling me:

ERROR: parser: parse error at or near "col1"

When I remove everything inside the first pair of brakets plus all four brakets, it is telling me:

ERROR: Cannot cast type 'numeric' to 'varchar'

I think that postgres as mature as it is now should really have a feature to change types of columns etc.

8: Response to alter column type problem. (response to 1)

Posted by Janine Ohmer on 06/20/01 08:15 PM

I think that postgres as mature as it is now should really have a feature to change types of columns etc.

FWIW, I'm pretty sure Oracle won't let you do this either unless the column is empty. It seems to be a problem they've all chosen not to address.

I'll let the reader decide whether they consider Oracle to be mature or not. :)

9: Response to alter column type problem. (response to 1)

Posted by Dan Wickstrom on 06/20/01 08:34 PM

postgresql is not smart with regards to casting. You need to provide the full coercion path.

insert ...
select ... mark_1::float8::varchar ...