PostgreSQL load images to DB

Question

I've already know how to store images in DB, just use bytea type in my table

And I've already can save images to DB via code in my project .net Core, I've just getting image by url and saving like there:

using (HttpResponseMessage res = await client.GetAsync(photo_url))
  using (HttpContent content = res.Content) {
    byte[] imageByte = await content.ReadAsByteArrayAsync();
     using (NpgsqlConnection conn = new NpgsqlConnection("ConnectionString")) {
      conn.Open();
      using (NpgsqlTransaction tran = conn.BeginTransaction())
      using (NpgsqlCommand cmd = new NpgsqlCommand("Photo_Save", conn)) {           
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("photo", NpgsqlTypes.NpgsqlDbType.Bytea, imageByte);
        cmd.ExecuteScalar();           
        tran.Commit();
  }
}

It's work well

But I need save to table images from my pc

Is there any way to upload images into the database without the code on the host or in any other project, just use local picture and connection to Postges DB?

yeah i seen just now. If i have seen this on July i would have answered on july 16 2018. Sorry for answering late.I have not seen this before.Apologies. — Ravi, Sep 19 '19 at 14:37

score 5 · Accepted Answer · answered Jul 17 '18 at 17:41

5

If you can use psql, you could use \lo_import to import the image and the lo_open and loread functions to read the contents as a bytea.

Assuming that I want to import file chuck.jpg into a table blobs, and the file does not exceed 1000000 bytes, that could be done like this:

test=> \lo_import chuck.jpg 
lo_import 152237

test=> INSERT INTO blobs VALUES (1, loread(lo_open(152237, 131072), 1000000));
INSERT 0 1

test=> \lo_unlink 152237
lo_unlink 152237

I used \lo_unlink to remove the temporary large object.

answered Jul 17 '18 at 17:41

Laurenz Albe

209,280
17
206
263

How I understand this, I could connect to host where Postgres locates. And there also must by image that I want to load. And with this code I can upload image? – Igor Cova Jul 18 '18 at 08:00
The image file is located on the client machine where `psql` is running. The server where the image is imported can be anywhere. – Laurenz Albe Jul 18 '18 at 10:02
This is really what I expected - good. I use PgAdmin on Win10 - I can't find tool psql for win10 - can you share the link with for it? – Igor Cova Jul 18 '18 at 10:23
If you have PostgreSQL installed, `psql` is also installed. Start it from the command line. – Laurenz Albe Jul 18 '18 at 10:30
No, I didn't. Installed only on host. Okay - can I install only psql without PostgreSQL? – Igor Cova Jul 18 '18 at 10:31
I don't know. Depends on the installation method. – Laurenz Albe Jul 18 '18 at 10:34
I found solution for it there https://stackoverflow.com/questions/33854798/how-do-i-install-just-the-client-tools-for-postgresql-on-windows – Igor Cova Jul 18 '18 at 10:35

score 0 · Answer 2 · answered Aug 24 '19 at 13:17

Assuming following scheme for images:

CREATE TABLE images (
  owner_id uuid references users(user_id), 
  image_id uuid primary key,
  added_timestamp timestamp with time zone,
  img bytea
);

This one can do same thing more smoothly without any binaries to be installed (works directly from pgAdmin with Postgresql 11 in my case)

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 images values
    (uuid('66032153-0afc-4124-a50a-c4ea386f4684'), 
    uuid_generate_v4(),
    now(),
    content_);
    end;
$$ language plpgsql

Credits to author of this source (function for importing XML).

Also as pointed out in other answer lo_open(loid,131072); can be adjusted to fit some maximum size if needed.

PostgreSQL load images to DB

2 Answers2

Linked