PG SQL Insert hex array into bytea array

Qifeng Li 0
Viewed 13

I have a table as follows.

create table my_table
(
    f1          bytea        not null,
    f2          bytea[]      not null,
);

I'm using the following insert statement to insert the hex values as bytea.

INSERT INTO my_table (f1,f2) 
VALUES (
  E'\\xF7C26945C70B646321D89202DE7FDCAB1A49833A26C68027C228437AE04FB2A8',
  '{
    0xA6697E974E6A320F454390BE03F74955E8978F1A6971EA6730542E37B66179BC,
    0x4B52414B00000000000000000000000000000000000000000000000000000000
   }'
);

When I use a select statement, a match on f1 works as expected.

SELECT * FROM my_table 
WHERE f1 = '\xF7C26945C70B646321D89202DE7FDCAB1A49833A26C68027C228437AE04FB2A8';

I'm trying to use the @> operator to filter f2 and it currently yields no matches.

SELECT * FROM my_table 
WHERE f2 @> ARRAY [('\xA6697E974E6A320F454390BE03F74955E8978F1A6971EA6730542E37B66179BC')::bytea];

When I try to reference f2, it doesn't work because the database seems to store my hex array as a bunch of numbers, something resembling'\30140912....'. Is there a way to insert a bytea[] into my table using SQL such that it's still stored as text like f1?

bytea hex blockchain postgresql
1 Answers

After trying a few different things, the following worked for me.

INSERT INTO my_table (f1,f2) 
VALUES (
  E'\\xF7C26945C70B646321D89202DE7FDCAB1A49833A26C68027C228437AE04FB2A8',
  '{
    \\xA6697E974E6A320F454390BE03F74955E8978F1A6971EA6730542E37B66179BC,
    \\x4B52414B00000000000000000000000000000000000000000000000000000000
   }'
);
Qifeng Li0
Related