error: failed to final exec copy: pq: invalid input syntax for type numeric

[burggraf]

I'm trying to copy the contents of a table in one PostgreSQL database to another PostgreSQL database. In the first database, I create the data:

create table t1 (id text, price numeric);
insert into t1 values ('a', 100.01),('b',99.40),('c',5);

In the second database I create the table:

create table t1 (id text, price numeric);

Then I execute the copy command:

./usql -c "\copy pg://postgres:[email protected]:5432/postgres?sslmode=disable pg://postgres@localhost/postgres?sslmode=disable 'select * from t1' 't1'"

The result is:

error: failed to final exec copy: pq: invalid input syntax for type numeric: "\x3130302e3031"

It doesn't matter if I try to copy from the remote to the local pg instance or vice versa, the error is always the same.

[kenshaw]

Thanks for catching this. The \copy command is very new, and in a very experimental state at the moment. There's a lot of logic involved with converting the underlying Go drivers' values. We'll try to fix this, but I would suggest in the interim trying to copy to a separate database or file first, or to avoid using the numeric type for now, until we fix the value conversion.

[burggraf]

I just verified this occurs with the DECIMAL data type as well. I'll do some more experiments with it.

[nineinchnick]

Can you try casting it to another type, like a varchar, in the SELECT?

[burggraf]

As an alternative, is there a way to open connections to 2 databases at once and query between them, such as: \c db1 \c db2 select * from db1.table into db2.table

[nineinchnick]

pg:[email protected]:49153/postgres=> create table t1 (id text, price numeric);
CREATE TABLE
pg:[email protected]:49153/postgres=> create table t2 (id text, price numeric);
CREATE TABLE
pg:[email protected]:49153/postgres=> insert into t1 values ('a', 100.01),('b',99.40),('c',5);
INSERT 3
pg:[email protected]:49153/postgres=> \copy 'pg://postgres:[email protected]:49153/postgres?sslmode=disable' 'pg://postgres:[email protected]:49153/postgres?sslmode=disable' 'select * from t1' t2
error: failed to final exec copy: pq: invalid input syntax for type numeric: "\x3130302e3031"
pg:[email protected]:49153/postgres=> \copy 'pg://postgres:[email protected]:49153/postgres?sslmode=disable' 'pg://postgres:[email protected]:49153/postgres?sslmode=disable' 'select id,price::varchar from t1' t2
COPY 3
pg:[email protected]:49153/postgres=> select * from t2;
┌────┬────────┐
│ id │ price  │
├────┼────────┤
│ a  │ 100.01 │
│ b  │ 99.40  │
│ c  │ 5      │
└────┴────────┘
(3 rows)

[burggraf]

LOL, I just tried cast(price as text) and that worked as well.

[burggraf]

And even though I'm casting them as TEXT they still go into the database as numeric. That works, it's just not what I'd expect.

[kenshaw]

@burggraf the reason here is that due to the way the Go SQL drivers work, we send a *interface{} to the SQL driver, and have the SQL driver return the type. By forcing it into a text or varchar type, the postgres driver will return the stored value as a string (as a base 10 decimal format). The type that gets sent back for numeric/decimal isn't well supported by the \copy command yet. In turn, the receiving database (not the driver!) will likely know how to interpret converting a string (passed by the driver) into the destination type. If a problem like this occurs with \copy, then one will need to CAST fields in the SELECT statement to an appropriate type -- whenever this is necessary, in almost all likelihood, this will almost always be the origin database's TEXT equivalent.

[nineinchnick]

I tried noting that in the README. If it's not clear enough and you'd have suggestions on how to improve that section please open up a PR.

[nineinchnick]

As an alternative, is there a way to open connections to 2 databases at once and query between them, such as: \c db1 \c db2 select * from db1.table into db2.table

This would require parsing the query, which I don't think we want to do. But we were discussing using variables for connection strings, so the copy command would be a bit shorter:

\set conn_src pg://postgres:[email protected]:49153/postgres?sslmode=disable
\set conn_dst pg://postgres:[email protected]:49153/postgres?sslmode=disable
\copy conn_src conn_dst table_src table_dst

They you could keep the \set commands for commonly used connetions in your ~/.usqlrc.

[nineinchnick]

@burggraf could you also try the pgx driver? It might handle types differently.

Is there something we can do here to improve this with the pg (libpq) driver? Maybe update the description in the README to stand out better? If not, can we close this issue?