lo_create(0) how to use with insert query

Question

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.

insert into hospital_image select 'MainLogoImage99999', lo_from_bytea(lo_create(-1),decode('/erewreerregrefgrege','base64')),'jpg','123'; — Ravi, Jan 25 '18 at 09:27
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; — Ravi, Jan 25 '18 at 09:29
ERROR: duplicate key value violates unique constraint "pg_largeobject_metadata_oid_index" DETAIL: Key (oid)=(4294967295) already exists. ********** Error ********** — Ravi, Jan 25 '18 at 09:37
select lo_creat(-1) returning oid of 35608 but when i use in the insert statement i am getting error that 35609 already exists. I dont know . — Ravi, Jan 25 '18 at 09:42
insert into static_image select 'MainLogoImage99999', lo_from_bytea(max(loid::bigint),decode('/erewreerregrefgrege','base64')),'jpg','123';ERROR: column "loid" does not exist LINE 2: lo_from_bytea(max(loid::bigint),decode('/erewreerregrefgrege... ^ ********** Error ********** ERROR: column "loid" does not exist SQL state: 42703 Character: 74 — Ravi, Jan 25 '18 at 09:44
i want an insert query as given the table structure which i dont want to provide loid dynamically.in an insert of statement lo_creat(-1) or lo_create(0) is not working. how to use lo_creat(-1) in the insert statement. Can you please help us to write me one quey for this one ? — Ravi, Jan 25 '18 at 09:48

Laurenz Albe · Accepted Answer · 2018-01-26T08:42:34.550

4

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.

edited Jan 26 '18 at 08:42

answered Jan 25 '18 at 10:01

Laurenz Albe

209,280
17
206
263

i checked that too.No it wont happen like that.lo_create gives you an oid which doesn't exist before.But for me it is giving every time the same error. in an insert statement i want to supply the oid from the functions of postgres like lo_creat(-1) or lo_create(0) – Ravi Jan 25 '18 at 10:05
i have given a table structure. can you help me to write a insert query for the above table structure. using dynamically supply loid with the help of postgres functions – Ravi Jan 25 '18 at 10:10
How *exactly* does the code look? In your question you use a constant 1. In reality you'd do something like `myoid := lo_create(0);`, then write the large object, then insert `myoid` into the table. – Laurenz Albe Jan 25 '18 at 12:54
INSERT INTO hospital_image( key, image, mime_type, version) VALUES ('MainLogoImage99999', lo_from_bytea(myoid := lo_create(0),decode('/9j/4AAQSkZJRgABAQEA3ADcAAD','base64')), 'jpg', 123); – Ravi Jan 25 '18 at 13:27
when i executed above statement i got following error ERROR: positional argument cannot follow named argument LINE 3: ...oImage99999', lo_from_bytea(myoid := lo_create(0),decode('/9... ^ – Ravi Jan 25 '18 at 13:27
^ ********** Error ********** ERROR: positional argument cannot follow named argument SQL state: 42601 Character: 141 Please help me how to get out of this error. – Ravi Jan 25 '18 at 13:28
Remove the `myoid :=` from the insert statement. – Laurenz Albe Jan 25 '18 at 14:09
ERROR: duplicate key value violates unique constraint "pg_largeobject_metadata_oid_index" DETAIL: Key (oid)=(34773) already exists. ********** Error ********** ERROR: duplicate key value violates unique constraint "pg_largeobject_metadata_oid_index" SQL state: 23505 Detail: Key (oid)=(34773) already exists. – Ravi Jan 25 '18 at 17:04
if i use the lo_create(0) i got the following above error. – Ravi Jan 25 '18 at 17:09
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/163889/discussion-between-ravi-and-laurenz-albe). – Ravi Jan 25 '18 at 17:10
1

I think I have finally understood your problem. See the corrected answer. – Laurenz Albe Jan 26 '18 at 08:43
yes you are right. After going through postgres docs,you are right. – Ravi Jan 27 '18 at 11:15

lo_create(0) how to use with insert query

1 Answers1