15个带有示例的PostgreSQL高级命令

一些开源应用程序随附于postgreSQL数据库。为了维护这些应用程序，公司可能不雇用全职的postgreSQL DBA。相反，他们可以要求现有的Oracle DBA，Linux系统管理员或程序员来维护potgreSQL。在本文中，我们将讨论15个实用的postgresql数据库命令，这些命令对DBA和专家psql用户都将有用。

另外，请参考我们之前的文章 15个实用的PostgreSQL DBA命令.

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

test=# SELECT 相对名称, 相对页面 FROM pg_class ORDER BY 相对页面 DESC;
              相对名称              | 相对页面
-----------------------------------+----------
 pg_proc                           |       50
 pg_proc_proname_args_nsp_index    |       40
 pg_depend                         |       37
 pg_attribute                      |       30

如果只需要postgres数据库中的第一个最大表，则将上述查询附加为limit：

# SELECT 相对名称, 相对页面 FROM pg_class ORDER BY 相对页面 DESC 限制1;
 相对名称 | 相对页面
---------+----------
 pg_proc |       50
(1 row)

• 相对名称 –关系/表的名称。
• 相对页面 –关系页（页数，默认情况下，一个页面为8kb）
• pg_class –系统表，其中维护关系的详细信息
• 限制1 –将输出限制为仅显示一行。

2.如何计算磁盘中的postgreSQL数据库大小？

pg_database_size是给出上述数据库大小的函数。它显示字节大小。

# SELECT pg_database_size('geekdb');
pg_database_size
------------------
         63287944
(1 row)

如果要使其漂亮地显示，请使用pg_size_pretty函数将字节大小转换为人类可以理解的格式。

# SELECT pg_size_pretty(pg_database_size('geekdb'));
 pg_size_pretty
----------------
 60 MB
(1 row)

3.如何计算磁盘中的postgreSQL表大小？

这是上述表使用的总磁盘空间大小，包括索引和烘烤的数据。您可能只想知道除索引以外的表大小，然后使用以下命令。

# 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

如下例所示，在输出末尾，如果该表中有索引，则将有一个标题为索引的部分。在下面的示例中，表pg_attribute具有两个btree索引。默认情况下，postgres使用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.如何在表上创建新索引时指定postgreSQL索引类型？

默认情况下，索引创建为btree。您还可以在创建索引语句期间指定索引的类型，如下所示。

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

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

6.如何使用postgreSQL事务？

如何开始交易？

# BEGIN -- start the transaction.

如何回滚或提交一个postgreSQL事务？

仅在您执行commit命令后，在BEGIN命令之后执行的所有操作都将提交到postgreSQL数据库。使用rollback命令撤消所有事务，然后再提交。

# ROLLBACK -- rollbacks the transaction.
# COMMIT -- commits the transaction.

7.如何查看postgreSQL用于SQL查询的执行计划？

# EXPLAIN query;

8.如何通过在服务器端执行查询来显示计划？

这将在服务器端执行查询，因此不会向用户显示输出。但是显示执行它的计划。

# EXPLAIN ANALYZE query;

9.如何生成一系列数字并将其插入表格中？

这将在表编号中插入1,2,3到1000作为千行。

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

10.如何计算postgreSQL表中的总行数？

这显示了表中的总行数。

# select count(*) from table;

下面的示例给出具有特定列值的行总数不为null。

# 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中查看基本的可用数据类型？

以下是显示可用基本数据类型及其输出的部分输出’s size.

test=# SELECT typname,typlen from pg_type where typtype ='b';
    代号    | typlen
----------------+--------
 bool           |      1
 bytea          |     -1
 char           |      1
 name           |     64
 int8           |      8
 int2           |      2
 int2vector     |     -1

• 代号–数据类型的名称
• 花粉–数据类型的长度

14.如何将postgreSQL查询的输出重定向到文件？

# \o 输出文件
# SELECT * FROM pg_class;

查询的输出将重定向到“output_file”。启用重定向后，select命令将不会在标准输出中显示输出。要再次启用向stdout的输出，请执行\ o，不带任何参数，如下所述。

# \o

如我们之前的文章中所述，您还可以 使用pg_dump和psql备份和还原postgreSQL数据库。

15.加密后存储密码。

PostgreSQL数据库可以使用crypt命令加密数据，如下所示。这可用于将您的自定义应用程序用户名和密码存储在自定义表中。

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

PostgreSQL crypt函数问题：

postgreSQL crypt命令可能无法在您的环境中运行，并显示以下错误消息。

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 crypt函数解决方案：

要解决此问题，请安装postgresql-contrib-your-version软件包，并在postgreSQL提示符下执行以下命令。

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

如果您喜欢这篇文章，您可能还会喜欢..