In this article, we will sse how to add or delete a column in PostgreSQL table step by step.
In some cases it is required to add column to a table or delete a column from table once it is created.
Continue reading How to add or delete a column in PostgreSQL table
In this article, we will see how to find all columns of a table in PostgreSQL.
A postgresql table not only contains user defined columns. It also adds column names like xmin, xmax,….
Find the all column names: Continue reading How to find all columns of a table in PostgreSQL
In this article, we will see How to create tablespace in PostgreSQL with examples and other actions can be performed related to PostgreSQL tablespace.
A tablespace is a location on the disk where PostgreSQL stores data files containing database objects e.g., tables, and indexes. PostgreSQL uses a tablespace to map a logical name to a physical location on disk.
PostgreSQL comes with two default tablespaces:
pg_default tablespace stores user data.
pg_global tablespace stores global data.
CREATE TABLESPACE tablespace_name [ OWNER owner_name] LOCATION 'directory'
Continue reading How to create tablespace in PostgreSQL with examples
In this article, we will see how to grant select to all tables in PostgreSQL. We can grant select privilege in PostgreSQL by using grant command.
Syntax to grant select to all tables in PostgreSQL:
Continue reading How to grant select to all tables in PostgreSQL
In this article, we will see PostgreSQL copy command with examples.
PostgreSQL COPY command moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query.
COPY FROM syntax:
Continue reading PostgreSQL copy command with examples
In this article, we will see how to do schema only backup and restore in PostgreSQL.
Schema only backup contains only objects definition like table definition.
To take a schema level backup in PostgreSQL database and restore on the another database, we use pg_dump utility with the option –schema-only or -s. If we gave –schema-only means, it dumps only the object definitions (schema), not data.
Continue reading How to do schema only backup and restore in PostgreSQL?
In this article, we will see solution for “ERROR: permission denied for table ‘table_name’ in PostgreSQL”
Reason: User doent have permissions to access that particular table or relation or view.
Continue reading ERROR: permission denied for table ‘table_name’ in PostgreSQL
In this article, we will see how to move tables from one schema to another in PostgreSQL. We can move the tables from one schema to another by using alter table command. Schemas are database objects. Same schema can be exists in different. But, tables of schema are different.
Syntax:
ALTER TABLE table_name SET SCHEMA schema_name;
Examples to move tables from one schema to another in PostgreSQL:
Continue reading How to move tables from one schema to another in PostgreSQL
In this article, we are going to how to take backup and restore table in PostgreSQL. We use pg_dump to take backup of table wiht –table option and psql or pg_restore tools are used to restore the table using existing backup.
1. Syntax to backup table data:
pg_dump -Fc --data-only -h
-p -U -W -d -t > /path_and_filename_forbackup
Continue reading How to take backup and restore table in PostgreSQL
In this article, we will see how to take backup of a table(s) in PostgreSQL. We can take the backup of single or multiple tables using pg_dump command with –table or -t option.
Syntax to take backup of table(s) in PostgreSQL:
Following syntax is used to take backup of table with table definition:
pg_dump -d databasename --table=yourTable --column-inserts > file_name.sql
Continue reading How to take backup of a table(s) in PostgreSQL