How to import/read large objects in postgresql
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.