0

i have a big postgres db with single table for product and attribute but i want two view of one with product and one with attribute.

for example now i have

tb_products

iditem | name | attributes
-----------------------
1      | art1 | a
2      | art1 | b    
3      | art1 | c
4      | art1 | d

what i need is

vs_product

iditem | name 
-----------------------
1      | art1 

vs_attrib

iditem |  attributes
-----------------------
1      | a
1      | b   
1      | c
1      | d

what i have tried is something like this and it seem to work, but is too slow when extractiong attributes

vs_product

SELECT
Min(.tb_products."id") AS "Product ID",
FROM
tb_products
GROUP BY
tb_products.item

vs_attrib

SELECT
a1."id" AS "oldid",
(
SELECT MIN
    ( a2."id" )  
FROM
    tb_products a2
WHERE 
    a1.name = a2.name
GROUP BY
    a1.name 
) AS "newid"
FROM
tb_products AS a1

any ideas?

  • What is the meaning of the id column in your example? – Laurenz Albe Sep 17 at 12:50
  • Why id=1 was taken from source table to first view and why another 3 values were ignored? – Akina Sep 17 at 12:54
  • too slow For 1st query create index tb_products (item, id).2nd query must be re-writed to avoid correlated subq in output list (use window function). – Akina Sep 17 at 12:57
  • @LaurenzAlbe i hope this is ok now – webbo Sep 17 at 13:00
  • @Akina first query is fast only the second, can you be more specific? – webbo Sep 17 at 13:02
0

only the second, can you be more specific?

For second view test

CREATE VIEW vs_attrib 
AS
SELECT MIN(iditem) OVER (PARTITION BY name) iditem, attributes
FROM tb_products 

Index tb_products (name, iditem) may increase view's performance.

  • only vs_attrib query is slow – webbo Sep 17 at 13:33
  • @webbo The code was given for it (view2). The name edited. – Akina Sep 17 at 13:39
  • it works perferct!! very fast – webbo Sep 17 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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