PostgreSQL テーブルを作成しデータをコピー(CTAS他)

準備

・コピー元のテーブルを作成する

方法１

・CTASでコピー先のテーブルを作成しレコードもコピーする

方法２

・select into fromでコピー先のテーブルを作成しレコードもコピーする

方法３

・create tableでコピー先のテーブルを作成してinsert selectでレコードをコピーする

コピー元のテーブルを作成する

create tableでデータを作成しinsertでデータを登録します。

１．create tableでテーブルを作成します。

CREATE TABLE IF NOT EXISTS employee(
	id INT,
	name VARCHAR(20) not null,
	romaji VARCHAR(20),
	created_at TIMESTAMP,
	updated_at TIMESTAMP,
	primary key(id)
);

7行目は、Primary keyを指定しています。

２．作成されたテーブル定義は以下のようになります。
CTASで作成されたテーブル定義と違いが出るため、通常のinsert文で作成されたテーブル定義を確認しています。

CREATE TABLE IF NOT EXISTS public.employee
(
    id integer NOT NULL,
    name character varying(20) COLLATE pg_catalog."default" NOT NULL,
    romaji character varying(20) COLLATE pg_catalog."default",
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    CONSTRAINT employee_pkey PRIMARY KEY (id)
)

8行目は、Primary keyを指定です。

３．上記のテーブルにInsert文でデータを登録します。

INSERT INTO employee VALUES (1,'鈴木','suzuki','2022/01/21 12:16:05.337','2022/01/21 12:16:05.337');
INSERT INTO employee VALUES (2,'田中','tanaka','2022/01/21 12:16:05.337','2022/01/21 12:16:05.337');
INSERT INTO employee VALUES (3,'佐藤','sato','2022/01/21 12:16:05.337','2022/01/21 12:16:05.337');

CTASでコピー先のテーブルを作成しレコードもコピーする

CTASとは

Create Table コピー先のテーブル
AS Select * from コピー元のテーブル;

Create Table As Selectの先頭の文字を取ってCTASと呼ばれます。

CTASは、テーブルとデータをコピーできます。

１．CTASを実行します。

create table employee2 
AS SELECT * FROM employee;

２．作成されたemployee2テーブルの定義は以下のようになります。

CREATE TABLE IF NOT EXISTS public.employee2
(
    id integer,
    name character varying(20) COLLATE pg_catalog."default",
    romaji character varying(20) COLLATE pg_catalog."default",
    created_at timestamp without time zone,
    updated_at timestamp without time zone
)

employeeテーブルには存在したprimary keyやnot nullはemployee2テーブルにはついていません。

employeeテーブルのデータはemployee2テーブルにコピーされます。

後からalter tableでidをprimary keyにすることはできます。

ALTER TABLE employee2 ADD PRIMARY KEY(id)

select into fromでコピー先のテーブルを作成しレコードもコピーする

select into from

Select * Into コピー先のテーブル
From コピー元のテーブル;

select into fromは、コピー元のテーブルを元にしてコピー先のテーブルを新規作成しデータもコピーします。

１．select into fromを実行します。

select * into employee3 from employee;

２．作成されたemployee3テーブルの定義は以下のようになります。

CREATE TABLE IF NOT EXISTS public.employee3
(
    id integer,
    name character varying(20) COLLATE pg_catalog."default",
    romaji character varying(20) COLLATE pg_catalog."default",
    created_at timestamp without time zone,
    updated_at timestamp without time zone
)

employeeテーブルには存在したprimary keyやnot nullはemployee3テーブルにはついていません。

employeeテーブルのデータはemployee3テーブルにコピーされます。

後からalter tableでidをprimary keyにすることはできます。

ALTER TABLE employee3 ADD PRIMARY KEY(id)

Create Tableでコピー先のテーブルを作成しinsert selectでレコードをコピーする

１．コピー先のテーブルを作成します。create tableを実行します。
primary key制約やnot null制約を設定できます。

CREATE TABLE IF NOT EXISTS employee4(
	id INT,
	name VARCHAR(20) not null,
	romaji VARCHAR(20),
	created_at TIMESTAMP,
	updated_at TIMESTAMP,
	primary key(id)
);

２．insert selectデータを登録します。

INSERT INTO employee4 SELECT * FROM employee

以下のようにselect文で列名を書いても登録できます。

INSERT INTO employee4
SELECT id,name,romaji,created_at,updated_at FROM employee