PostgreSQL Tutorial

• PostgreSQL - Introduction
• Install PostgreSQL on Windows
• Install PostgreSQL on Mac
• PostgreSQL - Loading a Database
• PostgreSQL - Create Database
• PostgreSQL - ALTER DATABASE
• PostgreSQL - DROP DATABASE
• PostgreSQL - Rename Database
• PostgreSQL - Copy Database
• PostgreSQL - Size of a Database
• PostgreSQL - Show Databases

Data Types

• PostgreSQL - Data Types
• PostgreSQL - Boolean Data Type
• PostgreSQL - CHAR Data Type
• PostgreSQL - VARCHAR Data Type
• PostgreSQL - TEXT Data Type
• PostgreSQL - NUMERIC Data Type
• PostgreSQL - SMALLINT Integer Data Type
• PostgreSQL - INTEGER Data Type
• PostgreSQL - BIGINT Integer Data Type
• PostgreSQL - Date Data Type
• PostgreSQL - Timestamp Data Type
• PostgreSQL - UUID Data Type
• PostgreSQL - TIME Data Type
• PostgreSQL - Interval Data Type
• PostgreSQL - User-defined Data Type
• PostgreSQL - Array Data Type
• PostgreSQL - hstore Data Type
• PostgreSQL - JSON Data Type

Querying & Filtering Data

• PostgreSQL - SELECT
• PostgreSQL - SELECT DISTINCT clause
• PostgreSQL - ORDER BY clause
• PostgreSQL - WHERE clause
• PostgreSQL - LIMIT clause
• PostgreSQL - FETCH clause
• PostgreSQL - IN operator
• PostgreSQL - IS NULL operator
• PostgreSQL - LIKE operator
• PostgreSQL - NOT LIKE operator
• PostgreSQL - BETWEEN operator
• PostgreSQL - HAVING clause
• PostgreSQL - GROUP BY clause

Managing Tables

• PostgreSQL - CREATE TABLE
• PostgreSQL - SELECT INTO
• PostgreSQL - Create Auto-increment Column using SERIAL
• PostgreSQL - CREATE SEQUENCE
• PostgreSQL - Identity Column
• PostgreSQL - ALTER TABLE
• PostgreSQL - Rename Table
• PostgreSQL - ADD COLUMN
• PostgreSQL - DROP COLUMN
• PostgreSQL - Change Column Type
• PostgreSQL - RENAME COLUMN
• PostgreSQL - DROP TABLE
• PostgreSQL - TRUNCATE TABLE
• PostgreSQL - Temporary Table
• PostgreSQL - Temporary table name
• PostgreSQL - Removing Temporary Table
• PostgreSQL - Copy a Table
• PostgreSQL - Comparing Tables
• PostgreSQL - Describe Table
• PostgreSQL - Show Tables
• PostgreSQL - Import CSV File Into Table

Modifying Data

• PostgreSQL - INSERT
• PostgreSQL - Insert multiple rows
• PostgreSQL - UPDATE
• PostgreSQL - DELETE
• PostgreSQL - Upsert

Conditionals

• PostgreSQL - CASE
• PostgreSQL - COALESCE
• PostgreSQL - NULLIF() Function
• PostgreSQL - CAST

Control Flow

• PostgreSQL - IF Statement
• PostgreSQL - CASE Statement
• PostgreSQL - Loop Statement
• PostgreSQL - While Loops
• PostgreSQL - For Loops
• PostgreSQL - Exit
• PostgreSQL - Continue

Transactions & Constraints

• PostgreSQL - Transactions
• PostgreSQL - COMMIT
• PostgreSQL - BEGIN
• PostgreSQL - ROLLBACK
• PostgreSQL - Primary Key
• PostgreSQL - Foreign Key
• PostgreSQL - CHECK Constraint
• PostgreSQL - UNIQUE Constraint
• PostgreSQL - NOT NULL Constraint

Working with JOINS & Schemas

• PostgreSQL - Joins
• PostgreSQL - LEFT JOIN
• PostgreSQL - INNER JOIN
• PostgreSQL - FULL OUTER JOIN
• PostgreSQL - SELF JOIN
• PostgreSQL - Schema
• PostgreSQL - CREATE SCHEMA
• PostgreSQL - DROP SCHEMA
• PostgreSQL - ALTER SCHEMA

Roles & Permissions

• PostgreSQL - CREATE ROLE
• PostgreSQL - ALTER ROLE
• PostgreSQL - DROP ROLE
• PostgreSQL - GRANT
• PostgreSQL - REVOKE
• PostgreSQL - Role Membership

Working with Sets

• PostgreSQL - UNION operator
• PostgreSQL - INTERSECT Operator
• PostgreSQL - EXCEPT Operator
• PostgreSQL - GROUPING SETS
• PostgreSQL - CUBE
• PostgreSQL - ROLLUP

Subquery & CTEs

• PostgreSQL - ANY Operator
• PostgreSQL - ALL Operator
• PostgreSQL - EXISTS Operator
• PostgreSQL - CTE
• PostgreSQL - Deleting Duplicate Rows using Subquery

