How to import/read large objects in postgresql

Owned by svitlana (Deactivated)
Last updated: Jul 30, 2018
3 min read

Problem

You need to import data into a textarea column, and the database is a postgresql (version 9.4 or higher)



You are working with the table grid editor to collect data around how IP routing should be modified.  The table grid is a very simple one detailing out

  • type of operation

  • sourceip

  • destip





Solution

The grid configuration is as follows

############################################ # General grid properties section #---------------------------------------- gd.ds = jira gd.tablename = Testtablegrid gd.allowAddRows = true gd.delete.allowed = true gd.cleanupTable = true gd.tracking = true gd.initFromClone=true gd.columns = ChangeType, SourceIP, DestIP ############################################ # Column section for the ChangeType column #---------------------------------------- col.ChangeType = Type of Change col.ChangeType.type = list col.ChangeType.list.size = 3 col.ChangeType.name1 = Add col.ChangeType.name2 = Delete col.ChangeType.name3 = Modification col.ChangeType.required=true col.ChangeType.tooltip = Indicate if this is a new addition or deletion of an old request or modification to an existing rule ############################################ # Column section for the SourceIP column #---------------------------------------- col.SourceIP = Source Host FQDN/IP(s) col.SourceIP.type=textarea col.SourceIP.rows=100 col.SourceIP.cols=100 col.SourceIP.tooltip = Include the correct source IP address and hostname ############################################ # Column section for the DestIP column #---------------------------------------- col.DestIP = Destination HOST FQDN/IP(s) col.DestIP.type=textarea col.DestIP.rows=100 col.DestIP.cols=100 col.DestIP.tooltip = Include the correct Destination IP address and hostname



When accessing the data in psql (or any other sql client), you find out that the data is not what you expect

Background information

Textarea columns are created as large objects allowing to store huge amount of data (the exact size depends a bit on the database - in case of postgresql, you can store up to 1 GB).
In postgresql, large objects are stored in a separate table, pg_largeobject, and the number is the id of that large object.  More information on this subject can be found in the postgresql documentation: 
https://www.postgresql.org/docs/current/static/largeobjects.html



Storing data

Assume that you plan to migrate the table grid tables from mysql to postgresql, you need to make sure that the content of the textarea attributes are stored in the pg_largeobject table (and not in the grid table itself).  If you would store the text information directly in the table you bump into mystical error messages such as



The proper way of storing this data is by using the lo_  functions as documented on 
https://www.postgresql.org/docs/current/static/lo-funcs.html



To store data into the example table grid, you would do compose following insert query

insert into testtablegrid_c10109 ( id, issueid,modified, sourceip, destip) values (6, 10000, 1, lo_from_bytea(0,'1.1.1.1'), lo_from_bytea(0,'2.2.2.2'));

 

The lo_from_bytea function will do all the hard work

  • Create a large object

  • write the content of the large object in the pg_largeobject table

  • Return the id of the large object

Reading data

Of course, when you write data, you would also like to read data (else it is not very useful, is it).  Here you need to use the lo_get function to retrieve the content of the large object

select lo_get(cast(sourceip as bigint)) from testtablegrid_c10109;



This results in a hexadecimal notation 

So, you need to do some extra conversion using the convert_from function





Qué?

In case all this stuff looks a bit complicated, you are right, it is.  
Just let us know and we'll try to help. Check our support options here.