Database Reference
In-Depth Information
Now, insert a row with the full path to the image in the following manner:
postgres=# INSERT INTO test_large_objects VALUES
(1, 'pg_disney_trip', lo_import('/tmp/pg_disney.jpg'));
ERROR: could not open server file "/tmp/pg_disney.jpg":
Permission denied
When using dummy data or images to test the large objects, make
sure that they are of some size or contain data.
It was intentionally done to remind us to give appropriate permissions to the ile we
have to store. Giving the appropriate permission to the
postgres
user will let you
add the ile to the database. This can be done in the following manner:
postgres=# INSERT INTO test_large_objects VALUES
(1, 'pg_disney_trip', lo_import('/tmp/pg_disney.jpg'));
INSERT 0 1
What happens here is that the
lo_import()
function has loaded the image inside the
pg_largeobject
table and returns an OID value as a reference to the large object.
If you query your table, you will see the OID value and not the actual image.
Let's query the user table, system table, and metadata table to see the new status.
Let's query the
test_large_objects
user table using the following statement:
postgres=# SELECT * FROM test_large_objects;
picture_id | name | picture_loc
------------+----------------+-------------
1 | pg_disney_trip | 74441
(1 row)
Now, query the
pg_largeobjects
system table using the following statement:
postgres=# SELECT loid FROM pg_largeobject;
loid
-------
74441
(1 row)
We'll now query the
pg_largeobjects_metadata
table using the following statement:
postgres=# SELECT oid FROM pg_largeobject_metadata;
oid
-------
74441
(1 row)
