I have a Table in my Database that contains values that are of type float8. I want to change these values to only contain decimal places configured in another table without changing the type.

CREATE TABLE config (
    id uuid NOT NULL,
    decimals int4 NOT NULL DEFAULT 2,
    CONSTRAINT config PRIMARY KEY (id)
);

CREATE TABLE event (
    value float8 NOT NULL,
    config_id uuid NOT NULL,
    id uuid NOT NULL,
    CONSTRAINT event_pkey PRIMARY KEY (id),
    CONSTRAINT config_id FOREIGN KEY (config_id) REFERENCES config(id)
);

My Tables would look like this:

I've tried doing it this way

ALTER TABLE event 
ALTER COLUMN value TYPE decimal(16, (SELECT config.decimals 
 FROM config, event 
 WHERE config.id = event.config_id));

that returns

ERROR: type modifiers must be simple constants or identifiers

After the Alter Table they should look like this:

CodePudding user response：

You can't change the data type per row. Instead, format the numbers in the query. Use to_char to format the value and repeat to build the format

select
  to_char(event.value, '999999999D' || repeat('9', config.decimals)
from event
left join config on config.id = event.config_id

Alternatively, leave this formatting up to the application layer.