postgres_sql - tetsuyaf1100/hello-world GitHub Wiki

sample

• table 作成

CREATE SEQUENCE IF NOT EXISTS sample_surrogate_key_seq;
CREATE TABLE  IF NOT EXISTS sample (
    surrogate_key integer DEFAULT nextval('public.sample_surrogate_key_seq'::regclass) NOT NULL,
    name text,
    modified_at timestamp(6) with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

• data 追加

insert into sample (name)values('aaa');

• column 追加

ALTER TABLE sample ADD COLUMN name2 text;

• column 削除

ALTER TABLE sample DROP COLUMN name2;

• column 型変更

ALTER TABLE sample ALTER COLUMN name TYPE varchar;

sample 外部キー制約

• 外部キー

CREATE TABLE cities (
         city     varchar(80) primary key,
	 location point
);

CREATE TABLE weather (
	city      varchar(80) references cities(city),
	temp_lo   int,
	temp_hi   int,
	prcp      real,
	date      date
);

myapp=# select * from cities ;
 city | location
------+----------
(0 rows)

myapp=# select * from weather ;
 city | temp_lo | temp_hi | prcp | date
------+---------+---------+------+------
(0 rows)


myapp=# INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".


myapp=# INSERT INTO cities VALUES ('Berkeley', point(1, 1));
INSERT 0 1
myapp=# INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
INSERT 0 1