15 PostgreSQL Database Practical command sharing _postgresql

Originally wanted to find the PostgreSQL database occupy Space Command found this blog, found that the provision of several
command is useful (but there are several senses that are sucks.) =), so he translated it over. In addition this article is 09, so the contents may be a little outdated, I collected the original text of useful comments on the last side.

Many open source software now uses PostgreSQL as their database system. But the company may not recruit a full-time PostgreSQL DBA to maintain it (Piglei: Not at home). It can be maintained by, for example, Oracle DBAs, Linux system administrators, or programmers. In this article, we will introduce 15 PostgreSQL database commands that are very useful to both Psql and DBAs.

1. How do I find the most space-occupying table in the PostgreSQL database?

Copy Code code as follows:

$/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 largest table, you can use the limit parameter to limit the number of results, like this:

Copy Code code as follows:

# SELECT Relname, relpages from Pg_class order by relpages DESC limit 1;
Relname | Relpages
---------+----------
Pg_proc | 50
(1 row)

1.relname-Relationship name/table name
2.relpages-Relationship pages (by default, one page size is 8kb)
3.pg_class-System tables, maintaining detailed information for all relations
4.limit 1-Limit return results to show only one row

2. How do I calculate the size of the hard disk the PostgreSQL database occupies?

Pg_database_size This method is specifically used to query the size of the database, and it returns the result unit of bytes (bytes). :

Copy Code code as follows:

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

If you want the result to be more intuitive, use the **pg_size_pretty** method, which converts the number of bytes into a more user-friendly format.

Copy Code code as follows:

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

3. How do I calculate the size of the hard disk occupied by the PostgreSQL table?

the table size shown in the following command is indexed and toasted data, and if you are interested in excluding the index only for the size of the table, you can use the command provided later.

Copy Code code as follows:

# SELECT Pg_size_pretty (pg_total_relation_size (' big_table '));
Pg_size_pretty
----------------
Mb
(1 row)

How do I query the size of an PostgreSQL table without an index?

Use **pg_relation_size** rather than **pg_total_relation_size** methods.

Copy Code code as follows:

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

4. How do I view the index of the PostgreSQL table?

Copy Code code as follows:

Syntax: # \d table_name

Let's take a look at this example and note that if your table has an index, you'll find a title Indexes in the back section of the command output, in this case, the Pg_attribut table has two btree types of indexes, and by default PostgreSQL uses an index type is Btree, because it applies to most situations.

Copy Code code as follows:

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 do I create an index of the specified type?

By default, indexes are btree types, but you can specify the type of the new index in the following ways.

Copy Code code as follows:

Syntax:create INDEX name on table USING index_type (column);

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

6. How do I use transactions in PostgreSQL?

How do I start a transaction?

Copy Code code as follows:

# Begin--Start a transaction

How do I commit or roll back a transaction?

All the actions you do after the BEGIN command will be truly committed to the PostgreSQL database only when you invoke the commit command. In addition, you can use the rollback command to roll back and forth all operations done in the transaction.

Copy Code code as follows:

# ROLLBACK--Rollback of current transaction
# Commit--Commit the current transaction

7. How do I view the PostgreSQL database's execution plan for an SQL query?

Copy Code code as follows:

# EXPLAIN query;

8. How do i show an execution plan by executing a query on the server side?

The following command executes the query on the server side, but does not give the query results to the user, but instead returns its actual execution plan.

Copy Code code as follows:

# EXPLAIN ANALYZE query;

9. How do you generate a sequence of numbers and insert them into a table?

The following command generates 1 to 1000 of these 1000 digits and inserts them into the numbers table.

Copy Code code as follows:

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

10. How to count the number of rows in the PostgreSQL table?

This command allows you to query the number of entries in all records in the list.

Copy Code code as follows:

# Select COUNT (*) from table;

This command queries the number of rows in the table for which the value of the specified column is not empty.

Copy Code code as follows:

# Select COUNT (col_name) from table;

This command queries the total number of rows in the table after the value of the set column.

Copy Code code as follows:

# Select COUNT (distinct col_name) from table;

11. How to query the value of a column in the table * * Second largest * *?

query for the largest value of a column

Copy Code code as follows:

# Select Max (col_name) from table;

Query for the second largest value in a column

Copy Code code as follows:

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

12. How to query the value of a column * * Second small * * in a table?

query for the smallest value of a column

Copy Code code as follows:

# Select min (col_name) from table;

Query for the second-smaller value of a column

Copy Code code as follows:

# select min (num) from number_table where num > (select min (num) from number_table);

13. How do I list the basic data types in the PostgreSQL database?

Some of the content is captured below, which shows the available data types and the number of bytes they occupy.

Copy Code code as follows:

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-The name of the type
2.typlen-size of type

14. How do you save the results of a single query as a file?

Copy Code code as follows:

# \o Output_file
# SELECT * from Pg_class;

The results of this query above will be saved to the "output_file" file. When redirection is activated, all subsequent queries will no longer print the results on the screen. If you want to turn on screen output again, you need to perform an O command with no arguments again.

Copy Code code as follows:

# \o

15. Store the encrypted password

the PostgreSQL database can use the following crypt command to encrypt data. This can be used to easily save your username and password.

Copy Code code as follows:

# SELECT Crypt (' Sathiya ', Gen_salt (' MD5 '));

Possible problems with the PostgreSQL crypt method:

Crypt in your environment may not be used, and provide the following error message.

Copy Code code as follows:

Error:function Gen_salt ("Unknown") does not exist
The Hint:no function matches the given name and argument types.
You could need to add explicit type casts.

Workaround:

To solve this problem, you need to install the postgresql-contrib-version of this package, and then execute the following command in Psql.

Copy Code code as follows:

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

Comments after the original text

What does that typtype= ' B ' mean in the 13th order?

Typtype= ' B ' means basetype. B==basetype.

PostgreSQL has several types of data: composite types, domains, and pseudo-types.

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

Get the second big/small value efficiency problem

Query is much faster if you want to query the second-smaller value of a column in a table:

Copy Code code as follows:

SELECT m from MyTable order by M LIMIT 1 OFFSET 1;

If M column has an index.

COUNT (*) Efficiency problem

Executing count (*) on a large table can have an obvious efficiency problem