≡ Menu

15 Advanced PostgreSQL Commands with Examples

postgreSQL DB
Some of the open source application comes with postgreSQL database. To maintain those application, companies may not hire a fulltime postgreSQL DBA. Instead they may request the existing Oracle DBA, or Linux system administrator, or programmers to maintain the potgreSQL. In this article let discuss about the 15 practical postgresql database commands which will be useful to both DBA and expert psql users.

Also, refer to our previous article about 15 Practical PostgreSQL DBA Commands.

1. How to find the largest table in the postgreSQL database?

$ /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 relname, relpages FROM pg_class ORDER BY relpages DESC;
              relname              | relpages
-----------------------------------+----------
 pg_proc                           |       50
 pg_proc_proname_args_nsp_index    |       40
 pg_depend                         |       37
 pg_attribute                      |       30


If you want only the first biggest table in the postgres database then append the above query with limit as:

# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)

  • relname – name of the relation/table.
  • relpages – relation pages ( number of pages, by default a page is 8kb )
  • pg_class – system table, which maintains the details of relations
  • limit 1 – limits the output to display only one row.

2. How to calculate postgreSQL database size in disk ?

pg_database_size is the function which gives the size of mentioned database. It shows the size in bytes.

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


If you want it to be shown pretty, then use pg_size_pretty function which converts the size in bytes to human understandable format.

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

3. How to calculate postgreSQL table size in disk ?

This is the total disk space size used by the mentioned table including index and toasted data. You may be interested in knowing only the size of the table excluding the index then use the following command.

# SELECT pg_size_pretty(pg_total_relation_size('big_table'));
 pg_size_pretty
----------------
 55 MB
(1 row)

How to find size of the postgreSQL table ( not including index ) ?

Use pg_relation_size instead of pg_total_relation_size as shown below.

# SELECT pg_size_pretty(pg_relation_size('big_table'));
 pg_size_pretty
----------------
 38 MB
(1 row)

4. How to view the indexes of an existing postgreSQL table ?

Syntax: # \d table_name

As shown in the example below, at the end of the output you will have a section titled as indexes, if you have index in that table. In the example below, table pg_attribute has two btree indexes. By default postgres uses btree index as it good for most common situations.

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. How to specify postgreSQL index type while creating a new index on a table ?

By default the indexes are created as btree. You can also specify the type of index during the create index statement as shown below.

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

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

6. How to work with postgreSQL transactions ?

How to start a transaction ?

# BEGIN -- start the transaction.

How to rollback or commit a postgreSQL transaction ?

All the operations performed after the BEGIN command will be committed to the postgreSQL database only you execute the commit command. Use rollback command to undo all the transactions before it is committed.

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

7. How to view execution plan used by the postgreSQL for a SQL query ?

# EXPLAIN query;

8. How to display the plan by executing the query on the server side ?

This executes the query in the server side, thus does not shows the output to the user. But shows the plan in which it got executed.

# EXPLAIN ANALYZE query;

9. How to generate a series of numbers and insert it into a table ?

This inserts 1,2,3 to 1000 as thousand rows in the table numbers.

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

10. How to count total number of rows in a postgreSQL table ?

This shows the total number of rows in the table.

# select count(*) from table;


Following example gives the total number of rows with a specific column value is not null.

# select count(col_name) from table;


Following example displays the distinct number of rows for the specified column value.

# select count(distinct col_name) from table;

11. How can I get the second maximum value of a column in the table ?

First maximum value of a column

# select max(col_name) from table;

Second maximum value of a column

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

12. How can I get the second minimum value of a column in the table ?

First minimum value of a column

# select min(col_name) from table;

Second minimum value of a column

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

13. How to view the basic available datatypes in postgreSQL ?

Below is the partial output that displays available basic datatypes and it’s size.

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
  • typname – name of the datatype
  • typlen – length of the datatype

14. How to redirect the output of postgreSQL query to a file?

# \o output_file
# SELECT * FROM pg_class;

The output of the query will be redirected to the “output_file”. After the redirection is enabled, the select command will not display the output in the stdout. To enable the output to the stdout again, execute the \o without any argument as mentioned below.

# \o


As explained in our earlier article, you can also backup and restore postgreSQL database using pg_dump and psql.

15. Storing the password after encryption.

PostgreSQL database can encrypt the data using the crypt command as shown below. This can be used to store your custom application username and password in a custom table.

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

PostgreSQL crypt function Issue:

The postgreSQL crypt command may not work on your environment and display the following error message.

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 function Solution:

To solve this problem, installl the postgresql-contrib-your-version package and execute the following command in the postgreSQL prompt.

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

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

