Test Your PostgreSQL UPSERT Command Knowledge with This Quiz

Progress

True / False

In PostgreSQL, the UPSERT feature allows you to either insert a new record or update an existing record if a conflict occurs.

True False

The UPSERT feature in PostgreSQL provides a way to either insert a new record or update an existing one if there is a conflict on a unique constraint or primary key.

True / False

The UPSERT operation is performed using the INSERT ON CONFLICT clause in PostgreSQL.

True False

PostgreSQL uses the INSERT ON CONFLICT clause to handle conflicts that arise during an insert operation, enabling the UPSERT functionality.

True / False

The ON CONFLICT clause can only be used with primary keys in PostgreSQL.

True False

The ON CONFLICT clause in PostgreSQL can be used with any unique constraint, not just primary keys.

True / False

In PostgreSQL, you can use the ON CONFLICT clause to perform a conditional update based on the values of other columns.

True False

PostgreSQL allows conditional updates in the ON CONFLICT clause using the WHERE condition to specify criteria for updating the record.

True / False

PostgreSQL's UPSERT operation can only handle conflicts on a single column.

True False

PostgreSQL's ON CONFLICT clause can handle conflicts on multiple columns by specifying a unique constraint that involves multiple columns.

True / False

The DO NOTHING action in the ON CONFLICT clause will ignore the insert operation entirely if a conflict occurs in PostgreSQL.

True False

The DO NOTHING action instructs PostgreSQL to ignore the insert operation if a conflict occurs, effectively preventing any changes.

True / False

In PostgreSQL, you can use the ON CONFLICT clause to perform complex updates involving multiple tables.

True False

While the ON CONFLICT clause itself is used within a single table, the accompanying UPDATE statement can involve subqueries and references to other tables for complex update operations.

True / False

PostgreSQL's UPSERT mechanism guarantees that no other transactions will see an inconsistent state during the operation.

True False

PostgreSQL's transactional model ensures that the UPSERT operation is atomic, meaning other transactions will not see an inconsistent state during the operation.

True / False

The ON CONFLICT clause can be combined with the RETURNING clause to retrieve the updated or inserted values in PostgreSQL.

True False

PostgreSQL allows the use of the RETURNING clause with ON CONFLICT to return the updated or inserted values as part of the UPSERT operation.

Q10

True / False

PostgreSQL's UPSERT functionality can be used with the INSERT ... SELECT statement to handle conflicts arising from a bulk insert.

True False

PostgreSQL supports the use of the INSERT ... SELECT statement with the ON CONFLICT clause to handle conflicts during bulk insert operations, providing flexibility in handling large data sets.

Q11

Single Choice

What does the UPSERT operation in PostgreSQL do?

Inserts new records only Updates existing records only Inserts new records or updates existing ones if they already existDeletes existing records

The UPSERT operation in PostgreSQL is used to either insert a new record or update an existing record if it already exists.

Q12

Single Choice

Which PostgreSQL clause is used to perform an UPSERT operation?

ON DUPLICATE KEY UPDATE INSERT INTO ON CONFLICTREPLACE INTO

PostgreSQL uses the ON CONFLICT clause to perform UPSERT operations, allowing you to specify what to do when a conflict occurs.

Q13

Single Choice

What must be specified in a PostgreSQL UPSERT operation to handle conflicts?

A primary key or unique constraint A foreign key A default valueA view

For PostgreSQL to perform an UPSERT operation, there must be a primary key or unique constraint that defines the conflict.

Q14

Single Choice

Given the following SQL code, what will the UPSERT operation do?

SQL Code

INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', 50000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary + 1000;

Insert a new employee with id 1 Update the salary of the employee with id 1 to 1000 Update the salary of the employee with id 1 by adding 1000Delete the employee with id 1

The ON CONFLICT clause specifies that if a conflict occurs on the id column, the existing record's salary will be updated by adding 1000 to the existing salary.

Q15

Single Choice

In PostgreSQL, which keyword is used within an UPSERT operation to refer to the values that caused the conflict?

VALUES EXCLUDED DEFAULTCONFLICTED

The EXCLUDED keyword in PostgreSQL is used to refer to the row that would have been inserted but caused a conflict, allowing it to be used in the DO UPDATE clause.

Q16

Single Choice

Which of the following PostgreSQL SQL code snippets correctly performs an UPSERT operation on a table named products?

SQL Code

INSERT INTO products (product_id, product_name, price)
VALUES (101, 'Laptop', 1500)
ON CONFLICT (product_id) DO UPDATE SET price = EXCLUDED.price;

