In my database I have an entry for a NUMERIC(5,1), so when I add entries to it, it will display, for example 1029.6, however I want a dash between then first and second digit (the number should be 1000<=n<6000), so it would actually be 1-029.6 how can I do this?

In my database I have an entry for a NUMERIC(5,1), so when I add entries to it, it will display, for example 1029.6, however I want a dash between then first and second digit (the number should be 1000<=n<6000), so it would actually be 1-029.6 how can I do this?

• sql
• postgresql

• Conceptually speaking is the value really a numeric value or a string value? Seems like you need to get the model right first. – The Impaler Commented Mar 29 at 23:42

1 Answer 1

Use to_char() data type formatting function in your select:
_{demo at db<>fiddle}

create table tbl(v numeric(5,1)check(v>=1000 and v<=6000));
insert into tbl values(1029.6);
select to_char(v,'0-000.0') from tbl;

If you want that format to be available without needing to_char() in your select, you can save that as a view or a generated column. Keep in mind that if the value is processed by anything else further, it's no longer a proper numeric so you need to undo that formatting or revert to the original value.

The check constraint makes sure your values really are in the range you specified:

insert into tbl values(6001);

ERROR:  new row for relation "tbl" violates check constraint "tbl_v_check"
DETAIL:  Failing row contains (6001.0).

insert into tbl values(0.1);

ERROR:  new row for relation "tbl" violates check constraint "tbl_v_check"
DETAIL:  Failing row contains (0.1).

The typemod rounds values above your specified precision:

insert into tbl values(3333.7777)returning*;

If they happen to be not just higher than the upper range limit, but also above the scale, it complains before the check constraint.

insert into tbl values(76543.21);

ERROR:  numeric field overflow
DETAIL:  A field with precision 5, scale 1 must round to an absolute value less than 10^4.