How to update bytea data in Postgres table?

Question

I am trying to import contents of a file locally stored into a column of a row in a table. The type of column is bytea.

Something like:

UPDATE server_info
SET key = lo_import('C:\Users\certificate.p12')
WHERE server_id = 1;

However, it gives below error:

ERROR:  column "key" is of type bytea but expression is of type oid

I tried casting. Used below line for it:

UPDATE server_info
SET key = lo_import('C:\Users\certificate.p12')::bytea
WHERE server_id = 1;

But it gives:

ERROR:  cannot cast type oid to bytea

I am new to using Postgres. Any leads in this matter will be helpful. Thank you

Which SQL client are you using? Some support that kind of "uploading" files into a bytea column. — user330315
– user330315, Commented Sep 19, 2018 at 8:25

Laurenz Albe · Accepted Answer · 2018-09-19 08:21:38Z

1

You won't be able to do that with just SQL.

You'll have to write a program in the language of your choice that reads the files into memory and uses that as parameter to an INSERT.

answered Sep 19, 2018 at 8:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PriMe Over a year ago

Okay. That way I am able to do it i.e. using a program. But wanted to know if it is possible via just SQL.

Ilya Khudyakov · Accepted Answer · 2019-02-27 11:06:16Z

0

Well, if you want to do it just with an SQL, you could first import it as an oid and then convert it to a bytea. But this a little bit weired.

ALTER TABLE server_info add column key_bytea bytea
UPDATE server_info SET key_bytea = lo_get(key)
ALTER TABLE server_info drop column key
ALTER TABLE server_info rename column key_bytea to key

answered Feb 27, 2019 at 11:06

Ilya Khudyakov

3113 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to update bytea data in Postgres table?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related