How to store decimal numbers in Postgresql database?

What’s the best way to store and retrieve decimal numbers in/from a PostgreSQL database table?
The ‘decimal’ field type is not available in Wappler, so should it be stored as a string and converted to a number on retrieval and back to a string again when writing to the database?

What do you mean? Where is it not available exactly?

I mean in the field type selection dropdown:

image

Or is it possible to use the ‘Integer’ type for decimal numbers too? According to this doc it isn’t, is it?

So you are referring to the Database Manager?
Of course it is available, just switch to Advanced mode:

Basic mode displays just the most used and most basic field types, which are more than enought for new users to setup a table.

1 Like

Oh, I am sorry @Teodor.
Didn’t think of switching to Advanced mode.
Found it now, thanks for explaining.

1 Like

Hi @Teodor,

Got another Decimal number database related problem:

In my PostgreSQL database I got this field:
image

Whenever I set the field type to Decimal and Apply the database changes, the field is changed back after that to Integer type again.

Secondly, in one of my server actions, I am trying to insert a 2 decimal number into this field:

This insert results in an integer being saved to the db instead of a decimal number.
Besides that it’s not correct that the field had type text in the Database Insert query builder, isn’t it?

Thanks for your help in advance.

Sorry Emiel, i am not sure i understand what your issue is exactly. Can you try explaining this more detailed?

Hi @teodor,

Okay, let me try again:

1st screenshot:
The unit_price field is of the Type Integer according to the database manager, although I set it to Type Decimal when I created the field. Also, when I try to change the field Type to Decimal and apply the db changes, the field is still an Integer field according to the db manager.

2nd screenshot:
When I use the Database query builder (under server action steps) to save a 2 decimal value to the unit_price field, the value is saved as a 0 decimal number, thus as an integer.
Besides that, according the Database Query builder (under server action steps) the field is of the text type, while it should be a number (as the field Type is set to integer/decimal).

Hope this is more clear. If not, please let me give it another try :slight_smile:

Are you sure you are in Advanced mode when viewing the changes and not reverted back to Basic?

Yes, very sure.

So was it working 15 days ago (when you last replied) and now it just stopped working?

Well, at that point I only created the fields and didn’t build the server actions to actually save (2 decimal) number data to it.

I do remember the field type being set back again to Integer back then after applying the change to Decimal type, but had that same issue with other field types in postgresql before and even made a bug report for that.

So I guess it wasn’t working at that point either, but I didn’t notice until now.

So just to confirm, you are using Postgres and:

  1. You are in Advanced mode
  2. You set a random integer field type to Decimal
  3. You apply changes and see a successful message
  4. Then you see the field type is again integer (i.e. immediately after the success message?)

What server type are you using?

Correct, @4.: Yes, directly changes back to Integer type after success message.
If you like I can shoot a video of it.
I am using Nodejs server type.

What if you create a new field and set its type to Decimal?

Good one, just tried that too. Same problem. :frowning:

Ok we are going to check this.

1 Like

Great, thanks, let me know if you need more info.