PostgreSQL: insert string in a large object from an SQL script without relying on an external file

Question

I know it's possible to insert into a large object from a PostgreSQL script using a lo_import():

INSERT INTO image (name, raster)
VALUES ('beautiful image', lo_import('/etc/motd'));

Te problem is, I'm trying to execute a script on a tightly locked up server, so I can't upload a file to it. Is it possible to insert a constant string into a large object without relying on an external file?

As long as the text in the file will fit inside of the field I don't think it will be an issue. — Elias
– Elias, Commented Mar 7, 2016 at 14:20
It's a large object, so the size won't be a problem; the question is: how do I store a string into a large object from inside a SQL script without pulling the contents from an external file? All examples I've found so far rely on an external file. — Haroldo_OK
– Haroldo_OK, Commented Mar 7, 2016 at 14:24
You can store up to 1 GB of text in a simple text or varchar field. Why do you need a actual large object (or bytea)? — Ihor Romanchenko
– Ihor Romanchenko, Commented Mar 7, 2016 at 15:43

jjanes · Accepted Answer · 2016-03-07 18:22:21Z

7

If the value is less than a 1GB, you can do this:

INSERT INTO image (name, raster)
    VALUES ('beautiful image', lo_from_bytea(0,$1));

The bytea type is a bit annoying. You may have to resort to driver-specific shenanigans to make the driver/client library understand that $1 is of type bytea. For example in with Perl's DBD::Pg, you have to prepare the statement and then do something like:

$insert->bind_param(1, $blob, { pg_type => PG_BYTEA });

answered Mar 7, 2016 at 18:22

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Haroldo_OK Over a year ago

Mmm... interesting... so if I wanted to store some large text into a large object, maybe I could do something like: INSERT INTO something (name, raster) VALUES ('some name', lo_from_bytea(0,'some really long text'::bytea))

jjanes Over a year ago

Yes. I think it will even do the cast to bytea for you if you omit it.

GreenTurtle Over a year ago

@jjanes Yes, it does. Just tested it on PostgreSQL 9.6.5 and it works without the cast.

Collectives™ on Stack Overflow

PostgreSQL: insert string in a large object from an SQL script without relying on an external file

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related