PostgresSQL/SQL文

SQL文 †

都道府県SQL †

※手書きでSQL書くことが少なくなったので、よく忘れるのでメモです。

DROP SEQUENCE prefectural_seq;
CREATE SEQUENCE "prefectural_seq" start 1 increment 1 maxvalue 9223372036854775807 minvalue 1 cache 1;

DROP TABLE prefectural;
CREATE TABLE prefectural (
  prefectural_id INT DEFAULT NEXTVAL('"prefectural_seq"'::text) NOT NULL,
  prefectural_name VARCHAR(100) NOT NULL,
  prefectural_insert TIMESTAMP NOT NULL DEFAULT NOW(),
  prefectural_update TIMESTAMP NOT NULL DEFAULT NOW(),
  CONSTRAINT PK_PREFECTURAL_ID PRIMARY KEY(prefectural_id)
)
INSERT INTO prefectural (prefectural_name) VALUES ('北海道'); 
INSERT INTO prefectural (prefectural_name) VALUES ('青森県');
INSERT INTO prefectural (prefectural_name) VALUES ('岩手県');
INSERT INTO prefectural (prefectural_name) VALUES ('宮城県');
INSERT INTO prefectural (prefectural_name) VALUES ('秋田県');
INSERT INTO prefectural (prefectural_name) VALUES ('山形県');
INSERT INTO prefectural (prefectural_name) VALUES ('福島県');
INSERT INTO prefectural (prefectural_name) VALUES ('茨城県');
INSERT INTO prefectural (prefectural_name) VALUES ('栃木県');
INSERT INTO prefectural (prefectural_name) VALUES ('群馬県');
INSERT INTO prefectural (prefectural_name) VALUES ('埼玉県');
INSERT INTO prefectural (prefectural_name) VALUES ('千葉県');
INSERT INTO prefectural (prefectural_name) VALUES ('東京都');