0

I found an issue of Postgres decimal places auto become 6 places when try to insert data from SQL Server into Postgres using OPENQUERY.

I have searched many references that suggested using CAST or Convert to limit decimal places from SQL Server, everything is work fine when I just tried select from the SQL Server side (It is 0.001), but whenever run the query like below, in Postgres (for example the 'Rounding' will become 0.001000).

For example:

INSERT INTO OPENQUERY(RND,
'SELECT
    name,
    rounding
FROM test.public.product_uom')
SELECT
    UoMID,
        0.001
FROM dbo.tUoM
WHERE UoMID IN ('YEAR', 'ZAK');

The expected result that I would like is to have the same value of Rounding when Insert into from SQL Server to Postgres that is 0.001. Any help or suggestions will be appreciated and thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Robert Chen
  • 1
  • 1
  • The field rounding is doesn't exist in SQL Server, so just pass a fixed value of 0.001. For information the data type in Postgres is Numeric. – Robert Chen May 14 '19 at 02:33
  • But you are inserting into a SQL Server table, right? So the data type of the target table in SQL Server is relevant, not the source –  May 14 '19 at 02:42
  • I insert into Postgres table which table name is 'product_uom' by using OPENQUERY that fetch the data from MSSQL which table name is 'tUoM'. But in the 'tUoM' doesn't have Rounding field so in order to cater the Postgres 'product_uom' which Rounding field is mandatory, I need to pass a fixed value which is 0,001. However when I run the Insert script the value becomes 0.001000 in 'product_uom' Rounding. Already use CAST or Convert but the same problem still persist too. – Robert Chen May 14 '19 at 02:48
  • Ah, I see. Then I misunderstood the code. Which tool do you use to display the data from Postgres? Sounds like this is a _display_ issue with your SQL client, rather than a data issue. –  May 14 '19 at 02:51
  • I am using PGAdmin version 9.6. – Robert Chen May 14 '19 at 02:54
  • There is no pgAdmin with that version (that's the Postgres version). You could try to use `SET extra_float_digits = 3;` to change the display. Not sure if pgAdmin respects that, you will need to check the options (I don't use pgAdmin, so I can't help with that) –  May 14 '19 at 02:57
  • Sorry for my misunderstanding, my PgAdmin version is 4. – Robert Chen May 14 '19 at 03:01
  • OK thanks, but the display is fine when I insert directly from postgres. Still need to look for another solution and still thank you for your suggestion. – Robert Chen May 14 '19 at 03:21
  • The data type in postgres is just numeric? What if you change to numeric(x,3)? You'll have to choose a value of x that makes sense for your data. – Jeremy May 14 '19 at 12:53

0 Answers0