DUnkn0wn1 DUnkn0wn1 - 4 months ago 15
SQL Question

Parsing hstore to remove sensitive information - PgSQL

How do I remove sensitive user information from hstore column attributes, table dim_user. And load the rest into aggregate tables. I am selecting from PgSQL database and using Pentaho Data Integration to load data into aggregate tables.

attributes
""DURATION"=>"1", "END_DATE"=>"2017-07-18", "START_DATE"=>"2016-07-18", "AUTO_RENEWAL"=>"true", "MEMBER_SINCE"=>"Mon Jul 18 14:31:01 EDT 2016", "DURATION_TYPE"=>"YEARS", "SHIPPING_ADDRESS"=>"1 Glos Cres, Toronto, ON Canada A9l 111", "SUBSCRIPTION_TYPE"=>"MY_ADVANTAGE""


Need remove value of "SHIPPING_ADDRESS" from above.

RESULT

""DURATION"=>"1", "END_DATE"=>"2017-07-18", "START_DATE"=>"2016-07-18", "AUTO_RENEWAL"=>"true", "MEMBER_SINCE"=>"Mon Jul 18 14:31:01 EDT 2016", "DURATION_TYPE"=>"YEARS", "SUBSCRIPTION_TYPE"=>"MY_ADVANTAGE"


Thanks.

Answer

The hstore functions and operators are described in the hstore documentation. It's relatively straightforward.

Instead of:

SELECT cola, colb, colc FROM dim_user;

You can do:

SELECT cola, colb, colc - 'SHIPPING_ADDRESS'::text FROM dim_user;
Comments