CODEWITHSUNDEEP
postgresql
PriMe
PriMe

Reputation: 55

How to update bytea data in Postgres table?

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

Upvotes: 1

Views: 7806

Answers (2)

Ilya Khudyakov
Ilya Khudyakov

Reputation: 311

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

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246063

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.

Upvotes: 1

Related Questions