invalid input syntax for integer: "" : Postgresql

Question

I am doing a migration from mssql to postgresql and I am facing issue in resolving one of the queries.

My Query:

SELECT 1, 
       CASE 
          WHEN cast((case 
                       when split_part(mt_outward_qty,'/',1) > '0' then  
                         substring(mt_outward_qty,0,split_part(mt_outward_qty,'/',1)::int) 
                       when split_part(mt_outward_qty,'.',1)>'0' then  
                         substring(mt_outward_qty,0,split_part(mt_outward_qty,'.',1)::int) 
                      else  
                       mt_outward_qty 
                   end) as int) > 0  
             THEN 'Y' 
             ELSE 'N' 
        END 
from STL_CS_Tra_requestdetails 
  LEFT JOIN STL_CS_Tra_storeopening ON req_id =  so_requestid 
  LEFT JOIN STL_CS_Tra_multiple_timinig ON req_id = mt_reqid

Upon executing this, I am getting:

invalid input syntax for integer: ""

Kindly help me in resolving this.

Look at this other question, maybe it will help you: PG COPY error: invalid input syntax for integer — Thierry P. Oliveira
– Thierry P. Oliveira, Commented Apr 9, 2021 at 12:32
You need to convert empty strings to a null value, e.g. using nullif(.., '') — user330315
– user330315, Commented Apr 9, 2021 at 12:45
It would be easier to help you, if you can post some sample data of that column mt_outward_qty (which seems to violate the most basic principles of good database design to begin with) — user330315
– user330315, Commented Apr 9, 2021 at 12:53
What does mssql do when you cast an empty string (produced by your case expression) as int, what is the expected behavior when mt_outward_qty is empty? — Bergi
– Bergi, Commented Apr 9, 2021 at 13:09

eshirvana · Accepted Answer · 2021-04-09 13:55:34Z

0

sql server automatically cast empty string to 0 when you cast to int

you can do it in postgresql like so :

( 0 || '')::int

answered Apr 9, 2021 at 13:55

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

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

Collectives™ on Stack Overflow

invalid input syntax for integer: "" : Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related