Restructuring a table with CREATE TABLE and INSERT INTO
postgres=# CREATE TABLE books (
postgres(# id integer UNIQUE,
postgres(# title text NOT NULL,
postgres(# author_id integer,
postgres(# subject_id integer);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "books_id_key" for table "books"
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into books values(7808, 'Java', 4156, 9);
INSERT 0 1
postgres=# insert into books values(4513, 'Javascript', 1866, 15);
INSERT 0 1
postgres=# insert into books values(4267, 'C#', 2001, 15);
INSERT 0 1
postgres=# insert into books values(1608, 'Oracle', 1809, 2);
INSERT 0 1
postgres=# insert into books values(1590, 'Sql Server', 1809, 2);
INSERT 0 1
postgres=# insert into books values(25908, 'Postgre SQL', 15990, 2);
INSERT 0 1
postgres=# insert into books values(1501, 'Python', 2031, 2);
INSERT 0 1
postgres=# insert into books values(190, 'Java by API', 16, 6);
INSERT 0 1
postgres=#
postgres=#
postgres=# select * from books;
id | title | author_id | subject_id
-------+-------------+-----------+------------
7808 | Java | 4156 | 9
4513 | Javascript | 1866 | 15
4267 | C# | 2001 | 15
1608 | Oracle | 1809 | 2
1590 | Sql Server | 1809 | 2
25908 | Postgre SQL | 15990 | 2
1501 | Python | 2031 | 2
190 | Java by API | 16 | 6
(8 rows)
postgres=#
postgres=# --Restructuring a table with CREATE TABLE and INSERT INTO
postgres=#
postgres=# CREATE TABLE new_books (
postgres(# id integer UNIQUE,
postgres(# title text NOT NULL,
postgres(# author_id integer,
postgres(# subject_id integer
postgres(# );
NOTICE: CREATE TABLE / UNIQUE will create implicit index "new_books_id_key" for table "new_books"
CREATE TABLE
postgres=#
postgres=# INSERT INTO new_books
postgres-# SELECT id, title, author_id, subject_id
postgres-# FROM books;
INSERT 0 8
postgres=#
postgres=# ALTER TABLE books RENAME TO old_books;
ALTER TABLE
postgres=#
postgres=# ALTER TABLE new_books RENAME TO books;
ALTER TABLE
postgres=#
postgres=# drop table books;
DROP TABLE
postgres=# drop table old_books;
DROP TABLE
postgres=#
postgres=#
Related examples in the same category