How to cp a source PG table with a numeric column

[emmanuel]

Greetings, and thanks for building this marvelous tool!

I'm trying to use dbcrossbar in a Redshift -> Postgres workflow. I was able to transfer one of my test tables without issue (and quite quickly!). Unfortunately, another of my test source tables has a couple of numeric fields in it, and it didn't cp successfully. I got an error like:

cannot use numeric columns with PostgreSQL yet

Which originates (is raised) in the match clause here

dbcrossbar/dbcrossbarlib/src/drivers/postgres/csv_to_binary/mod.rs

Lines 273 to 281 in 03e404c

	PgScalarDataType::Numeric => {
	// The only sensible way to make this work is to port PostgresSQL's
	// own `decimal` parser from C, because it's an unusual internal
	// format built using very complicated parsing rules (and `numeric`
	// needs to be a perfectly-accurate type).
	Err(format_err!(
	"cannot use numeric columns with PostgreSQL yet",
	))
	}

, which includes a helpful comment clarifying that this intentionally omitting conversion of numeric is due to the potential for precision loss in conversions owing to the complexity of Postgres's numeric format.

I think I understand the issues here. Namely, I believe the issue is that dbcrossbar's intermediate CSV representation implies converting all data into a text representation, and the text representation of PG's numeric type is complex, with significant complexity and potential for parsing issues. Dealing with those correctly is best accomplished by importing PG's numeric parser and using it directly. Is that about right?

Do you have any advice about alternatives or workarounds? Redefining the schema of my source tables is not an option for me, and many of the source tables I deal with include numeric columns. Is there a way to specify the target schema to avoid this conversion (I don't think so)?

There's so much about dbcrossbar that is very compelling for my needs; I'm hoping that this issue doesn't prove to be a blocker for my use!

[emmanuel]

I've been able to work around this in a fashion. My approach is to export the source schema to a .sql file on disk, change the numeric column definitions to text, refer to that .sql as the target schema when loading with dbcrossbar, and then run an alter column ... set type ... afterward. This is rather clunky, and not ideal, but it works for now.

[seamusabshere]

@emmanuel

that's basically what we do internally now, except we convert numeric to float (we don't need the precision) instead of text

note it's easier to do dbcrossbar schema conv [...] dbcrossbar-schema:foo.json than postgres-sql because you can deserialize, edit, and reserialize easily (as opposed to parsing sql)

sorry for the inconvenience!

[emk]

Yeah, my apologies on this one. numeric is not a type where I'd want to risk losing any precision at all, because people use numeric when they want to pay for flawless precision. And we can't use PostgreSQL's own parser when loading using BINARY format.

The only fix here is to dig out the native parser and port it to Rust, unfortunately. I'd be willing to mentor someone and review a PR.

But dumping dbcrossbar schema conv [...] dbcrossbar-schema:foo.json to disk and editing them (possibly using a script) is a legitimate workaround. My apologies for the inconvenience.