Doing a Where in on Multiple Columns in Postgresql

Doing a WHERE IN on multiple columns in Postgresql

It should, at least I've done it before in other SQLs.

Have you tried it? You can test it with SET times_chosen = times_chosen

How to use IN clause with multiple columns on same data in postgresql?

You can use arrays and the operator <@ (is contained by), e.g.:

with my_table(name1, name2) as (
values ('Emily', 'Bob'), ('Ben', 'Jack'), ('Emily', 'James')
)

select *
from my_table
where array[name1, name2] <@ array['Emily', 'Jack', 'James', 'Chloe'];

 name1 | name2 
-------+-------
 Emily | James
(1 row)

See also: How to use same list twice in WHERE clause?

SQL Query where clause for multiple columns in same table

You can use EXISTS:

SELECT d1.* 
FROM data_five_minutes d1 
WHERE d1.date_time = '2019-01-02 13:15:00'
  AND EXISTS (
    SELECT 1
    FROM data_five_minutes d2
    WHERE d2.date_time = '2019-01-01 11:10:00'
      AND d2.script_id = d1.script_id
      AND d2.open < d1.open
  );

SQL WHERE.. IN clause multiple columns

You can make a derived table from the subquery, and join table1 to this derived table:

select * from table1 LEFT JOIN 
(
   Select CM_PLAN_ID, Individual_ID
   From CRM_VCM_CURRENT_LEAD_STATUS
   Where Lead_Key = :_Lead_Key
) table2
ON 
   table1.CM_PLAN_ID=table2.CM_PLAN_ID
   AND table1.Individual=table2.Individual
WHERE table2.CM_PLAN_ID IS NOT NULL

PostgreSQL : Display multiple columns from one column value based on conditions

You need to use left outer join for this. The query will look like this:

CREATE TEMP TABLE tmp as
SELECT ID
FROM table
GROUP BY ID;

SELECT tmp.id, t1.Value as First_Name, t2.value as Last_name
FROM tmp
LEFT OUTER JOIN table t1 on tmp.id = t1.id and t1.Attribute_id = 'attribute_1'
LEFT OUTER JOIN table t2 on tmp.id = t2.id and t2.Attribute_id = 'attribute_2'
;

The first query will get all ids, then we will join all attributes to these ids.

How to select an array with multiple columns in postgres?

Something like this?

SELECT
  customer_id,
  array_agg(row(street, city, state, zip))
FROM
  customer_address
GROUP BY
  customer_id

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=dc0d405710375207f412d81dee96dd70

Postgresql group by multiple columns and sort within each group

Use FIRST_VALUE() window function:

SELECT DISTINCT order_id, 
       FIRST_VALUE(doc_id) OVER (
         PARTITION BY order_id 
         ORDER BY amount DESC NULLS LAST, rating DESC NULLS LAST, percent DESC NULLS LAST, customer_id
       ) doc_id
FROM orders;

See the demo.

Postgres where clause over two columns from subquery

Refactor the subquery to a join.

Say you have

SELECT a, b FROM t1 WHERE (x,y) IN (SELECT x1, y1 FROM t2 WHERE ...)

which won't work. You rewrite to

SELECT a, b
FROM t1
INNER JOIN (
   -- subquery here:
   SELECT x1, y1
   FROM t2
   WHERE ...
) AS some_alias
WHERE t1.x = some_alias.x1
  AND t1.y = some_alias.y1;

Note, in PostgreSQL you shouldn't use a CTE (WITH query) for this.

You can abbreviate

WHERE t1.x = some_alias.x1
  AND t1.y = some_alias.y1;

to

WHERE (x, y) = (x1, y1)

though.

How to do multiple columns update on different where condition using PostgreSQL Upsert Using INSERT ON CONFLICT statement

You could update multiple columns with CASE conditions in a single DO UPDATE clause.

INSERT INTO customers  (
    name
    ,email
    ,updated_datetime
    )
VALUES (
    'Microsoft'
    ,'hotline@microsoft.com'
    ,now()
    ) ON CONFLICT(name) DO

UPDATE
SET email = CASE 
        WHEN customers.updated_datetime < excluded.updated_datetime
            THEN excluded.email
        ELSE customers.email --default when condition not satisfied
        END
    ,is_active_datetime = CASE 
        WHEN customers.active != excluded.active
            THEN current_timestamp
        ELSE customers.is_active_datetime
    END;

Demo

Split column to multiple columns in PostgreSQL

Given this table:

CREATE TABLE split_column (
    id integer PRIMARY KEY,
    col text NOT NULL
)

And this data:

INSERT INTO split_column (id, col) VALUES
    (1, 'a,b,c,d,e,f'),
    (2, 'b,c,e,f'),
    (3, 'c,d,e,f')

The first row contains all values:

SELECT string_to_array(col, ',') AS values
FROM split_column
ORDER BY id
LIMIT 1

---