[Solved] You want to insert a new user with email and last_login into a table users(email TEXT UNIQUE, last_login TIMESTAMP).... Which query correctly implements this conditional update? | PostgreSQL

PostgreSQL - Set Operations and Advanced Queries

You want to insert a new user with email and last_login into a table users(email TEXT UNIQUE, last_login TIMESTAMP). If the email already exists, update last_login only if the new timestamp is more recent. Which query correctly implements this conditional update?

AINSERT INTO users (email, last_login) VALUES ('a@b.com', '2024-01-01') ON CONFLICT DO NOTHING WHERE EXCLUDED.last_login > users.last_login;

BINSERT INTO users (email, last_login) VALUES ('a@b.com', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = users.last_login WHERE EXCLUDED.last_login > users.last_login;

CINSERT INTO users (email, last_login) VALUES ('a@b.com', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login WHERE EXCLUDED.last_login > users.last_login;

DINSERT INTO users (email, last_login) VALUES ('a@b.com', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login;

Step-by-Step Solution

Solution:

Step 1: Understand conditional update with WHERE clause
PostgreSQL allows a WHERE clause in DO UPDATE to conditionally apply the update.
Step 2: Check condition for updating last_login
INSERT INTO users (email, last_login) VALUES ('a@b.com', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login WHERE EXCLUDED.last_login > users.last_login; updates last_login only if the new timestamp is greater than the existing one, using WHERE EXCLUDED.last_login > users.last_login.
Final Answer:
INSERT INTO users (email, last_login) VALUES ('a@b.com', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login WHERE EXCLUDED.last_login > users.last_login; -> Option C
Quick Check:
Use WHERE in DO UPDATE for conditional update [OK]

Quick Trick: Use WHERE in DO UPDATE to update conditionally [OK]

Common Mistakes:

Forgetting WHERE clause for conditional update
Using DO NOTHING with WHERE incorrectly
Updating unconditionally without checking timestamps

Master "Set Operations and Advanced Queries" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Which query correctly implements this conditional update?

Step 1: Understand conditional update with WHERE clause

Step 2: Check condition for updating last_login

Final Answer:

Quick Check:

Want More Practice?