PostgreSQL : Cast Data Type from Text to Bigint when using WHERE IN

Question

I've problem when try to cast data type from TEXT to BIGINT when using WHERE IN on PostgreSQL in procedure. This always gives

operator does not exist: bigint = text. Try cast the variable in the query.

But still get the same notice. This is example query:

    DECLARE

     -- $1 params text

    BEGIN
      SELECT * FROM table_a where 
      colId IN($1); // notice is here, colId is bigint
    END 

    /*Call the procedure*/

    SELECT my_function('1,2,3,4,5');

How do we cast the variable? Thanks!

Pavel Stehule · Accepted Answer · 2018-04-03 04:56:24Z

2

Using strings for id list is wrong design. You can use a arrays in PostgreSQL.

For example

CREATE OR REPLACE FUNCTION foo(VARIADIC ids int[])
RETURNS SETOF table_a AS $$
SELECT * FROM table_a WHERE id = ANY($1)
$$ LANGUAGE sql;

SELECT foo(1,2,3);

But, usually wrapping simple SQL to functions looks like broken design. Procedures should not to replace views.

answered Apr 3, 2018 at 4:56

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

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

2 Comments

Ugy Astro Over a year ago

Thanks, this method is much more scientific. Previously sorry for the simple use of SQL, I just wanted to make it look simple and focus on the issues at hand. Despite the fact that SQL is very long. Really! thank's, you again helped solve my problem @Pavel Stehule :)

Pavel Stehule Over a year ago

@UgyAstro use views instead this kind of functions.

Collectives™ on Stack Overflow

PostgreSQL : Cast Data Type from Text to Bigint when using WHERE IN

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related