CODEWITHSUNDEEP
postgresql
vipin p
vipin p

Reputation: 111

Inserting values into multiple columns by splitting a string in PostgreSQL

I have the following heap of text:

"BundleSize,155648,DynamicSize,204800,Identifier,com.URLConnectionSample,Name,
URLConnectionSample,ShortVersion,1.0,Version,1.0,BundleSize,155648,DynamicSize,
16384,Identifier,com.IdentifierForVendor3,Name,IdentifierForVendor3,ShortVersion,
1.0,Version,1.0,".

What I'd like to do is extract data from this in the following manner:

BundleSize:155648
DynamicSize:204800
Identifier:com.URLConnectionSample
Name:URLConnectionSample
ShortVersion:1.0
Version:1.0

BundleSize:155648
DynamicSize:16384
Identifier:com.IdentifierForVendor3 
Name:IdentifierForVendor3
ShortVersion:1.0
Version:1.0

All tips and suggestions are welcome.

Upvotes: 0

Views: 830

Answers (1)

rkhayrov
rkhayrov

Reputation: 10260

It isn't quite clear what do you need to do with this data. If you really need to process it entirely in the database (looks like the task for your favorite scripting language instead), one option is to use hstore.

Converting records one by one is easy:

Assuming

%s = BundleSize,155648,DynamicSize,204800,Identifier,com.URLConnectionSample,Name,URLConnectionSample,ShortVersion,1.0,Version,1.0

SELECT * FROM each(hstore(string_to_array(%s, ',')));

Output:

     key      |          value          
--------------+-------------------------
 Name         | URLConnectionSample
 Version      | 1.0
 BundleSize   | 155648
 Identifier   | com.URLConnectionSample
 DynamicSize  | 204800
 ShortVersion | 1.0

If you have table with columns exactly matching field names (note the quotes, populate_record is case-sensitive to key names):

CREATE TABLE data (
    "BundleSize" integer, "DynamicSize" integer, "Identifier" text,
    "Name" text, "ShortVersion" text, "Version" text);

You can insert hstore records into it like this:

INSERT INTO data SELECT * FROM
    populate_record(NULL::data, hstore(string_to_array(%s, ',')));

Things get more complicated if you have comma-separated values for more than one record.

%s = BundleSize,155648,DynamicSize,204800,Identifier,com.URLConnectionSample,Name,URLConnectionSample,ShortVersion,1.0,Version,1.0,BundleSize,155648,DynamicSize,16384,Identifier,com.IdentifierForVendor3,Name,IdentifierForVendor3,ShortVersion,1.0,Version,1.0,

You need to break up an array into chunks of number_of_fields * 2 = 12 elements first.

SELECT hstore(row) FROM (
    SELECT array_agg(str) AS row FROM (
        SELECT str, row_number() OVER () AS i FROM
        unnest(string_to_array(%s, ',')) AS str
    ) AS str_sub
    GROUP BY (i - 1) / 12) AS row_sub
WHERE array_length(row, 1) = 12;

Output:

 "Name"=>"URLConnectionSample", "Version"=>"1.0", "BundleSize"=>"155648", "Identifier"=>"com.URLConnectionSample", "DynamicSize"=>"204800", "ShortVersion"=>"1.0"
 "Name"=>"IdentifierForVendor3", "Version"=>"1.0", "BundleSize"=>"155648", "Identifier"=>"com.IdentifierForVendor3", "DynamicSize"=>"16384", "ShortVersion"=>"1.0"

And inserting this into the aforementioned table:

INSERT INTO data SELECT (populate_record(NULL::data, hstore(row))).* FROM ...

the rest of the query is the same.

Upvotes: 1

Related Questions