Comments (14)
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
from usql.
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
from usql.
How about a regular SELECT CAST(col1 AS date)
? If you're inserting it into a date column, you can't use text.
from usql.
Note, you have to use the csvq
driver's syntax, not PostgreSQL's.
from usql.
If a driver can't return data in a format another driver would understand, that's a limitation of the driver, not usql
.
from usql.
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.
from usql.
You should cast it to a date, not string/text/varchar.
from usql.
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?
from usql.
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.
from usql.
I just wanted to report a problem when using the pgx
driver when postgres
is ok.
from usql.
Thanks, but we're not planning on adding type conversion logic in \copy
.
from usql.
@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.
from usql.
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
.
from usql.
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.
from usql.
Related Issues (20)
- Support log message for NOTIFY/LISTEN HOT 4
- uniqueidentifier formatting on sql server HOT 16
- No foreign keys displayed on sql server HOT 1
- Is terminal graphics in WezTerm under Windows supposed to work? HOT 1
- feature: connection aliases HOT 4
- '\timing on' not work in greenplum 6 HOT 5
- paste multi-line sql is a bit slow? HOT 2
- feature: add duckdb driver to base drivers HOT 1
- version 0.17.5 quit sliently on windows 7 HOT 2
- numbers displayed as 1.450817032e+ HOT 5
- I hope to add a feature similar to the grep filtering in Linux. HOT 3
- couchbase error: n1ql: Connection failed Post
- Feature request: implement the \bind meta-command HOT 6
- Using usql as a library (or subprocess) HOT 5
- Batch insert in \copy to DB destination HOT 1
- How to .usqlpass HOT 6
- Parentheses issue when \copying to postgres from sqlite HOT 2
- connect to clickhouse over http from cmdline HOT 4
- Crashed after executing the command HOT 10
- error: sqlserver: TLS Handshake failed: tls: server selected unsupported protocol version 301 HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from usql.