Invalid field types?

[silvioprog]

Hi.

Consider the following table definition and data:

CREATE TABLE product (
    id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
    value NUMERIC NOT NULL,
    createdAt DATE NOT NULL
);

INSERT INTO product (value, createdAt) VALUES (1.23, CURRENT_DATE);
INSERT INTO product (value, createdAt) VALUES (1234.56, CURRENT_DATE);

now, consider the following code:

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

interface Product {
  value: number;
  createdAt: Date;
}

const client = new Client(
  "postgresql://postgres:postgres@127.0.0.1:5432/postgres"
);

await client.connect();

const result =
  await client.queryObject<Product>`SELECT value, createdAt FROM product`;
const [product1, product2] = result.rows;
console.log(product1, product2);
console.log(typeof product1.createdAt, typeof product2.value);

await client.end();

and its result:

% deno run -A main.ts 
{ value: "1.23", createdat: 2023-02-27T03:00:00.000Z } { value: "1234.56", createdat: 2023-02-27T03:00:00.000Z }
undefined string

however, I guess it should be:

% deno run -A main.ts 
{ value: 1.23, createdat: 2023-02-27T03:00:00.000Z } { value: 1234.56, createdat: 2023-02-27T03:00:00.000Z }
object number

is this a bug, or something I'm doing wrong? 🙁

I really would appreciate to return the same types from the table and object definition, since I'm going to make expressions using those types.

TIA for any help!

[ChristianSiegert]

@silvioprog You define value as numeric. Since numeric can have a high precision (“up to 131072 digits before the decimal point; up to 16383 digits after the decimal point”), I guess it can only be represented as string in JavaScript. Try defining it as integer, bigint, real or double precision.

If you are working with currency, use integer (product price in cents), and format it in the UI as decimal (price divided by 100).

Postgres numeric types: https://www.postgresql.org/docs/15/datatype-numeric.html

[silvioprog]

Hey @ChristianSiegert, thanks a lot for the feedback!

The first part was fixed by:

-     value NUMERIC NOT NULL,
+     value DOUBLE PRECISION NOT NULL,

and the result is:

undefined number

Now, my question is: why typeof product1.createdAt returns undefined? 🤔

Thanks once again for the information!

[ChristianSiegert]

@silvioprog If you look closely at your output, you will see that the returned object properties are lowercase (createdat). Accessing createdAt means accessing an undefined property.

Postgres automatically lowercases column names if they are not quoted (StackOverflow). You have multiple choices to solve this problem:

1. Put double quotes around your column names to prevent Postgres from lowercasing them.
2. Use all lowercase with underscores in your column names and use the camelcase: true option in the deno-postgres methods so column created_at is transformed to property name createdAt in the result. Docs
3. Use all-lowercase property names to match the lowercase column names. (Ugly)

[silvioprog]

It solved the problem like a charm. Thank you very much, @ChristianSiegert! 🚀

[zingmane]

@ChristianSiegert, @silvioprog
May I ask a follow-up question?

I also have problems to query "non integer numbers".

Here is my db and data:

CREATE TABLE some_table (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  my_numeric1 double precision,
  my_numeric2 real,
  my_numeric3 numeric(5, 2)
);

INSERT INTO some_table (my_numeric1, my_numeric2, my_numeric3)
  VALUES (1.23, 1.23, 1.23);

My code is:

type SomeTable = {
  id: number;
  myNumeric1: number;
  myNumeric2: number;
  myNumeric3: number;
};

const foo = await queryObject<SomeTable>("SELECT * FROM some_table WHERE id = $1", [1]);
console.log(`###LOG###: ${JSON.stringify(foo, null, 2)}`);

// log is:
// ###LOG###: {
//   "command": "SELECT",
//   "rowCount": 1,
//   "warnings": [],
//   "query": {
//     "args": [
//       "1"
//     ],
//     "result_type": 1,
//     "text": "SELECT * FROM some_table WHERE id = $1"
//   },
//   "columns": [
//     "id",
//     "my_numeric1",
//     "my_numeric2",
//     "my_numeric3"
//   ],
//   "rows": [
//     {
//       "id": 1,
//       "my_numeric1": "1.23",
//       "my_numeric2": "1.23",
//       "my_numeric3": "1.23"
//     }
//   ]
// }

The result is always casted to string, except the db type is integer. I'm using the newest version
The result is always casted to a string unless the db type is an integer, although you say it should work with real or double precision as well.
I use the latest version 0.17.0 together with postgres 14.2, maybe that is the problem?

If all does not help, I can also transform the value back with parseFloat. But it would be nicer to have this automatically.
Is there another trick?

[ChristianSiegert]

@zingmane I can confirm real and double precision are returned as string (v0.17.0 with Postgres 14.2). I assumed both would be returned as number but this is not the case. I checked the tests and returning strings seems intentional behavior: float4 test, float8 test (float4 and float8 are postgres aliases for real and double precision).

[zingmane]

Thanks @ChristianSiegert for fast reply.

I did some searching around and saw that one can override the parsers in the sister project node-postgres. (see: brianc/node-postgres#811 (comment))
Maybe that would be a way to provide the functionality?

Apart from that, if the type is narrow enough, you could easily return the value as number (e.g. for NUMERIC(10,2)).
For now, I'll parse the value manually.