usql
usql copied to clipboard
error: failed to final exec copy: pq: invalid input syntax for type numeric
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.
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.
I just verified this occurs with the DECIMAL data type as well. I'll do some more experiments with it.
Can you try casting it to another type, like a varchar, in the SELECT?
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
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)
LOL, I just tried cast(price as text)
and that worked as well.
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.
@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.
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.
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
.
@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?