{"id":2671,"date":"2022-02-23T11:38:53","date_gmt":"2022-02-23T02:38:53","guid":{"rendered":"https:\/\/www.oqiita.com\/?p=2671"},"modified":"2022-02-23T11:38:53","modified_gmt":"2022-02-23T02:38:53","slug":"%e3%80%90postgresql%e3%80%91%e3%83%a1%e3%83%a2","status":"publish","type":"post","link":"https:\/\/www.oqiita.com\/?p=2671","title":{"rendered":"\u3010PostgreSQL\u3011\u30e1\u30e2"},"content":{"rendered":"

\u30dd\u30b9\u30b0\u30ec\u306eALTER\u6587\u3002\u5fd8\u308c\u308b\u304b\u3089\u30e1\u30e2\u3002<\/p>\n

<\/p>\n

<\/p>\n

\u30ab\u30e9\u30e0\u306e\u578b\u5909\u66f4<\/h3>\n
ALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d ALTER COLUMN \u30ab\u30e9\u30e0\u540d TYPE \u578b;<\/code><\/pre>\n
\u578b\u306f\u3053\u306e\u3078\u3093\uff1f
\nsmallint
\ninteger
\nbigint
\ndate
\ntimestamp without time zone
\ncharacter varying(40)<\/p>\n
\u305d\u306e\u4ed6\u306f\u3053\u306e\u3078\u3093\u3002
\nhttps:\/\/www.postgresql.jp\/document\/9.3\/html\/datatype.html<\/a><\/p>\n
\u30ab\u30e9\u30e0\u8ffd\u52a0<\/h3>\n
ALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d ADD COLUMN \u30ab\u30e9\u30e0\u540d \u578b NOT NULL;\nALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d ADD COLUMN \u30ab\u30e9\u30e0\u540d \u578b ;<\/code><\/pre>\n
NULL\u5236\u7d04\u8a2d\u5b9a<\/h3>\n
ALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d ALTER COLUMN \u30ab\u30e9\u30e0\u540d SET NOT NULL;<\/code><\/pre>\n
NULL\u5236\u7d04\u524a\u9664<\/h3>\n
ALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d ALTER COLUMN \u30ab\u30e9\u30e0\u540d DROP NOT NULL;<\/code><\/pre>\n
\u5916\u90e8\u30ad\u30fc\u5236\u7d04\u524a\u9664<\/h3>\n
ALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d DROP CONSTRAINT \u5916\u90e8\u30ad\u30fc\u540d;<\/code><\/pre>\n
\u5916\u90e8\u30ad\u30fc\u5236\u7d04\u4f5c\u6210<\/h3>\n
ALTER TABLE ONLY \u30c6\u30fc\u30d6\u30eb\u540d\n    ADD CONSTRAINT \u5916\u90e8\u30ad\u30fc\u540d FOREIGN KEY (xxx_no) REFERENCES \u5916\u90e8\u30ad\u30fc\u30c6\u30fc\u30d6\u30eb\u540d(yyy_no);<\/code><\/pre>\n
\u30c6\u30fc\u30d6\u30eb\u306e\u30ea\u30cd\u30fc\u30e0<\/h3>\n
ALTER TABLE \u5909\u66f4\u524d\u306e\u540d\u524d RENAME TO \u5909\u66f4\u5f8c\u306e\u540d\u524d;<\/code><\/pre>\n
INDEX\u306e\u540d\u524d\u5909\u66f4<\/h3>\n
ALTER INDEX \u5909\u66f4\u524d\u306e\u540d\u524d RENAME TO \u5909\u66f4\u5f8c\u306e\u540d\u524d;<\/code><\/pre>\n
\u4e3b\u30ad\u30fc\u5909\u66f4<\/h3>\n
ALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d DROP CONSTRAINT \u30c6\u30fc\u30d6\u30eb\u540d_pkey;\nALTER TABLE \u30c6\u30fc\u30d6\u30eb\u540d ADD CONSTRAINT \u30c6\u30fc\u30d6\u30eb\u540d_pkey PRIMARY KEY(xxx_no);<\/code><\/pre>\n
\u30c6\u30fc\u30d6\u30eb\u30b3\u30e1\u30f3\u30c8<\/h3>\n
COMMENT ON TABLE \u30c6\u30fc\u30d6\u30eb\u540d IS '\u30b3\u30e1\u30f3\u30c8';<\/code><\/pre>\n
\u30ab\u30e9\u30e0\u30b3\u30e1\u30f3\u30c8<\/h3>\n
COMMENT ON COLUMN \u30c6\u30fc\u30d6\u30eb\u540d.\u30ab\u30e9\u30e0\u540d IS '\u30b3\u30e1\u30f3\u30c8';<\/code><\/pre>\n
\u30c6\u30fc\u30d6\u30eb\u4e00\u89a7<\/h3>\n
select\n\t pg_stat_user_tables.relname as TABLE_NAME\n\t,pg_description.description as TABLE_COMMENT\nfrom\n\t pg_stat_user_tables\n\t,pg_description\nwhere\n\tpg_stat_user_tables.relname in (\n\t\tselect \n\t\t\trelname as TABLE_NAME\n\t\tfrom \n\t\t\tpg_stat_user_tables\n\t)\n\tand\n\tpg_stat_user_tables.relid=pg_description.objoid\n\tand\n\tpg_description.objsubid=0\norder by\n\tpg_stat_user_tables.relname\n;<\/code><\/pre>\n
\u63a5\u7d9a\u4e2d\u30bb\u30c3\u30b7\u30e7\u30f3\u306e\u78ba\u8a8d<\/h3>\n
SELECT pid,* FROM pg_stat_activity where datname = 'DB\u540d';<\/code><\/pre>\n
\u90aa\u9b54\u306a\u30bb\u30c3\u30b7\u30e7\u30f3\u306f\u30ad\u30eb\uff01<\/h3>\n
SELECT pg_cancel_backend(12345);\nSELECT pg_terminate_backend(12345);<\/code><\/pre>\n
\u4ee5\u4e0a<\/p>\n","protected":false},"excerpt":{"rendered":"
\u30dd\u30b9\u30b0\u30ec\u306eALTER\u6587\u3002\u5fd8\u308c\u308b\u304b\u3089\u30e1\u30e2\u3002<\/p>\n","protected":false},"author":1,"featured_media":2857,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[90],"tags":[],"class_list":{"0":"post-2671","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-db","8":"entry"},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.oqiita.com\/wp-content\/uploads\/2022\/02\/sango_postgres_icatch.png","jetpack_shortlink":"https:\/\/wp.me\/p8QluP-H5","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts\/2671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2671"}],"version-history":[{"count":3,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts\/2671\/revisions"}],"predecessor-version":[{"id":2858,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/posts\/2671\/revisions\/2858"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=\/wp\/v2\/media\/2857"}],"wp:attachment":[{"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oqiita.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}