Thread: Need help how to reproduce MySQL binary to PosgreSQL

Hello,

how I can reproduce MySQL binary to PosgreSQL and use follow 
functionality in PG.

On mysql I have table with 3 fields:

CREATE TABLE table (
   user_id int,
   user_info binary(4),
   user_data binary(16)
);

then I use them for example:

UPDATE table set user_info = CHAR(0, 2, 0, 0), user_data = CHAR(123, 1, 
0, 2) WHERE user_id = 1;

select and insert them as:

SELECT HEX(user_info) AS user_info, HEX(user_data) AS user_data FROM 
table;
INSERT INTO table (user_info, user_data) VALUES 
(UNHEX(hexdata_commng_from_app), UNHEX(hexdata_commng_from_app));

I check PG does not have functions HEX and UNHEX. Some one can help a 
little ?

Regards,
HS

On 09-08-2020 09:20, Ashesh Vashi wrote:
> Hi,
> 
> Have you checked this?
> https://stackoverflow.com/questions/44813386/mysqls-hex-and-unhex-equivalent-in-postgres
> 
> --
> 
> Thanks & Regards,
> 
> Ashesh Vashi
> EnterpriseDB INDIA: Enterprise PostgreSQL Company [1]
> 
> _http://www.linkedin.com/in/asheshvashi_ [2]
> 
> On Sun, Aug 9, 2020 at 11:42 AM Condor <condor@stz-bg.com> wrote:
> 
>> Hello,
>> 
>> how I can reproduce MySQL binary to PosgreSQL and use follow
>> functionality in PG.
>> 
>> On mysql I have table with 3 fields:
>> 
>> CREATE TABLE table (
>> user_id int,
>> user_info binary(4),
>> user_data binary(16)
>> );
>> 
>> then I use them for example:
>> 
>> UPDATE table set user_info = CHAR(0, 2, 0, 0), user_data = CHAR(123,
>> 1,
>> 0, 2) WHERE user_id = 1;
>> 
>> select and insert them as:
>> 
>> SELECT HEX(user_info) AS user_info, HEX(user_data) AS user_data FROM
>> 
>> table;
>> INSERT INTO table (user_info, user_data) VALUES
>> (UNHEX(hexdata_commng_from_app), UNHEX(hexdata_commng_from_app));
>> 
>> I check PG does not have functions HEX and UNHEX. Some one can help
>> a
>> little ?
>> 
>> Regards,
>> HS
> 
> 

Yea, I checked it, but because is 3 years old solution I expect these 
functions to be implemented long ago and just have another names or to 
have similar functions that do the same functionality. It's seems not, 
Okay will use the solution from sof.

Regards,
HS

On Sun, 9 Aug 2020 at 12:49, Condor <condor@stz-bg.com> wrote:
> Yea, I checked it, but because is 3 years old solution I expect these
> functions to be implemented long ago and just have another names or to
> have similar functions that do the same functionality. It's seems not,
> Okay will use the solution from sof.

Those functions _have_ been implemented long ago with other names, as
per the answer to the link given.

The functions you want are encode and decode, or to_hex if you want to
encode integers. The only reason to use the CREATE FUNCTION shims as
per the answer is if you don't want to change your code.

You might find https://pgxn.org/dist/mysqlcompat/ helpful too.

Geoff