问题

I am using postgres and pgadmin.
我正在使用PostgreSQL和pgAdmin。

I am trying to insert a record into table called movie_revenues, I get the following error:
我试图插入一条记录到名为movie_revenues的表中，但我收到以下错误：

duplicate key value violates unique constrain "movie_revenues_pkey"
Key (revenue_id) = (9) already exists
重复键值违反了唯一约束 "movie_revenues_pkey"
键值 (revenue_id) = (9) 已经存在

This is the record I tried to insert:
这是我尝试插入的记录：

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

The create code for the movie_revenues is the following:
movie_revenues的创建代码如下：

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
)
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:
movies表的代码如下：

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
)
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.
我之前在movies表中添加了一条id为54的记录，现在在movie_revenues表中尝试插入movie_id为54，domestic_takings为120，international_takings为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.
我想要做的是，在movie_revenues表中插入新记录时，如果international_takings大于400，就在名为top_movies的表中创建一个触发器来复制记录。

This is the code for top_movies and the function and trigger:
这是top_movies、函数和触发器的代码：

-- Create the top_movies table
-- 创建top_movies表
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
-- 在movie_revenues表上创建触发器
CREATE OR REPLACE FUNCTION insert_top_movies()
RETURNS TRIGGER AS $$
BEGIN
-- Check if the international_takings is greater than 4000
-- 检查international_takings是否大于4000
IF NEW.international_takings > 4000 THEN
-- Insert a new row in the top_movies table
-- 在top_movies表中插入新行
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?
但是为什么我在movie_revenues表中插入记录时会收到上述错误消息？

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

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
)

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
)

-- 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();

答案1

得分: 1

问题出现是因为 movie_revenue 中有具有大于 ID 生成序列返回的最后一个值的 revenue_id 值的行，而下一个序列值与其中一个匹配。

运行以下内容将同步 ID 生成序列与表中的最大 ID：

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;

如果序列落后于表中的最大 ID，则将返回新的序列 last_value；否则，不返回任何内容，序列保持不变。

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;

答案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