CODEWITHSUNDEEP
postgresql
Madhava
Madhava

Reputation: 87

How to insert binary data in Postgres oid column

I have following query 

 INSERT INTO SESSIONINFO(ID, LASTMODIFICATIONDATE, RULESBYTEARRAY, STARTDATE) VALUES 
(1, NULL, '\xaced0005774d0a060805100418005243080010001a3918002000320608011000180042121a080a044d41494e100022060a044d41494e52090a0744454641554c545a0c0a0a0805320608001000180122026800')

When i try to execute above statement getting following error

ERROR: invalid input syntax for type oid: "\xaced0005774d0a060805100418005243080010001a3918002000320608011000180042121a080a044d41494e100022060a044d41494e52090a0744454641554c545a0c0a0a0805320608001000180122026800"

How to solve this..

Upvotes: 3

Views: 6734

Answers (2)

Piro
Piro

Reputation: 1435

To insert into column of type oid you can use lo_from_bytea function:

Create a large object and store data there, returning its OID. Pass 0 to have the system choose an OID.

So in your example:

INSERT INTO SESSIONINFO(ID, LASTMODIFICATIONDATE, RULESBYTEARRAY, STARTDATE) VALUES 
(1, NULL, lo_from_bytea(0, '\xaced0005774d0a060805100418005243080010001a3918002000320608011000180042121a080a044d41494e100022060a044d41494e52090a0744454641554c545a0c0a0a0805320608001000180122026800'))

Upvotes: 2

Craig Ringer
Craig Ringer

Reputation: 324511

I think you've confused oid and bytea.

bytea is used for binary data in a row. Your RULESBYTEARRAY column should almost certainly have bytea as its type. See binary data types in the manual.

The oid column type is a simple 32-bit unsigned integer. It's used by PostgreSQL to refer to system tables and all sorts of other things. One of the uses is to refer to rows in the pg_largeobject table, which can be used to store file-like objects.

If you want to store and retrieve binary data you almost certainly want to use bytea-typed fields, not pg_largeobject. pg_largobject is intended for very, very big data and/or data that you want to read and modify without having to load the whole lot each time and write it all back again whenever you change anything.

Upvotes: 1

Related Questions