postgresql question

  1. 7 days ago

    nicolás c

    Aug 9 Pre-Release Testers, Xojo Pro argentina

    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

  2. Jean-Yves P

    Aug 9 Pre-Release Testers, Xojo Pro Answer Europe (France, Besancon)

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

  3. Tobias B

    Aug 9 Pre-Release Testers, Xojo Pro Bern, Switzerland

    @nicoláscanessa What i need is the same but to replace multiple "=" and "AND" operators

    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?

  4. nicolás c

    Aug 9 Pre-Release Testers, Xojo Pro argentina

    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)

  5. Tobias B

    Aug 9 Pre-Release Testers, Xojo Pro Bern, Switzerland
    Edited 7 days ago

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

  6. nicolás c

    Aug 9 Pre-Release Testers, Xojo Pro argentina

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

  7. Jean-Yves P

    Aug 9 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

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

  8. Tobias B

    Aug 9 Pre-Release Testers, Xojo Pro Bern, Switzerland
    Edited 7 days ago

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

    [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)

    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)
    
  9. nicolás c

    Aug 9 Pre-Release Testers, Xojo Pro argentina
    Edited 7 days ago
    "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

    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

  10. Jean-Yves P

    Aug 9 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

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

  11. Dave S

    Aug 9 San Diego, California USA
    Edited 7 days ago

    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)
  12. Louis D

    Aug 9 Pre-Release Testers, Xojo Pro Montreal, QC, Canada
    Edited 7 days ago

    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
  13. Dave S

    Aug 9 San Diego, California USA
    Edited 7 days ago
    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

or Sign Up to reply!