Can't add new column of type int using default of varchar

Moderator: NorbertKrupa

Post Reply
beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

Can't add new column of type int using default of varchar

Post by beth » Thu Apr 21, 2016 3:01 pm

Howdy,

I am trying to add a new column to a table that has a data type of int using another column in the table as the default value. That column in a varchar. Vertica won;t let me do this :evil: Is this a bug?

Code: Select all

dbadmin=> create table size (pk varchar(10));
CREATE TABLE
dbadmin=> insert into size values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> alter table size add column pk2 int default pk;
ROLLBACK 2632:  Column "pk2" is of type int but the default expression is of type varchar
HINT:  You will need to rewrite or cast the expression
dbadmin=> alter table size add column pk2 int default pk::varchar;
ROLLBACK 2632:  Column "pk2" is of type int but the default expression is of type varchar
HINT:  You will need to rewrite or cast the expression
dbadmin=> alter table size add column pk2 int default to_char(pk);
ROLLBACK 2632:  Column "pk2" is of type int but the default expression is of type varchar
HINT:  You will need to rewrite or cast the expression
How can I do this?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Can't add new column of type int using default of varchar

Post by JimKnicely » Thu Apr 21, 2016 3:19 pm

Hey Beth,

I think you meant to do this:

Code: Select all

alter table size add column pk2 int default pk::INT;
;)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

Re: Can't add new column of type int using default of varchar

Post by beth » Fri Apr 22, 2016 2:32 am

Oh brother. I looked at that for hours today. I'm an idiot. Thanks, Jim... :oops: :oops: :oops:

Post Reply

Return to “Vertica SQL”