postgresql question

I use postgresql IN operator as syntactic sugar to avoid using multiple “=” and “OR”. Here you can see how IN work–> https://www.techonthenet.com/postgresql/in.php

What i need is the same but to replace multiple “=” and “AND” operators

use ALL the same way you use IN
https://www.postgresql.org/docs/9.0/static/functions-subquery.html#AEN16897

in that case, just write FALSE as A = 1 AND A = 2 would never be true. Joking aside, you might want to have a look to row value comparison like (A, B) = (1, 2) (see here or here) - but I’m not sure what you are trying to accomplish in the end. Can you explain more?

thanks both for your anwers. what i am trying to do is just use syntactic sugar. instead of using WHERE id = 10 OR id = 15 or id = 9 i use WHERE id IN(10,15,9)
and now… instead of using WHERE id = 10 AND id = 15 AND id = 9 i can use WHERE id ALL(10,15,9)

but how can id be equal to 10 and 15 and 9 at the same time?!

I have two tables: orders and status.
I want to view the orders that contain ALL the following 3 associated states: 0010, 0015, 0009

it’s not logical, there is something missing… you can’t get all three values in a same record.

I did some experiments. Given the following schema (is this like you are using?)

[code][local] bussmann@~=# \d orders
Table “public.orders”
???
? Column ? Type ? Collation ? Nullable ? Default ?
???
? order_id ? integer ? ? not null ? ?
? date ? date ? ? ? ?
? customer ? text ? ? ? ?
? amount ? numeric ? ? ? ?
???
Indexes:
“orders_pkey” PRIMARY KEY, btree (order_id)
Referenced by:
TABLE “order_status” CONSTRAINT “order_status_order_id_fkey” FOREIGN KEY (order_id) REFERENCES orders(order_id)

[local] bussmann@~=# \d order_status
Table “public.order_status”
???
? Column ? Type ? Collation ? Nullable ? Default ?
???
? order_id ? integer ? ? ? ?
? status ? text ? ? ? ?
???
Foreign-key constraints:
“order_status_order_id_fkey” FOREIGN KEY (order_id) REFERENCES orders(order_id)

[local] bussmann@~=# TABLE orders;
???
? order_id ? date ? customer ? amount ?
???
? 1 ? 2017-01-01 ? customer 1 ? 100 ?
? 2 ? 2017-01-01 ? customer 1 ? 50 ?
? 3 ? 2017-01-01 ? customer 2 ? 75 ?
???
(3 rows)

[local] bussmann@~=# TABLE order_status;
???
? order_id ? status ?
???
? 1 ? 0010 ?
? 1 ? 0008 ?
? 1 ? 0100 ?
? 2 ? 0009 ?
? 2 ? 0010 ?
? 2 ? 0023 ?
? 2 ? 0015 ?
???
(7 rows)
[/code]

we can use an array_agg and array containment operator like in the following examples:

