cant store 0 or 1 as boolean laravel postgresql

Question

I was able to store boolean values as 0 or 1 , but suddenly it keeps returning me the error

column \"trial\" is of type boolean but expression is of type integer

Im using postgresql 13 with laravel 8

how can I solve this ?

@AhmedRafie, php-pgsql has updated on my system, and this problem happened to all my Laravel-Postgres projects — Foad Tahmasebi, May 02 '21 at 12:22

score 6 · Answer 1 · answered May 02 '21 at 12:41

6

If you are on PHP 7.4.18 or 8.0.5 then watch for a breaking change:

From Github PHP #6801:

In summary, previously if you had a boolean column on a Postgres table, you could bind an integer value (0 or 1 typically) in the where condition against that column using PDO::PARAM_INT and everything was fine. However, you will now receive an error.

While the fix was already reverted, we need to downgrade to 7.4.16 / 8.0.3 or wait for the next patch release. See PHP Issue #80892

answered May 02 '21 at 12:41

Martin Meixger

2,547
24
26

This is pretty crazy. I hit this bug silently through my Docker build process, and was very confused upon reading the CI failures as to why this would be happening. I thought I was being conservative enough specifying `php:8.0-alpine` in my Docker image, but clearly not. Disappointing this wasn't caught before release. – marked-down May 03 '21 at 20:50

score 0 · Answer 2 · answered May 01 '21 at 22:33

0

As I am not sure about your model because you did not share it... If you READ THE DOCUMENTATION you will find out that you can cast a value.

So, your model should have a property $casts like this:

protected $casts = [
    'trial' => 'boolean'
];

This will allow you to input any value and it will always cast it back to boolean so you can do $model->trial = 1, or $model->trial = '1' or $model->trial = 2 or any true-ish value and it will cast it to true, so you should not have any more problems...

Same goes for false-ish values like 0 or null, etc.

answered May 01 '21 at 22:33

matiaslauriti

7,065
4
31
43

I was able to insert 0 and 1 without specifying the $casts for a long time , I dont know whats new in the case to prevent that ? – Ahmed Rafie May 01 '21 at 22:38
What did you update ? We cannot know anything if we do not know what have you done for this to happen... did you change your database like MySQL to PostgreSQL ? Did you update Laravel to 8 ? Did you run a new migration ? What were you running before this started to happen ? – matiaslauriti May 01 '21 at 22:39
I can doubt about the upgrade I did to laravel from 5.8 to 8 , but it was working fine for at least 4 days since that . – Ahmed Rafie May 01 '21 at 22:41
Are you working with someone else ? I mean, using a VCS like Git so he/she could have changed the code ? Can you share any data like that ? This is not magic that you update and works for X amount of time and then does not, something happened in the middle. Maybe PostgreSQL got updated ? – matiaslauriti May 01 '21 at 22:47
Im using docker image version 13 for postgresql , can it be updated after restarting docker-compose ? – Ahmed Rafie May 01 '21 at 22:50
No, you must rebuild it to get it updated (only if you are doing `docker-compose up -d --build` or you down the image and then up again). Also, you have to be using `image: postgres:latest` for this to happen, if you are using a tag version, should be no problem then. Or you go inside the running container and manually update it. – matiaslauriti May 01 '21 at 22:53

score 0 · Answer 3 · answered May 12 '21 at 08:38

I use this. It's worked for me.
'pgsql' => [
  'driver' => 'pgsql',
  'url' => env('DATABASE_URL'),
  'host' => env('DB_HOST', '127.0.0.1'),
  'port' => env('DB_PORT', '5432'),
  'database' => env('DB_DATABASE', 'forge'),
  'username' => env('DB_USERNAME', 'forge'),
  'password' => env('DB_PASSWORD', ''),
  'charset' => 'utf8',
  'prefix' => 'tbt_',
  'prefix_indexes' => true,
  'schema' => 'public',
  'sslmode' => 'prefer',
  'options' => extension_loaded('pdo_pgsql') ? array_filter([
    PDO::ATTR_EMULATE_PREPARES => false,
  ]) : [],
  ],

score -1 · Answer 4 · answered May 01 '21 at 22:21

-1

As far as I remember, you could store int in a bool column in postgres, I tried it on my several projects. Maybe you need to alter your trial column using:

ALTER TABLE mytable ALTER COLUMN trial DROP DEFAULT;
ALTER TABLE mytable ALTER trial TYPE bool USING CASE WHEN trial=0 THEN FALSE ELSE TRUE END;
ALTER TABLE mytable ALTER COLUMN trial SET DEFAULT FALSE;

or

ALTER TABLE mytable ALTER COLUMN trial DROP DEFAULT;
ALTER TABLE mytable ALTER trial TYPE bool USING trial::boolean;
ALTER TABLE mytable ALTER COLUMN trial SET DEFAULT FALSE;

source

another way is just convert integer to boolean using php:

$t = 1;
$f = 0;
$b = !!t; //true
$a = !!f; //false

answered May 01 '21 at 22:21

Muhammad Dyas Yaskur

6,914
10
48
73

Since this is laravel related questions, probably the op doesn't use direct sql to create the tables but migrations. – Elias Soares May 01 '21 at 22:24
We can use RAW SQL query in the migration. https://stackoverflow.com/questions/28787293/run-raw-sql-in-migration – Muhammad Dyas Yaskur May 01 '21 at 22:26
For sure, but we can also nou use it. That's advantage of the schema builder – Elias Soares May 01 '21 at 22:27
schema builder doesn't support `using case` – Muhammad Dyas Yaskur May 01 '21 at 22:28

score -1 · Accepted Answer · answered May 01 '21 at 23:34

-1

SOLVED

after a lot of searching here and there , I found a query to update the pg_cast to allow 0 and 1 as boolean

update pg_cast set castcontext='a' where casttarget = 'boolean'::regtype;

answered May 01 '21 at 23:34

Ahmed Rafie

67
2
9

cant store 0 or 1 as boolean laravel postgresql

5 Answers5

Linked