does not succed to export data in csv with \copy

[LuisBL]

Playing with Northwind db:

  $ wget -q https://github.com/jpwhite3/northwind-SQLite3/raw/main/dist/northwind.db
  $ ls -lh northwind.db
  -rw-rw-r-- 1 luis luis 588K oct.  15 08:58 northwind.db
  $ 

  $ usql northwind.db
  sq:northwind.db->
  sq:northwind.db-> \dt
          | Categories           | TABLE 
          ... 
          | Customers            | TABLE
          | Employees            | TABLE 
          | Order Details        | TABLE 
          | Orders               | TABLE 
          | Products             | TABLE 
  sq:northwind.db-> 

I got csvq driver:

$ wget -q https://github.com/mithrandie/csvq/releases/download/v1.18.1/csvq-v1.18.1-freebsd-amd64.tar.gz
$ tar xzvf csvq-v1.18.1-linux-amd64.tar.gz
$ mv csvq-v1.18.1-linux-amd64/csvq ~/bin/
$ csvq --version
csvq version 1.18.1
$

I try to create a 3 columns csv file with the content of Customers table:

sq:northwind.db-> \copy sqlite3:northwind.db csvq:/out.csv 'SELECT CompanyName, Region, Country FROM Customers LIMIT 3' 'out(CompanyName, Region, Country)'
error: failed to begin transaction: driver: bad connection
sq:northwind.db->  

Documentation to import (I try to export) https://github.com/xo/usql#importing-data-from-csv doesn't help ;(

[nineinchnick]

Check if csvq:/out.csv is correct. What happens if you connect using this DSN, can you create a table and insert records into it?

[LuisBL]

$ rm -f out.csv; touch out.csv 
$ usql csvq:/out.csv
error: csvq: driver: bad connection
$ usql csvq://out.csv
error: csvq: driver: bad connection
$ usql csvq:out.csv
error: csvq: driver: bad connection
$ 

No way to to connect to this driver,

Go csvq is there:

$ csvq --version
csvq version 1.18.1
$

So no way to execute

-- test
DROP TABLE IF EXISTS out;
CREATE TABLE out(
    CompanyName VARCHAR(255) NOT NULL,
    Region VARCHAR(255),
    Country VARCHAR(255) NOT NULL
);

[nineinchnick]

When I was testing it, I found that you have to open a directory, and it'll have separate tables for every csv file in that directory, so try csvq://. to open the current directory.

[nineinchnick]

Note that this is not an issue with usql, but just how csvq works.

[LuisBL]

As soon as I succeed to have a way to create/wirte from usql a CSV file I will provide a MergeR to improve the usql documentation.

csql is a psql like with an exception for \copy.
\copy is the default way to create CSV files on psql, in psql with no need of csvq, it is possible to write csv files with full controle on csv-separator:

-- save my_table to output.csv with psql
COPY my_table TO '/path/to/output.csv' WITH CSV DELIMITER ';';

I'm looking a way to do this with usql.

[LuisBL]

I would have preferred to have a native usql way to do it without need to use csvq Go driver, but if it is the only way to have write CSV with control on the delimiter, below my effort to make it work.

I understand from your note that I have to create a TABLE with the csvq driver.

first I check that I can write on a csv with csvq ==> ok

$ rm out
$ usql csvq://.
Connected with driver csvq (CSVQ v1.18.1)
Type "help" for help.

cs:.=> CREATE TABLE out(aa, bb, cc);
CREATE TABLE
cs:.=> INSERT INTO out (aa, bb, cc) VALUES ('Acme Corp', 'West', 'United States');
INSERT 1
cs:.=> INSERT INTO out (aa, bb, cc) VALUES ('Bcme Corp', 'Rest', 'Znited States');
INSERT 1
cs:.=> \! cat out
aa,bb,cc
Acme Corp,West,United States
Bcme Corp,Rest,Znited States
cs:.=> 

now I check I can read it: ==> ok

$ usql csvq://.
Connected with driver csvq (CSVQ v1.18.1)
Type "help" for help.

cs:.=> select * from out;
    aa     |  bb  |      cc       
-----------+------+---------------
 Acme Corp | West | United States 
 Bcme Corp | Rest | Znited States 
(2 rows)

cs:.=>  

## now I try to make work the usql \copy command to write in a CSV file ==> ko

$ usql northwind.db 
Connected with driver sqlite3 (SQLite3 3.42.0)
Type "help" for help.

sq:northwind.db=> \copy sqlite3:northwind.db csvq:/. 'SELECT CompanyName, Region, Country FROM Customers LIMIT 3' 'out(a, b, c)'
error: failed to exec insert: sql: converting argument $1 type: unsupported type: *sql.NullString
sq:northwind.db=>  

even with type conversion ==> ko

sq:northwind.db=> \copy sqlite3:northwind.db csvq:/. "SELECT COALESCE(CompanyName, '') AS CompanyName, COALESCE(Region, '') AS Region, COALESCE(Country, '') AS Country FROM Customers LIMIT 3" 'out(aa, bb, cc)'
error: failed to exec insert: sql: converting argument $1 type: unsupported type: **interface {}
sq:northwind.db=>  

[kenshaw]

@LuisBL DuckDB has been added to usql, and it also supports CSV. You might want to try it, as it is likely a more robust database implementation than csvq is.

[murfffi]

I think PR #461 fixes this.