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
select convert_from(lo_get(cast(sourceip as bigint)),'utf8') from testtablegrid_c10109;
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.