The code will insert a new product or update the price if product_id conflicts. The code will only insert a new product. The code will delete the existing product with product_id.The code will raise an error.

This code will insert a new product into the products table or update the price if a product with the same product_id already exists.

Q17

Single Choice

Consider the following SQL code:

SQL Code

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (10, 1001, '2024-08-18')
ON CONFLICT (order_id) WHERE customer_id = 1001
DO UPDATE SET order_date = EXCLUDED.order_date + INTERVAL '1 day';

Insert a new order or update the order date for customer 1001 by adding one day Insert a new order only Update all orders by adding one dayRaise a syntax error

This code performs an UPSERT operation, updating the order_date by adding one day if a conflict occurs for order_id where customer_id is 1001.

Q18

Single Choice

How would you perform an UPSERT operation in PostgreSQL that only updates the record if a condition is met?

SQL Code

INSERT INTO inventory (item_id, stock)
VALUES (50, 200)
ON CONFLICT (item_id) DO UPDATE SET stock = EXCLUDED.stock WHERE inventory.stock < 100;

The stock will be updated only if it is less than 100 The stock will be updated unconditionally The code will raise an errorThe stock will be reset to 0

The WHERE clause in the DO UPDATE part restricts the update to cases where the existing stock is less than 100

Q19

Single Choice

What does the following SQL code do in PostgreSQL?

SQL Code

INSERT INTO accounts (account_id, balance)
VALUES (1001, 5000)
ON CONFLICT (account_id) DO NOTHING;

Inserts a new account if it doesn't exist, otherwise does nothing Inserts a new account or updates the balance Deletes the account if it already existsUpdates the balance unconditionally

The DO NOTHING option ensures that if a conflict occurs on account_id, no action is taken

Q20

Single Choice

Given the following table structure and SQL code, what will the outcome be?

SQL Code

CREATE TABLE vendors (
 vendor_id SERIAL PRIMARY KEY,
 vendor_name VARCHAR(100) UNIQUE,
 address VARCHAR(200)
);

INSERT INTO vendors (vendor_name, address)
VALUES ('Tech Corp', '123 Tech Street')
ON CONFLICT (vendor_name) DO UPDATE SET address = '456 Tech Avenue';

Insert a new vendor named 'Tech Corp' Update the address of 'Tech Corp' to '456 Tech Avenue' Delete the vendor 'Tech Corp'Raise an error due to the conflict

: Since vendor_name is unique, the ON CONFLICT clause triggers when trying to insert 'Tech Corp' again, updating the address to '456 Tech Avenue' instead of inserting a new record. These questions cover a range of difficulties and test different aspects of PostgreSQL's UPSERT operation, including SQL code snippets and specific features.

Q21

Multiple Choice

Which SQL command performs an UPSERT operation by inserting a new row or updating an existing one based on a conflict in PostgreSQL?

SQL Code

INSERT INTO users (user_id, username)
VALUES (1, 'jdoe')
ON CONFLICT (user_id)
DO UPDATE SET username = EXCLUDED.username;

INSERT INTO users (user_id, username) VALUES (1, 'jdoe') ON CONFLICT (user_id) DO UPDATE SET username = EXCLUDED.username; INSERT INTO users (user_id, username) VALUES (1, 'jdoe') ON CONFLICT DO NOTHING; INSERT INTO users (user_id, username) VALUES (1, 'jdoe') ON CONFLICT (username) DO UPDATE SET username = EXCLUDED.username;INSERT INTO users (user_id, username) VALUES (1, 'jdoe') ON CONFLICT (user_id) DO NOTHING;

This command tries to insert a new row into the 'users' table. If the 'user_id' already exists, it updates the 'username' instead of inserting a new row.

Q22

Multiple Choice

Which SQL command performs an UPSERT operation with a condition in the DO UPDATE clause in PostgreSQL?

SQL Code

INSERT INTO products (product_id, price)
VALUES (101, 19.99)
ON CONFLICT (product_id)
DO UPDATE SET price = EXCLUDED.price
WHERE products.price < EXCLUDED.price;

INSERT INTO products (product_id, price) VALUES (101, 19.99) ON CONFLICT (product_id) DO UPDATE SET price = EXCLUDED.price WHERE products.price < EXCLUDED.price; INSERT INTO products (product_id, price) VALUES (101, 19.99) ON CONFLICT (product_id) DO NOTHING; INSERT INTO products (product_id, price) VALUES (101, 19.99) ON CONFLICT (price) DO UPDATE SET price = EXCLUDED.price;INSERT INTO products (product_id, price) VALUES (101, 19.99) ON CONFLICT (product_id) DO UPDATE SET price = EXCLUDED.price;