Comments on this entry are closed.

  • adam May 23, 2009, 6:15 am

    Your article looks great.

    In 13th command – How to view the basic available datatypes in postgreSQL ?

    SELECT typname,typlen from pg_type where typtype=’b’;

    what is meant by typtype=’b’ ? what does it refer?

  • Harsh Agrawal May 24, 2009, 2:36 pm

    Thanks man.. This is very useful for me

  • Srini May 31, 2009, 4:52 pm

    Adam,

    typtype=’b’ means that data is a basetype. b==basetype.

    PostgreSQL data types are divided into base types, composite types, domains, and pseudo-types.

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

  • Chris August 27, 2009, 12:14 pm

    Great article. The commands to find overall table/database size were extremely useful.

  • Andrew J. Lazarus December 15, 2009, 12:32 pm

    For getting the second-minimum of a table, if you are not concerned with tie values, it’s much faster to use
    SELECT m FROM mytable ORDER BY m LIMIT 1 OFFSET 1;
    if m is indexed

  • Adam July 29, 2010, 9:26 am

    To get the n’th min or max from a table, you could do something like this:
    SELECT col_name FROM table ORDER BY col_name OFFSET 10 LIMIT 1;

    Use ORDER BY DESC for max

  • Mota December 29, 2010, 3:26 pm

    Thank You!
    This article is very useful for me.

  • Paritosh January 8, 2011, 3:37 pm

    Great Article! You are requested to inform me about your next article

  • Jesper Wallin January 19, 2011, 7:52 am

    Thanks for a really useful article! Some things are considered bad practice on bigger tables (like COUNT(*) for example), but it sure does the job. 🙂

  • syed March 15, 2011, 12:18 am

    Wonderful!! It helped lot

    But I have 2 more queries , I am trying to find out whether any autosize or autogrowth Option is available in the Postgresql which causes db to grow beyond allocated size..

    2) how to find the actual used space from the allocated database size?

    Thanks,
    Syed

  • Paritosh March 15, 2011, 8:03 am

    Thank you very much for this useful tutorial.

  • Bob February 14, 2012, 2:48 am

    Very nice tips ! Got straight to the point for table size computation, thanks.

  • mike March 6, 2012, 11:27 am

    Thanks for this! Still dealing with my old Sybase habits, so these examples are very helpful.

  • priya July 4, 2012, 3:58 am

    thank u it was useful

  • Avinash Rao G.K. July 25, 2012, 2:49 am

    Thanks It was useful.

  • balaji August 12, 2012, 1:15 am

    really helpful, thank you very much

  • Rajib Mostafiz August 24, 2012, 8:06 pm

    Thanks man . It was very helpful.

  • Erhan S. August 27, 2012, 10:45 am

    Thanks man.

  • MarkM September 13, 2012, 7:53 am

    Thanks a lot.
    select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname);
    datname | size
    —————–+———
    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 MB
    xdp_idm | 30 MB

  • VINAY Kr. SHARMA January 28, 2013, 12:48 am

    Try this to get the middle value of a column:

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

  • sriram February 15, 2013, 3:41 am

    thank u!!!

  • yogi May 15, 2013, 4:41 am

    It’s awesome , it helps a lot!! gr8.

  • john October 6, 2013, 10:18 pm

    thanks a lot for your great contribution. It’s very useful and save a lof of my times

  • sharma December 28, 2013, 5:57 am

    thanks..its usefull and real help…mind blowing workk..thums up for you…;-)

  • dias June 15, 2014, 8:31 am

    How could postgres be used (without procedure – in a read-only transaction) to run a query in a certain condition matches?

    for instance, a query that is scheduled to run periodically but at certain point, when condition matches, there’s no need to further run the query, since data is already stored.

    I am searing for something like:
    IF EXISTS (SELECT myfield FROM mytable WHERE something…) THEN
    (the query…)

    Any idea?

    Thanks a lot.

  • Jesse May 26, 2015, 10:28 am

    If you want it to be shown pretty, then use pg_size_pretty function which converts the size in bytes to human understandable format.

  • Sany June 3, 2015, 11:21 am

    postgreSQL database size throws error at the second line

  • l.jans November 17, 2015, 9:50 am

    This i really good,

  • jasen April 30, 2016, 3:31 pm

    second max: (equivalent to your code above)

    select distinct col_name from table order by col_name desc offset 1 limit 1;

    second max: (as most most would define it)

    select col_name from table order by col_name desc offset 1 limit 1;

    third max:

    select col_name from table order by col_name desc offset 2 limit 1;