Inserting Data in Postgres

Sep 7, 2024

This post is more of a reference of all the ways to insert data into a Postgres database.

Let’s start out with an NBA schema.

1
create table teams (
2
  id serial primary key,
3
  city text not null,
4
  name text not null,
5
  unique (name)
6
);
7

8
create table players (
9
  id serial primary key,
10
  team_id int references teams(id),
11
  name text,
12
  height int
13
);

Insert with Values

1
insert into
2
  teams (city, name)
3
values
4
  ('Atlanta', 'Hawks'),
5
  ('Boston', 'Celtics'),
6
  ('Brooklyn', 'Nets'),
7
  ('Charlotte', 'Hornets'),
8
  ('Chicago', 'Bulls'),
9
  ('Cleveland', 'Cavaliers'),
10
  ('Dallas', 'Mavericks'),
11
  ('Denver', 'Nuggets'),
12
  ('Detroit', 'Pistons'),
13
  ('Golden State', 'Warriors'),
14
  ('Houston', 'Rockets'),
15
  ('Indiana', 'Pacers'),
16
  ('Los Angeles', 'Clippers'),
17
  ('Los Angeles', 'Lakers'),
18
  ('Memphis', 'Grizzlies'),
19
  ('Miami', 'Heat'),
20
  ('Milwaukee', 'Bucks'),
21
  ('Minnesota', 'Timberwolves'),
22
  ('New Orleans', 'Pelicans'),
23
  ('New York', 'Knicks'),
24
  ('Oklahoma City', 'Thunder'),
25
  ('Orlando', 'Magic'),
26
  ('Philadelphia', '76ers'),
27
  ('Phoenix', 'Suns'),
28
  ('Portland', 'Trail Blazers'),
29
  ('Sacramento', 'Kings'),
30
  ('San Antonio', 'Spurs'),
31
  ('Toronto', 'Raptors'),
32
  ('Utah', 'Jazz'),
33
  ('Washington', 'Wizards');

Using default values can be helpful.

1
create table s(
2
  id serial primary key
3
);
4

5
insert into s
6
default values
7
returning *;

id
1

Insert Select

1
insert into
2
  players (name, team_id)
3
select
4
  'Mascot' as name,
5
  teams.id as team_id
6
from
7
  teams;

If you’re inserting a lot of data, this is a good way to go.

1
-- this inserts 10K rows
2
insert into
3
  players (name, team_id)
4
select
5
  concat('Player ', t.number) as name,
6
  teams.id
7
from
8
  generate_series(1, 10 * 1000) as t(number)
9
cross join teams
10
where
11
  teams.name = 'Hawks';

Upserting, or Handling Conflicts

Let’s say we want to insert a row, but only if there’s not a conflict.

1
insert into
2
  teams (city, name)
3
values
4
  ('New Jersey', 'Nets') on conflict (name) do nothing;

What if we want to

insert the row if it doesn’t exist
do nothing if it does
but return the row regardless

1
insert into
2
  teams (city, name)
3
values
4
  ('New Jersey', 'Nets') on conflict (name) do update
5
set
6
  -- this does nothing
7
  city = teams.city
8
returning *;

id	city	name
3	Brooklyn	Nets

Or, we may want to actually update the existing row. This is the “upsert” pattern.

1
insert into
2
  teams (city, name)
3
values
4
  ('New Jersey', 'Nets') on conflict (name) do update
5
set
6
  city = excluded.city
7
returning *;

id	city	name
3	New Jersey	Nets

Note that there needs to be a unique constraint on whatever is in the on conflict clause. If not, you will get ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification.

Furthermore, we can call out the constraint by name. To demonstrate, let’s add a number column to the players table and make it unique per team.

1
alter table players add column number int;
2
alter table players add constraint players_number_unique unique (number, team_id);

Now, let’s try to violate it.

1
insert into players (name, number, team_id)
2
select t.name, 0, teams.id
3
from unnest(array['LeBron James', 'Anthony Davis']) as t
4
cross join teams
5
where teams.name = 'Lakers';

Sweet, we get ERROR: duplicate key value violates unique constraint "players_number_unique".

1
insert into players (name, number, team_id)
2
select t.name, 0, teams.id
3
from unnest(array['LeBron James', 'Anthony Davis']) as t
4
cross join teams
5
where teams.name = 'Lakers'
6
on conflict on constraint players_number_unique do nothing
7
returning *;

id	team_id	name	height	number
9	14	LeBron James		0

That is cool, but it is usually better just to specify the column names directly.

Upserting via a Merge Statement

There’s another way to do insert on conflict - the merge statement.

1
merge into teams as target
2
using (values ('New Jersey', 'Nets')) as source (city, name)
3
on target.name = source.name
4
when not matched then
5
  insert (city, name)
6
  values (source.city, source.name)
7
when matched then
8
  update
9
  set
10
    city = source.city,
11
    name = source.name;

You can do a bit more with merge than you can with insert on conflict.

With a merge statement, you can have multiple matching conditions, while insert on conflict only allows one.
With a merge statement, you can have complex conditions in the matching clause, rather than only being able to match on a unique constraint.
With a merge statement, you can delete a row on a match.

Insert in a CTE

To set this example up, let’s change our schema a bit.

1
alter table teams drop constraint teams_name_key;
2
alter table teams add column formerly int references teams(id) on delete set null;

Now, we can do an insert statement in a CTE:

1
with
2

3
supersonics as (
4
  insert into
5
    teams (city, name)
6
  values
7
    ('Seattle', 'SuperSonics')
8
  returning *
9
)
10

11
update teams
12
set
13
  formerly = supersonics.id
14
from supersonics
15
where
16
  teams.name = 'Thunder'
17
  and teams.city = 'Oklahoma City'
18
returning teams.*;

id	city	name	formerly
21	Oklahoma City	Thunder	34

Copying Data From a File

$ cat teams.csv
id,city,name,formerly
1,Boston,Celtics,
2,Los Angeles,Lakers,
3,Chicago,Bulls,
4,Atlanta,Hawks,
5,San Antonio,Spurs,
6,Miami,Heat,
7,Houston,Rockets,
8,New York,Knicks,
9,Syracuse,Nationals,
10,Detroit,Pistons,
11,Philadelphia,76ers,9

Let me start with a clean slate for this:

1
truncate teams cascade;

Now, I can copy the data in, using the copy command.

1
copy teams (id, city, name, formerly)
2
from
3
  '/path/to/teams.csv' delimiter ',' csv header;
4

5
select * from teams;

id	city	name	formerly
1	Boston	Celtics
2	Los Angeles	Lakers
3	Chicago	Bulls
4	Atlanta	Hawks
5	San Antonio	Spurs
6	Miami	Heat
7	Houston	Rockets
8	New York	Knicks
9	Syracuse	Nationals
10	Detroit	Pistons
11	Philadelphia	76ers	9

You can even insert data from a program in this way:

1
copy teams (id, city, name)
2
from
3
  program 'bash -c "echo -e ''1,Boston,Celtics\n2,Los Angeles,Lakers\n3,Chicago,Bulls''"'
4
with
5
  (format csv, delimiter ',');

Wow! You read the whole thing. People who make it this far sometimes want to receive emails when I post something new.

I also have an RSS feed.