一份菜单

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

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

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

  1. 50个Linux Sysadmin教程
  2. 50个最常用的Linux命令(包括示例)
  3. 排名前25位的最佳Linux性能监视和调试工具
  4. 妈妈,我找到了! 15个实用的Linux Find命令示例
  5. Linux 101 Hacks第二版电子书 Linux 101黑客手册

Bash 101 Hacks书 Sed和Awk 101黑客手册 Nagios Core 3书 Vim 101黑客手册

{ 29 评论… 加一 }

  • adam 2009年5月23日,上午6:15

    您的文章看起来很棒。

    在第十三命令–如何在postgreSQL中查看基本的可用数据类型?

    从pg_type中选择typtype,typlen,其中typtype =’b’;

    what is meant by typtype =’b’?它指的是什么?

  • 严厉的阿格劳瓦尔 2009年5月24日,下午2:36

    谢谢男人..这对我非常有用

  • Srini 2009年5月31日,下午4:52

    Adam,

    typtype =’b’表示数据是基本类型。 b ==基本类型。

    PostgreSQL数据类型分为基本类型,复合类型,域和伪类型。

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

  • Chris 2009年8月27日,下午12:14

    很棒的文章。查找表/数据库整体大小的命令非常有用。

  • 安德鲁·J·拉撒路 2009年12月15日,下午12:32

    为了获得表的第二个最小值,如果您不关心领带值,则它’s much faster to use
    从mytable ORDER BY m LIMIT 1 OFFSET 1中选择m;
    如果m被索引

  • Adam 2010年7月29日,上午9:26

    得到n’表格中的最小值或最大值,您可以执行以下操作:
    SELECT col_name FROM table ORDER BY col_name OFFSET 10 LIMIT 1;

    最多使用ORDER BY DESC

  • Mota 2010年12月29日,下午3:26

    谢谢!
    这篇文章对我非常有用。

  • 帕里托什 2011年1月8日,下午3:37

    好文章!请您将下一篇文章告知我

  • 杰斯珀·沃林 2011年1月19日,上午7:52

    感谢您写的非常有用的文章!有些事情在较大的表上(例如COUNT(*))被认为是不好的做法,但确实可以做到。 ðŸ™,

  • syed 2011年3月15日,上午12:18

    精彩!!它帮助了很多

    但是我还有两个查询,我试图找出Postgresql中是否有任何autosize或autogrowth选项可用,这会导致db超出分配的大小。

    2)如何从分配的数据库大小中找到实际使用的空间?

    谢谢,
    Syed

  • 帕里托什 2011年3月15日,上午8:03

    非常感谢您这个有用的教程。

  • Bob 2012年2月14日,上午2:48

    非常好的提示!马上到表大小计算点,谢谢。

  • mike 2012年3月6日,上午11:27

    谢谢你!仍然处理我以前的Sybase习惯,因此这些示例非常有帮助。

  • priya 2012年7月4日,上午3:58

    谢谢你,这很有用

  • Avinash Rao G.K. 2012年7月25日,上午2:49

    谢谢这很有用。

  • balaji 2012年8月12日,上午1:15

    真的很有帮助,非常感谢

  • 拉吉布·莫斯塔菲兹(Rajib Mostafiz) 2012年8月24日,晚上8:06

    谢啦 。这非常有帮助。

  • Erhan S. 2012年8月27日,上午10:45

    谢啦。

  • MarkM 2012年9月13日,上午7:53

    非常感谢。
    从pg_database中按pg_database_size(datname)选择datname,pg_size_pretty(pg_database_size(datname))作为大小。
    datname |尺寸
    —————–+———
    template1 | 6025 kB
    template0 | 6025 kB
    xdpdb | 6025 kB
    xdp_xtb | 6025 kB
    xdp_bps | 6129 kb
    fixserver_bps | 6129 kb
    postgres | 6129 kb
    fixdb | 6129 kb
    xdp_noble | 6209 kb
    fixserver_xtb | 6225 kB
    fixserver_noble | 7577 KB
    fixserver_idm | 11兆字节
    xdp_idm | 30兆字节

  • VINAY Kr。莎玛 2013年1月28日,上午12:48

    尝试以下操作以获取列的中间值:

    SELECT ID FROM tbl_1 ORDER BY ID DESC LIMIT 1 OFFSET(SELECT COUNT(id)FROM tbl_1)/ 2;

  • 斯里拉姆 2013年2月15日,上午3:41

    感谢你!!!

  • yogi 2013年5月15日,上午4:41

    It’太棒了,它有很大帮助! gr8。

  • john 2013年10月6日,晚上10:18

    非常感谢您的巨大贡献。它’非常有用,可以节省我的时间

  • 夏尔马 2013年12月28日,上午5:57

    谢谢..它的有用和真正的帮助…令人兴奋的工作..为您服务…;-)

  • dias 2014年6月15日,上午8:31

    postgres如何使用(无过程)–在只读事务中)在特定条件下运行查询是否匹配?

    例如,预定要定期运行但在特定条件下在条件匹配时运行的查询’由于数据已经存储,因此无需进一步运行查询。

    我正在寻找类似的东西:
    如果存在(从mytable中选择myfield的地方)…) THEN
    (查询…)

    任何的想法?

    非常感谢。

  • Jesse 2015年5月26日,上午10:28

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

  • Sany 2015年6月3日,上午11:21

    postgreSQL数据库大小在第二行抛出错误

  • l.jans 2015年11月17日,上午9:50

    我真的很好

  • jasen 2016年4月30日,下午3:31

    第二个最大值:(等于上面的代码)

    通过col_name desc offset 1 限制1从表顺序中选择不同的col_name;

    第二个最大值:(大多数人会定义)

    按col_name desc偏移量1限制1从表顺序中选择col_name;

    第三最大:

    按col_name desc偏移量2限制1从表顺序中选择col_name;

发表评论