Can't change datatype of column in postgres

Question

I'm trying to change the datatype of a column to varchar but get the following error

ERROR:  syntax error at or near "type" at character 40

My code looks as follows

alter table n_logs alter column action type varchar(100);

I'm running PostgreSQL 7.4.13 (Yeah, I know I need to upgrade)

andersonbd1 · Accepted Answer · 2009-08-19 14:52:59Z

8

I don't think you can do that: http://www.postgresql.org/docs/7.4/interactive/ddl-alter.html

You should split it into 3 steps

add new column
copy values from 1st column to 2nd
drop old column

answered Aug 19, 2009 at 14:52

andersonbd1

5,46614 gold badges48 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Luc M Over a year ago

I would add 4. rename new column to old column

itsols Over a year ago

@andersonbd1 Adding a new column is only at the END of a table. Can't we put the column in the original place like with mysql?

andersonbd1 Over a year ago

@itsols - not sure... just curious - why is the order important to you?

itsols Over a year ago

@andersonbd1 When we work in teams AND even as a single programmer, I think having the fields in a particular order contribute towards self-documentation AND helps prevent mistakes/oversights to some degree. For example, if you see a table with 30 fields like ID, Name, Address, ..... and PHONE at the end, this can be misleading. Also someone may take for granted that there's no phone in it. Also we waste time looking through all fields to see if a particular field is in or not. BTW I Found a method to do this. I have another post with an answer. So it IS possible :)

SchmitzIT · Accepted Answer · 2013-02-06 14:28:44Z

3

I know you said you already found a solution, but your command would have worked if you took out the "action" keyword, just like this:

ALTER TABLE table_name_here 
ALTER COLUMN column_name_here type varchar(100);

The SQL above worked for me, thanks.

edited Feb 6, 2013 at 14:28

SchmitzIT

9,73912 gold badges71 silver badges96 bronze badges

answered Feb 6, 2013 at 14:05

econnat

311 bronze badge

Collectives™ on Stack Overflow

Can't change datatype of column in postgres

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related