This command inserts a new row into the 'products' table or updates the 'price' of an existing row only if the new price is higher than the current price.

Q23

Multiple Choice

Which SQL command performs an UPSERT operation using a composite key in PostgreSQL?

SQL Code

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1, 1001, 2)
ON CONFLICT (order_id, product_id)
DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity;

INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1001, 2) ON CONFLICT (order_id, product_id) DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity; INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1001, 2) ON CONFLICT (order_id) DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity; INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1001, 2) ON CONFLICT (product_id) DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity;INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1001, 2) ON CONFLICT DO NOTHING;

This command tries to insert a new row into the 'order_items' table. If a row with the same 'order_id' and 'product_id' exists, it updates the 'quantity' by adding the existing and new quantities.

Q24

Multiple Choice

Which SQL command performs an UPSERT operation with a RETURNING clause in PostgreSQL?

SQL Code

INSERT INTO customers (customer_id, email)
VALUES (42, 'john.doe@example.com')
ON CONFLICT (customer_id)
DO UPDATE SET email = EXCLUDED.email
RETURNING customer_id, email;

INSERT INTO customers (customer_id, email) VALUES (42, 'john.doe@example.com') ON CONFLICT (customer_id) DO UPDATE SET email = EXCLUDED.email RETURNING customer_id, email; INSERT INTO customers (customer_id, email) VALUES (42, 'john.doe@example.com') ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email RETURNING customer_id, email; INSERT INTO customers (customer_id, email) VALUES (42, 'john.doe@example.com') ON CONFLICT (customer_id) DO UPDATE SET email = EXCLUDED.email;INSERT INTO customers (customer_id, email) VALUES (42, 'john.doe@example.com') ON CONFLICT (customer_id) DO NOTHING RETURNING customer_id, email;

This command performs an UPSERT on the 'customers' table and returns the 'customer_id' and 'email' of the inserted or updated row.

Q25

Multiple Choice

Which SQL command performs an UPSERT operation by inserting default values if the row doesn't exist in PostgreSQL?

SQL Code

INSERT INTO inventory (product_id, stock)
VALUES (100, 50)
ON CONFLICT (product_id)
DO UPDATE SET stock = inventory.stock + EXCLUDED.stock;

INSERT INTO inventory (product_id, stock) VALUES (100, 50) ON CONFLICT (product_id) DO UPDATE SET stock = inventory.stock + EXCLUDED.stock; INSERT INTO inventory (product_id, stock) VALUES (100, 50) ON CONFLICT (product_id) DO NOTHING; INSERT INTO inventory (product_id, stock) VALUES (100, 50) ON CONFLICT (product_id) DO UPDATE SET stock = EXCLUDED.stock;INSERT INTO inventory (product_id, stock) VALUES (100, 50) ON CONFLICT DO NOTHING;

This command inserts a new row into the 'inventory' table or updates the 'stock' of an existing row by adding the new stock to the current stock.

Q26

Multiple Choice

Which SQL command uses a WHERE clause in the UPSERT operation to conditionally update an existing row in PostgreSQL?

SQL Code

INSERT INTO employees (employee_id, salary)
VALUES (1, 50000)
ON CONFLICT (employee_id)
DO UPDATE SET salary = EXCLUDED.salary
WHERE employees.salary < EXCLUDED.salary;

INSERT INTO employees (employee_id, salary) VALUES (1, 50000) ON CONFLICT (employee_id) DO UPDATE SET salary = EXCLUDED.salary WHERE employees.salary < EXCLUDED.salary; INSERT INTO employees (employee_id, salary) VALUES (1, 50000) ON CONFLICT (employee_id) DO UPDATE SET salary = EXCLUDED.salary; INSERT INTO employees (employee_id, salary) VALUES (1, 50000) ON CONFLICT DO NOTHING;INSERT INTO employees (employee_id, salary) VALUES (1, 50000) ON CONFLICT (salary) DO UPDATE SET salary = EXCLUDED.salary;

This command inserts a new row or updates the 'salary' of an existing row in the 'employees' table, but only if the new salary is higher than the current one.

Q27

Multiple Choice

Which SQL command uses an UPSERT operation to update multiple columns in PostgreSQL?

SQL Code

INSERT INTO users (user_id, username, email)
VALUES (1, 'jdoe', 'jdoe@example.com')
ON CONFLICT (user_id)
DO UPDATE SET username = EXCLUDED.username, email = EXCLUDED.email;

