I have a query. Working with Postgres 11.5.

copy (
  select "HOBIS",
        "IMG URL",
  from (
    select distinct on ("HOBIS")
            product_shop_id as "HOBIS",
            product_name as "NAME",
            concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/?utm_source=email&utm_medium=Katalog&utm_campaign=Osvetleni_2019') as "URL",
            concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "IMG URL",
            when si_unit = 'KS' or si_unit = 'L' or si_unit = 'KG' or si_unit = 'M' then price_tax
            else si_unit_price_tax
            end as "PRICE ORIGINAL",
    from s_product
      join unnest(array['114260','114261','114262','114265','114266','114270','114268','114269','104978','104976','112293','106611','112294','106613','112295','107161','107162',
                        '309973','309974','309975','309976','309978','309979','337944','337945','337946','337947','337948','321790','321791','321792','337147','337146','337145','337144','327099', .....]
                  ) with ordinality as x(shopid, idx) 
        on s_product.product_shop_id = x.shopid
      left join s_product_image on s_product.product_id = s_product_image.product_id
      left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
    where s_product.product_shop_id in (... same list as above ... )
      and image_order = '0'
     and s_pricelist_generated_lists.group_id = '1'
    order by "HOBIS"
    ) t
    order by t.idx
) to stdout (format csv, quote '"');

But it is not working with Postgres 9.3.24 (with ordinality was introduced in 9.4).

ERROR:  syntax error at or near "with ordinality"
LINE 303: ]) with ordinality as x(shopid, idx) on s_product.product_sh...

Is there some (easy) way to adjust this query for Postgres 9.3.24?

  • Unrelated, but: the where s_product.product_shop_id in (... ) with the same IDs as the ones in the array is useless. The JOIN already filters those rows.
    – user1822
    Oct 2, 2019 at 11:53
  • @a_horse_with_no_name Thanks.
    – genderbee
    Oct 2, 2019 at 11:55

1 Answer 1


Instead of

SELECT unnest, ordinality
FROM unnest(ARRAY[6,5,4,3,2,1]) WITH ORDINALITY;

you can also write

SELECT unnest, row_number() OVER () AS ordinality
FROM unnest(ARRAY[6,5,4,3,2,1]);

Something like:

from s_product
  join (
    select unnest, row_number() over ()
    from unnest(array[....])
  ) as x(shopid, idx) 
    on s_product.product_shop_id = x.shopid

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.