User-defined Functions

• PostgreSQL - CREATE FUNCTION Statement
• PostgreSQL - Function parameter modes
• PostgreSQL - Function Overloading
• PostgreSQL - Function that returns table
• PostgreSQL - Drop Function

Important In-Built Functions

• PostgreSQL - AVG() Function
• PostgreSQL - COUNT() Function
• PostgreSQL - MAX() Function
• PostgreSQL - MIN() Function
• PostgreSQL - SUM() Function
• PostgreSQL - FIRST_VALUE Function
• PostgreSQL - LAST_VALUE Function
• PostgreSQL - NTH_VALUE Function
• PostgreSQL - ROW_NUMBER Function
• PostgreSQL - CURRENT_DATE Function
• PostgreSQL - CURRENT_TIME Function
• PostgreSQL - EXTRACT Function
• PostgreSQL- CONCAT Function
• PostgreSQL - FORMAT Function
• PostgreSQL - UPPER function
• PostgreSQL- LOWER function
• PostgreSQL - REGEXP_MATCHES Function
• PostgreSQL - REGEXP_REPLACE Function
• PostgreSQL - REPLACE Function

PostgreSQL PL/pgSQL

• PostgreSQL - Dollar-Quoted String Constants
• PostgreSQL - Block Structure

Variables & Constants

• PostgreSQL - Variables
• PostgreSQL - Select Into
• PostgreSQL - Row type variables
• PostgreSQL - Record type variable
• PostgreSQL - Constants

Stored Procedures

• PostgreSQL - Introduction to Stored Procedures
• PostgreSQL - CREATE PROCEDURE
• PostgreSQL - Drop Procedure

Working with Triggers

• PostgreSQL - Trigger
• PostgreSQL - CREATE TRIGGER
• PostgreSQL - DROP TRIGGER
• PostgreSQL - ALTER TRIGGER
• PostgreSQL - Disabling a Trigger
• PostgreSQL - Enabling a Trigger

Working with Views & Indexes

• PostgreSQL - CREATE INDEX
• PostgreSQL - DROP INDEX
• PostgreSQL - List Indexes
• PostgreSQL - UNIQUE Index
• PostgreSQL - Partial Index
• PostgreSQL - Index On Expression
• PostgreSQL - REINDEX
• PostgreSQL - Multicolumn Indexes

Errors & Exception Handling

• PostgreSQL - Errors and Messages
• PostgreSQL - Exception Handling
• PostgreSQL - Assert

PostgreSQL - Size of a Database

In PostgreSQL, the size of a database can be checked using SQL functions provided by the database system. Here are a few ways you can determine the size of a database:

1. Total Size of a Specific Database

   This query provides the total size of a specific database:

   SELECT pg_size_pretty(pg_database_size('your_database_name')) AS size;
   

   Replace 'your_database_name' with the name of the database you're interested in.

2. Size of All Databases

   This query provides the sizes of all databases in your PostgreSQL instance:

   SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
   FROM pg_database;
   

3. Size of All Tables in a Database

   To get the size of all tables within a particular database:

   SELECT schemaname, tablename, 
          pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
          pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS data_size,
          pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename)) AS external_size
   FROM pg_tables
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
   ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
   

   • pg_total_relation_size(): returns the total size of a table, including all of its associated auxiliary objects (indexes, toast tables, etc.).
   • pg_relation_size(): returns the size of the table itself.

4. Size of Indexes

   If you're interested in the size of the indexes:

   SELECT schemaname, indexname, 
          pg_size_pretty(pg_relation_size(schemaname || '.' || indexname)) AS index_size
   FROM pg_indexes
   WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
   ORDER BY pg_relation_size(schemaname || '.' || indexname) DESC;
   

Always ensure you have necessary permissions to execute these functions, especially if you're not the database administrator.

1. PostgreSQL database size query: To find the size of a PostgreSQL database, you can use the following SQL query:

   SELECT pg_size_pretty(pg_database_size('your_database_name')) AS database_size;
   

2. How to check PostgreSQL database size: You can check the size of a PostgreSQL database using:

   SELECT pg_size_pretty(pg_database_size('your_database_name')) AS database_size;
   

3. PostgreSQL show database size command:

   SELECT pg_size_pretty(pg_database_size('your_database_name')) AS database_size;
   

4. List all databases and sizes in PostgreSQL:

   SELECT datname, pg_size_pretty(pg_database_size(datname)) AS database_size
   FROM pg_database;
   

5. PostgreSQL get database size in MB/GB:

   SELECT pg_size_pretty(pg_database_size('your_database_name')) AS database_size;
   

6. Query to find largest tables in PostgreSQL:

   SELECT table_name, pg_size_pretty(pg_total_relation_size(table_name)) AS table_size
   FROM information_schema.tables
   ORDER BY pg_total_relation_size(table_name) DESC
   LIMIT 10;
   

7. Vacuum PostgreSQL database to reclaim space:

   VACUUM FULL;
   

8. PostgreSQL analyze table size:

   ANALYZE VERBOSE your_table;