Insert a string into a bytea column-CodePudding

I want to insert text data into a Postgres bytea column using the concat function or the || operator. I am getting an error

column "name" is of type bytea but expression is of type text

create table test(
   name bytea
);

insert into test(name) values(concat('abac-' , 'test123'));
insert into test(name) values('aa' || 'bb');

I am executing the insert inside a stored procedure. If want to add the argument like

(concat('abac-' , row.name , 'test123'));

How do I do it?

CodePudding user response：

Perform a type cast after concatenating the values:

INSERT INTO test (name)
   VALUES (CAST('abac-' || row.name || 'test123' AS bytea));

Note: The difference between || and concat is how they behave with NULLs.

CodePudding user response：

You need to cast both strings to bytea, for example:

insert into test(name) values('aa'::bytea || 'bb'::bytea);