error csv upload with pgx driver (encode date column)

[artemklevtsov]

Hi,

I faced with problem when tried to upload CSV using pgx driver. postgres driver is ok.

To reproduce:

printf "col1,col2\n2020-01-01,0\n" > test.csv

docker run --rm --detach --name pg -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres:alpine

PGX_DSN="pgx://postgres:postgres@localhost:5432/postgres"
PG_DSN="postgres://postgres:postgres@localhost:5432/postgres"

usql --command "CREATE TABLE test(col1 date, col2 int)" "${PGX_DSN}"

usql --set="dsn=${PGX_DSN}" --command "\copy csvq://. :dsn 'SELECT * FROM test' 'test'"
usql --set="dsn=${PG_DSN}" --command "\copy csvq://. :dsn 'SELECT * FROM test' 'test'"

docker stop pg

Results:

usql --set="dsn=${PGX_DSN}" --command "\copy csvq://. :dsn 'SELECT * FROM test' 'test'"
# error: ERROR: COPY from stdin failed: unable to encode (*interface {})(0xc001b14a00) into binary format for date (OID 1082): cannot find encode plan (SQLSTATE 57014)
usql --set="dsn=${PG_DSN}" --command "\copy csvq://. :dsn 'SELECT * FROM test' 'test'"
# COPY 1

[nineinchnick]

Can you try casting the column to a specific data type in the SELECT statement? The copy command doesn't try to convert data types on purpose, to keep the implementation simple. This is called out here: https://github.com/xo/usql#datatype-compatibilty-and-casting

[artemklevtsov]

Seems I can't cast columns with the CSV source.

usql --set="dsn=${PGX_DSN}" --command "\copy csvq://. :dsn 'SELECT col1::text, col2 FROM test' 'test'"
# error: [L:1 C:8] constant COL1::TEXT is not defined

[nineinchnick]

How about a regular SELECT CAST(col1 AS date)? If you're inserting it into a date column, you can't use text.

[nineinchnick]

Note, you have to use the csvq driver's syntax, not PostgreSQL's.

[nineinchnick]

If a driver can't return data in a format another driver would understand, that's a limitation of the driver, not usql.

[artemklevtsov]

I checked the csvq ref doc:

usql --set="dsn=${PGX_DSN}" --command "\copy csvq://. :dsn 'SELECT STRING(col1) AS col1, col2 FROM test' 'test'"
# error: ERROR: COPY from stdin failed: unable to encode (*interface {})(0xc001611d90) into binary format for date (OID 1082): cannot find encode plan (SQLSTATE 57014)

Without success.

[nineinchnick]

You should cast it to a date, not string/text/varchar.

[artemklevtsov]

I understand, but csvq don't support date type. Cast to DATETIME also don't work. Can you show a correct query based on my example?

[nineinchnick]

So you'd have to copy the data into another table and do the conversion in PostgreSQL as a next step. But I'd recommend using PostgreSQL native tools for reading the CSV.

[artemklevtsov]

I just wanted to report a problem when using the pgx driver when postgres is ok.

[nineinchnick]

Thanks, but we're not planning on adding type conversion logic in \copy.

[kenshaw]

@artemklevtsov Please understand there's minimal things that we can do for this. usql currently has 44 different SQL drivers that it supports, and there are bound to be difficulties with this.

As such, there are other drivers that support CSV -- specifically DuckDB and the ADODB drivers (Windows). Additionally, I believe there are also ways to import CSV into SQLite3 databases, so that might be a viable solution as well.

[kenshaw]

Also -- PostgreSQL natively supports importing CSVs using their COPY instruction. If you expose the file to a place where the server can see it, you should be able to use that command cleanly with usql.

[artemklevtsov]

Thanks for the replies. I tried to switch from the psql. I want to use one cli tool for all operations for the most dbs. Upload directly on server is not possible in my cases. Now I can use usql witn postgres driver anyway.