Problem:
While importing csv data file into pgsql database using php, we received the following error in laravel log,
Next Illuminate\Database\QueryException: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xf8 (SQL: insert into "user_data" ("user_id","pref_name", "first_name", "last_name", "worker_name") values (123, Einar, Einar, Dørum, Dørum) returning "id") in /vendor/laravel/framework/src/Illuminate/Database/Connection.php:647
Solution:
Database encoding and input data encoding should be in same format.
So need to check the database table encoding first,
To get/check encoding of your database in pgAdmin, right-click the database name and select ‘Properties’.
And in Laravel/php code, encoding the data with same format , in my case it’s UTF8, so we changed the code as follows before insert data into db table.
$data->pref_name = utf8_encode($data[2]);
Other possible error logs for the same are,
local.ERROR: PDOException: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xe9 0x2c 0x20 in /vendor/laravel/framework/src/Illuminate/Database/Connection.php:323
local.ERROR: PDOException: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xf6 0x6d in /vendor/laravel/framework/src/Illuminate/Database/Connection.php:323
Next Illuminate\Database\QueryException: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xf6 0x6d
