unable to read composite types from CSV files

[Ian Bell]

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)"

[David G. Johnston]

Post by Ian Bell
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?
*Test CSV file contents*
(1,2,3)
(4,5,6)
(7,8,9)

Your csv data contains commas. In order to treat them as data instead of
structure you have to double-quote the field/value containing the affected
data.

David J.

[Ian Bell]

Hello David,



Bingo! I changed the CSV file to the following and it works!



"(1, 2, 3)"

"(4, 5, 6)"

"(7, 8, 9)"



If you don’t mind, could you point out the section in the PostgreSql manual/documentation where this is discussed/explained. I have repeatedly read section 8.16.6 (i.e. Composite Type Input and Output Syntax) but it does not appear to include this detail.



I spent the whole day struggling with this problem and I am very much relieved that you were able to offer a solution so quickly. A big thanks!



Ian





From: David G. Johnston [mailto:***@gmail.com]
Sent: 19 April, 2018 20:34
To: ***@ianbellsoftware.com
Cc: pgsql-novice <pgsql-***@postgresql.org>
Subject: Re: unable to read composite types from CSV files



On Thursday, April 19, 2018, Ian Bell <***@ianbellsoftware.com <mailto:***@ianbellsoftware.com> > wrote:

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?



Test CSV file contents

The contents of the CSV file (i.e. ‘testComposite.csv’) is:



(1,2,3)

(4,5,6)

(7,8,9)





Your csv data contains commas. In order to treat them as data instead of structure you have to double-quote the field/value containing the affected data.



David J.

[David G. Johnston]

Post by Ian Bell
If you don’t mind, could you point out the section in the PostgreSql
manual/documentation where this is discussed/explained. I have repeatedly
read section 8.16.6 (i.e. Composite Type Input and Output Syntax) but it
does not appear to include this detail.
Your problem is lower-level than the composite type, it's not

understanding csv. Read the copy docs, csv format section to learn more.

https://www.postgresql.org/docs/9.6/static/sql-copy.html

The syntax for the composite is embedded in a csv file so first you have to
parse the csv then you pass the result to the composite input constructor.
If you enter it literally in a script file you can ignore the csv format
stuff and go immediately to the syntax of the Typed data you wish to create.

David J.