Auto-generated id not returned on insert?

[whitehorsesoft]

In the docs for writing manual SQL, the insert section says:

...insert should return the autogenerated primary key integer for the row inserted (if any)

I'm unable to get the PK as stated here. Should I be doing something else?

Versions

PostgreSQL 15.1
Ruby 3.1
Sequel Gem 5.64
PG Gem 1.4.5

Schema

create table t1
(
    id integer generated always as identity
        constraint t1_pk primary key,
    c1 text
)
;

Code

# CONNSTR set properly (not shown here)
DB = Sequel.connect(CONNSTR)
ds = DB['insert into test.t1 (c1) values (?)', 'hi there']
# incorrectly (?) sets id to nil, even though value is saved correctly in db with auto-generated PK
id = ds.insert

[jeremyevans]

The postgres adapter doesn't set the auto-generated PK if you are using custom SQL for the insert. Among other things, the postgres adapter uses RETURNING to get the primary key value, which it cannot do if you are providing custom SQL. Maybe the documentation you pointed to should be updated to specify that it depends on the adapter in use, and it the case on PostgreSQL.

[whitehorsesoft]

Prob a low priority, I doubt there are many who are manually writing SQL inserts like this. I'll do a workaround. Thank you for the quick reply!