The name column has a unique constraint to guarantee the uniqueness of customer names. M圜offee table consists of three columns: id, name, and preference. Create a test table CREATE TABLE m圜offee ( In Postgres 9.5 (and later), this could be easily achieved using single line INSERT ON CONFLICT statement. Until now, to solve this, this would require writing a complex stored procedure in SQL, PL/pgSQL, C, Python, etc. So how can I record all drink preference of for my customer in one place, without creating a duplicate row in database. There’s one problem though, the preferences change over time. I own a coffee shop, and I want to keep a record of drink preferences for all my customers. Here, let me explain this using coffee shop scenario – coffee makes almost everything easy 😛 Coffee Shop Scenario ☕ Postgres 9.5 (and later) has introduced UPSERT (INSERT ON CONFLICT) operation, that can allow either updating the data for duplicate row in or just silently skipping the duplicate row, without any error. During the insert, if a duplicate record is found, entire insert batch will fail with “ duplicate key violation error“. Say, we have a use case to insert all records from a text file sent by an external app every day into our database table that has a PRIMARY KEY defined. UPSERT is a combination of Insert and Update, driven by a “ PRIMARY KEY” on the table. Most modern-day relational database systems use SQL MERGE (also called UPSERT) statements to INSERT new records or UPDATE existing records if a matching row already exists.

Postgresql insert into table