Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Changing the Data Type of a Column in an External Table

SarahLSarahL Administrator
edited June 2018 in Tips from the Team

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!


dbadmin=> CREATE EXTERNAL TABLE external_data (c1 INT, c2 INT, c3 VARCHAR(100)) AS COPY FROM '/home/dbadmin/external_data.txt';

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

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);
dbadmin=> SELECT * FROM external_data;
 c1 |  c2   |  c3
  1 | DATA1 | LINE1
  2 | DATA2 | LINE2
  3 | DATA3 | LINE3
(3 rows)

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.