Search code examples
sqlpostgresqlpentahokettlehstore

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.

Solution

  • 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;