In this article, we will see how to find PostgreSQL table size and number of rows with single query. We can the table size and number of rows by querying against the table pg_stat_user_tables.
Query to find PostgreSQL table size and number of rows
Following query will return the PostgreSQL table size and number of rows of that table.
select pg_relation_size(relid) as tablesize,schemaname, n_live_tup from pg_stat_user_tables where relname='table_name';
In this article, we will see how to take backup and restore PostgreSQL table.
It is administrator regular activity to take backup and restore PostgreSQL table from production server to development or UAT servers.
PostgreSQL Tools for Backup and Restore table:
1. pg_dump: Extract a PostgreSQL table into a script file or other archive file.
2. psql: PostgreSQL interactive terminal used to load backup taken using pg_dump.
3. scp is used to copy backup of production to development server.
Note: pg_dump,psql, scp needs to be executed from Linux shell. Not from psql tool.
On Source PostgreSQL Server:
1. Take backup of table using pg_dump.
2. SCP the backup file from source to target(copy backup file from source to target)
On Target PostgreSQL Server:
3. Restore the table using psql tool
4. Verify the table size with production
Step 1. Take backup of table using pg_dump
Here, I am going to take backup of the table ‘student’ from the database ‘r2schools’ on source PostgreSQL server.
Following error received when tried to drop table from PostgreSQL server.
ERROR: cannot drop table emp because other objects depend on it
DETAIL: view customer_view depends on table emp
HINT: Use DROP ... CASCADE to drop the dependent objects too.
In this article, we will see how to find the table size in PostgreSQL. We can find the table size by using pg_relation_size. pg_relation_size returns the size of a table in bytes. If we want more readable format, use pg_size_pretty() function.
We can also find the size of PostgreSQL table, using meta command \dt+ table_name.
In this article, we will see how to rename PostgreSQL Table. ALTER TABLE is the command used to rename table in PostgreSQL Server. To execute ALTER TABLE, we must be owner of table or superuser or granted alter table permission on the table.
In this article, we are going to see How to get the PostgreSQL table structure. Table structure is nothing but to get the list of columns, datatypes and constrains information.
In this tutorials, we have explained how to check list of privileges on a table in PostgreSQL. We can get the privileges by using SQL statement and meta-command.
1 . SQL Statement to get list of permissions on a table.
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='customer'
In this article, we will see how to change owner of table in PostgreSQL. To change owner of the table, you must be owner of the table or must have alter table or superuser permissions.
It is database administrator routine task to change table owner in PostgreSQL. This can be done with ALTER TABLE statement.
Syntax to Change table owner in PostgreSQL:
ALTER TABLE public.table_name OWNER TO another_username;
Verify the owner of the table by running below command: