date:20130223

Re: [SQL] Summing & Grouping in a Hierarchical Structure

2013-02-23 Thread Don Parris

Misa & Ben,

Thanks.  I guess I overlooked that in the docs.  I did see the part about
the 65KB.  I don't see my labels getting overly long.  I'll be fine with
Herbs_Spices.  I make up the labels myself, so it's really no big deal.

Alexander & Bryan - Many thanks for the help!



On Sat, Feb 23, 2013 at 3:13 PM, Ben Morrow  wrote:

> Quoth parri...@gmail.com (Don Parris):
> >
> > Is it possible to use spaces in the ltree path, like so:
> > TOP.Groceries.Food.Herbs & Spices
> >
> > Or do the elements of the path have to use underscores and dashes?
>
> From the docs:
>
> |  A label is a sequence of alphanumeric characters and underscores (for
> |  example, in C locale the characters A-Za-z0-9_ are allowed). Labels
> |  must be less than 256 bytes long. [...]
> |
> | A label path is a sequence of zero or more labels separated by dots,
> | for example L1.L2.L3, representing a path from the root of a
> | hierarchical tree to a particular node. The length of a label path
> | must be less than 65Kb, but keeping it under 2Kb is preferable.
>
> If you need to store non-alphanumeric labels, one answer (as long as
> they aren't too long) would be to use URL-encoding, like
>
> TOP.Groceries.Food.Herbs_20_26_20Spices
>
> Of course, you would need to encode _ as well, and you would need to be
> sure the labels weren't going to come out too long. Another alternative
> would be to MD5 each label and use (say) the first 10 bytes of that MD5
> in hex as the ltree label. (Annoyingly there's only one
> non-alphanumeric, so you can't use base64.) If you were going to do that
> you would need to consider the possibility of an attacker arranging a
> hash collision: I don't know where you're labels come from, so I don't
> know if this would be an issue.
>
> Ben
>
>


-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/

GPG Key ID: F5E179BE

Re: [SQL] Summing & Grouping in a Hierarchical Structure

2013-02-23 Thread Ben Morrow

Quoth parri...@gmail.com (Don Parris):
> 
> Is it possible to use spaces in the ltree path, like so:
> TOP.Groceries.Food.Herbs & Spices
> 
> Or do the elements of the path have to use underscores and dashes?

>From the docs:

|  A label is a sequence of alphanumeric characters and underscores (for
|  example, in C locale the characters A-Za-z0-9_ are allowed). Labels
|  must be less than 256 bytes long. [...]
| 
| A label path is a sequence of zero or more labels separated by dots,
| for example L1.L2.L3, representing a path from the root of a
| hierarchical tree to a particular node. The length of a label path
| must be less than 65Kb, but keeping it under 2Kb is preferable.

If you need to store non-alphanumeric labels, one answer (as long as
they aren't too long) would be to use URL-encoding, like

TOP.Groceries.Food.Herbs_20_26_20Spices

Of course, you would need to encode _ as well, and you would need to be
sure the labels weren't going to come out too long. Another alternative
would be to MD5 each label and use (say) the first 10 bytes of that MD5
in hex as the ltree label. (Annoyingly there's only one
non-alphanumeric, so you can't use base64.) If you were going to do that
you would need to consider the possibility of an attacker arranging a
hash collision: I don't know where you're labels come from, so I don't
know if this would be an issue.

Ben



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Summing & Grouping in a Hierarchical Structure

2013-02-23 Thread Misa Simic

Hi Don,

To be honest with you - i dont know - but think it is not...

We use it to solve the problem with hierarchy relations - but it is nowhere
"visible" to users in the app...

Our internal rule is to use ids in ltree structure to solve many others
problems, actually to easy get, actual category info... From any point...

So if needed, it is easy from 1.2.3.4, get: TOP.Groceries.Food.Herbs &
Spices if needed... Each of them are actually category names in the table...

Kind regards,

Misa


On Saturday, February 23, 2013, Don Parris wrote:

> Misa,
>
> Is it possible to use spaces in the ltree path, like so:
> TOP.Groceries.Food.Herbs & Spices
>
> Or do the elements of the path have to use underscores and dashes?
>
>
> On Sat, Feb 23, 2013 at 7:19 AM, Misa Simic  wrote:
>
> Hi Don,
>
> Yes, its better to use it in category table...
>
> Bryan, how many levels there will be - we dont know...
>
> With one table - and ltree is solved all cases...
>
> To add new subcategory user just picks the parent category... So it is
> easy to add chain ring to gear... As category...
>
> In another transaction table is category_id, amount...
>
>
> Don already posted query for sum... In these case just category and
> transaction table should be joined  sum amount, group by functions on
> lpath(depending what is the goal...)
>
> Kind Regards,
>
> Misa
>
>
>
> On Saturday, February 23, 2013, Bryan L Nuse wrote:
>
>
>
> This works fine:
> test_ltree=> SELECT path, trans_amt FROM testcat;
>   path   | trans_amt
> -+---
>  TOP.Transportation.Auto.Fuel| 50.00
>  TOP.Transportation.Auto.Maintenance | 30.00
>  TOP.Transportation.Auto.Fuel| 25.00
>  TOP.Transportation.Bicycle.Gear | 40.00
>  TOP.Transportation.Bicycle.Gear | 20.00
>  TOP.Transportation.Fares.Bus| 10.00
>  TOP.Transportation.Fares.Train  |  5.00
>  TOP.Groceries.Food.Beverages| 30.00
>  TOP.Groceries.Food.Fruit_Veggies| 40.00
>  TOP.Groceries.Food.Meat_Fish| 80.00
>  TOP.Groceries.Food.Grains_Cereals   | 30.00
>  TOP.Groceries.Beverages.Alcohol.Beer| 25.00
>  TOP.Groceries.Beverages.Alcohol.Spirits | 10.00
>  TOP.Groceries.Beverages.Alcohol.Wine| 50.00
>  TOP.Groceries.Beverages.Juice   | 45.00
>  TOP.Groceries.Beverages.Other   | 15.00
> (16 rows)
>
>
>  So if I want to see:
>  TOP.Groceries| 240.00
>  TOP.Transportation | 180.00
>
>
>
>  Hello Don,
>
>  Perhaps I am missing something about what your constraints are, or what
> you're trying to achieve, but is there any reason you could not use a
> series of joined tables indicating parent-child relationships?  The
> following example follows that in your previous posts.  Note that this
> approach (as given) will not work if branches stemming from the same node
> are different lengths.  That is, if you have costs associated with
> "Transportation.Bicycle.Gear", you could not also have a category
> "Transportation.Bicycle.Gear.Chain_ring".  (To add the latter category,
> you'd have to put costs from the former under something like
> "Transportation.Bicycle.Gear.General" -- or modify the approach.)  However,
> lengthening the "Alcohol" branches, e.g., by tacking on a level5 table
> would be easy.  Notice that level3 and level4 are not true look-up
> tables, since they may contain duplicate cat values.
>
>  If I'm off base, by all means specify just how.
>
>  Regards,
> Bryan
>
>  --
>
>  CREATE TABLE level1 (
>   cat   text  PRIMARY KEY
> );
>
>  CREATE TABLE level2 (
>cat   text   PRIMARY KEY,
>parent   text   REFERENCES level1(cat)
> );
>
>  --
> D.C. Parris, FMP, Linux+, ESL Certificate
> Minister, Security/FM Coordinator, Free Software Advocate
> http://dcparris.net/ 
> 
> GPG Key ID: F5E179BE
>

Re: [SQL] Update HSTORE record and then delete if it is now empty - What is the correct sql?

2013-02-23 Thread Ashwin Jayaprakash

Thanks Tom. I'll try it out soon.

Re: [SQL] Update HSTORE record and then delete if it is now empty - What is the correct sql?

2013-02-23 Thread Ashwin Jayaprakash

Thanks, that makes sense.


On Fri, Feb 22, 2013 at 9:53 PM, Ian Lawrence Barwick wrote:

> 2013/2/23 Ashwin Jayaprakash :
> (...)
> >
> > Q2: What the best way to check if an HSTORE is empty? Is this it
> > "array_length(akeys(data), 1) is null"?
>
> Just a quick answer to your second question: I suspect it might be more
> efficient to check your HSTORE column against an empty HSTORE, e.g.
>
>   WHERE val = ''::HSTORE
>
> HTH
>
> Ian Barwick
>

Re: [SQL] Summing & Grouping in a Hierarchical Structure

2013-02-23 Thread Don Parris

Misa,

Is it possible to use spaces in the ltree path, like so:
TOP.Groceries.Food.Herbs & Spices

Or do the elements of the path have to use underscores and dashes?


On Sat, Feb 23, 2013 at 7:19 AM, Misa Simic  wrote:

> Hi Don,
>
> Yes, its better to use it in category table...
>
> Bryan, how many levels there will be - we dont know...
>
> With one table - and ltree is solved all cases...
>
> To add new subcategory user just picks the parent category... So it is
> easy to add chain ring to gear... As category...
>
> In another transaction table is category_id, amount...
>
>
> Don already posted query for sum... In these case just category and
> transaction table should be joined  sum amount, group by functions on
> lpath(depending what is the goal...)
>
> Kind Regards,
>
> Misa
>
>
>
> On Saturday, February 23, 2013, Bryan L Nuse wrote:
>
>>
>>>
>>> This works fine:
>>> test_ltree=> SELECT path, trans_amt FROM testcat;
>>>   path   | trans_amt
>>> -+---
>>>  TOP.Transportation.Auto.Fuel| 50.00
>>>  TOP.Transportation.Auto.Maintenance | 30.00
>>>  TOP.Transportation.Auto.Fuel| 25.00
>>>  TOP.Transportation.Bicycle.Gear | 40.00
>>>  TOP.Transportation.Bicycle.Gear | 20.00
>>>  TOP.Transportation.Fares.Bus| 10.00
>>>  TOP.Transportation.Fares.Train  |  5.00
>>>  TOP.Groceries.Food.Beverages| 30.00
>>>  TOP.Groceries.Food.Fruit_Veggies| 40.00
>>>  TOP.Groceries.Food.Meat_Fish| 80.00
>>>  TOP.Groceries.Food.Grains_Cereals   | 30.00
>>>  TOP.Groceries.Beverages.Alcohol.Beer| 25.00
>>>  TOP.Groceries.Beverages.Alcohol.Spirits | 10.00
>>>  TOP.Groceries.Beverages.Alcohol.Wine| 50.00
>>>  TOP.Groceries.Beverages.Juice   | 45.00
>>>  TOP.Groceries.Beverages.Other   | 15.00
>>> (16 rows)
>>>
>>>
>>>  So if I want to see:
>>>  TOP.Groceries| 240.00
>>>  TOP.Transportation | 180.00
>>>
>>>
>>>
>>  Hello Don,
>>
>>  Perhaps I am missing something about what your constraints are, or what
>> you're trying to achieve, but is there any reason you could not use a
>> series of joined tables indicating parent-child relationships?  The
>> following example follows that in your previous posts.  Note that this
>> approach (as given) will not work if branches stemming from the same node
>> are different lengths.  That is, if you have costs associated with
>> "Transportation.Bicycle.Gear", you could not also have a category
>> "Transportation.Bicycle.Gear.Chain_ring".  (To add the latter category,
>> you'd have to put costs from the former under something like
>> "Transportation.Bicycle.Gear.General" -- or modify the approach.)  However,
>> lengthening the "Alcohol" branches, e.g., by tacking on a level5 table
>> would be easy.  Notice that level3 and level4 are not true look-up
>> tables, since they may contain duplicate cat values.
>>
>>  If I'm off base, by all means specify just how.
>>
>>  Regards,
>> Bryan
>>
>>  --
>>
>>  CREATE TABLE level1 (
>>   cat   text  PRIMARY KEY
>> );
>>
>>  CREATE TABLE level2 (
>>cat   text   PRIMARY KEY,
>>parent   text   REFERENCES level1(cat)
>> );
>>
>>  CREATE TABLE level3 (
>>cat   text,
>>parent   text   REFERENCES level2(cat),
>>cost   numeric(6,2)
>> );
>>
>>  CREATE TABLE level4 (
>>cat   text,
>>parent   text,
>>cost   numeric(6,2)
>> );
>>
>>
>>  INSERT INTO level1
>>   VALUES ('Transportation'),
>>  ('Groceries');
>>
>>  INSERT INTO level2
>>   VALUES ('Auto', 'Transportation'),
>>  ('Bicycle', 'Transportation'),
>>  ('Fares', 'Transportation'),
>>  ('Food', 'Groceries'),
>>  ('Beverages', 'Groceries');
>>
>>  INSERT INTO level3
>>   VALUES ('Fuel', 'Auto', 50.00),
>>  ('Maintenance', 'Auto', 30.00),
>>  ('Fuel', 'Auto', 25.00),
>>  ('Gear', 'Bicycle', 40.00),
>>  ('Gear', 'Bicycle', 20.00),
>>  ('Bus', 'Fares', 10.00),
>>  ('Train', 'Fares', 5.00),
>>  ('Beverages', 'Food', 30.00),
>>  ('Fruit_Veg', 'Food', 40.00),
>>  ('Meat_Fish', 'Food', 80.00),
>>  ('Grains_Cereals', 'Food', 30.00),
>>  ('Alcohol', 'Beverages', NULL),
>>  ('Juice', 'Beverages', 45.00),
>>  ('Other', 'Beverages', 15.00);
>>
>>  INSERT INTO level4
>>   VALUES ('Beer', 'Alcohol', 25.00),
>>  ('Spirits', 'Alcohol', 10.00),
>>  ('Wine', 'Alcohol', 50.00);
>>
>>
>>  CREATE VIEW all_cats AS (
>> SELECT a.cat AS level4,
>>b.cat AS level3,
>>c.cat AS level2,
>>d.cat AS level1,
>>CASE WHEN a.cost IS NULL THEN 0
>> WHEN a.cost IS NOT NULL THEN a.cost
>>  END
>>+ CASE WHEN b.cost IS NULL THEN 0
>>   WHEN b.cost IS NOT NULL THEN b.co

Re: [SQL] Summing & Grouping in a Hierarchical Structure

2013-02-23 Thread Don Parris

Hi Bryan,

The detailed category listing is dead simple to create: SELECT cat_id,
cat_name, sum(amount) FROM category, line-item GROUP BY cat_name, cat_id;
But... as you say, the result is a LOT of numbers over the course of
160-ish categories of all levels.  Which is why it is so important to be
able to summarize.  I want to summarize by the highest-levels of the
category tree, precisely so as not to get a headache looking at the
details. But it is still important to be able to drill down when I want
more details.

On Sat, Feb 23, 2013 at 11:15 AM, Bryan L Nuse  wrote:

> >  That said, now that I have finally gotten the chance to try ltree, I
> think I like it a lot.
>
> Hello Don,
>
> Yes, after looking at ltree --which I had not done before-- I have to
> agree with Misa that it looks like the right solution for your problem.
>  That is not to say that "brute force" SQL couldn't provide a workable
> arrangement; but ltree looks very flexible, especially as it allows you to
> assign cost values to non-terminal nodes.  If it were me, though, I'd still
> make use of VIEWs to report results of the workhorse queries:  staring at a
> list of items like "Transportation.Bicycle.Gear.Chain_ring" sounds like
> headache.  That's a matter of taste, of course.
>
> Bryan
>

-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/

GPG Key ID: F5E179BE

Re: [SQL] Summing & Grouping in a Hierarchical Structure

2013-02-23 Thread Bryan L Nuse

>  That said, now that I have finally gotten the chance to try ltree, I think I 
> like it a lot. 

Hello Don,

Yes, after looking at ltree --which I had not done before-- I have to agree 
with Misa that it looks like the right solution for your problem.  That is not 
to say that "brute force" SQL couldn't provide a workable arrangement; but 
ltree looks very flexible, especially as it allows you to assign cost values to 
non-terminal nodes.  If it were me, though, I'd still make use of VIEWs to 
report results of the workhorse queries:  staring at a list of items like 
"Transportation.Bicycle.Gear.Chain_ring" sounds like headache.  That's a matter 
of taste, of course.

Bryan


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Summing & Grouping in a Hierarchical Structure

2013-02-23 Thread Don Parris

Hi Bryan,

My current (maybe about to be deprecated now) category table is a recursive
table that I join on the line-item table.  Your suggestion of using a table
for each level of category is probably a bit easier to manage in some
respects.  However, as you pointed out, it becomes unwieldy when various
branches of the category tree have different lengths.  One effectively has
to have the same number of levels in all branches.  If only reality were so
refined!  :-)  That is why I chose the recursive table structure that I
gather is fairly common where the depth of a tree is not necessarily "even"
or known.

However, I like the view you created - I have not really used views very
much so far, so it is good to see this use case for a view.  That said, now
that I have finally gotten the chance to try ltree, I think I like it a
lot.  I wonder how portable it is, but it seems to do the trick extremely
well.  Mind you, I am not necessarily looking to change DBMSes anytime
soon, but it is still good to know how to handle recursive/hierarchical
structures... ummm... the old-fashioned way(?).  ;-)

I think using WITH RECURSIVE would probably do the trick for my recursive
category table, but - so far - the WITH construct makes my eyes glaze
over.  I feel like I will have returned the *one ring* if I get that.

On Fri, Feb 22, 2013 at 11:54 PM, Bryan L Nuse  wrote:

>   
>
>  Hello Don,
>
>  Perhaps I am missing something about what your constraints are, or what
> you're trying to achieve, but is there any reason you could not use a
> series of joined tables indicating parent-child relationships?  The
> following example follows that in your previous posts.  Note that this
> approach (as given) will not work if branches stemming from the same node
> are different lengths.  That is, if you have costs associated with
> "Transportation.Bicycle.Gear", you could not also have a category
> "Transportation.Bicycle.Gear.Chain_ring".  (To add the latter category,
> you'd have to put costs from the former under something like
> "Transportation.Bicycle.Gear.General" -- or modify the approach.)  However,
> lengthening the "Alcohol" branches, e.g., by tacking on a level5 table
> would be easy.  Notice that level3 and level4 are not true look-up
> tables, since they may contain duplicate cat values.
>
>  If I'm off base, by all means specify just how.
>
>  Regards,
> Bryan
>
>  --
>
>  CREATE TABLE level1 (
>   cat   text  PRIMARY KEY
> );
>
>  CREATE TABLE level2 (
>cat   text   PRIMARY KEY,
>parent   text   REFERENCES level1(cat)
> );
>
>  CREATE TABLE level3 (
>cat   text,
>parent   text   REFERENCES level2(cat),
>cost   numeric(6,2)
> );
>
>  CREATE TABLE level4 (
>cat   text,
>parent   text,
>cost   numeric(6,2)
> );
>
>
>  INSERT INTO level1
>   VALUES ('Transportation'),
>  ('Groceries');
>
>  INSERT INTO level2
>   VALUES ('Auto', 'Transportation'),
>  ('Bicycle', 'Transportation'),
>  ('Fares', 'Transportation'),
>  ('Food', 'Groceries'),
>  ('Beverages', 'Groceries');
>
>  INSERT INTO level3
>   VALUES ('Fuel', 'Auto', 50.00),
>  ('Maintenance', 'Auto', 30.00),
>  ('Fuel', 'Auto', 25.00),
>  ('Gear', 'Bicycle', 40.00),
>  ('Gear', 'Bicycle', 20.00),
>  ('Bus', 'Fares', 10.00),
>  ('Train', 'Fares', 5.00),
>  ('Beverages', 'Food', 30.00),
>  ('Fruit_Veg', 'Food', 40.00),
>  ('Meat_Fish', 'Food', 80.00),
>  ('Grains_Cereals', 'Food', 30.00),
>  ('Alcohol', 'Beverages', NULL),
>  ('Juice', 'Beverages', 45.00),
>  ('Other', 'Beverages', 15.00);
>
>  INSERT INTO level4
>   VALUES ('Beer', 'Alcohol', 25.00),
>  ('Spirits', 'Alcohol', 10.00),
>  ('Wine', 'Alcohol', 50.00);
>
>
>  CREATE VIEW all_cats AS (
> SELECT a.cat AS level4,
>b.cat AS level3,
>c.cat AS level2,
>d.cat AS level1,
>CASE WHEN a.cost IS NULL THEN 0
> WHEN a.cost IS NOT NULL THEN a.cost
>  END
>+ CASE WHEN b.cost IS NULL THEN 0
>   WHEN b.cost IS NOT NULL THEN b.cost
>  END AS cost
>   FROM level4 a
> FULL JOIN
> level3 b
> ON (a.parent = b.cat)
>   FULL JOIN
>   level2 c
>   ON (b.parent = c.cat)
> FULL JOIN
> level1 d
> ON (c.parent = d.cat)
>   ORDER BY level1, level2, level3, level4
> );
>
>
>
>  SELECT * FROM all_cats;
>
>   level4  | level3 |  level2   | level1 | cost
> -++---++---
>  Beer| Alcohol| Beverages | Groceries  | 25.00
>  Spirits | Alcohol| Beverages | Groceries  | 10.00
>  Wine| Alcohol| Beverages | Groceries  | 50.00
>  | Juice  | Beverages | Groceries  | 45.00
>  | Other  | Beverages | Groceries

Re: [SQL] Summing & Grouping in a Hierarchical Structure

2013-02-23 Thread Misa Simic

Hi Don,

Yes, its better to use it in category table...

Bryan, how many levels there will be - we dont know...

With one table - and ltree is solved all cases...

To add new subcategory user just picks the parent category... So it is easy
to add chain ring to gear... As category...

In another transaction table is category_id, amount...


Don already posted query for sum... In these case just category and
transaction table should be joined  sum amount, group by functions on
lpath(depending what is the goal...)

Kind Regards,

Misa



On Saturday, February 23, 2013, Bryan L Nuse wrote:

>
>>
>> This works fine:
>> test_ltree=> SELECT path, trans_amt FROM testcat;
>>   path   | trans_amt
>> -+---
>>  TOP.Transportation.Auto.Fuel| 50.00
>>  TOP.Transportation.Auto.Maintenance | 30.00
>>  TOP.Transportation.Auto.Fuel| 25.00
>>  TOP.Transportation.Bicycle.Gear | 40.00
>>  TOP.Transportation.Bicycle.Gear | 20.00
>>  TOP.Transportation.Fares.Bus| 10.00
>>  TOP.Transportation.Fares.Train  |  5.00
>>  TOP.Groceries.Food.Beverages| 30.00
>>  TOP.Groceries.Food.Fruit_Veggies| 40.00
>>  TOP.Groceries.Food.Meat_Fish| 80.00
>>  TOP.Groceries.Food.Grains_Cereals   | 30.00
>>  TOP.Groceries.Beverages.Alcohol.Beer| 25.00
>>  TOP.Groceries.Beverages.Alcohol.Spirits | 10.00
>>  TOP.Groceries.Beverages.Alcohol.Wine| 50.00
>>  TOP.Groceries.Beverages.Juice   | 45.00
>>  TOP.Groceries.Beverages.Other   | 15.00
>> (16 rows)
>>
>>
>>  So if I want to see:
>>  TOP.Groceries| 240.00
>>  TOP.Transportation | 180.00
>>
>>
>>
>  Hello Don,
>
>  Perhaps I am missing something about what your constraints are, or what
> you're trying to achieve, but is there any reason you could not use a
> series of joined tables indicating parent-child relationships?  The
> following example follows that in your previous posts.  Note that this
> approach (as given) will not work if branches stemming from the same node
> are different lengths.  That is, if you have costs associated with
> "Transportation.Bicycle.Gear", you could not also have a category
> "Transportation.Bicycle.Gear.Chain_ring".  (To add the latter category,
> you'd have to put costs from the former under something like
> "Transportation.Bicycle.Gear.General" -- or modify the approach.)  However,
> lengthening the "Alcohol" branches, e.g., by tacking on a level5 table
> would be easy.  Notice that level3 and level4 are not true look-up
> tables, since they may contain duplicate cat values.
>
>  If I'm off base, by all means specify just how.
>
>  Regards,
> Bryan
>
>  --
>
>  CREATE TABLE level1 (
>   cat   text  PRIMARY KEY
> );
>
>  CREATE TABLE level2 (
>cat   text   PRIMARY KEY,
>parent   text   REFERENCES level1(cat)
> );
>
>  CREATE TABLE level3 (
>cat   text,
>parent   text   REFERENCES level2(cat),
>cost   numeric(6,2)
> );
>
>  CREATE TABLE level4 (
>cat   text,
>parent   text,
>cost   numeric(6,2)
> );
>
>
>  INSERT INTO level1
>   VALUES ('Transportation'),
>  ('Groceries');
>
>  INSERT INTO level2
>   VALUES ('Auto', 'Transportation'),
>  ('Bicycle', 'Transportation'),
>  ('Fares', 'Transportation'),
>  ('Food', 'Groceries'),
>  ('Beverages', 'Groceries');
>
>  INSERT INTO level3
>   VALUES ('Fuel', 'Auto', 50.00),
>  ('Maintenance', 'Auto', 30.00),
>  ('Fuel', 'Auto', 25.00),
>  ('Gear', 'Bicycle', 40.00),
>  ('Gear', 'Bicycle', 20.00),
>  ('Bus', 'Fares', 10.00),
>  ('Train', 'Fares', 5.00),
>  ('Beverages', 'Food', 30.00),
>  ('Fruit_Veg', 'Food', 40.00),
>  ('Meat_Fish', 'Food', 80.00),
>  ('Grains_Cereals', 'Food', 30.00),
>  ('Alcohol', 'Beverages', NULL),
>  ('Juice', 'Beverages', 45.00),
>  ('Other', 'Beverages', 15.00);
>
>  INSERT INTO level4
>   VALUES ('Beer', 'Alcohol', 25.00),
>  ('Spirits', 'Alcohol', 10.00),
>  ('Wine', 'Alcohol', 50.00);
>
>
>  CREATE VIEW all_cats AS (
> SELECT a.cat AS level4,
>b.cat AS level3,
>c.cat AS level2,
>d.cat AS level1,
>CASE WHEN a.cost IS NULL THEN 0
> WHEN a.cost IS NOT NULL THEN a.cost
>  END
>+ CASE WHEN b.cost IS NULL THEN 0
>   WHEN b.cost IS NOT NULL THEN b.cost
>  END AS cost
>   FROM level4 a
> FULL JOIN
> level3 b
> ON (a.parent = b.cat)
>   FULL JOIN
>   level2 c
>   ON (b.parent = c.cat)
> FULL JOIN
> level1 d
> ON (c.parent = d.cat)
>   ORDER BY level1, level2, level3, level4
> );
>
>
>
>  SELECT * FROM all_cats;
>
>   level4  | level3 |  level2   | level

Re: [SQL] Update HSTORE record and then delete if it is now empty - What is the correct sql?

2013-02-23 Thread Tom Lane

Ashwin Jayaprakash  writes:
> Hi, here's what I'm trying to do:
>- I have a table that has an HSTORE column
>- I would like to delete some key-vals from it
>- If after deleting key-vals, the HSTORE column is empty, I'd like to
> delete the entire row

> with update_qry as(
>   update up_del as r
>   set data = delete(data, 'c=>678')
>   where name = 'cc'
>   returning r.*
> )
> delete from up_del
> where name in (select name from update_qry)
> and array_length(akeys(data), 1) is null;

> *Q1: *That DELETE statement does not work

Nope, it won't, because a single query can only update any particular
table row once, and the DELETE plus its WITH clauses is still only a
single query.

If you want "no empty hstore values" to be an invariant of your data
structure, then expecting every update query to implement that correctly
seems like a pretty bad idea anyway.  Consider using a trigger to do
that, ie something like BEFORE UPDATE FOR EACH ROW DO "if new hstore
value is null then delete the row and return null".

A problem with that approach is that the returned count of updated rows
won't be very meaningful, and RETURNING values likewise.  If that's a
problem for you, you could use an AFTER trigger instead, which will be a
little slower but it hides the deletes behind the scenes.  (Note: a
DELETE issued in a trigger is a separate query, which is why it doesn't
fall foul of the limitation your WITH query did.)

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Summing & Grouping in a Hierarchical Structure

Re: [SQL] Summing & Grouping in a Hierarchical Structure

Re: [SQL] Summing & Grouping in a Hierarchical Structure

Re: [SQL] Update HSTORE record and then delete if it is now empty - What is the correct sql?

Re: [SQL] Update HSTORE record and then delete if it is now empty - What is the correct sql?

Re: [SQL] Summing & Grouping in a Hierarchical Structure

Re: [SQL] Summing & Grouping in a Hierarchical Structure

Re: [SQL] Summing & Grouping in a Hierarchical Structure

Re: [SQL] Summing & Grouping in a Hierarchical Structure

Re: [SQL] Summing & Grouping in a Hierarchical Structure

Re: [SQL] Update HSTORE record and then delete if it is now empty - What is the correct sql?

11 matches

Site Navigation

Mail list logo

Footer information