2

I'm working to create a new table from an external file in Netezza, but am getting the following error:

Unsupported external table reference, unable to derive shape

I get the same error whether trying to create a new table or insert into an existing table. Here is the sql I'm using:

            select * from external 'FILEPATH.txt' using (delim '|');
Eric
  • 23
  • 3

1 Answers1

2

you need to define the column format in your query. then the query will fire

SYSTEM.ADMIN(ADMIN)=> select * from external '/tmp/testfile.txt' (v1 int, v2 int) using (delim '|');
 V1 | V2 
----+----
  3 |  4
  3 |  6
(2 rows)

note that when inserting into an existing table you don't need to specify the types

SYSTEM.ADMIN(ADMIN)=> create table test (v1 int, v2 int);
CREATE TABLE
SYSTEM.ADMIN(ADMIN)=> insert into test select * from external '/tmp/testfile.txt' using (delim '|');
INSERT 0 2
Mike DeRoy
  • 133
  • 7
  • Ah, thank you! I was hoping it would just pull in generic data types on its own like it would if you were making a copy of an internal table. – Eric Apr 08 '21 at 15:07