How to Insert Into PostgreSQL

Here's the shortest and easiest way to insert in PostgreSQL. You only have to specify the values, but you have to pass all values, and they have to be in order. So if you have 10 columns, you have to specify 10 values.

-- Assuming the users table has only three columns: first_name, last_name, and email, and in that order
insert into users values ('John', 'Doe', 'john@doe.com');

If you have many columns, but only want to specify some:

insert into users (first_name) values ('John');

If you want to insert into a JSON column, just wrap the valid JSON in a single quoted string:

insert into users (preferences) values ('{ "beta": true }');

If inserting a row would violate a unique constraint, you can use Postgres' on conflict clause to specify what to do when that happens. For example, imagine you have a webhook system and you want to gracefully handle duplicate webhooks:

-- If we already recorded this webhook, do nothing
insert into stripe_webhooks (event_id)
values ('evt_123')
on conflict do nothing;

You can also do "upserts" (update or insert) in Postgres:

-- Assuming you have a unique index on email
insert into users (email, name)
values ('john@doe.com', 'Jane Doe')
on conflict (email) do update set name = excluded.name; -- excluded.name refers to the 'Jane Doe' value