Write an efficient query in PostgreSQL

Question

Table stockadj1, column category. it have Multiple columns (like idno,name,category) . But i want to get category with out duplicate values and some default values also come if its (table) there or not. So i wrote a query like this

select 
    'Damages' as category 
union all 
select 
    'Excess Stock' as category 
union all 
select 
    'Shortage' as category 
union all 
select 
    'Stock Journal' as category 
union all 
select 
    distinct category 
from 
    stockadj1 
where 
    category <> '' 
    and lower(category) not in ('damages', 'excess stock', 'shortage', 'stock journal') 
order by 
    category

The default values are

 'Damages', 'Excess Stock', 'Shortage', 'Stock Journal'

Those default values must come with query so am using like this. But I think its a pretty much work. How to get efficient (feasible) solution?

EDIT

Here default value means those values which maybe present in table or not.

I am using Postgresql version 9.0.3

You guys never been in inventory control before? I knew what he was asking straight off. :( — Tony Hopkinson
– Tony Hopkinson, Commented Oct 19, 2013 at 11:08

score 2 · Accepted Answer · 2013-10-21 06:16:44Z

2

Not sure what you mean with "its a pretty much work", but the following requires much less typing

select category
from stockadj1
where category <> ''
union
values ('Damages'),
       ('Excess Stock'),
       ('Shortage')
       ('Stock Journal')
order by category;

The union will make sure duplicates are removed, so there is no need to do a distinct on the overall result.

edited Oct 21, 2013 at 6:16

answered Oct 19, 2013 at 13:09

user330315

Sign up to request clarification or add additional context in comments.

Comments

Tony Hopkinson · Accepted Answer · 2013-10-19 11:03:45Z

0

Wouldn't say it was inefficient, messy and a tad fragile though.

Create a Categories table, if you haven't one already. Add a column to mark it is as default, if not all of them are defaults then it's a simple union

select Category from Categories Where IsDefault = 1
Union
select category from stockadj1 where category <> ''

Shouldn't need the distinct as union is a set operation.

Looking at the rest of it, normalising with a category_id would be a really good idea, you are going to be in a bad place, if some office wally says Excess Stock should be Excess stock.

Then you wouldn't need a union at all, everything would come out of the categories table.

If you need category to be free format or a choice from categories, then add a category Id for other, and have two fields in stock adjust, id and category_description. Only fill description in, if the catgeory_id is other and you end up with.

select Category from Categories Where IsDefault = 1
Union
select category_description from stockadj1 s 
Inner join Categories c On c.Category_id = s.Category_Id
Where c.Category_type = 'Other'

Category_type indicate it's an 'Other' type and you want this sort of behaviour.

Should do the job nicely and you can maintain 99% of it just by changing the categories table.

edited Oct 19, 2013 at 11:03

answered Oct 19, 2013 at 10:49

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

4 Comments

Sathish Over a year ago

Thank you for your reply sir. stockadj1 table have mutilple columns so i can't add default values in first time. In frond end user select default value then only insert to back end. Then only i wrote that like query. So tell he how to change the query in efficent manner

CompanyDroneFromSector7G Over a year ago

"Categories"? Table names should be singular

Tony Hopkinson Over a year ago

@SATSON, first query With Category (ies) table will do what you asked for. No idea what the rest of your comment is about. As I stated in the rest of my answer, your real problem is your schema is inefficient, therefore any query on it will also be at least as inefficient.

Tony Hopkinson Over a year ago

@bukko, that's a convention and the least of the OP's problems.

Collectives™ on Stack Overflow

Write an efficient query in PostgreSQL

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related