[PostgreSQL] INSERT INTO SELECT

1.  첫번째 방법

INSERT INTO [생성할 테이블]
SELECT [컬럼1, 컬럼2, ...]
FROM [가져올 테이블]
WHERE [조건]

 

아래와 같이 에러 문구가 나올 경우 두번째 방법을 사용하시면 됩니다.

ERROR: column "id" is of type bigint but expression is of type timestamp without time zone 
Hint: You will need to rewrite or cast the expression. 
Position: 34

 

 

2.  두번째 방법 (serial)

INSERT INTO [생성할 테이블](컬럼1, 컬럼2, ...)
SELECT * 
FROM [가져올 테이블]
WHERE [조건]

 

 

3.  예제 

작성기준 환경

OS	macOS
postgres --version (명령어)	postgres (PostgreSQL) 14.7 (Homebrew)

 

 

users 테이블

CREATE TABLE users (
    id                            bigserial primary key,
    created_at                    timestamp,
    updated_at                    timestamp,
    deleted_at                    timestamp,
    state                         varchar(10),
    user_name                     varchar(64) UNIQUE,
    hashed_password               varchar(255),
    age                           smallint
);

 

 

members 테이블

CREATE TABLE members (
    id                            bigserial primary key,
    created_at                    timestamp,
    updated_at                    timestamp,
    deleted_at                    timestamp,
    state                         varchar(10),
    member_name                   varchar(64) UNIQUE,
    age                           smallint
);

 

 

INSERT INTO SELECT  ( users -> members )

INSERT INTO members ( state, member_name, age )
SELECT
    state, user_name as member_name, age
FROM users
WHERE user_name IS NOT NULL;

 

 

참조

[stackoverflow]: https://stackoverflow.com/questions/51620422/insert-into-a-table-with-serial-using-select

INSERT INTO a table with serial using SELECT *