Inserting multiple rows, some columns with the same value

Question

I'm using Postgres 14.4

I have a table:

users
-----
id
email
is_active
created_at
updated_at

I would like to be able to insert a batch of users, with some columns being set to the same value for all rows:

INSERT INTO users 
(name, email)
('Jimmy Smith', '[email protected]'),
('Dave Jones',  '[email protected]'), 
DEFAULT VALUES (is_active, created_at, updated_at) (TRUE, current_timestamp, current_timestamp)

I cannot change the DDL of the table to have these defaults.

The above code obviously won't work, but is what I'd imagine the DEFAULT VALUES syntax to achieve. I imagine it would be possible to do with joining to a temporary table.

Is there any way of doing this without having to repeat the 'active', 'created_at' and 'updated_at' values for each row in the SQL statement?

you mean this? dbfiddle.uk/…

Jim Jones
– Jim Jones

2022-08-03 10:27:15 +00:00
Commented Aug 3, 2022 at 10:27 — Jim Jones
– Jim Jones, Commented Aug 3, 2022 at 10:27
note that name has no column in the target table

Jim Jones
– Jim Jones

2022-08-03 10:28:57 +00:00
Commented Aug 3, 2022 at 10:28 — Jim Jones
– Jim Jones, Commented Aug 3, 2022 at 10:28

Jim Jones · Accepted Answer · 2022-08-03 10:51:52Z

1

If an ALTER TABLE to change the columns' default values isn't an option, you can join the values to be inserted and the default values in a SELECT statement and redirect it to the INSERT, e.g.

INSERT INTO users (email,is_active, created_at, updated_at) 
SELECT email,is_active, created_at, updated_at
FROM 
 (VALUES ('Jimmy Smith', '[email protected]'),
         ('Dave Jones',  '[email protected]')
 ) insert_values (name,email),
 (VALUES (true, current_timestamp, current_timestamp)) 
   default_values (is_active, created_at, updated_at);

Despite of being less bulky, I see no real improvement in this approach. I would still prefer to, if possible, alter the columns' default values, or just repeat the values in every INSERT record:

INSERT INTO users (email,is_active, created_at, updated_at) VALUES
('[email protected]',TRUE, current_timestamp, current_timestamp),
('[email protected]',TRUE, current_timestamp, current_timestamp)

If the amount of columns used in the INSERT vary and you're not sure when a NULL might come in, use COALESCE with the default value in the SELECT:

INSERT INTO users (email,is_active, created_at, updated_at) 
SELECT 
  email,
  COALESCE(insert_values.is_active::boolean,  default_values.is_active),
  COALESCE(insert_values.created_at::timestamp, default_values.created_at), 
  COALESCE(insert_values.updated_at::timestamp, default_values.updated_at)
FROM 
 (VALUES ('[email protected]',false,NULL,NULL), -- overriding 'is_active'
         ('[email protected]',false,NULL,NULL)   -- overriding 'is_active'
 ) insert_values (email,is_active, created_at, updated_at),
 (VALUES (true, current_timestamp, current_timestamp)) 
   default_values (is_active, created_at, updated_at);

Demo: db<>fiddle

edited Aug 3, 2022 at 10:51

answered Aug 3, 2022 at 10:32

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kevin Sedgley Over a year ago

Perfect - thanks Jim. It's adding a large amount of users, and I wanted it to be editable for non devs.

Jim Jones Over a year ago

@KevinSedgley I see. So that might indeed make the statement more readable (although not faster). I added a slight improvement, in case some inserted records do have a value in the default columns. dbfiddle.uk/… consider accepting the answer in case it solves your problem. cheers

Collectives™ on Stack Overflow

Inserting multiple rows, some columns with the same value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related