RETURNING
That’s possible with a single round-trip to the database:
INSERT INTO tbl(filename)
VALUES ('my_filename')
RETURNING tbl_id;
tbl_id would typically be a serial or IDENTITY (Postgres 10 or later) column. More in the manual.
Explicitly fetch value
If filename needs to include tbl_id (redundantly), you can still use a single query.
Use lastval() or the more specific currval():
INSERT INTO tbl (filename)
VALUES ('my_filename' || currval('tbl_tbl_id_seq') -- or lastval()
RETURNING tbl_id;
See:
- Reference value of serial column in another column during same INSERT
If multiple sequences may be advanced in the process (even by way of triggers or other side effects) the sure way is to use currval('tbl_tbl_id_seq').
Name of sequence
The string literal 'tbl_tbl_id_seq' in my example is supposed to be the actual name of the sequence and is cast to regclass, which raises an exception if no sequence of that name can be found in the current search_path.
tbl_tbl_id_seq is the automatically generated default for a table tbl with a serial column tbl_id. But there are no guarantees. A column default can fetch values from any sequence if so defined. And if the default name is taken when creating the table, Postgres picks the next free name according to a simple algorithm.
If you don’t know the name of the sequence for a serial column, use the dedicated function pg_get_serial_sequence(). Can be done on the fly:
INSERT INTO tbl (filename)
VALUES ('my_filename' || currval(pg_get_serial_sequence('tbl', 'tbl_id'))
RETURNING tbl_id;
db<>fiddle here
Old sqlfiddle