Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I need a way to insert multiple rows into sql table, and return all inserted primary key ids with one sql query.
A working raw sql query (which i tested in psql) looks like this:
insert into technologies (name, user_created) values ('test 1', 4),('test_2', 4) returning id
In docs i only found method allowing to insert only one row and return its id:
DB::table('technologies')->insertGetId(['name'=> 'two', 'user_created' => 4], 'id');
If it's not possible is there a potential workaround for this? Probably extending Laravel DB class and implementing a method for this behavior.
need a way to insert multiple rows into sql table, and
return all inserted primary key idswith one sql query.
You can't even do that in pure sql, or at least mysql/mariadb and probably some others. At least not efficiently and accurately. Google "mysql bulk insert ids" or whatever db you're using. They usually only return the single most recently inserted id.
Lock out outsiders from using.
Get current max id.
Do your inserts.
Query where id is greater than the max id mentioned above.
You won't be able to accomplish in one single query.
@CRONIX - insert into technologies (name, user_created) values ('test 1', 4),('test_2', 4) returning id
not sure about mysql, but in psql works fine
Yes, but the point is Query Builder/Eloquent mostly only have commands for sql that is common to all db's it supports out of the box. It's basically "the least common denominator" for all platforms. If MySQL has feature x, but postgres doesn't have it, most likely it's not in there, etc. The point of it is to be able to swap out database engines without having to rewrite code/queries. You'll have to write your own postgres specific implementation for that, or just use raw queries.
I came up with the following solution for psql:
$vals = "(?, ?), (?, ?)";
DB::select(DB::raw("insert into technologies (name, user_created) values " . $vals . " returning id"), array('test 1', 4, 'test2', 4));
$vals can be adjusted to the number of rows, to be inserted.
Correct me if i'm wrong, but the statement should be safe from sql injection attacks.
I think an idea I saw on stack overflow is the best idea for all of these databases.
First make a column for some marker like a hash, and when you insert all of the multiple rows, just include the other column with the same value for all in the mass insert, sort of like an md5sum of the unixtimestamp in milliseconds or similar.
Then do a select where grouping column is equal to said md5sum, and you don't have to worry about multiple users, race conditions, or the like...
Please or to participate in this conversation.