日本一二三区视频,日本动漫青椒影视,人人爽视频免费公开

主頁 > 知識庫 > 15個postgresql數據庫實用命令分享

15個postgresql數據庫實用命令分享

最初是想找postgresql數據庫占用空間命令發(fā)現的這篇blog，發(fā)現其中提供的幾
條命令很有用（但也有幾條感覺是充數的=。=），于是就把它翻譯過來了。另外這篇文章是09年的，所以里面的內容可能有點過時，我收集了原文中有用的評論放在了最后面。

現在有不少開源軟件都在使用postgreSQL作為它們的數據庫系統。但公司可能不會招一些全職的postgreSQL DBA來維護它（piglei: 在國內基本也找不到）。而會讓一些比如說Oracle DBA、Linux系統管理員或者程序員去維護。在這篇文章中我們會介紹15個無論是對psql老鳥還是DBA都非常實用的postgresql數據庫命令。

1. 如何找到postgreSQL數據庫中占空間最大的表？

復制代碼代碼如下:

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
       \h for help with SQL commands
       \&; for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

如果你只想要最大的那個表，可以用limit參數來限制結果的數量，就像這樣：

復制代碼代碼如下:

# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;

 relname | relpages

---------+----------

 pg_proc |       50

(1 row)

1.relname - 關系名/表名
2.relpages - 關系頁數（默認情況下一個頁大小是8kb）
3.pg_class - 系統表, 維護著所有relations的詳細信息
4.limit 1 - 限制返回結果只顯示一行

2. 如何計算postgreSQL數據庫所占用的硬盤大??？

pg_database_size 這個方法是專門用來查詢數據庫大小的，它返回的結果單位是字節(jié)（bytes）。:

復制代碼代碼如下:

# SELECT pg_database_size('geekdb');

pg_database_size

------------------

         63287944

(1 row)

如果你想要讓結果更直觀一點，那就使用**pg_size_pretty**方法，它可以把字節(jié)數轉換成更友好易讀的格式。

復制代碼代碼如下:

# SELECT pg_size_pretty(pg_database_size('geekdb'));

 pg_size_pretty

----------------

 60 MB

(1 row)

3. 如何計算postgreSQL表所占用的硬盤大??？

下面這個命令查出來的表大小是包含索引和toasted data的，如果你對除去索引外僅僅是表占的大小感興趣，可以使用后面提供的那個命令。

復制代碼代碼如下:

# SELECT pg_size_pretty(pg_total_relation_size('big_table'));

 pg_size_pretty

----------------

 55 MB

(1 row)

如何查詢不含索引的postgreSQL表的大??？

使用**pg_relation_size**而不是**pg_total_relation_size**方法。

復制代碼代碼如下:

# SELECT pg_size_pretty(pg_relation_size('big_table'));

 pg_size_pretty

----------------

 38 MB

(1 row)

4. 如何查看postgreSQL表的索引？

復制代碼代碼如下:

Syntax: # \d table_name

讓我們看下面這個例子，注意如果你的表有索引的話，你會在命令輸出內容的后面那部分找到一個標題 Indexes ，在這個例子中，pg_attribut表有兩個btree類型的索引，默認情況下postgreSQL使用的索引類型都是btree，因為它適用于絕大多數情況。

復制代碼代碼如下:

test=# \d pg_attribute

   Table "pg_catalog.pg_attribute"

    Column     |   Type   | Modifiers

---------------+----------+-----------

 attrelid      | oid      | not null

 attname       | name     | not null

 atttypid      | oid      | not null

 attstattarget | integer  | not null

 attlen        | smallint | not null

 attnum        | smallint | not null

 attndims      | integer  | not null

 attcacheoff   | integer  | not null

 atttypmod     | integer  | not null

 attbyval      | boolean  | not null

 attstorage    | "char"   | not null

 attalign      | "char"   | not null

 attnotnull    | boolean  | not null

 atthasdef     | boolean  | not null

 attisdropped  | boolean  | not null

 attislocal    | boolean  | not null

 attinhcount   | integer  | not null

Indexes:

    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)

    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

5. 如何創(chuàng)建一個指定類型的索引？

默認情況下的索引都是btree類型的，但是你可以用下面的方法來指定新索引的類型。

復制代碼代碼如下:

Syntax: CREATE INDEX name ON table USING index_type (column);

# CREATE INDEX test_index ON numbers using hash (num);

6. 如何在postgreSQL中使用事務？

