Postgres insert procedure throws foreign key exception

Question

I have two tables vendor_service_place and places with places.id as a foreign key in vendor_service_table. I have created a procedure which first inserts an entry into places table and then takes that id from LASTVAL() and insert an entry into vendor_service_table. But when I am executing this function I am getting

insert or update on table "vendor_service_place" violates foreign key 
constraint "fk_places"
DETAIL:  Key (place_id)=(2057) is not present in table "places".
CONTEXT:  SQL function "insert_data" statement 2

This is my insert procedure:

CREATE FUNCTION insert_data(vendorid integer,
                        serviceid integer,
                        name text,
                        address text,
                        latitude text,
                        longitude text,
                        contact_info text,
                        rating numeric,
                        description text) RETURNS bigint AS $$
    INSERT INTO places(name,address,latitude,longitude,contact_info,created_at)
    VALUES (name,address,latitude,longitude,contact_info,current_timestamp);

    INSERT INTO vendor_service_place(vendor_id,service_id,place_id,description,rating,created_at)
    VALUES (vendorid,serviceid,LASTVAL(),description,rating,current_timestamp);
    SELECT LASTVAL() as result;
$$ LANGUAGE SQL;

I am suspecting that Postgres performs some kind of batching where it executes both these statements together, that's probably why Its not able to find the id in places table. Any ideas on how to do it properly?

Please edit your question and add the create table statement for the table places — user330315
– user330315, Commented Feb 2, 2018 at 7:31
@joop: lastval() returns the last generated sequence value: postgresql.org/docs/current/static/functions-sequence.html — user330315
– user330315, Commented Feb 2, 2018 at 18:35
I didn't know it could be used without any arguments. Seems like a bad habit to me... — joop
– joop, Commented Feb 5, 2018 at 11:00

mchouhan · Accepted Answer · 2018-03-20 20:40:44Z

Seems using lastval() to get last insert id is not recommend if you are doing multiple inserts. Postgres not returning lastval() properly. Procedure is working fine after replacing LastVal() with return id statement.

      DECLARE
              insert_id bigint;
      BEGIN
      INSERT INTO places(name,address,latitude,
                         longitude,contact_info,
                         created_at,coordinates)
      VALUES (name,address,latitude,
              longitude,contact_info,
              current_timestamp,
              ST_SetSRID(ST_MakePoint(cast (longitude as numeric),cast (latitude as numeric)),4326))
              returning id into insert_id;

      INSERT INTO vendor_service_place(vendor_id,service_id,place_id,
                                       description,rating,created_at)
      VALUES (vendorid,serviceid,insert_id,
              description,rating,current_timestamp);
      return insert_id;
      END

Collectives™ on Stack Overflow

Postgres insert procedure throws foreign key exception

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related