Insert image into postgresql database

Question

I would like to know how can I insert a "bytea" image into my postgreSql database table? I searched the forums for hours and saw the same question posted dozens of times, but so far have not found a single answer. All I see is how to insert .jpeg into the old column, and that is not what I need.

Here is the database table:

create table category  (
"id_category" SERIAL,
"category_name" TEXT,
"category_image" bytea,
constraint id_cat_pkey primary key ("id_category"))without oids;

and when I add a new line, it does not work:

insert into category(category_name,category_image) values('tablette', lo_import('D:\image.jpg'));

Answer 1

insert into category(category_name,category_image) values('tablette', bytea('D:\image.jpg'));

The above solution works if istea column type

insert into category(category_name,category_image) values('tablette', lo_import('D:\image.jpg'));

The above solution works if the column type is oid, i.e. Blob

insert into category(category_name,category_image) values('tablette',decode('HexStringOfImage',hex));

. - HexString . - hex . ​​ bytea postgres.

Answer 2

( ) .

create or replace function img_import(filename text)
  returns void
  volatile
  as $$
    declare
        content_ bytea;
        loid oid;
        lfd integer;
        lsize integer;
    begin
        loid := lo_import(filename);
        lfd := lo_open(loid,131072);
        lsize := lo_lseek(lfd,0,2);
        perform lo_lseek(lfd,0,0);
        content_ := loread(lfd,lsize);
        perform lo_close(lfd);
        perform lo_unlink(loid);

    insert into category values
    ('tablette',
    content_);
    end;
$$ language plpgsql

select * from img_import('D:\image.jpg'); , .

Answer 3

:

create or replace function bytea_import(p_path text, p_result out bytea) 
                       language plpgsql as $$
    declare
      l_oid oid;
    begin
      select lo_import(p_path) into l_oid;
      select lo_get(l_oid) INTO p_result;
      perform lo_unlink(l_oid);
    end;$$;

:

insert into table values(bytea_import('C:\1.png'));

Answer 4

SQL Workbench - Database Explorer - ...