First off, I'm not a DBA at all, I'm more of a front-end guy, but I'm currently hitting a brick wall and can't figure out how to tackle it. A database I am working on has a table with several columns. The table represents completed tasks, the columns are:

TaskID             -  -  -  1. On-site
DateStart         |         2. Return-to-base
DateEnd           |         3. Chargeable
Type -  -  -  -  -          4. Complaint
Reference                   5. Rebook

Reference is used when the customer provides their own ID for the task, typically this is for example "THR#000123123" or "IX#01212". It is always in the format "ABC#123".

Now, I need to categorise these into multiple arbitrary categories and subcategories based on only the type and reference fields:

  1. Chargeable (type = 3)

    1. By company IX (reference like "IX#%")

    2. All other Chargeable

  2. Non-chargeable (type <> 3)

    1. By company THR (reference like "THR#%")

      1. Return-to-base (type = 2)

      2. On-site (type = 1)

    2. Return to base (type = 2)

    3. All other non-Chargeable

A task can only belong to one category, so if a task for company THR was "return-to-base", it must only be under category 2.1.1, not 2.2. I realise these categories seem very arbitrary but this structure represents the most useful break-down of almost any selection of tasks from the table.

My question is, is it possible to create a view that contains the TaskID and its category? Or should I just implement this logic in the front-end after selecting every TaskID, Type and Reference?

  • Are IX and THR the only companies which are special in categorising? Commented Sep 27, 2012 at 6:52
  • @dezso: Yes, IX and THR are the only "important" companies. There are others as well but are mostly irrelevant when looking at the bigger picture.
    – dreamlax
    Commented Sep 27, 2012 at 9:15
  • If Rebook (type = 5) is a subcategory of Chargeable (type = 3), does that mean that a TaskID can be assigned more than one type (3 and 5 in this case)?
    – Andriy M
    Commented Sep 27, 2012 at 11:22
  • @AndriyM: Sorry that's a typo, good spotting. Each task only has one type, in this case, "Rebook" implies "non-chargeable". I'll edit the question. Not quite sure how that got there...
    – dreamlax
    Commented Sep 27, 2012 at 11:45

2 Answers 2


Ok, so my answer here is conditioned on the following assumptions:

  1. This categorization needs to be relatively stable and
  2. It may be re-used in various ways, and
  3. It needs to perform well.

The obvious, simple answer is to use table methods in PostgreSQL. What you do is create a SQL language function which does not hit the table and returns the value you want. Make it immutable so you can index the output if you want to query against it, etc. Note the specific rules aren't really clear from your post but this should get you started.

CREATE OR REPLACE FUNCTION subcategory(task) -- task is table name
    SELECT CASE WHEN $1.type = 3 THEN
                CASE WHEN $I.reference LIKE 'IX#%' 
                     THEN 'Company IX Chargeable'
                     ELSE 'Other Chargeable'
                WHEN $1.reference LIKE 'IX#%' THEN 
                CASE WHEN $1.type = 5 THEN 'Company IX Rebool'
                     ELSE 'Company IX Other'
                WHEN $1.reference LIKE 'THR#%' AND $1.type IN (1, 2) THEN
                CASE WHEN $1.type = 2 THEN 'THR Return to Base'
                     WHEN $1.type = 1 THEN 'THR On Site'
                WHEN $1.type = 2 THEN 'Return to Base'
                ELSE 'Other Nonchargeable'

You can then query this using:

SELECT t.subcategory FROM task t;

Note that the table name is non-optional here. The parser converts this to:

SELECT subcategory(t) FROM task t;

You can use this however in any part of the select statement including the where clause. If it proves slow, you can add cost estimates (but I think this should be fast), and you can even index the output using PostgreSQL's functional indexes.

On the performance side, LIKE and substring() don't seem to be significantly different:

postgres=# select count(*) from generate_series(1, 10000000);
(1 row)

Time: 9007.618 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
(1 row)

Time: 13653.000 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
(1 row)

Time: 16681.860 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE s::text like '1%';
(1 row)

Time: 17163.470 ms
postgres=# select count(*) from generate_series(1, 10000000) s WHERE substring(s::text from 1 for 1) = '1';
(1 row)

Time: 17052.004 ms

So sometimes one is faster than the other, but they don't seem out of line with eachother.

  • Wouldn't using substr instead of LIKE be faster? Commented Sep 27, 2012 at 10:50
  • On an other note, I think you mean the exact opposite: ' which does not hit the table' Commented Sep 27, 2012 at 10:51
  • No, I mean that. The function I posted operates on the type, not on the table. Therefore executing the function doesn't require actual disk I/O in itself. This is important for performance and flexibility. For example you can safely index the output because the input is tied to the data in the tuple not in the table per se. Will test the performance on the other side. Commented Sep 27, 2012 at 11:13
  • This is looking promising... I've been trying to maintain some sort of "hierarchical" system just as the categorisation looks, but using a "flat" approach would be much simpler. I'll fiddle around with this and let you know how I get on.
    – dreamlax
    Commented Sep 27, 2012 at 11:51
  • You can nest the case statements however you like to provide something a little more hierarchical. Also for more on table methods see ledgersmbdev.blogspot.com/2012/08/… Commented Sep 27, 2012 at 11:55

Yes, you could do this in SQL, but personally I wouldn't. You will need to use string functions quite extensively which are much more expensive from a resource perspective, than doing it in the front end code. I would expect a front-end solution to this problem to run significantly faster.

Of course this has caveats depending on your architecture bottlenecks and the amount of rows. I did have a similar tokenization issue on a table that contained just under a billion rows and after testing both SQL only and .NET only solutions, the final answer came from a SQL FullText/.NET hybrid.

  • Well, I don't anticipate anywhere near a billion rows, probably in the low millions actually. I will fiddle around with .NET but I can't seem to get npgsql working on Windows Server 2008 R2. I'll persevere though as .NET is the ideal environment for the rest of the applications.
    – dreamlax
    Commented Sep 27, 2012 at 9:13
  • .NET, I'd really the ideal environment for string manipulation as well, where possible Commented Sep 27, 2012 at 9:19
  • 2
    I don't think it's that bad. and if you really need more, there is always pl/perl.... adding an answer. Commented Sep 27, 2012 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.