EAV with multiple values per attribute
Your answer


  more than one attribute with same name (Color for example),


So then create a different attribute with more specificity.


  I am also required to store the image photo url.


This is just another attribute...

Product Attribute
-----------------
2 | photo_exterior

Product Attribute Value
-----------------------
id: 1
attribute_id: 2
value: 01/a0/01a0sdadasfsdfsdfasdfasfdfsafs.jpg


All of that said, I wouldn't be doing this unless I had to. I would be using JSONB.


  What is the best way to fetch products with all attributes?


I'm unsure what kind of resultset you want. Are you asking how do you query an EAV? If you can make it, you can certainly do the joins to get the result set out of it.


  Is there any way to fetch each product with all attributes with one query (one product -> one row)?


Yes, if you don't know how many attributes you have, you'll likely end up using hstore or jsonb though. See jsonb_agg()

X-Y problem?

Examining speed in order to get you to reasses JSONB and save your life. Create a million products, rename the attribute using jsonb.

CREATE TABLE products(id,name,data)
AS
        SELECT x, 'foo'::text, jsonb_build_object('color', 'blue') AS data
        FROM generate_series(1,1e6) AS gs(x);


UPDATE products
SET data = data-'color' || jsonb_build_object('colour', data-&gt;'color');
Time: 5063.010 ms


On my x230 laptop, with an SSD. That's 197 rows/ms? You recreating Amazon that you have to update faster than that?