Question 168680 in PostgreSQL Question posted in
The official documentation can be found here.

Have you had any issues inserting records into Postgresql?

I am using postgres and pgadmin.
I am trying to insert a record into table called movie_revenues, I get the following error:

duplicate key value violates unique constrain "movie_revenues_pkey"
Key (revenue_id) = (9) already exists

This is the record I tried to insert:

INSERT INTO movie_revenues(
     movie_id, domestic_takings, international_takings)
    VALUES (54, 120, 420);

The create code for the movie_revenues is the following:

CREATE TABLE IF NOT EXISTS public.movie_revenues
(
    revenue_id integer NOT NULL DEFAULT nextval('movie_revenues_revenue_id_seq'::regclass),
    movie_id integer,
    domestic_takings numeric(6,2),
    international_takings numeric(6,2),
    CONSTRAINT movie_revenues_pkey PRIMARY KEY (revenue_id),
    CONSTRAINT movie_revenues_movie_id_fkey FOREIGN KEY (movie_id)
        REFERENCES public.movies (movie_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

The code for the movies table is the following:

CREATE TABLE IF NOT EXISTS public.movies
(
    movie_id integer NOT NULL DEFAULT nextval('movies_movie_id_seq'::regclass),
    movie_name character varying(50) COLLATE pg_catalog."default",
    movie_length integer,
    movie_lang character varying(20) COLLATE pg_catalog."default",
    release_date date,
    age_certificate character varying(5) COLLATE pg_catalog."default",
    director_id integer,
    CONSTRAINT movies_pkey PRIMARY KEY (movie_id),
    CONSTRAINT movies_director_id_fkey FOREIGN KEY (director_id)
        REFERENCES public.directors (director_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

I had previously added a record in the movies table with id 54, now in the table movie_revenues I try to insert that movie_id of 54, domestic_takings of 120 and international_takings of 420.

What I wanted to do was to create a trigger on the movie_revenues table if I insert a new record in the table, when international_takings was more then 400, to make a copy in table called top_movies.
This is the code for top_movies and the function and trigger:

-- Create the top_movies table
CREATE TABLE top_movies (
    revenue_id SERIAL PRIMARY KEY,
    movie_id INTEGER REFERENCES movies(movie_id),
    international_takings NUMERIC
);

-- Create the trigger on the movie_revenue table
CREATE OR REPLACE FUNCTION insert_top_movies()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if the international_takings is greater than 4000
    IF NEW.international_takings > 4000 THEN
        -- Insert a new row in the top_movies table
        INSERT INTO top_movies (movie_id, international_takings)
        VALUES (NEW.movie_id, NEW.international_takings)
        ORDER BY movie_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER top_movies_trigger
AFTER INSERT ON movie_revenues
FOR EACH ROW
EXECUTE FUNCTION insert_top_movies();

But why am I getting the mentioned error when I insert a recode on the movies_revenues table?

Tags:

3

Answers


  1. 0

    try getting the nextvalue of the sequence movies_movie_id_seq and then run your query.

    SELECT nextval('movies_movie_id_seq');
    Login or Signup to reply.
  2. 0

    Use the function currval()/lastval() to obtain the current/last value issued to that sequence. If you want to define the new value to reach next time you invoke nextval(), you can call setval() function. See more details at https://www.postgresql.org/docs/current/functions-sequence.html

    Login or Signup to reply.
  3. 0

    The problem is occurring because movie_revenue has rows with revenue_id values that are greater than the last value returned by the ID generation sequence and the next sequence value matches one of those.

    Running the following will synchronize the ID generation sequence with the maximum ID in the table:

    SELECT SETVAL('movie_revenues_revenue_id_seq', max_id) AS new_last_value
  FROM movie_revenues_revenue_id_seq s
    CROSS JOIN (SELECT MAX(revenue_id) AS max_id FROM movie_revenues) mr
  WHERE s.last_value < mr.max_id;

    The new sequence last_value will be returned if the sequence was behind the maximum ID in the table; otherwise, nothing is returned and the sequence is unchanged.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search