[local] bussmann@~=# WITH o_s AS (
[more]1(#     SELECT
[more]1(#         order_id,
[more]1(#         array_agg(status) AS stati
[more]1(#     FROM
[more]1(#         order_status
[more]1(#     GROUP BY
[more]1(#         order_id
[more]1(# )
[more]2-# SELECT
[more]3-#     o.*,
[more]4-#     o_s.stati
[more]5-# FROM
[more]6-#     orders o
[more]7-#     LEFT OUTER JOIN o_s USING (order_id)
[more]8-# WHERE
[more]9-#     o_s.stati @> ARRAY['0010', '0015', '0009']
[more]10-# ;
???????????????????????????????????????????????????????????????????????
? order_id ?    date    ?  customer  ? amount ?         stati         ?
???????????????????????????????????????????????????????????????????????
?        2 ? 2017-01-01 ? customer 1 ?     50 ? {0009,0010,0023,0015} ?
???????????????????????????????????????????????????????????????????????
(1 row)

[local] bussmann@~=# SELECT
[more]2-#     o.*
[more]3-# FROM
[more]4-#     orders o
[more]5-# WHERE 
[more]6-#     (
[more]6(#         SELECT
[more]6(#             array_agg(status)
[more]6(#         FROM
[more]6(#             order_status
[more]6(#         WHERE
[more]6(#             order_id = o.order_id
[more]6(#     ) @> ARRAY [ '0010', '0015', '0009' ];
???????????????????????????????????????????????
? order_id ?    date    ?  customer  ? amount ?
???????????????????????????????????????????????
?        2 ? 2017-01-01 ? customer 1 ?     50 ?
???????????????????????????????????????????????
(1 row)

[local] bussmann@~=# SELECT
[more]2-#     o.*
[more]3-# FROM
[more]4-#     orders o
[more]5-#     LEFT OUTER JOIN order_status 
[more]6-#         USING (order_id)
[more]7-# GROUP BY
[more]8-#     order_id
[more]9-# HAVING
[more]10-#     array_agg(status) @> ARRAY [ '0010', '0015', '0009' ]
[more]11-# ;
???????????????????????????????????????????????
? order_id ?    date    ?  customer  ? amount ?
???????????????????????????????????????????????
?        2 ? 2017-01-01 ? customer 1 ?     50 ?
???????????????????????????????????????????????
(1 row)

however w/o using arrays, I’m not immediately seeing a nice solution:

[local] bussmann@~=# SELECT
[more]2-#     o.*
[more]3-# FROM
[more]4-#     orders o
[more]5-# WHERE
[more]6-#     EXISTS (
[more]6(#         SELECT *
[more]6(#         FROM order_status
[more]6(#         WHERE order_id = o.order_id
[more]6(#             AND status = '0010'
[more]6(#     ) AND EXISTS (
[more]6(#         SELECT *
[more]6(#         FROM order_status
[more]6(#         WHERE order_id = o.order_id
[more]6(#             AND status = '0015'
[more]6(#     ) AND EXISTS (
[more]6(#         SELECT *
[more]6(#         FROM order_status
[more]6(#         WHERE order_id = o.order_id
[more]6(#             AND status = '0009'
[more]6(#     )
[more]7-# ;
???????????????????????????????????????????????
? order_id ?    date    ?  customer  ? amount ?
???????????????????????????????????????????????
?        2 ? 2017-01-01 ? customer 1 ?     50 ?
???????????????????????????????????????????????
(1 row)

[code]“ORDERS” TABLE
id_order | id_client | id_product
or_001 c_003 p_002
or_002 c_003 p_003
or_003 c_001 p_003
or_004 c_002 p_001

“STATUS_TRACKING” TABLE
id_status | id_order | id_status | status_timestamp
st_001 or_001 s_001 2001-01-01 01:01:01
st_002 or_001 s_002 2001-01-01 01:01:01
st_003 or_001 s_003 2001-01-01 01:01:01
st_004 or_001 s_004 2001-01-01 01:01:01
st_005 or_002 s_001 2001-01-01 01:01:01
st_006 or_002 s_002 2001-01-01 01:01:01
st_007 or_002 s_003 2001-01-01 01:01:01
st_008 or_004 s_001 2001-01-01 01:01:01
st_009 or_004 s_004 2001-01-01 01:01:01
st_010 or_004 s_001 2001-01-01 01:01:01[/code]

i want to list the id_order from “ORDERS” TABLE where
status has passed through states s_002 AND s_003 AND s_004
on this case i should get ONLY or_001

I would say something with array_agg
but ALL will not work.

if you are joining the tables then perhaps

if a.id in(10,15,9) OR b.id in(10,15,9) OR c.id in(10,15,9)

There are two tables with the name id_status. Surely, that can’t be correct. Also, it would be far easier to have one column per type of status. Example: 0009 = Pricing_OK, 0010 = Processed, 0015 = Billed. I would use the following:

id_order Pricing_OK Processed Processed_Time Billed Billed_Time 1001 True True 20180809154503 False 1002 True False False 1003 True True 20180808223344 True 20180808110234

SELECT id_order, COUNT(DISTINCT id_status) FROM status_tracking WHERE status_id IN("S_002","S_003","S_004")
group by id_order
HAVING COUNT(distinct id_status)=3