lo_create(0) how to use with insert query

insert into hospital_image 
select 'HospitalImage', 
       lo_from_bytea(1,decode('/9j/4AAQSkZJRgABAQEA3ADcAAD','base64')),
       'jpg',
       '123'
where not exists (select null from pg_largeObject where loid=1);

CREATE TABLE hospital_image ( 
   key character varying(30) NOT NULL, 
   image oid NOT NULL,
   mime_type character varying(30) NOT NULL, 
   version numeric(8,0) NOT NULL,
   CONSTRAINT 
      pk_hospital_image PRIMARY KEY (key)
) WITH ( OIDS=FALSE );

 ALTER TABLE
    hospital_image OWNER TO postgres;

Here in the above Statement we are supplying the loid manually as 1. Instead we want to get the loid dynamically using lo_create(0). When I use lo_create(0) as per the Postgres docs, Iget an exception.

I used both lo_creat(-1) and lo_create(0). Both doesn't work. It is saying loid exists already. how to use the above functions in my query.

My SQL statement for including a variable OID is:

INSERT INTO hospital_image (key, image, mime_type, version)
VALUES ('MainLogoImage99999',
        lo_from_bytea(lo_create(0),
                      decode('/9j4AAQSkZJRgABAQEA3ADcAAD',
                      'base64'))‌,
        'jpg',
        123);

The error message is:

ERROR: duplicate key value violates unique constraint "pg_largeobject_metadata_oid_index"
SQL state: 23505
Detail: Key (oid)=(34773) already exists.

Solution

Both lo_creat(-1) (the argument doesn't matter) and lo_create(0) will create a new large object and return its OID.

lo_create(-1) is the same as lo_create(4294967295) – OIDs are unsigned 4-byte integers.

lo_from_bytea also creates a new large object, so if you pass it the result from lo_create, it complains that it cannot create a large object with the same number again.

Just pass 0 instead of lo_create(0) as the first argument to lo_from_bytea.