Using returning on insert in laravel and postgresql

Question

I'm trying to insert values into postgresql 10 with Laravel 5.6. I have a table like this:

test
----
test_id     (identity generated by default, primary key)
test_name

My problem is getting the test_id that was just inserted back.

$test_id = \DB::connection('pgsql')->insert(
    'INSERT INTO test (test_name) VALUES (?) RETURNING test_id', 
    ['hello world']
);

If I print out $test_id, it just prints 1. I'm doing this over a loop, with different names. Everything inserts correctly, but even if test_id is 6 in the database, I keep getting back 1.

How can I correctly return $test_id in Laravel?

@berkaykılıç I get an error: Trying to get property 'test_id' of non-object — Alex
– Alex, Commented May 23, 2018 at 19:13

Andrew · Accepted Answer · 2018-05-23 20:04:06Z

4

Can use insertGetId, eg:

$id = DB::table('users')->insertGetId(
    [ 'name' => 'first' ]
);

or try this:

$id = DB::getPdo()->lastInsertId();

In your case this worked:

$id = DB::connection('pgsql')
    ->table('test')
    ->insertGetId(['test_name' => 'hello world'], 'test_id');

https://laravelcode.com/post/laravel-55-get-last-inserted-id-with-example

edited May 23, 2018 at 20:04

answered May 23, 2018 at 19:10

Andrew

20.4k13 gold badges111 silver badges122 bronze badges

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

3 Comments

Alex Over a year ago

Using insertGetId gives me the error: Call to undefined method Illuminate\Database\PostgresConnection::insertGetId(). Any idea why?

Andrew Over a year ago

ah, guess method doesnt belong to post gres connection but to the query builder. updated answer, maybe u can see a way to use it.

Alex Over a year ago

I'm using multiple types of databases so DB::table wouldn't work. I did figure it out based on the table method. If you want to update your answer I can accept it, otherwise I'll post my own. The final query looks like: $test_id = DB::connection('pgsql')->table('test')->insertGetId(['test_name' => 'hello world'], 'test_id'); I guess as a second parameter you can specify what you want it to return.

Romain 'Maz' BILLOIR · Accepted Answer · 2020-03-24 15:35:53Z

1

kind of old but I've just solved this issue in a funny way and wanted to share my answer.

It's kind of weird but the use of select and selectOne laravel function is the key:

// Single row insertion
$insertion = DB::selectOne("INSERT INTO cities (user_uuid, postal_code) VALUES (?, ?) RETURNING uuid", [$name, $postal_code]);

echo $insertion->uuid;


// Multiple rows insertion
$insertion = DB::select("INSERT INTO cities (user_uuid, postal_code) VALUES (?, ?), (?, ?) RETURNING uuid", [$name, $postal_code, $name, $postal_code]);

foreach($insertion as $city) {
    echo $city->uuid;
}

Have fun :)

answered Mar 24, 2020 at 15:35

Romain 'Maz' BILLOIR

5266 silver badges13 bronze badges

Collectives™ on Stack Overflow

Using returning on insert in laravel and postgresql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related