Insert Multiple New Rows Into Table

Question

Assume I have following table : movies with primary key movie_id a consecutive sequences of number starting 1.

movie_id | title | year | rating
1          movie1  1988   9
2          movie2  1970   8
3          moive3  2004   5
4          moive4  1999   7
5          moive5  2015   6

My goal is to create sequel for movie released before year 1990 at year 2020, the updated table would look like something :

movie_id  |       title       | year | rating
1          movie1               1988    9
2          movie2               1970    8
3          moive3               2004    5
4          moive4               1999    7
5          moive5               2015    6
6          moive1: the sequel   2020    9
7          movie2: the sequel   2020    8

I know how to get subsquery : movie released before 1990:

SELECT *
FROM movies
WHERE year < 1990;

But don't how to move on with this.

Here is my table schema:

CREATE TABLE movies (
  movie_id INTEGER PRIMARY KEY,
  title VARCHAR NOT NULL,
  year INTEGER NOT NULL,
  rating FLOAT NOT NULL,
  UNIQUE (title,year)) ;

what do you mean by sequence on the table? @Jean-FrançoisSavard — boxy, Mar 07 '15 at 02:16

Rigel1121 · Accepted Answer · 2015-03-07T08:50:12.657

1

Use INSERT...SELECT Method. Then concatenate the string ":the sequel" in title, Lastly, Add the '2020' string in year. See below:

INSERT INTO movies
SELECT 
      title || ': the sequel' AS title,
      '2020' AS year,
      rating 
FROM movies
WHERE year<1990

edited Mar 07 '15 at 08:50

answered Mar 07 '15 at 02:18

Rigel1121

2,022
1
17
24

Thanks , so psql handle keys by default? – boxy Mar 07 '15 at 02:22
Your welcome. Yes , **PSQL** will handle the auto increment of `movie_id` unless it is configured. – Rigel1121 Mar 07 '15 at 02:24
Actually , it gives me ERROR: null value in column "movie_id" violates not-null constraint. I will update my table schema in the question. – boxy Mar 07 '15 at 02:26
Just set your `movie_id` as auto increment. – Rigel1121 Mar 07 '15 at 02:27
We are not allow to change the schema of the table , but I was thinking , since the movie_id is a consecutive number , can we do something like max(movie_id) + 1? – boxy Mar 07 '15 at 02:32
You can change the schema of the table using this command:`ALTER TABLE movie ADD COLUMN movie_id BIGSERIAL PRIMARY KEY;` – Rigel1121 Mar 07 '15 at 02:34
sorry for the troublesome, this is part of assignment question , which we shouldn't be changing the table schema. – boxy Mar 07 '15 at 02:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/72446/discussion-between-rigel1121-and-boxy). – Rigel1121 Mar 07 '15 at 02:55
1

Thanks , I solved it by adding a subquery that output a max (movie_id)+1, that does the trick. – boxy Mar 07 '15 at 03:05
1

max (movie_id)+1 won't scale well. Another option would be to add a sequence to the database (not altering the table schema) and `INSERT INTO movies(movie_id, title,year,rating) SELECT nextval('my_sequence'), title || ': the sequel' AS title, '2020' AS year, rating FROM movies WHERE year<1990` – pojo-guy Mar 07 '15 at 03:32
Thanks for the suggestion @pojo-guy, I've edited my answer already. I remove the fields in the insert into which indicates that all the columns will be inserted with some values. – Rigel1121 Mar 07 '15 at 03:36
1

This is dubious advice, because one cannot simply change an `integer` data type to `serial` or `bigserial`. Detailed explanation and instructions: http://stackoverflow.com/questions/24657693/creating-a-postgresql-sequence-to-a-field-which-is-not-the-id-of-the-record/24659884#24659884 – Erwin Brandstetter Mar 07 '15 at 08:46

Insert Multiple New Rows Into Table

1 Answers1