Tag: Table

How to find PostgreSQL table size and number of rows with single query

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';

Example to find PostgreSQL table size and number of rows
Continue reading How to find PostgreSQL table size and number of rows with single query

Related Posts:

  1. How to check list of privileges on a table in PostgreSQL
  2. How to find the table size in PostgreSQL
  3. How to get the PostgreSQL table structure
  4. How to take backup and restore a PostgreSQL table

How to take backup and restore a PostgreSQL table

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.

pg_dump -d r2schools -t student > /opt/PGBACKUPS/student_table.sql

Step 2. SCP the backup file from source to target

scp -p student_table.sql root@mongodb2:/home/r2schools/pgbackups

Step 3. Restore the table using psql tool

Connect to the target PostgreSQL server and navigate to backup file scp location and verify production backup copied or not.

postgres@mongodb2:/home/r2schools/pgbackups$ pwd
/home/r2schools/pgbackups
postgres@mongodb2:/home/r2schools/pgbackups$ ls
student_table.sql

Now perform restore operation of table ‘student’ on target server using psql command. Here my target server hostname is ‘mongodb2’.

psql -d r2schools < student_table.sql

Where -d is the database in which table exists.

Step 4. Verify the table size with production

Connect to the r2schools database and verify the total row count of table on source and target PostgreSQL servers.

On Source:

On Target:

Both source and target servers contains same number of rows.

So in this article, we have successfully performed backup and restore of PostgreSQL table.

Related Posts:

  1. How to check list of privileges on a table in PostgreSQL
  2. How to get the PostgreSQL table structure
  3. How to perform custom format backup and restore of PostgreSQL database
  4. How to take backup and restore a PostgreSQL Database

How to change column datatype in PostgreSQL

In this article, we will see how to change column datatype in PostgreSQL. We can change the data type of a column by using the ALTER TABLE statement.

Two scenarios are there:
1. Change column datatype of table with data.
2. Change column datatype of table without data.

PostgreSQL change column data type statement:

To change the data type of a column, you use the ALTER TABLE statement as follows:

ALTER TABLE table_name
 ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

Continue reading How to change column datatype in PostgreSQL

Related Posts:

  1. How to check list of privileges on a table in PostgreSQL
  2. How to find the table size in PostgreSQL
  3. How to get the PostgreSQL table structure
  4. How to rename column in PostgreSQL

ERROR: cannot drop table because other objects depend on it

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.

ERROR: cannot drop table because other objects depend on it

Continue reading ERROR: cannot drop table because other objects depend on it

Related Posts:

  1. How to check list of privileges on a table in PostgreSQL
  2. How to find the table size in PostgreSQL
  3. How to get the PostgreSQL table structure
  4. How to take backup and restore a PostgreSQL table

How to find the table size in PostgreSQL

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.

Find the table size in PostgreSQL examples:

1. Find the PostgreSQL table size using pg_relation_size.
Continue reading How to find the table size in PostgreSQL

Related Posts:

  1. How to check list of privileges on a table in PostgreSQL
  2. How to find PostgreSQL table size and number of rows with single query
  3. How to get the PostgreSQL table structure
  4. PostgreSQL describe table

How to rename column in PostgreSQL

In this article, we will see how to rename a column in PostgreSQL with examples using ALTER TABLE command with RENAME parameter.

To rename a column of a table, you use the ALTER TABLE statement with RENAME COLUMN clause as follows:

Syntax to PostgreSQL Rename column:

ALTER TABLE table_name 
RENAME COLUMN column_name TO new_column_name;

Continue reading How to rename column in PostgreSQL

Related Posts:

  1. How to drop multiple columns in PostgreSQL
  2. How to move tables from one schema to another in PostgreSQL
  3. How to rename PostgreSQL Table
  4. How to rename table in PostgreSQL

How to rename PostgreSQL Table

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.

PostgreSQL Rename Table Syntax:

ALTER TABLE name RENAME TO new_name

Continue reading How to rename PostgreSQL Table

Related Posts:

  1. How to check list of privileges on a table in PostgreSQL
  2. How to get the PostgreSQL table structure
  3. How to rename column in PostgreSQL
  4. How to rename PostgreSQL User

How to get the PostgreSQL table structure

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.

PostgreSQL table structure using meta command:

\d+ table_name;

Example:

\d+ dept

Output:
Continue reading How to get the PostgreSQL table structure

Related Posts:

  1. Check the database size in PostgreSQL
  2. How to check list of privileges on a table in PostgreSQL
  3. How to take backup and restore a PostgreSQL table
  4. psql FATAL Ident authentication failed for user

How to check list of privileges on a table in PostgreSQL

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'

Continue reading How to check list of privileges on a table in PostgreSQL

Related Posts:

  1. Change table owner in PostgreSQL
  2. Grant and Revoke Privileges in PostgreSQL
  3. How to grant permissions on all tables to a user in PostgreSQL
  4. How to take backup and restore a PostgreSQL table

Change table owner in PostgreSQL

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:

\dt table_name;

Example:
Continue reading Change table owner in PostgreSQL

Related Posts:

  1. Grant and Revoke Privileges in PostgreSQL
  2. How to grant permissions on all tables to a user in PostgreSQL
  3. Lock and Unlock PostgreSQL User
  4. PostgreSQL Create User