PostgreSQL – How to view the Size of a Table

ByAdmin

To get the size of a specific table in PostgreSQL, you can use the pg_total_relation_size function. This function returns the total size of a table in bytes, including the main data file, indexes, and associated objects. Below is the syntax:

SELECT pg_size_pretty(pg_total_relation_size('your_table_name'));

where 'your_table_name' with the name of the table you want to get the size for. When you execute this query, it will return the size of the table in a human-readable format, making it easier to understand.

SELECT pg_size_pretty(pg_total_relation_size('my_table'));

This query will return the size of the “my_table” table in a human-readable format like “123 MB” or “2 GB.”

This query provides an estimate of the total size of the table, including its indexes and related objects. If you want to get more detailed information about the size of individual components (e.g., the table itself, indexes), you can use other PostgreSQL functions and queries like pg_relation_size and pg_indexes_size.

Similar Posts

PostgreSQL – How to Move Sequence to a Different Schema

ByAdmin

To move a sequence to a different schema in PostgreSQL, you can use the ALTER SEQUENCE command. The basic syntax is as follows: ALTER SEQUENCE current_schema.sequence_name SET SCHEMA new_schema; where current_schema is the name of the schema where the sequence currently resides, sequence_name is the name of the sequence, and new_schema is the name of…

PostgreSQL – How to Change a User’s Password

ByAdmin

To change a user’s password in PostgreSQL, you can use the ALTER USER statement. Here’s how you can change a user’s password: Connect to PostgreSQL: You need to connect to your PostgreSQL server using a user account with superuser or administrative privileges, as only superusers can change the passwords of other users. Change the Password:To…

PostgreSQL – How to Extract Date from Timestamp

ByAdmin

You can extract date from a timestamp using various functions depending on the specific part of the date you want to extract. Here are some commonly used functions for extracting different parts of a date from a timestamp: Extracting Date Only (Year, Month, and Day):To extract only the date (year, month, and day) from a…

PostgreSQL – How to Get the Last Day of Month From a Date

ByAdmin

To get the last day of the month from a date in PostgreSQL, you can use the DATE_TRUNC function in combination with some date arithmetic. Here’s how you can do it: SELECT (DATE_TRUNC(‘MONTH’, your_date_column) + INTERVAL ‘1 MONTH – 1 DAY’) AS last_day_of_month FROM your_table; where your_date_column is the name of your date column and…

PostgreSQL – How to Check if User is Superuser

ByAdmin

The is_superuser configuration setting or attribute determines whether a user has superuser privileges or not. Superusers have the highest level of access and control over a PostgreSQL database cluster, including the ability to perform critical administrative functions. The is_superuser setting is a boolean attribute associated with database roles (users). When a user is designated as…