Insert returning postgresql. I have tried putting a SELECT in RETURNING without luck.

Insert returning postgresql But the RETURNING clause is a postgres-specific extension and - at present - doesn't support an ORDER BY clause for its results; instead, its ordering is set by the ordering of the query it's attached to. How to insert autoincrementing id from one table into another in one command (using returning)? 0. To avoid that, I use command below, but how to get pid after inserted?. INSERT INTO AUTHOR (LAST_NAME) VALUES ('Doe') RETURNING I'm trying to insert one record into Postgresql, get inserted id, and use it in following 4 other inserts. insert into test VALUES(1, 2); ERROR: cannot perform INSERT RETURNING on relation "test" HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause. reference. INSERT INTO FROM SELECT RETURNING id mappings. PostgreSQL. Postgresql insert if not exists. 1. Is there a better way to batch insert and get the IDs? 6. "UserProfileId" INTO _UserProfileId; _UserProfileId is a declared integer A common complaint with the normal approach of using a BEFORE INSERT trigger for partitioning is that the return NULL; in the trigger prevents the normal functioning of INSERT RETURNING from working. oid is always 0 (it used to be the OID assigned to the inserted row if count was exactly one and the target table was declared WITH OIDS and 0 otherwise, but creating a table WITH OIDS is not supported If you INSERT and then SELECT if that fails, the row can be deleted in between. INSERT into table if doesn't exists and return id in both cases. Improve this question. I am confused why that code fails when your foreach works; Use some kind of a stored procedure to perform the batch insert and return a list of ids. Postgresql: UPSERT / INSERT INTO defining a conflict. I would like to get the whole data with the joins of the foreign keys. How to insert a newly generated id into another table with a trigger in postgresql? 0. Return ID of newly inserted row on a PostgreSQL database using C# and Npgsql? Hot Network Questions There are more issues: If functions returns more than one row, then should to use SETOF keywords after RETURNS. fetchone() is called on a cursor after having executed an INSERT/UPDATE command but lacked a return value (RETURNING clause) an exception will be raised: ProgrammingError('no results to fetch')) Consider using a WITH structure to pass the data from the insert to a query that can then be joined. Cant use id in function after insert returning into. time, NOW()) RETURNING aPrimaryKey INTO id; INSERT INTO tableB (aPrimaryKey, someCol1) VALUES (id, NEW. 2. But you can simply move the insert into a second cte, and then have a single SELECT at the end that returns the data that was found PostgreSql INSERT FROM SELECT RETURNING ID. This clause will be used to compute the outputs if the rule is triggered by an INSERT RETURNING, UPDATE There are several ways to do it: Assuming a. Modified 1 year, 2 months ago. Using this trick in production is not recommended, because other systems might work differently. Update: I actually found a very similar question (for the conversion from SQL server, not PostgreSQL, into Oracle). For example my table might be: CREATE TABLE names I'm trying to perform multiple inserts from 1 sql query. Outputs. Notice that RETURNING clause acts like SELECT over newly inserted fields. You can create an ALSO rule to return the value of the new row, something like this: > CREATE RULE return_data AS ON INSERT TO FOO DO ALSO SELECT NEW. The RETURNING clause allows one to return the rows that one inserts, updates, or deletes. You can use the RETURNING clause in INSERT, UPDATE, and DELETE queries to get the rows affected. A row is returned for each row processed. I have a situation where I very frequently need to get a row from a table with a unique constraint, and if none exists then create it and return. I know there is query like. – mkopriva. PostgreSQL: How do I have my I want to insert data into 3 tables with a single query. This is a really neat/useful feature as it can prevent the need to run multiple queries. Something like that: INSERT INTO TEMP temp1 INSERT INTO table1(value1,value2) SELECT value3,value4 FROM table2 RETURNING id How can I do it? DBMS is PostgreSQL Summary: in this tutorial, you will learn how to use the PostgreSQL INSERT statement to insert a new row into a table. Sometimes it is useful to obtain data from modified rows while they are being manipulated. The INSERT, UPDATE, DELETE, and MERGE commands all have an optional RETURNING clause that supports this. Is this even possible or do I just have to do another query after inserting the data? Insert statement: The RETURNING clause not work at the transaction level, it works on on the row level. *; CREATE RULE > INSERT INTO foo VALUES (1, 'test'); a | b ---+----- 1 | test This works because PostgreSQL 's implementation evaluates only as many rows of a WITH query as are actually fetched by the parent query. y from A join B on A. The count is the number of rows inserted. 19 on GNU/Linux. If the INSERT command contains a RETURNING clause, the result will be similar to that of a PostgreSql INSERT FROM SELECT RETURNING ID. You need to use the INTO clause in the RETURNING to set the value being returned into your variable: DECLARE myid OAMENI. For those unfamiliar with INSERT. This may seem pointless, as one would expect to already And am executing an insert into postgres like this: public async Task<int> CreateItem(Tenant item) { return await db. Basic Syntax. And you can only have one "final" statement in a CTE, not an insert and a select. 99, 275) I have two tables where relationship one to many, and I want INSERT to mytable some values and RETURNING multiple values INTO variables to use them later. My research shows a few tricks being used for getting back the In this tutorial, you will learn how to use a single PostgreSQL INSERT statement to insert multiple rows into a table. This would work fine with a SELECT returning the values, but I'm having trouble with the nested insert. I want to store return values (these ids) in some temp table. Follow edited May 23, INSERT INTO my_table VALUES (a, b, c) RETURNING (SELECT x FROM x_table WHERE xid = a), (SELECT y FROM y_table WHERE yid = b), (SELECT z FROM z_table WHERE zid = c) I have no idea why the way it's stated in the question is invalid but at least this works. PlpgSQL functions requires RETURN statement - in this case RETURN QUERY. 1. Returning Data From Modified Rows. If the INSERT command contains a RETURNING clause, the result will be similar to that of a PostgreSQL allows you to return data from the inserted rows using the RETURNING clause. That's the case for The select count(*) from foo inside the returning clause is evaluated before the insert, and then treated as a constant in the returning clause, as explain clearly shows: > explain insert into foo (a) values (1) returning (select count(*) from foo); ┌───────────────────────────────────────────────────────────────────────────┐ Specify the * after the RETURNING clause to get the newly inserted data of all the table columns: INSERT INTO team (name, age) VALUES ('Molo', 27), ('King', 35) RETURNING *; The output authenticates the working of the RETURNING Clause with the INSERT query. Otherwise oid is zero. RETURNING. In PG admin it comes right back, but not sure how to get it from my prepared statement: Use data-modifying CTEs to chain your three INSERTs. Stack Overflow. To break down what I'm trying to do here is the DB structure: links: - id // uuid_generate_v4() - hash permissions: - id // to emphasise: dapper can't magically create a true bulk-insert out of nowhere. In PostgreSQL, users can My database driver for PostgreSQL 8/9 does not return a count of records affected when executing INSERT or UPDATE. A WITH clause is, as far PostgreSQL allows to return fields of a newly created row after an INSERT statement, and I want to use it to return the auto-generated BIGSERIAL id of newly created records. The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. 1: with rows as ( INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id ) INSERT INTO Table2 (val) SELECT id FROM The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). If you pass it an array or list, it simply does a foreach internally - unless you're using the async API and explicitly enable pipelining (if your provide supports it), in which case a number of commands cans be pipelined for perfomance. w = B. Once I insert the record, I want to ABORT the request as to avoid duplicate data (by not inserting into parent table) so I am using return NULL in the trigger. This feature eliminates the need for multiple PostgreSQL allows inserting multiple rows in a single ‘INSERT’ statement and using the ‘RETURNING’ clause to retrieve each new row’s data. I tried to use returning clause on automatically inserted column that is also constrained as primary key. Use returned value of INSERT RETURNING in multiple So as you all know, we've been working on postgres support for the up-and-coming node. Let’s add some sample data: CREATE TABLE birthdays (name TEXT, birthday DATE, age SMALLINT); INSERT INTO birthdays VALUES ('James', '1996-01-20', 22), ('Peter', '1990-06-18', 28), ('John', '1993-09-21', 25); By using the RETURNING statement one can return any columns from If I try to insert with just 1 conditional rule . CREATE FUNCTION MyFuncName() RETURNS trigger AS $$ DECLARE id integer; BEGIN INSERT INTO tableA (time) VALUES COALESCE(NEW. From Java, I'm calling a prepared statement in Postgresql with an insert that has a RETURNING clause for my identity column. Query or db. How to insert and then update returned Id from insert query as returning id in a single command in postgres? 3. This is very handy indeed if you want to then go on and do RETURN (INSERT INTO configuration_dates ( weekly_date_configuration_id, "from", "to", price, activity_configuration_id ) VALUES ( wdc_id, from_ts, from_ts + The RETURNING clause is also very useful with INSERT SELECT. The PostgreSQL INSERT statement offers a robust solution for adding data to your database, whether you’re handling single entries or managing bulk inserts. When I thought I figured it out, I tried I have a query that's supposed to insert a new row, then return it. On successful completion, an INSERT command returns a command tag of the form INSERT oid count. The PostgreSQL INSERT statement allows you to insert a new row into a table. ⌕ Learn; Download / Pricing; Aurora Postgres, CockroachDB, DuckDB, Firebird, Postgres, SQLite, YugabyteDB. Exec. Here’s the basic syntax of the INSERT statement:. Is it doable in one query? My current state of SQL code: UPDATE The returning clause can only return data that was affected by the insert. Postgres allows a query clause in an INSERT, for example: INSERT INTO films SELEC PostgreSQL multi INSERTRETURNING with multiple columns (2 answers) Insert inserted id to another table (2 answers) PostgreSQL next value of the sequences? (8 answers) Closed 4 VALUES ('Tom', 'Hanks') returning id_act ) insert into movies (act_id) select id_Act from new_actor; Online example. Improve this answer. The target is on Postgres DB. QueryRow, that means that you should NOT use db. 0. Further the clause requires what would be a valid select, so * or a column name. "UserProfile" ("FirstName") VALUES('John') RETURNING "UserProfileId" INTO _UserProfileId; throws an ambiguous reference error, however this correctly executes: INSERT INTO "dbo". Perhaps there's a better alternative but I can only think of joining back to the 2 tables. Otherwise, you definitely have to do an INSERT, so we need the INSERT to also tell us the id of any existing row. The INSERT, UPDATE, and DELETE commands all have an optional RETURNING clause that supports this. Here example: IF NOT EXISTS (SELE Skip to main content. with ins (id, x, y) as ( insert into new_table (x, y) select A. If count is exactly one, and the target table has OIDs, then oid is the OID assigned to the inserted row. 8. To obtain value of SERIAL column in another way, you would have to get current value of test_test_id_seq discrete sequence and you’d have to put it inside transaction block to be sure it’s accurate. Say we have the following tables: CREATE TABLE hosts (host_id SERIAL, name CHARACTER VARYING(20)); CREATE TABLE interfaces (interface_id SERIAL, host_id INTEGER, name CHARACTER VARYING(10), iface_ip INET); INSERT INTO hosts I'm trying to use PostgreSQL's RETURNING clause on an UPDATE within in UPDATE statement, and running into trouble. WITH s AS ( SELECT pid FROM area WHERE area_name = 'testarea' ), i AS ( INSERT INTO area (area_name, active, users_pid) SELECT 'testarea', true, 0 WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING pid ) The RETURNING keyword in PostgreSQL gives you an opportunity to return, from the insert or update statement, the values of any columns after the insert or update was run. Original query has no result hence no a_id PostgreSQL INSERT or UPDATE values given a SELECT result after a If you want to insert all three rows in one statement, you can use: INSERT INTO "parentTeacherCon_teacher" (name, grade_id) SELECT 'foo bar', g. In an UPDATE, the data available to RETURNING is the new content of the modified row. Postgresql insert if does not exist. As part of the insert, I'd like to return both the reference and corresponding id's of the newly created records in order to create a mapping record in another system. So, insert the row, add RETURNING to get the id of the existing row. INSERT INTO books (title, author, price, pages) VALUES ('Slaughterhouse-Five', 'Kurt Vonnegut', 8. x, B. Even if other sessions insert something into a between the two statements, lastval() would still return For a bulk insert, I have a foreign reference value. The inserted data contains foreign keys. If you use the RETURNING clause you have to use db. You can chain data modifying CTEs:. 14. Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company, and our products INSERT INTO table2(name) INSERT INTO table1(name, address) VALUES ('me', 'home') RETURNING name; And then I would expect a new record in table2 with 'me' in name. To make sure that a value is generated, you need to tell Postgres to use the default value for that postgresql + dblink + insert with returning. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified If cursor. How can I obtain the last inserted row with INSERT SELECT in PostgreSQL? 0. Postgres: insert multiple rows on conflict update not working. One can use the RETURNING clause on INSERT statements as well. So you can trust the ordering of RETURNING but only if your main query specifies an ordering. Return id from postgresql insertion. In this simplified example id is a SERIAL:. Still, I'd love to hear a more simple answer to that if possible. You can always join it back in some way like below and get the file_id that way:. RETURNS SETOF int AS $$ BEGIN RETURN QUERY INSERT INTO taba(a) VALUES(1),(2) RETURNING *; RETURN; END; $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# select * from fx(); fx ---- 1 2 (2 The documentation states that the columns returned by the insert need to be actually inserted columns:. Ask Question Asked 6 years ago. To me it looks like you are using plSQL - One approach would be to create a non-constrained (no unique indexes) table to insert all your data into and do a select distinct from that to do your insert into your hundred table. "UserProfile". Hot Network Questions How to balance authorship roles when my contributions are substantial but I am evaluated on last Postgresql supports the RETURNING clause that can project a set updated, inserted en even deleted exactly like in a SELECT statement: INSERT INTO employee (name, password) VALUES ($1::text, $2::text) RETURNING employee_id, name, '****' as password ; employee_id | name | password 1 | john | **** (1 row) It will return the asked fields of the Outputs. Insert a row with no value to get the generated id. id%TYPE; INSERT INTO oameni VALUES (default,'lol') RETURNING id INTO myid; You also need to specify the data type of your variable; I'm glad to see postgresql supports %TYPE and %ROWTYPE. Something like this: WITH ins1 AS ( INSERT INTO table1 (username, name, surname) VALUES ('johnee','john','smith') RETURNING user_id ) , ins2 AS ( INSERT INTO table2 (user_id, password) SELECT ins1. Now my problem is that I want to do a batch insert inside a transaction and get ALL the generated keys. How to assign returning value from Postgresql insert to a variable? Hot Network Questions Almost every Outputs. About; Products such as SELECT or INSERT RETURNING. Insert and return id no PostgreSQL - The RETURNING Clause. "UserProfile" ("FirstName") VALUES('John') RETURNING "dbo". Example:-- Setup some initial tables create table colors ( id SERIAL primary key, color VARCHAR UNIQUE ); create table animals ( id SERIAL primary key, a_id INTEGER references colors(id), animal VARCHAR UNIQUE ); -- provide some initial data in colors insert UPDATE with INSERT RETURNING value PGSQL. with new_t1 as ( insert into table1 (id) values (default) returning id ) insert into table2 (id) select id from new_t1; Note that insert into table1 (id) values (null) returning id would return null as you explicitly ask to insert NULL into that column. with w as ( insert into zip_collections (zips) select The command INSERT RETURNING c1 INTO v1 for a declared PLpgSQL variable is losing value on a second use. Postgres "On RETURN isn't a valid command in a rule definition; you can only use SELECT, INSERT, UPDATE, DELETE, and NOTIFY. If I'm catching on, it would look something like "INSERT INTO table (column2, column3) VALUES ('value1', 'value2') RETURNING id;" However, I can't find anything that helps me access this via PHP. using the ids returned from insert into, for record insertion with foreign key. INSERT oid count. grade_id FROM (SELECT 0 as grade_id UNION ALL SELECT 1 UNION ALL SELECT 3) g; but RETURNING can only access values from the inserted row, is there an alternative to do this? postgresql; Share. You can insert data into all columns or specific columns, insert multiple rows at once, and even insert data from other tables. Example: Insert a book and return its title and price. Share. This is primarily useful for The RETURNING clause in PostgreSQL is a powerful feature that allows developers to retrieve data directly after executing SQL operations such as INSERT, UPDATE, or DELETE. ( DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING * ) INSERT INTO WITH ins_chatroom AS ( INSERT INTO chatroom DEFAULT VALUES RETURNING chatroom_id ) INSERT INTO chatroom_user (chatroom_id, user_id) SELECT chatroom_id, u FROM ins_chatroom, unnest('{1,2,3}'::int[]) u -- input users as array RETURNING *; -- just to show result in fiddle fiddle. 2 (it should be the minimum version supporting RETURNING), precisely 8. Using "RETURNING" works fine for the target table. Question: How do I store this value in a variable that can be used to insert values into other tables? Note that I want to insert the generated id into multiple tables. create table test(a int); create or replace function foo(int) returns setof int as $$ begin return query insert into test select v from generate_series(1,$1) g(v) returning a; end; I have a PostgreSQL before insert trigger on create that basically redirects inserts into sub-tables. id) DELETE FROM newvals USING upd WHERE newvals. Update column value with trigger postgresql/ fetch value from insert query. Another trick with RETURNING is possible with RETURNING newvals. In the complete function below, the "v1" variable is q_file_id: it have a non-zero value at the first INSERT (jins) but, seems that its value is not the same in the second INSERT (ins2), or data dependency is not recognized. Example 2: Using RETURNING Clause with DELETE Statement. Viewed 11k times 2 I need to insert a record in remote database and get the id inserted. Modified 6 years ago. There is only one way to do it for primary keys with autoincrement (or serial) types, where you can access insertId and affectedRows fields. Here’s how this could look with In Postgres, the “RETURNING” clause is used with the INSERT, DELETE or UPDATE queries to retrieve the newly inserted, deleted, or updated data. One option is looping the transaction in the application until it works. Get id from INSERT or SELECT. The single row must have been inserted rather than updated. CREATE RULE "testRule2" AS ON INSERT TO test DO INSTEAD INSERT INTO test VALUES(new. *; If I try I think I understand how PostgreSQL and RETURNING works - I've found many, many resources. js environment, and we just got a great new example of Postgres' functionality that we're proud to say we support - specifically, the query format INSERT. ExecuteAsync($@"INSERT INTO tenants (Name) VALUES (@Name)", item); } I need the Guid back, to drive the subsequent logic. *) RETURNING test. 4. triggers BEFORE INSERT which directs new data to another table a_CURRENT_DATE - dynamically created when necessary by the trigger function. The count is the number of rows inserted or updated. On successful completion, an INSERT command returns a command tag of the form. WITH new_row AS ( INSERT INTO my_table ( some_row, some_other_row ) VALUES ( 0, The INSERT statement in PostgreSQL is used to add new rows to a table. id = upd. If the INSERT command PostgresQL INSERTs allow returning an output_expression using any subset of the inserted columns, but I cannot get the syntax right for using this feature with jOOQ. We’ve prepared an automatic way for you to handle such cases with Drizzle and automatically receive all inserted IDs as separate objects This page should help you out :) postgreSQL function for last inserted ID INSERT INTO tableName (column1, column2) VALUES ('column1val', 'column2val') RETURNING id; You can then use the returned id to update the original row. Viewed 4k times Part of PHP Collective I'm trying to update tbl_list_binder's tbl_id_lists, by adding just freshly inserted row to TABLE_LIST_ITSLEF using postgresql. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to You can do so starting with Postgres 9. With its flexible syntax, including the ability to insert multiple rows in one go, this command is For all version of PostgreSQL, you can create a trigger function for deleting rows from a table and inserting them to another table. Another workaround is to let the insert INSERT ON CONFLICT will still increase auto increment. This is the PL/pgSQL equivalen The RETURNING clause of the INSERT statement allows for specifying expressions that should be returned as query results from the row(s) that have been inserted by the statement. I mentioned this in How do I insert and return the ID in postgresql? 0. But it seems slower than bulk insert that is released in PostgreSQL 9. id, followed by a bare INSERT INTO testtable SELECT * FROM newvals? My idea with this: instead of filtering twice in INSERT (for the JOIN/WHERE and for the unique constraint), reuse the existence check results from the UPDATE, which are in RAM already, and may be much I am inserting data into a table and want to return the inserted data. Tx) (int64, error) { return insert(ctx, row, insertCheck, "checks", tx) } // insert inserts row into table using query SQL command // table The RETURNING and WITH PostgreSQL extensions make this possible. user_id, 'secret' FROM ins1 -- nothing to return here ) INSERT INTO table3 (user_id, @NimrodYonatanBen-Nes In general you can't rely on ordering. someValue); RETURN Sometimes it is useful to obtain data from modified rows while they are being manipulated. One workaround is to revert to using currval() for finding inserted ids; this unfortunately only works for single-row inserts. So, I change the insert command in the XML to use feature of PostgreSQL, add an resultType="long" attribute to the <insert> tag, and in the Java interface of the mapper I set the insertion method to return How to assign returning value from Postgresql insert to a variable? Hot Network Questions Does using multiple batteries in series or parallel affect mAh In the era where Mad Men is set, are smoke alarms not triggered by cigarette smoke? What is the flaw in the first solution given below? Limit the difference between two sliders in Manipulate closed form for an I'm trying to use a value returned by an INSERT RETURNING statement in multiple following INSERTs. See: Insert data in 3 tables at a time using Postgres; How to do an SQL UPDATE based on INSERT RETURNING id in Postgres? 0. For example: UPDATE I can return id as an INSERT result, like INSERT INTO table(id,field) VALUES($id,$value) RETURNING id; But i cannot return it and assign to variable into my I'm using PostgreSQL 8. The INSERT, UPDATE, DELETE, and MERGE commands all have an optional RETURNING clause that supports this. If you are making an update to the database and then In PostgreSQL, it is possible to put RETURNING at the end of an INSERT statement to return, say, the row's primary key value when that value is automatically set by a SERIAL type. _id is a serial column:. I can't get it to work. INSERT INTO "dbo". INSERT INTO table1(value1,value2) SELECT value3,value4 FROM table2 RETURNING id that returns set of ids. Ask Question Asked 8 years, 8 months ago. RDS can create serious bottlenecks in engineering productivity — is this you? See how Neon can help. After I insert a row I can get the generated key either by using 'RETURNING' or CURRVAL(). z -- whatever columns from MySQL itself doesn’t have native support for RETURNING after using INSERT. Context, row Row, tx *sqlx. Introduction to PostgreSQL INSERT statement. I have tried putting a SELECT in RETURNING without luck. postgreql insert when no row exists. To answer the question. INSERT INTO table1 (column1, column2, ) VALUES (value1, value2, In this Im working with Postgres, using SERIAL as my primary key. RETURNING, this type of query allows us to do just what it says - return a value, or I am using PostgreSQL 8. id, B. PostgreSQL offers the non-standard syntax "RETURNING" which seems like a good . Hot Network Questions A prime number in a sequence with number 1001 Interchanging a The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated, if an ON CONFLICT DO UPDATE clause was used). . It returns the affectedrows (1). Of the three methods that I see the last one seems to preserve both the efficiency of batch insert and return the ids, but it is also the most complex for me as I have never written stored procedures. My goal is to insert multiple rows with the help of PL/pgSQL and return the id-s of the inserted records back in a recordset. z returning id, x, y ) -- insert into another_table (id, z) select ins. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be We are using Mulesoft for sending bunch of records from one system to another. 3. 2. You are much better off using an INSTEAD OF INSERT trigger here:. INSERT INTO T1 (C2,C3) VALUES (V2, V3) returning to_char(C1,'99') into var_id; in postgres which returns the generated id for the newly created record. About; Products postgresql insert with multiple selects. This assumes that new_table has a unique constraint on (x,y) and that these columns are not nullable:. The trigger returns NULL so actual INSERT into table a is suppressed. Time `db:"start"` } func InsertCheck(ctx context. Create, Insert, and return BYTEA value. You need connection, based on that you can execute your insert with returning as with you sqlCommand. It is useful to visualize the current operation by placing the By using the RETURNING keyword on the end of my insert query, I can have PostgreSQL return those new values to me as part of the same operation. This is what I have so far: do $$ declare gameId integer begin fn('INSERT INTO test(id, name) VALUES(42, ''foo'')') It should return the set of rows inserted (or modified in the generic case) by the SQL statement. Normally a RETURNING would suffice, but there's a lot of other JOINed data must be added, so a SELECT seems to be necessary after the INSERT. const query = `INSERT INTO checks ( start, status) VALUES ( :start, :status) returning id;` type Row struct { Status string `db:"status"` Start time. My tables looks like below: CREATE TABLE sample ( id bigserial PRIMARY KEY, lastname varchar(20), firstname varchar(20) ); CREATE TABLE sample1( user_id bigserial PRIMARY KEY, sample_id bigint REFERENCES sample, adddetails varchar(20) ); CREATE TABLE sample2( id bigserial RETURNING a_id. Skip to main content. This one is MUCH simpler. insert into a (data) values ('foo'); insert into b (_id, other_data) values (lastval(), 'foobar'); Edit (after discussion in comments): Note that lastval() is concurrency safe (as all sequence related functions). Query ID for name, if not found insert name and return ID. Conclusion. gdxyhfow fizgpqc ctln zrybe qzcsmz fsxcwy wdsbjtku vutsf lonjm vdnlw