0

I am using PostgreSQL 11.5 on Ubuntu

Issue

I have sql with joining tables and with where condition, which looks like this.

...

where
s_product.product_shop_id in (
'237002',
'230041',
'467173',
'464431',
'318417',

...

When I run sql.

copy(
select distinct on ("HOBIS")
-- s_product.product_id,
product_shop_id as "HOBIS",
product_name as "NAME",
concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019') as "URL",
concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "IMG URL",
price_tax as "PRICE VAT",
case when price_rec > price_tax then concat(price_rec) end as "PRICE ORIGINAL"
from
s_product
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 (
'237002',
'230041',
'467173',
'464431'
)
and image_order = '0'
and s_pricelist_generated_lists.group_id = '1'

--order by "HOBIS"
) to stdout (format csv, quote '"');

Results are sorted by s_product.product_shop_id.

...

230041,"Interiérové dveře Klasik plné dub archico, 3D povrch - 60 cm L",https://eshop.unihobby.cz/stavba-dvere-interierove-dvere-dvere-klasik-plne-dub-archico-3d-povrch/136280p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/klasik-plne-dub-archico.jpg,990.00,1390.00
237002,Vinylová podlaha Kronostep - True Grit,https://eshop.unihobby.cz/stavba-podlahy-vinylove-podlahy-podlaha-vinylova-kronostep-true-grit-5-0mm/151328p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/237002-krono-original-krono-xonic-r018-true-grit-3.jpg.big.jpg,1058.00,1323.00
318417,Primalex Malvena na fasády 1 l,https://eshop.unihobby.cz/bydleni-barvy-a-laky-venkovni-barvy-primalex-malvena-na-fasady-1l/153358p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/318417-malvena-5l.jpg,199.00,
464431,Benzínová řetězová pila CSH46 Scheppach,https://eshop.unihobby.cz/zahrada-zahradni-technika-retezove-pily-benzinove-retezove-pily-pila-retezova-benzinova-csh46-scheppach/150162p/?utm_source=kupi&utm_medium=letak&utm_campaign=9_2019,https://eshop.unihobby.cz/bin/product/4/464431-csh46-scheppach-diy-garten-titel-neu-na.jpg,3290.00,

...

But I want to have results as they are in where condition. Is possible to have results as they are in where condition? And why PG sort results when I have no sort condition in my sql?

Thanks.

  • 2
    It just seems that way but it isn't guaranteed to remain that way unluess you use ORDER BY – scsimon Aug 29 at 20:17
  • If you don't have an order by your result is not sorted at all – a_horse_with_no_name Aug 29 at 20:28
  • @a_horse_with_no_name Nope, no order by condition, see updated question. – genderbee Aug 29 at 20:39
  • Unrelated, but: why are you passing numbers as strings? You shouldn't do that. '1' is a string literal, 1 is a number – a_horse_with_no_name Aug 30 at 5:50
1

You can do that if you change the way you pass the values to the WHERE clause. One way is to join to an unnested array and use the array index for sorting.

As you need a different sort order for the distinct on () you have to wrap the query in a derived table. The inner part uses an order by on the "distinct column". And the result can then be sorted by the array index.

select "HOBIS", "NAME", "URL", ... -- all columns but the `idx`
from (
  select distinct on ("HOBIS")
            product_shop_id as "HOBIS",
            ..., 
            x.idx
  ...
  from s_product
    join unnest(array['237002','230041','467173','464431']) with ordinality as x(shopid, idx) 
      on s_product.product_shop_id = x.shopid
    left join ...
    ...
  order by "HOBIS"
) t
order by t.idx

This behaves a bit different to an IN condition, so you have to make sure that every shop ID only appears once in the array.


And why PG sort results when I have no sort condition in my sql?

Postgres does not sort your results.

Without an order by clause, the database is free to return the rows in any order it wants. The "order" results from the different steps in the execution plan. If you see some perceived "order", then this probably stems from retrieving rows through an index.

But the "order" you see there is in no way guaranteed. It can change because rows have been changed or inserted into the table, or because the query optimizer chooses to use a different execution plan. Or because some other transaction is running a similar statement.

  • SQL Error [42883]: ERROR: operator does not exist: character varying = integer, probably some trifle? And what about distinct in select? Can it be cause of ordering? – genderbee Aug 30 at 7:45
  • @genderbee why are you storing numbers in a varchar column? (I fixed the array) - distinct on () without an order by doesn't really make sense to begin with. – a_horse_with_no_name Aug 30 at 7:46
  • Thank you, it is not my idea with varchars. SQL Error [42P10]: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions. I tried to change field on select line, but still errors. I removed distinct on and seems it works. – genderbee Aug 30 at 7:55
  • But there duplicates by product_shop_id now. – genderbee Aug 30 at 8:03
  • @genderbee: see my edit. You need to use a two step approach for the sorting. The one from the derived table (inner select) is needed for a deterministic result of the distinct on () and the outer then puts the rows back into the order of the array. – a_horse_with_no_name Aug 30 at 8:04

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.