Change table column's data type
Moderator: NorbertKrupa
Change table column's data type
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).
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).
Last edited by Julie on Fri Apr 27, 2012 7:30 pm, edited 1 time in total.
Thanks,
Juliette
Juliette
Re: Change table column's data type
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 ]:
Our src table definition:
Insert data:
Lets' check:
Now our challenge:
Let's insert old data (old data truncated to 25, there no way to know what is original data):
Let's RENAME tables and DROP unused:
DONE! Test?
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
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)
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
>> >> >> >> >> >> \.
Code: Select all
SELECT DISTINCT(LENGTH(x)) FROM foo;
LENGTH
--------
25
(1 row)
Code: Select all
CREATE TABLE foo_tmp (x CHAR(32));
Code: Select all
INSERT INTO foo_tmp
SELECT *
FROM foo;
Code: Select all
ALTER TABLE foo RENAME TO foo2;
ALTER TABLE foo_tmp RENAME TO foo;
DROP TABLE foo2;
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)
Re: Change table column's data type
This is retarded. How can a business class database not allow easy manipulation of a base table?
THANKS - BECKSTER
Re: Change table column's data type
@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].
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].
Re: Change table column's data type
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
THANKS - BECKSTER
Re: Change table column's data type
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.
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.