{"id":1022,"date":"2023-11-24T00:19:46","date_gmt":"2023-11-23T15:19:46","guid":{"rendered":"https:\/\/shinke1987.net\/?p=1022"},"modified":"2023-12-21T23:23:29","modified_gmt":"2023-12-21T14:23:29","slug":"postgresql%e3%81%a7%e6%83%85%e5%a0%b1%e3%82%b9%e3%82%ad%e3%83%bc%e3%83%9e%e3%81%ae%e5%88%a9%e7%94%a8","status":"publish","type":"post","link":"https:\/\/shinke1987.net\/?p=1022","title":{"rendered":"PostgreSQL\u3067\u60c5\u5831\u30b9\u30ad\u30fc\u30de\u306e\u5229\u7528"},"content":{"rendered":"\n

public\u30b9\u30ad\u30fc\u30de\u306e\u30c6\u30fc\u30d6\u30eb\u4e00\u89a7\u8868\u793a<\/h2>\n\n\n
\nSELECT * FROM information_schema.tables WHERE table_schema = 'public';\n<\/pre><\/div>\n\n\n
\u30e6\u30fc\u30b6\u4e00\u89a7\u8868\u793a<\/h2>\n\n\n
\nSELECT * FROM information_schema.enabled_roles;\n<\/pre><\/div>\n\n\n
\u5236\u7d04\u4e00\u89a7\u8868\u793a<\/h2>\n\n\n
\nSELECT * FROM information_schema.table_constraints WHERE table_schema = 'public';\n\n\u307e\u305f\u306f\nSELECT table_name, constraint_type, constraint_name FROM information_schema.table_constraints WHERE table_schema = 'public';\n\n\u307e\u305f\u306f\nSELECT constraint_name, check_clause FROM information_schema.check_constraints WHERE constraint_schema = 'public';\n<\/pre><\/div>\n\n\n
public\u30b9\u30ad\u30fc\u30de\u306e\u5217\u4e00\u89a7\u8868\u793a<\/h2>\n\n\n
\nSELECT table_name, column_name, column_default, is_nullable, data_type, character_maximum_length FROM information_schema.columns WHERE table_schema = 'public';\n\n\uff08column\u30c6\u30fc\u30d6\u30eb\u306e\u5217\u3092\u78ba\u8a8d\u3057\u305f\u3051\u308c\u3070\u6b21\u306e\u30b3\u30de\u30f3\u30c9\uff09\nSELECT column_name FROM information_schema.columns WHERE table_name = 'columns' AND table_schema = 'information_schema';\n<\/pre><\/div>\n\n\n
\u5404\u30c6\u30fc\u30d6\u30eb\u306e\u5217\u6570\u3092\u8868\u793a<\/h2>\n\n\n
\nSELECT table_name, count(column_name) AS column_amount FROM information_schema.columns WHERE table_schema = 'public' GROUP BY table_name;\n<\/pre><\/div>\n\n\n
\u5217\u6570\u304c\u6700\u3082\u591a\u3044\u30c6\u30fc\u30d6\u30eb\u3092\u8868\u793a<\/h2>\n\n\n
\nSELECT table_name, count(column_name) AS column_amount FROM information_schema.columns WHERE table_schema = 'public' GROUP BY table_name ORDER BY column_amount DESC LIMIT 1;\n<\/pre><\/div>\n\n\n
\u5916\u90e8\u30ad\u30fc\u306e\u6570\u3092\u30c6\u30fc\u30d6\u30eb\u3054\u3068\u306b\u8868\u793a<\/h2>\n\n\n
\nSELECT table_name, count(constraint_type) FROM information_schema.table_constraints WHERE constraint_schema = 'public' AND constraint_type = 'FOREIGN KEY' GROUP BY table_name;\n<\/pre><\/div>\n\n\n
\u5404\u30c6\u30fc\u30d6\u30eb\u306e\u5916\u90e8\u30ad\u30fc\u306e\u53c2\u7167\u5148\u3092\u78ba\u8a8d<\/h2>\n\n\n
\n\uff08\u5404\u30c6\u30fc\u30d6\u30eb\u306e\u4e3b\u30ad\u30fc\u3082\u8868\u793a\u3055\u308c\u308b\u3002\u6c17\u306b\u3059\u308b\u306a\u3089\u4e0a\u306einformation_schema.table_constraints\u30c6\u30fc\u30d6\u30eb\u3092\u7d50\u5408\u3059\u308c\u3070\u826f\u3044\u306f\u305a\uff09\n\nSELECT\n    key.table_name,\n    key.column_name,\n    key.constraint_name,\n    usage.table_name,\n    usage.column_name\nFROM\n        information_schema.key_column_usage AS key\n            INNER JOIN\n            information_schema.constraint_column_usage AS usage\nON key.constraint_name = usage.constraint_name\nWHERE\n    key.table_schema = 'public';\n<\/pre><\/div>\n\n\n
\u6307\u5b9a\u3057\u305f\u30c6\u30fc\u30d6\u30eb\u306e\u30b7\u30fc\u30b1\u30f3\u30b9\u3092\u78ba\u8a8d<\/h2>\n\n\n
\nSELECT\n    c.table_name, c.column_name, c.column_default\nFROM\n    information_schema.columns AS c\nWHERE\n    c.table_schema = '\u30b9\u30ad\u30fc\u30de\u540d'\n    AND c.table_name = '\u30c6\u30fc\u30d6\u30eb\u540d'\n    AND c.table_catalog = 'DB\u540d'\n    AND c.column_default LIKE '%regclass%';\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"
public\u30b9\u30ad\u30fc\u30de\u306e\u30c6\u30fc\u30d6\u30eb\u4e00\u89a7\u8868\u793a \u30e6\u30fc\u30b6\u4e00\u89a7\u8868\u793a \u5236\u7d04\u4e00\u89a7\u8868\u793a public\u30b9\u30ad\u30fc\u30de\u306e\u5217\u4e00\u89a7\u8868\u793a \u5404\u30c6\u30fc\u30d6\u30eb\u306e\u5217\u6570\u3092\u8868\u793a \u5217\u6570\u304c\u6700\u3082\u591a\u3044\u30c6\u30fc\u30d6\u30eb\u3092\u8868\u793a \u5916\u90e8\u30ad\u30fc\u306e\u6570\u3092\u30c6\u30fc\u30d6\u30eb\u3054\u3068\u306b\u8868\u793a \u5404\u30c6\u30fc\u30d6\u30eb\u306e\u5916\u90e8\u30ad\u30fc\u306e\u53c2\u7167\u5148 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[56],"tags":[57,99],"class_list":["post-1022","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-postgresql","tag-sql"],"_links":{"self":[{"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/posts\/1022","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/shinke1987.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1022"}],"version-history":[{"count":5,"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/posts\/1022\/revisions"}],"predecessor-version":[{"id":1029,"href":"https:\/\/shinke1987.net\/index.php?rest_route=\/wp\/v2\/posts\/1022\/revisions\/1029"}],"wp:attachment":[{"href":"https:\/\/shinke1987.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1022"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/shinke1987.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1022"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/shinke1987.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1022"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}