Change table column's data type

[Julie]

Hello,

How do we change the data type of a table column? For instance, I would like to increase the size of several columns from VARCHAR(25) to VARCHAR(100).

[id10t]

Hi, Julie.

Scenario is next:
1. create new_table
2. copy to it a data
3. rename src_table for drop
4. rename new_table to name of src_table
5. drop renamed src_table
====

Data for example [ LENGTH(md5 hash string) = 32 ]:

Code: Select all

$ for i in {a..f}; do echo $i | md5sum | cut -d" " -f1; done
60b725f10c9c85c70d97880dfe8191b3
3b5d5c3712955042212316173ccf37be
2cd6ee2c70b0bde53fbe6cac3c8b8bb1
e29311f6f1bf1af907f9ef9f44b8328b
9ffbf43126e33be52cd2bf7e01d627f9
9a8ad92c50cae39aa2c5604fd0ab6d8c

Our src table definition:

Code: Select all

CREATE TABLE foo (x VARCHAR(25));

Code: Select all

test_db=> \d foo
                                  List of Fields by Tables
 Schema | Table | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key 
--------+-------+--------+-------------+------+---------+----------+-------------+-------------
 public | foo   | x      | varchar(25) |   32 |         | f        | f           | 
(1 row)

Insert data:

Code: Select all

COPY foo FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 60b725f10c9c85c70d97880dfe8191b3
3b5d5c3712955042212316173ccf37be
2cd6ee2c70b0bde53fbe6cac3c8b8bb1
e29311f6f1bf1af907f9ef9f44b8328b
9ffbf43126e33be52cd2bf7e01d627f9
9a8ad92c50cae39aa2c5604fd0ab6d8c
>> >> >> >> >> >> \.

Lets' check:

Code: Select all

SELECT DISTINCT(LENGTH(x)) FROM foo;
 LENGTH 
--------
     25
(1 row)

Now our challenge:

Code: Select all

CREATE TABLE foo_tmp (x CHAR(32));

Let's insert old data (old data truncated to 25, there no way to know what is original data):

Code: Select all

INSERT INTO foo_tmp
SELECT *
FROM  foo;

Let's RENAME tables and DROP unused:

Code: Select all

ALTER TABLE foo     RENAME TO foo2;
ALTER TABLE foo_tmp RENAME TO foo;
DROP TABLE foo2;

DONE! Test?

Code: Select all

COPY foo FROM STDIN;

60b725f10c9c85c70d97880dfe8191b3
3b5d5c3712955042212316173ccf37be
2cd6ee2c70b0bde53fbe6cac3c8b8bb1
e29311f6f1bf1af907f9ef9f44b8328b
9ffbf43126e33be52cd2bf7e01d627f9
9a8ad92c50cae39aa2c5604fd0ab6d8c
\.

SELECT DISTINCT(LENGTH(x)) from foo;
 LENGTH 
--------
     25
     32
(2 rows)

Code: Select all

test_db=> \d foo
                                  List of Fields by Tables
 Schema | Table | Column |   Type   | Size | Default | Not Null | Primary Key | Foreign Key 
--------+-------+--------+----------+------+---------+----------+-------------+-------------
 public | foo   | x      | char(32) |   32 |         | f        | f           | 
(1 row)

[Julie]

Thank you very much for your help!

[becky]

This is retarded. How can a business class database not allow easy manipulation of a base table?

[id10t]

@becky
May be... but, for 11+ ears old - Vertica has no bad achievements
Due Vertica design (data replication,compression,epochs) and Julie's case it oblige to do explicit data type coercion.

PS
Mind if I ask you - why "business class" database must provide such functionality (modify column data type in "easy" way)? Just because it is "business"? As database migration ability? There are special ETL tools for it. As columnar db... ok it has... some legitimation as "wish". I don't think that any database provides "easy way" when there are data in it, but if it does - it still must deal with existing data (in a lower level, implicit), i.e. somehow to update existing data [IMHO].

[becky]

sKwa, We'll I guess I'm just used to being able to do things like dropping and adding columns when necessary. I suppose it's going to take me a while to get used to the new physicality of the columnar databases. But I don't think that a data model will ever be completely stagnate. We're going to need to change things. Heck, every time a new manager comes on board I need to add columns, take away columns, add tables drop tables, etc. It drive me nuts. But thanks for your help

[id10t]

Hi, Beck.

If each Monday you need to change data type for some column so try to define data type "wide" as much it possible, i.e. instead INTEGER or FLOAT define NUMERIC type or VARCHAR, while in queries do casing. IMHO its a better solution than modifying column data type.