ٙࣅCheck੍ͱͯ͠͏Tips͕͋ͬͨɻ CREATE TABLE users ( id SERIAL NOT NULL CONSTRAINT users_pkey PRIMARY KEY, gender integer NOT NULL, gender_text NOT NULL VARCHAR(2) GENERATED ALWAYS AS (CASE WHEN (gender = 0) then 'உੑ' WHEN (gender = 1) then 'ঁੑ' ELSE NULL END) STORED ); 24
NULL CONSTRAINT member_pkey PRIMARY KEY, profile JSON NOT NULL ); JSONBܕ CREATE TABLE member ( id SERIAL NOT NULL CONSTRAINT member_pkey PRIMARY KEY, profile JSONB NOT NULL ); 29
nothing INSERT INTO public.option_user VALUES (1, 'Berte Kliement') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (2, 'Lula Da Costa') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (3, 'Rodi Rapport') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (4, 'Iris Runacres') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (5, 'Peirce MacPike') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (6, 'Pat Haresnaip') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (7, 'Fiann Gascard') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (8, 'Pauly Cantera') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (9, 'Leo Drewes') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (10, 'Joane Clague') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (11, 'Annabelle Stollberger') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (12, 'Nichole Andreaccio') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (13, 'Cahra Gabites') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (14, 'Zola Laudham') ON CONFLICT DO NOTHING; INSERT INTO public.option_user VALUES (15, 'Tanitansy Lacrouts') ON CONFLICT DO NOTHING; 44
AS $$ BEGIN IF ( NEW.pref = 'Ԭࢁ') THEN INSERT INTO okayama_cities VALUES (NEW.*); ELSIF ( NEW.pref = 'ౡ') THEN INSERT INTO hiroshima_cities VALUES (NEW.*); ELSE RAISE EXCEPTION 'ERROR'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; 52
city) ) PARTITION BY LIST (pref); CREATE TABLE okayama_cities PARTITION OF japan_cities FOR VALUES IN ('Ԭࢁ'); CREATE TABLE hiroshima_cities PARTITION OF japan_cities FOR VALUES IN ('ౡ'); 55
integer not null, sale_data date not null ) PARTITION BY RANGE (sale_data); CREATE TABLE sale_1 PARTITION OF sale FOR VALUES FROM (CURRENT_DATE::timestamp) TO (CURRENT_DATE::timestamp + '1 year'); CREATE TABLE sale_2 PARTITION OF sale FOR VALUES FROM (CURRENT_DATE::timestamp + '1 year') TO (CURRENT_DATE::timestamp + '2 year'); ※ ͜Ε·Ͱ syntax error ͩͬͨɻ 59
null primary key ) PARTITION BY RANGE (id); CREATE TABLE users_1 PARTITION OF users FOR VALUES FROM (1) TO (100000); CREATE TABLE users_2 PARTITION OF users FOR VALUES FROM (100001) TO (200000); CREATE TABLE user1_profile ( id BIGSERIAL PRIMARY KEY, user_id INTEGER references users_1 (id) deferrable initially deferred, name VARCHAR(25) NOT NULL ); 62