Database | PostgreSQL Tutorials and Solutions

Download and load PostgreSQL sample database

In this article, we will see download and load PostgreSQL sample database. PostgreSQL sample database is used for practice purpose only.
We can download the PostgreSQL sample database dvdrental from the github

Download and load PostgreSQL sample database:

1. Go to link https://github.com/robconery/dvdrental.

2. Create database with the name dvdrental from either Linux shell or psql shell.

From Linux shell

createdb dvdrental

From psql shell:

create database dvdrental;

3. From the Linux shell navigate to Downloads directory and execute below restore command.

pg_restore -d dvdrental dvdrental.tar

4. Then, connect to psql shell and run below command to verify PostgreSQL sample database loaded or not.

select * from pg_database where datname='dvdrental';

\l dvdrental

How to find database size in PostgreSQL

In this article, we will see how to find database size in PostgreSQL server. We can find database size in PostgreSQL by using SQL Statement and size of the files that make up the PostgreSQL database server.

1. Find the size of current database in PostgreSQL server.

 select pg_size_pretty(pg_database_size(current_database()));

Continue reading How to find database size in PostgreSQL

How to rename PostgreSQL database

In this article, we will see How to rename PostgreSQL database. ALTER DATABASE command is used to change the name of database in PostgreSQL Server. Only the database owner or a superuser can rename a database and non-superuser owners must also have the CREATEDB privilege.

ALTER DATABASE Syntax:

ALTER DATABASE database_name RENAME TO new_database_name [ [ WITH ] option [ ... ] ]

Continue reading How to rename PostgreSQL database

Check the database size in PostgreSQL

It is daily activity to monitor the database size on any database server. We have to check the database size either for planning or space monitoring.

1. Query to check the database size in PostgreSQL:

select datname database_name,pg_database_size(datname) from pg_database where datname='name of the database';

Continue reading Check the database size in PostgreSQL

How to get top 10 tables in PostgreSQL Server database

In this article, we are going to find out the top 10 tables in PostgreSQL Database by SQL query.

To get top 10 tables in PostgreSQL Server database:

SELECT table_name
,pg_relation_size(table_schema || '.' || table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

Output:

 table_name | size 
------------+------
 stock      | 8192
 orderline  | 8192
 barcode    | 8192
 customer   | 8192
 item       | 8192
 orderinfo  |    0
(6 rows)

To get top 5 tables in PostgreSQL Server database:

SELECT table_name
,pg_relation_size(table_schema || '.' || table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 5;

Output:

 table_name | size 
------------+------
 stock      | 8192
 orderline  | 8192
 barcode    | 8192
 customer   | 8192
 item       | 8192
(5 rows)

How to get list of databases in PostgreSQL

We can get the list of databases in PostgreSQL by using the met-command \l or by querying on catalog table ‘pg_database’. There is no show databases in PostgreSQL(psql)

Get the list of databases by using the meta-command \l:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  
 
-----------+----------+----------+-------------+-------------+----------------------
-
 lonprod1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          
+
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          
+
           |          |          |             |             | postgres=CTc/postgres

How to get list of databases in PostgreSQL

Get the list of databases by using the catalog table pg_database:

postgres=# select datname from pg_database;
  datname  
-----------
 postgres
 lonprod1
 template1
 template0
(4 rows)

How to get list of tables in PostgreSQL Database

We can get the list of tables by using the meta-command \dt or by using SQL statement.

Get the list of tables in current schema of PostgreSQL by using \dt:

Get the list of tables in PostgreSQL by using SQL statement:
Continue reading How to get list of tables in PostgreSQL Database

Download and load PostgreSQL sample database:

Related Posts:

Related Posts:

ALTER DATABASE Syntax:

Related Posts:

Related Posts:

Related Posts:

Related Posts:

Related Posts: