Buggy NULL conversion on /copy from Postgres to sqlite

[ejoubaud]

Hi there,

First thanks for this great tool, it's super helpful to manipulate data from different source DB engines 🙇

That said it looks like the \copy from NULL values doesn't work great, at least from pg to sqlite:

▶ usql sqlite://file.sqlite
Connected with driver sqlite3 (SQLite3 3.39.4)
Type "help" for help.

sq:file.sqlite=> CREATE TABLE input (value INTEGER);
CREATE TABLE
sq:file.sqlite=> \copy pg://user@localhost?sslmode=disable sqlite://file.sqlite 'SELECT NULL AS value' input
error: failed to scan row: sql: Scan error on column index 0, name "value": converting NULL to string is unsupported
sq:file.sqlite=> \copy pg://user@localhost?sslmode=disable sqlite://file.sqlite 'SELECT NULL::timestamp AS value' input
error: failed to scan row: sql: Scan error on column index 0, name "value": unsupported Scan, storing driver.Value type <nil> into type *time.Time
sq:file.sqlite=> \copy pg://user@localhost?sslmode=disable sqlite://file.sqlite 'SELECT NULL::boolean AS value' input
error: failed to scan row: sql: Scan error on column index 0, name "value": sql/driver: couldn't convert <nil> (<nil>) into type bool
sq:file.sqlite=> \copy pg://user@localhost?sslmode=disable sqlite://file.sqlite 'SELECT NULL::integer AS value' input
error: failed to scan row: sql: Scan error on column index 0, name "value": converting NULL to int32 is unsupported

Looks like the trip into Go land doesn't account very well for the possibility of NULL values.

[ejoubaud]

I found a way to make it work, for pg at least:

sq:file.sqlite=> \copy pg://course@localhost?sslmode=disable sqlite://file.sqlite 'SELECT EXTRACT(EPOCH FROM NULL::timestamp) AS value' input
COPY 1
sq:file.sqlite=> \copy pg://course@localhost?sslmode=disable sqlite://file.sqlite 'SELECT NULL::numeric AS value' input
COPY 1
sq:file.sqlite=> select value, value is null from input;
 value | value is null
-------+---------------
       |             1
       |             1
(2 rows)

If anyone has the same issue you can cast your NULL to ::numeric in the source pg query, rather than ::integer or ::bool. That works for a Postgres source, not sure if it's a problem with other source DBMS.

[nineinchnick]

Could you also try with the pgx driver? It might handle untyped nulls differently.