Postgresql – How to prevent PostgreSQL from automatically rounding numeric types

decimalpostgresql

I have a simple schema and query for a MWE.

Schema:

CREATE TABLE ttable (
  tcol Numeric(19,4)
)

Query:

INSERT INTO ttable (tcol) VALUES ('123.45678');
SELECT * FROM ttable;

Fiddle

Result:

123.4568

In this case, I have lost precision. I entered five decimal places, but Postgres automatically rounded down to four. I want this to be an error, or at least a warning of some kind that I can detect, so that I can tell the user about the loss of precision.

How can I make this happen?

Edit:

This question is obviously not a duplicate of this question. In that question, the user is using a client application which is rounding value below the stored precision. My question is about Postgres itself rounding the data to fit, not the client displaying. This would be obvious to anyone who looked further than the title of both questions.

Best Answer

I want this to be an error

You could use a check constraint that validates the scale of the value, rather than the definition of the data type:

CREATE TABLE ttable 
(
  tcol numeric,
  constraint check_tcol_scale check (scale(tcol) <= 4)
);

Note also that the column itself needs to be redefined as just numeric, rather than numeric(19,4).

Then the following:

insert into ttable values (123.45678);

will result in

ERROR: new row for relation "ttable" violates check constraint "check_tcol_scale"
   Detail: Failing row contains (123.45678)

But

insert into ttable values (123.4567);

will succeed.

Check out this fiddle to see this solution in action.

Your third query has issues

SELECT * FROM articles WHERE user_id = $1 ORDER BY published_date DESC LIMIT 1;

ORDER BY published_date DESC, but published_date can be NULL (no NOT NULL constraint). That's a loaded foot-gun if there can be NULL values, unless you prefer NULL values over the latest actual published_date.

Either add a NOT NULL constraint. Always do that for columns that can't be NULL.
Or make that ORDER BY published_date DESCNULLS LAST and adapt the index accordingly.

"articles_user_id_published_date_idx" btree (user_id, published_date DESC NULLS LAST)

Details in this recent, related answer:

Extremely slow query on indexed column in Postgres

Convert `published_date` to an actual `date`

While 'published_date' is always rounded, it's effectively just a date which occupies 4 bytes instead of 8 for the timestamp. You would best move that up in the table definition to come before the two timestamp columns, so you don't lose the 4 bytes to padding:

...
body           | text
published_date | date   --     <---- here
created_at     | timestamp without time zone
updated_at     | timestamp without time zone

Smaller on-disk storage does make a difference for performance.

Configuring PostgreSQL for read performance

More importantly, your index on (user_id, published_date) would now just occupy 32 bytes per index entry instead of 40, because 2x4 bytes do not incur extra padding. And that would make a noticeable difference for performance.

Aside: this index is not relevant to the demonstrated queries. Delete unless indexes unless used elsewhere:

~~"index_articles_on_published_date" btree (published_date)~~

Best Answer

Related Solutions

Postgresql – changing float->numeric casts from assignment to implicit, dangerous

Postgresql – Do fixed-width rows improve PostgreSQL read performance

Your third query has issues

Convert published_date to an actual date

Related Question

Convert `published_date` to an actual `date`