INSERT INTO users (user_id, username, email) VALUES (1, 'jdoe', 'jdoe@example.com') ON CONFLICT (user_id) DO UPDATE SET username = EXCLUDED.username, email = EXCLUDED.email; INSERT INTO users (user_id, username) VALUES (1, 'jdoe') ON CONFLICT (user_id) DO UPDATE SET username = EXCLUDED.username; INSERT INTO users (user_id, username, email) VALUES (1, 'jdoe', 'jdoe@example.com') ON CONFLICT (user_id) DO NOTHING;INSERT INTO users (user_id, username, email) VALUES (1, 'jdoe', 'jdoe@example.com') ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email;

This command performs an UPSERT on the 'users' table, updating both the 'username' and 'email' if a conflict occurs on 'user_id'.

Q28

Multiple Choice

Which SQL command performs an UPSERT operation and logs the old values using the RETURNING clause in PostgreSQL?

SQL Code

INSERT INTO orders (order_id, total)
VALUES (10, 100)
ON CONFLICT (order_id)
DO UPDATE SET total = EXCLUDED.total
RETURNING order_id, old_total = orders.total;

INSERT INTO orders (order_id, total) VALUES (10, 100) ON CONFLICT (order_id) DO UPDATE SET total = EXCLUDED.total RETURNING order_id, old_total = orders.total; INSERT INTO orders (order_id, total) VALUES (10, 100) ON CONFLICT (total) DO UPDATE SET total = EXCLUDED.total RETURNING order_id, total; INSERT INTO orders (order_id, total) VALUES (10, 100) ON CONFLICT (order_id) DO UPDATE SET total = EXCLUDED.total;INSERT INTO orders (order_id, total) VALUES (10, 100) ON CONFLICT DO NOTHING RETURNING order_id, total;

This command performs an UPSERT on the 'orders' table and returns the 'order_id' and old 'total' values if an update occurs.

Q29

Multiple Choice

Which SQL command performs an UPSERT operation with a condition on the conflict target in PostgreSQL?

SQL Code

INSERT INTO sales (sale_id, amount)
VALUES (101, 500)
ON CONFLICT (sale_id)
DO UPDATE SET amount = EXCLUDED.amount
WHERE sales.amount < EXCLUDED.amount;

INSERT INTO sales (sale_id, amount) VALUES (101, 500) ON CONFLICT (sale_id) DO UPDATE SET amount = EXCLUDED.amount WHERE sales.amount < EXCLUDED.amount; INSERT INTO sales (sale_id, amount) VALUES (101, 500) ON CONFLICT DO NOTHING; INSERT INTO sales (sale_id, amount) VALUES (101, 500) ON CONFLICT (amount) DO UPDATE SET amount = EXCLUDED.amount;INSERT INTO sales (sale_id, amount) VALUES (101, 500) ON CONFLICT (sale_id) DO UPDATE SET amount = EXCLUDED.amount;

This command inserts a new row into the 'sales' table or updates the 'amount' if a row with the same 'sale_id' exists and the new amount is higher than the current amount.

Q30

Multiple Choice

Which SQL command performs an UPSERT operation using a subquery to determine the value to be inserted or updated in PostgreSQL?

SQL Code

INSERT INTO inventory (product_id, stock)
VALUES (100, (SELECT MAX(stock) FROM inventory) + 10)
ON CONFLICT (product_id)
DO UPDATE SET stock = inventory.stock + EXCLUDED.stock;

INSERT INTO inventory (product_id, stock) VALUES (100, (SELECT MAX(stock) FROM inventory) + 10) ON CONFLICT (product_id) DO UPDATE SET stock = inventory.stock + EXCLUDED.stock; INSERT INTO inventory (product_id, stock) VALUES (100, (SELECT stock FROM inventory WHERE product_id = 100) + 10) ON CONFLICT (product_id) DO UPDATE SET stock = inventory.stock + EXCLUDED.stock; INSERT INTO inventory (product_id, stock) VALUES (100, (SELECT MAX(stock) FROM inventory) + 10) ON CONFLICT DO NOTHING;INSERT INTO inventory (product_id, stock) VALUES (100, (SELECT MAX(stock) FROM inventory) + 10) ON CONFLICT (stock) DO UPDATE SET stock = inventory.stock + EXCLUDED.stock;

This command inserts a new row into the 'inventory' table with a stock value based on a subquery or updates the existing stock by adding the new stock value to the current one.

PostgreSQL Database Quiz Questions