Changing the Data Type of a Column in an External Table

[Deleted User] · June 2018

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!