Database-migration ms sql to postgresql

Question

I am getting a syntax error when converting between SQL Server to PostgreSQL. Any thoughts?

IF (var_port_with_bmrk_total_mv != 0 AND var_bmrk_info IS NOT NULL) THEN
    BEGIN
        insert into t$tmp_diff
                select @asof_dt asof_dt,@choiceID choiceID ,p.input_array_type ,p.group_order, CONVERT(DECIMAL(32,10),p.port_value/@var_port_total_mv) port_value,convert(decimal(32,10), isnull(bmrk_value/@port_with_bmrk_total_mv,0)) bmrk_value
                from  t$tmp_port_sum p, t$tmp_bmrk_sum b
                where p.input_array_type=b.input_array_type and p.group_order = b.group_order
    END;
ELSE

Original before conversion

insert into #tmp_other_diff 
select @asof_dt asof_dt,@choiceID choiceID , b.input_array_type,b.grouping,convert(decimal(32,10),0)  port_value, (bmrk_value/@port_with_bmrk_total_mv) bmrk_value
from   #tmp_bmrk_other_sum b
where  b.key_value not in ( select p.key_value from #tmp_port_other_sum p)

Error message:

Error occurred during SQL query execution

Reason:
SQL Error [42601]: ERROR: syntax error at or near ","
Position: 9030

the relevant comma being:

CONVERT(DECIMAL(32,10),p.port_value

Belayer · Accepted Answer · 2022-09-27 18:00:16Z

2

There is no convert() function in Postgres. Use the SQL standard cast or the Postgres extension ::data type. In this case:

...., cast(0 as decimal(30,10)) port_value, .... 
     OR 
...., 0::decimal(30,10) port_value, ...

Note: No comma after the expression. In the original port_value is the column alias. You need to keep it that way.

answered Sep 27, 2022 at 18:00

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database-migration ms sql to postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related