如何開始一個事務？

復制代碼代碼如下:

# BEGIN -- 開始事務

如何提交或回滾一個事務？

只有當你調用COMMIT命令后，你在BEGIN命令后所做的所有操作才會真正的被提交到postgreSQL數據庫。另外你還可以使用ROLLBACK命令來回滾事務中做的所有操作。

復制代碼代碼如下:

# ROLLBACK -- 回滾當前事務

# COMMIT -- 提交當前事務

7. 如何查看postgreSQL數據庫對某個SQL查詢的執(zhí)行計劃？

復制代碼代碼如下:

# EXPLAIN query;

8. 如何通過在服務端執(zhí)行查詢來顯示執(zhí)行計劃？

下面這個命令會在服務器端執(zhí)行查詢，但是并不會把查詢結果給用戶，而是返回它實際的執(zhí)行計劃。

復制代碼代碼如下:

# EXPLAIN ANALYZE query;

9. 怎么生成一個序列的數字并把它們插入到一個表中？

下面這個命令將會生成1到1000這一千個數字并插入到numbers表中。

復制代碼代碼如下:

# INSERT INTO numbers (num) VALUES ( generate_series(1,1000));

10. 如何統計postgreSQL表里面的行數？

這個命令可以查詢出表里所有記錄的條數。

復制代碼代碼如下:

# select count(*) from table;

這個命令會查詢出表中指定列的值不為空的所有行數.

復制代碼代碼如下:

# select count(col_name) from table;

這個命令會查詢出表中按制定列的值去重后的總行數。

復制代碼代碼如下:

# select count(distinct col_name) from table;

11. 如何查詢表中某列**第二大**的值？

查詢某列最大的值

復制代碼代碼如下:

# select max(col_name) from table;

查詢某列中第二大的值

復制代碼代碼如下:

# SELECT MAX(num) from number_table where num   ( select MAX(num) from number_table );

12. 如何查詢表中某列**第二小**的值？

查詢某列最小的值

復制代碼代碼如下:

# select min(col_name) from table;

查詢某列第二小的值

復制代碼代碼如下:

# SELECT MIN(num) from number_table where num > ( select MIN(num) from number_table );

13. 如何列出postgreSQL數據庫中基本的數據類型？

下面截取了部分內容，這個命令可以展示可用的數據類型和它們所占用的字節(jié)數。

復制代碼代碼如下:

test=# SELECT typname,typlen from pg_type where typtype='b';

    typname     | typlen

----------------+--------

 bool           |      1

 bytea          |     -1

 char           |      1

 name           |     64

 int8           |      8

 int2           |      2

 int2vector     |     -1

1.typname - 類型的名稱
2.typlen - 類型的大小

14. 如何把某一次查詢的結果保存為一個文件？

復制代碼代碼如下:

# \o output_file

# SELECT * FROM pg_class;

上面這個查詢的結果將會被保存到到"output_file"文件中。當重定向被激活后，之后的所有查詢都不再會把結果打印在屏幕上了。如果要再次打開屏幕輸出，需要再執(zhí)行一次不帶任何參數的 o 命令。

復制代碼代碼如下:

# \o

15. 存儲加密后的密碼

PostgreSQL數據庫可以使用下面的crypt命令來加密數據。這可以用來方便的用來保存你的用戶名和密碼。

復制代碼代碼如下:

# SELECT crypt ( 'sathiya', gen_salt('md5') );

PostgreSQL crypt方法可能存在的問題：

crypt在你的環(huán)境下可能會用不了，并提供下面這個報錯信息。

復制代碼代碼如下:

ERROR:  function gen_salt("unknown") does not exist

HINT:  No function matches the given name and argument types.

         You may need to add explicit type casts.

解決方法：

為了解決這個問題，你需要安裝 postgresql-contrib-版本這個包，然后在psql中執(zhí)行下面這個命令。

復制代碼代碼如下:

# \i /usr/share/postgresql/8.1/contrib/pgcrypto.sql

原文后的評論

在第13個命令中，那個typtype='b'是什么意思？

typtype='b'表示basetype。b==basetype.

PostgreSQL有這么幾種數據類型: composite types, domains, and pseudo-types.

http://developer.postgresql.org/pgdocs/postgres/extend-type-system.html

獲取第二大/小的值效率問題

如果要查詢一個表中某列第二小的值，這樣查詢要快得多：

復制代碼代碼如下: