Hello,
I am initializing tables with data imported/read from CSV files. This is working well except for those tables that contain composite types.
When attempting to import/read CSV files containing composite data then the error message I get is: ERROR: extra data after last expected column. My questions are a follow:
1) Can composite types be imported from CSV files? Or can the CSV file only contain base types (e.g. text, integer, real)? If composite types CANNNOT be imported from CSV files then please let me know – in this case you can ignore the remainder of this post.
2) If composite types can indeed be read/imported from CSV files then would you please take a look at the sample code/data provided below and tell me why it generates the error message ‘extra data after last expected column’. In other words, what am I doing wrong?
BTW, if I remove the parenthesis in the CSV file, read each field separately into a temporary table then I can successfully copy the contents of the temporary table into the ‘permanent’ table. However, it would be much more convenient to read composite types directly from the CSV file and avoid working with temporary tables.
I am working with PostgreSQL 10.3 (compiled by Visual C++ build 1800, 64-bit) running under Windows 10 64 bit. I am using NotePad++ as an editor. The sample code is run in PSQL.
SQL Test code
The test code I am running in PSQL is as follows:
\! cls
\c postgres;
drop database if exists TestDB;
create database TestDB
with
owner = postgres
encoding = 'UTF8'
LC_COLLATE = 'English_United States.1252'
LC_CTYPE = 'English_United States.1252'
tablespace = pg_default
connection limit = -1;
\connect testdb;
create schema Test;
create type Test.MyType as(
X int,
Y int,
Z int
);
create table Test.A(
id serial not null primary key,
MT1 Test.MyType
);
\copy Test.A( MT1 ) from './testComposite.csv' with CSV DELIMITER ',';
Test CSV file contents
The contents of the CSV file (i.e. ‘testComposite.csv’) is:
(1,2,3)
(4,5,6)
(7,8,9)
Error Message
The error message I get is as follows:
psql:testComposite.sql:42: ERROR: extra data after last expected column
CONTEXT: COPY a, line 1: "(1, 2, 3)"
