Self-hosted database proxy and Oberservable database return differently

Community Help
1

I have a set of queries that I’ve been running happily over a self-hosted database proxy. I decided to try the Observable database connection and am connected up just fine, but I’m seeing very many of my numeric values returned as strings. They return as numbers over the proxy.

Is this a bug? Something “as designed” that I would need to work around?

UPDATE: I’m hitting the same Postgres database with the same query in both cases, using this syntax:

tsdb.sql`SELECT...`
2

Are you using aggregation functions in your select? To my knowledge those have to be cast explicitly, e.g.

select count(*)::int
3

Even in cases like that, the proxy does the right thing but I’m seeing a string over the Observable connection. Example:

(AVG(outlet_pressure) / 100.0)::numeric(9,2) AS "avg_outlet_pressure" results in avg_outlet_pressure: "76.80"

4

But that was the hint I needed… thanks @mootari :slight_smile:

Looks like numeric comes back as string, presumably to preserve the requested formatting. Still weird that it was different per kind of database connection, but oh well… working now!

5

So the solution for the snippet I shared is:

ROUND(AVG(outlet_pressure) / 100.0), 2)::double precision AS "avg_outlet_pressure"

Why the two methods of connecting do different things, I have no idea. :slight_smile:

6

It’s a limitation of JavaScript. Postgres returns INT8 which can represent numbers that exceed the MAX_SAFE_INTEGER range, hence pg (the library that the data connector uses for Postgres) returns strings instead.

1 Like
7

Good to know… I use pg, too… I hadn’t run into this yet. Thanks!

8

If you want to explore this further, you can inspect the returned schema directly:

(await (await DatabaseClient("db")).sql`select count(1)`).schema[0]

Object {
  name: "count"
  type: "string"
  nullable: true
  databaseType: "bigint"
}
3 Likes
9

Very cool