myVertica  

Vertica Blog

Changing the Data Type of a Column in an External Table: Quick Tip

Jim Knicely authored this tip. External tables let you query data stored in files that are accessible to the Vertica database, but not managed by it. When you create the external table, you have to provide column names along with their data types.

What happens if you get a data type incorrect? Luckily, you can change it!

Example: dbadmin=> CREATE EXTERNAL TABLE external_data (c1 INT, c2 INT, c3 VARCHAR(100)) AS COPY FROM '/home/dbadmin/external_data.txt'; CREATE TABLE dbadmin=> \d external_data; List of Fields by Tables Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key --------+---------------+--------+--------------+------+---------+----------+-------------+------------- public | external_data | c1 | int | 8 | | f | f | public | external_data | c2 | int | 8 | | f | f | public | external_data | c3 | varchar(100) | 100 | | f | f | (3 rows) dbadmin=> SELECT * FROM external_data; c1 | c2 | c3 ----+----+---- (0 rows) Where is my data? dbadmin=> \! cat /home/dbadmin/external_data.txt 1|DATA1|LINE1 2|DATA2|LINE2 3|DATA3|LINE3 Oh. The second column is a VARCHAR, not an INT. Let’s fix that and reload: dbadmin=> ALTER TABLE external_data ALTER COLUMN c2 SET DATA TYPE VARCHAR(100); ALTER TABLE dbadmin=> SELECT * FROM external_data; c1 | c2 | c3 ----+-------+------- 1 | DATA1 | LINE1 2 | DATA2 | LINE2 3 | DATA3 | LINE3 (3 rows) Have fun!