Binary UUID in Laravel with Postgres?

Question

PostgreSQL

There is a need to store the ID in the UUID format After reading
various sources, it was decided to generate a uuid, convert it to a binary and store it in the database in this form, and convert it from a binary form to a regular uuid when outputting

What was done:

In migration: In the model:
$table->binary('id')->primary();

parent::creating(static function (self $model) {
            $model->setIncrementing(false);
            $model->{$model->getKeyName()} = Uuid::uuid4()->toString();
        });

All this works well, but in sqlite, when migrating to postgres, an error occurs:

SQLSTATE[22021]: Character not in repertoire: 7 ERROR:  invalid byte sequence for encoding "UTF8": 0x85 (SQL: insert into "users" ("name", "email", "email_verified_at", "password", "remember_token", "id", "updated_at", "created_at") values (Rocky Bartoletti, [email protected], 2020-05-06 08:03:08, $2y$10$b/4r5FCM5tlAj6XG1fu7Ie4OPFmS5BhU3ssKAswbo2yUTYerOpI2W, IctZ1tOPQg, ��z�]�Dq�7��6L�
                                               , 2020-05-06 08:03:08, 2020-05-06 08:03:08))

With postgres so far, you still can’t understand what he wants from me .. As I understand from the Internet, he does not perceive the binary in UTF well.
The column that is created during migration has the bytea format.

I tried to copy this query and paste it directly into the database:

insert into "users" ("name", "email", "email_verified_at", "password", "remember_token", "id", "updated_at",
                     "created_at")
values ('Rocky Bartoletti', '[email protected]', '2020-05-06 08:03:08', '$2y$10$b/4r5FCM5tlAj6XG1fu7Ie4OPFmS5BhU3ssKAswbo2yUTYerOpI2W', 'IctZ1tOPQg', '��z�]�Dq�7��6L�
                                               ', '2020-05-06 08:03:08', '2020-05-06 08:03:08')

Everything was inserted ok, but maybe it's because of the quotes, without quotes, he swears at ']', but I think that he just tried to translate into a string and in fact there is another character

In general, I don’t understand what to do here and where look .. It is possible to store the UUID simply in the text version, but it is too simple. I'd like to figure out what's wrong here.

Answer 1

Before trying to reinvent the wheel, check out the stock features. If you really really want to use uuid instead of the more compact and faster bigint, then postgresql already has this data type.
Stored as a fixed length field of 16 bytes.
By converting it to bytea, you will only increase, not decrease, its size. Because for bytea, it will also need to store the length of the data in this field.

With postgres so far on you and I can’t understand what he wants from me .. As I understand from the Internet, he does not perceive the binary in UTF well

I mean, that's how it's meant to be. If you have a base in utf8, then all text fields are deliberately checked to have something valid for utf8 in them.