Using PostgreSQL with Laravel - SQLSTATE[22P02]

[wayz9]

Hi,

This is my first thread ever here. I have recently switched to Postgres with Laravel to handle a much heavier database.
The exception Im getting:

Illuminate\Database\QueryException with message 'SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type bigint: "b" CONTEXT: unnamed portal parameter $1 = '...' (SQL: select * from "posts" where "user_id" = b)'

Steps to reproduce:

1. Create a model, basic Post model.

2. Then add a foreign key and make it constrained. I have used this syntax for past couple of months:
   $table->foreignIdFor(User::class)->constrained();

3. Then try to query all of the records that belong to a single user.
   Post::where('user_id', 1)->get() this will work, same as Post::whereUserId(1)->get().

Now try to use something that is not an integer you will get a QueryException, example Post::whereUserId('b')->get()

Seems to be also trigered when you try something like Post::whereIn('user_id', ['a', 1, 2])->get()

Switching to MySQL Driver does not reproduce the issue, it simply runs the query and I get empty collection as expected.
Wonder if this is intended behaviour or a bug? Just wanted to ask first before opening an Issue.

PHP Version 8.1.0
Laravel Version: 9.18.0
PostgresSQL Version: 14.2

[erikn69]

The query exception comes from PostgreSQL, not from laravel, laravel builds the query like normally do
It is a PostgreSQL intended behaviour to avoid mixing data types

[wayz9]

Thanks for replying, yeah after some research I came to the same answer, thanks.