PG SQL intercepts the string to the specified character position

I'm doing it today PG Data to HIVE Data exchange tasks for , Because a field is PG Medium is Varchar type ,hive yes bigint, And it happened PG The stored values of this field in are added with decimal places , Cause field type conversion failure .

Now you need to cut off the part after the decimal point in the string .
At first, I tried to use CHARINDEX To get the position of the decimal point , And then use substring The function intercepts the value before the position .

select CAST(SUBSTRING(sal_qty, 1 ,  CHARINDEX('.',sal_qty)-1)as bigint)

But the runtime found that PG There is no CHARINDEX function .

SQL  error  [42883]: ERROR: function charindex(unknown, character varying) does not exist
   Suggest ：No function matches the given name and argument types. You might need to add explicit type casts.

Find the corresponding replacement function in the official documentation
https://www.postgresql.org/docs/current/functions-string.html
Find one position function

position ( substring text IN string text ) → integer

Returns first starting index of the specified substring within string, or zero if it's not present.

position('om' in 'Thomas') → 3

Test it

select sal_qty, cast(SUBSTRING(sal_qty, 1 , position ('.'  in sal_qty) - 1) as bigint) as sal_qty_int from table_test

Return results , In line with expectations .

In addition, you can try to use strpos function , Function and position identical , But pay attention to the parameter position

strpos ( string text, substring text ) → integer

Returns first starting index of the specified substring within string, or zero if it's not present. (Same as position(substring in string), but note the reversed argument order.)

strpos('high', 'ig') → 2

select sal_qty, cast(SUBSTRING(sal_qty, 1 ,  strpos(sal_qty,'.')-1)as bigint) as sal_qty_int from table_test