\u7b49\u6548\u4e8e MySQL \u91cc\u5934\u7684 int auto_increment \uff0c\u6bd4\u5982\uff1a<\/p>\n
create table (\n id int auto_increment\n);<\/pre>\n\u7b49\u6548\u4e8e PostgreSQL \u91cc\u5934\u7684\uff1a<\/p>\n
create table (\n id serial\n);<\/pre>\nCREATE TABLE foo (\n id SERIAL,\n bar varchar\n);\n\nINSERT INTO \"foo\" (bar) values ('blah');\nINSERT INTO \"foo\" (bar) values ('blah');\n\nSELECT * FROM foo;\n1,blah\n2,blah<\/pre>\n\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n
\n
- postgresql int auto_increment<\/a><\/li>\n
- http:\/\/stackoverflow.com\/questions\/787722\/postgresql-autoincrement<\/a><\/li>\n
- http:\/\/www.postgresql.org\/docs\/9.3\/static\/datatype-numeric.html<\/a><\/li>\n
- http:\/\/www.postgresqltutorial.com\/postgresql-data-types\/<\/a><\/li>\n
- http:\/\/dba.stackexchange.com\/questions\/1281\/how-do-i-specify-that-a-column-should-be-auto-incremented-in-pgadmin<\/a><\/li>\n<\/ul>\n
2.PostgreSQL\u7684\u5efa\u8868\u3001\u63d2\u5165\u64cd\u4f5c<\/h5>\n
create table company(\n id int primary key not null,\n name text not null,\n age int not null,\n address char(50),\n salary real,\n join_date\t date\n);\n\ninsert into company (id,name,age,address,salary,join_date) values (1, 'paul', 32, 'california', 20000.00 ,'2001-07-13');\ninsert into company (id,name,age,address,join_date) values (2, 'allen', 25, 'texas', '2007-12-13');\ninsert into company (id,name,age,address,salary,join_date) values (3, 'teddy', 23, 'norway', 20000.00, default );\ninsert into company (id,name,age,address,salary,join_date) values (4, 'mark', 25, 'rich-mond ', 65000.00, '2007-12-13' ), (5, 'david', 27, 'texas', 85000.00 , '2007-12-13');<\/pre>\n\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n
\n
- http:\/\/www.postgresqltutorial.com\/postgresql-insert\/<\/a><\/li>\n
- http:\/\/www.tutorialspoint.com\/postgresql\/postgresql_insert_query.htm<\/a><\/li>\n
- http:\/\/www.postgresql.org\/docs\/9.0\/static\/dml-insert.html<\/a><\/li>\n
- http:\/\/www.tutorialspoint.com\/postgresql\/postgresql_create_table.htm<\/a><\/li>\n
- http:\/\/www.tutorialspoint.com\/postgresql\/postgresql_insert_query.htm<\/a><\/li>\n<\/ul>\n
3.\u5982\u4f55\u5feb\u901f\u83b7\u53d6PostgreSQL\u6570\u636e\u5e93\u4e2d\u884c\u7684\u4e2a\u6570\uff1f<\/h5>\n
cur.execute(\"select reltuples from pg_class where oid = 'public.%s'::regclass;\" % (table_name,))\ncur.execute(\"select n_live_tup from pg_stat_user_tables where relname=%s;\" % (table_name,))\ncur.execute(\"select count(*) from %s;\" % (table_name,))<\/pre>\n\n
- http:\/\/stackoverflow.com\/questions\/14570488\/how-do-i-speed-up-counting-rows-in-a-postgresql-table<\/a><\/li>\n
- https:\/\/wiki.postgresql.org\/wiki\/Slow_Counting<\/a><\/li>\n
- https:\/\/wiki.postgresql.org\/wiki\/Count_estimate<\/a><\/li>\n<\/ul>\n
4.PostgreSQL\u5e38\u89c1\/\u6709\u7528\u7684\u64cd\u4f5c\u547d\u4ee4<\/h5>\n
1.\u67e5\u627e\u6700\u5927\u7684\u8868\n# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;\n# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;\n\nrelname \u2013 name of the relation\/table.\nrelpages - relation pages ( number of pages, by default a page is 8kb )\npg_class \u2013 system table, which maintains the details of relations\nlimit 1 \u2013 limits the output to display only one row.\n\n2.\u8ba1\u7b97PostgreSQL\u6570\u636e\u5e93\u5728\u78c1\u76d8\u4e2d\u6240\u5360\u5927\u5c0f\n# SELECT pg_database_size('geekdb');\n# SELECT pg_size_pretty(pg_database_size('geekdb'));\n\n3.\u8ba1\u7b97\u67d0\u4e2a\u8868\u5728\u78c1\u76d8\u4e2d\u6240\u5360\u5927\u5c0f\n# SELECT pg_size_pretty(pg_total_relation_size('big_table'));\n# SELECT pg_size_pretty(pg_relation_size('big_table'));\t#\u4e0d\u5305\u62ec\u7d22\u5f15\n\n4.\u67e5\u770b\u67d0\u4e00\u8868\u7684\u6240\u6709\u7d22\u5f15\u4fe1\u606f\n# \\d table_name\n\n5.\u5feb\u901f\u4ea7\u751f\u5927\u91cf\u8fde\u7eed\u6570\u5b57\u5e76\u63d2\u5165\u8868\u4e2d\n# INSERT INTO numbers (num) VALUES ( generate_series(1,1000));\n\n6.\u8ba1\u7b97\u67d0\u4e2a\u8868\u4e2d\u884c\u7684\u4e2a\u6570\n# select count(*) from table;\n# select count(col_name) from table;\t#\u5217col_name\u4e2d\u975e\u7a7a\u884c\u7684\u4e2a\u6570\n# select count(distinct col_name) from table;\n\n7.\u8ba1\u7b97\u67d0\u4e2a\u8868\u67d0\u4e00\u5217\u7684\u6700\u503c\n# select max(col_name) from table;\n# SELECT MAX(num) from number_table where num < ( select MAX(num) from number_table );\t#\u6b21\u5927\u503c\n# select min(col_name) from table;\n# SELECT MIN(num) from number_table where num > ( select MIN(num) from number_table );\t#\u6b21\u5c0f\u503c\n\n8.\u67e5\u770bPostgreSQL\u4e2d\u6709\u54ea\u4e9b\u53ef\u7528\u7684\u57fa\u7840\u6570\u636e\u7c7b\u578b\n# SELECT typname,typlen from pg_type where typtype='b';\n\ntypname \u2013 name of the datatype\ntyplen \u2013 length of the datatype\n\n9.\u5c06\u67e5\u8be2\u7684\u7ed3\u679c\u5bfc\u51fa\u81f3\u6587\u4ef6\n# \\o output_file\n# SELECT * FROM pg_class;\n# \\o\n\n10.\u67e5\u770b\u5f53\u524d\u6240\u5728\u6570\u636e\u5e93\n# SELECT current_database();\n# \\conninfo<\/pre>\n\n
- http:\/\/www.thegeekstuff.com\/2009\/05\/15-advanced-postgresql-commands-with-examples\/<\/a>\u00a0 #Nice<\/li>\n
- http:\/\/razorsql.com\/articles\/postgresql_limit_query.html<\/a><\/li>\n
- https:\/\/www.digitalocean.com\/community\/tutorials\/how-to-create-data-queries-in-postgresql-by-using-the-select-command<\/a><\/li>\n
- http:\/\/stackoverflow.com\/questions\/2276644\/list-all-tables-in-postgresql-information-schema<\/a><\/li>\n<\/ul>\n
5.\u76f8\u5173\u8c03\u8bd5\u4fe1\u606f<\/h5>\n
\n
- sql – http:\/\/stackoverflow.com\/questions\/5267715\/whats-the-correct-query-for-getting-the-current-number-of-connections-in-a-postg<\/a><\/li>\n
- sql – http:\/\/stackoverflow.com\/questions\/18907047\/postgres-db-size-command<\/a><\/li>\n
- http:\/\/stackoverflow.com\/questions\/464623\/how-can-you-get-the-active-users-connected-to-a-postgresql-database-via-sql<\/a><\/li>\n
- postgresql – http:\/\/dba.stackexchange.com\/questions\/58312\/psql-show-current-database<\/a><\/li>\n<\/ul>\n
6.PostgreSQL\u4e2d\u7684show databases\u547d\u4ee4\uff1a\u201cpostgresql show databases of select\u201d<\/h5>\n
mysql: show databases;\npostgresql: \\l\npostgresql: SELECT datname FROM pg_database;\npostgresql: SELECT datname FROM pg_database WHERE datistemplate = false;<\/pre>\n\n
- http:\/\/dba.stackexchange.com\/questions\/1285\/how-do-i-list-all-databases-and-tables-using-psql<\/a><\/li>\n<\/ul>\n
7.PostgreSQL\u4e2d\u7684show tables\u547d\u4ee4\uff1a\u201cpostgresql show tables of select\u201d<\/h5>\n
mysql: show tables;\npostgresql: \\d\npostgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';\npostgresql: SELECT table_schema || '.' || table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');\npostgresql: SELECT table_schema,table_name FROM information_schema.tables where table_schema = 'public' ORDER BY table_schema,table_name;<\/pre>\n\n
- http:\/\/stackoverflow.com\/questions\/12445608\/psql-list-all-tables<\/a><\/li>\n
- http:\/\/onewebsql.com\/blog\/list-all-tables<\/a><\/li>\n
- http:\/\/stackoverflow.com\/questions\/769683\/show-tables-in-postgresql<\/a><\/li>\n
- http:\/\/stackoverflow.com\/questions\/2276644\/list-all-tables-in-postgresql-information-schema<\/a><\/li>\n<\/ul>\n
8.PostgreSQL\u4e2d\u7684describe table\u547d\u4ee4\uff1a\u201cpostgresql show tables of select\u201d\u3001\u201cpostgresql show columns\u201d<\/h5>\n
mysql: show columns;\npostgresql: \\d table\npostgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';\npostgresql: select column_name from information_schema.columns;\npostgresql: select table_name, column_name from information_schema.columns;\npostgresql: select column_name from information_schema.columns where table_name='pg_proc';<\/pre>\n\n
- http:\/\/stackoverflow.com\/questions\/109325\/postgresql-describe-table<\/a><\/li>\n
- http:\/\/dba.stackexchange.com\/questions\/22362\/how-do-i-list-all-columns-for-a-specified-table<\/a><\/li>\n
- http:\/\/www.linuxscrew.com\/2009\/07\/03\/postgresql-show-tables-show-databases-show-columns\/<\/a><\/li>\n
- http:\/\/www.linux4beginners.info\/?q=node\/pgsql-show-tables-show-databases-show-columns<\/a><\/li>\n<\/ul>\n
9.PostgreSQL\u4e2d\u7684select top N\u547d\u4ee4\uff1a\u201cpostgresql show tables of select\u201d<\/h5>\n
\n
- http:\/\/stackoverflow.com\/questions\/2597098\/get-the-last-n-rows-in-the-database-in-order<\/a><\/li>\n<\/ul>\n
10.\u5982\u4f55\u5728PostgreSQL\u4e2d\u67e5\u8be2\u53e6\u5916\u4e00\u4e2a\u6570\u636e\u5e93\u7684\u8868\u5185\u5bb9\uff1f<\/h5>\n
\u6bd4\u5982\u5728MySQL\u4e2d\u662f\uff1a<\/p>\n
Current_DB_1 #> Select * From DB_2.TABLE_2;<\/pre>\n\u53c2\u8003\u4fe1\u606f\uff1a<\/h6>\n
\u4e0d\u80fd\u76f4\u63a5\u67e5\uff0c\u9700\u8981\u5b89\u88c5dblink\uff0c\u6216\u8005postgres_fdw<\/p>\n
# With PostgreSQL 9.1 or later, installation of additional modules has been simplified.\n\n\u5b89\u88c5 dblink \u6269\u5c55\npostgres=# create extension dblink;\n\u5982\u679c\u63d0\u793a\u201cCREATE EXTENSION\u201d\uff0c\u8bf4\u660e\u5b89\u88c5\u6210\u529f\u3002\n\n\u5b89\u88c5 postgres_fdw \u6269\u5c55\npostgres=# create extension postgres_fdw;\n\u5982\u679c\u63d0\u793a\u201cCREATE EXTENSION\u201d\uff0c\u8bf4\u660e\u5b89\u88c5\u6210\u529f\u3002<\/pre>\n\u56e0\u4e3aPostgreSQL\u66f4\u63a8\u8350\u4e00\u4e2adb\uff0c\u4e0b\u9762\u5efa\u591a\u4e2aschema\uff1b\u548cOracle\u7c7b\u4f3c\uff0cMySQL\u6ca1\u6709schema\u7684\u6982\u5ff5\u3002<\/p>\n
\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n
\n
- https:\/\/wiki.postgresql.org\/wiki\/FAQ<\/a><\/li>\n
- http:\/\/stackoverflow.com\/questions\/46324\/possible-to-perform-cross-database-queries-with-postgres<\/a><\/li>\n
- http:\/\/www.postgresql.org\/docs\/9.4\/static\/postgres-fdw.html<\/a><\/li>\n
- http:\/\/www.postgresql.org\/docs\/9.4\/static\/dblink.html<\/a><\/li>\n
- http:\/\/stackoverflow.com\/questions\/3862648\/how-to-use-install-dblink-in-postgresql<\/a><\/li>\n
- http:\/\/stackoverflow.com\/questions\/5075193\/installing-dblink-for-postgres-9<\/a><\/li>\n
- http:\/\/michael.otacoo.com\/postgresql-2\/first-steps-with-dblink-on-postgres\/<\/a><\/li>\n
- =<\/li>\n
- PostgreSQL\u6570\u636e\u5e93dblink\u548cpostgres_fdw\u6269\u5c55\u4f7f\u7528\u6bd4\u8f83<\/a><\/li>\n
- Postgresql 9.3\u5916\u90e8\u6570\u636e\u5c01\u88c5\u7684\u65b0\u529f\u80fd<\/a><\/li>\n
- Postgresql DBLink – Dufe\u738b\u5f6c – \u535a\u5ba2\u56ed<\/a><\/li>\n
- Postgresql\u4e2d\u5e38\u7528dblink\u6269\u5c55\u7684\u4f7f\u7528<\/a><\/li>\n
- PostgreSQL DBLink\u7684\u4f7f\u7528<\/a><\/li>\n
- postgresql \u5982\u4f55\u5b89\u88c5dblink_\u767e\u5ea6\u77e5\u9053<\/a><\/li>\n
- PostgreSQL\u7684\u5c0f\u6280\u5de7 dblink<\/a><\/li>\n
- Postgres\u8bbf\u95ee\u5176\u4ed6PostgresQL\u6570\u636e\u5e93\u7684\u529f\u80fdDBLINK<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
1.PostgreSQL\u4e2d\u7684serial \u7b49\u6548\u4e8e MySQL \u91cc\u5934\u7684 int auto_increment \uff0c […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,12],"tags":[482,404],"class_list":["post-2035","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-tools","tag-count","tag-postgresql"],"views":3088,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2035","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=2035"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2035\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2035"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2035"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2035"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}