select * from depesz;

in postgresql 8.2, in contrib, is great new datatype called hstore.

if you’re not familiar with it - check the docs.

in short - this is indexable associative-array (hash) in one field.

i was given a task to convert some text field to hstore field.

text is very simple, contains set of “words”, separated by spaces, where each “word” contains:

• textual identifier, containing only lower case letters
• “:” - separator
• numerical (0 or positive integer) value

example value:

abc:123 def:1 bg:3 foo:99

luckily, the format is really simple.

now, i have to convert it to hstore column. adding a column is a no-brainer, but converting is apparently not so simple.

first, i tried making “replace” calls to change format of data to format acceptable for hstore:

# select replace(replace('abc:123 def:1 bg:3 foo:99', ' ', ', '), ':', '=>');
replace
----------------------------------
abc=>123, def=>1, bg=>3, foo=>99
(1 row)

but, while this works:

# select 'abc=>123, def=>1, bg=>3, foo=>99'::hstore;
hstore
--------------------------------------------------
"bg"=>"3", "abc"=>"123", "def"=>"1", "foo"=>"99"
(1 row)

this doesn’t:

# select replace(replace('abc:123 def:1 bg:3 foo:99', ' ', ', '), ':', '=>')::hstore;
ERROR: cannot cast type text to hstore
LINE 1: ...'abc:123 def:1 bg:3 foo:99', ' ', ', '), ':', '=>')::hstore;

oops. got to find better way. luckily, it’s not very hard:

text2hstore converting functions seems to be quite simple:

CREATE OR REPLACE FUNCTION text_to_hstore(input TEXT) RETURNS hstore as $BODY$
DECLARE
input_parts TEXT[];
output hstore := ”;
item TEXT[];
i int;
BEGIN
IF input IS NULL OR input = ” THEN
RETURN output;
END IF;
IF NOT input ~ ‘^[a-z]+:[0-9]( [a-z]+:[0-9]+)*$’ THEN
raise exception ‘input doesn”t match validating regexp [%]‘, input;
END IF;
input_parts := string_to_array(input, ‘ ‘);
for i in array_lower( input_parts, 1 ) .. array_upper( input_parts, 1 ) loop
item := string_to_array( input_parts[i], ‘:’ );
output := output || tconvert( item[1], item[2] );
END loop;
RETURN output;
END;
$BODY$ language plpgsql;

what it does is:

1. if given string (input) is null, or is empty string - return empty hstore
2. if given string doesn’t match validation regexp - raise exception (i want to be sure the conversion is ok)
3. split input string to array of strings, where each item in array is “word” (code:value)
4. iterate over array of strings, for each item do:
5. split item into code and value (stored as item array)
6. add new key to output hstore
7. after finishing all “words”, return generated hstore.

so, how fast does it work?

i did test on some random test table, with following dataset:

# select count(*), count(text_field), min(length(text_field)), max(length(text_field)), sum(length(text_field)) from test ;
count | count | min | max | sum
---------+---------+-----+-----+----------
1502922 | 1349308 | 0 | 238 | 11262277
(1 row)

creating new table with hstore value took:

# create table t2 as select text_to_hstore(text_field) from test ;
SELECT
Time: 47898.277 ms

not bad. of course the function is very task-specific, but perhaps somebody will use it for similar case.