How to locate the files in which a table or index (relation) is stored in PostgreSQL

PostgreSQL refers to tables and indexes as relations. In every database you will find information (meta data) about relations in the pg_class table.

Relations are stored in files in a directory for each database in the base directory. (If you don't know how to locate the base directory or the database's directory you can refer to my blog entries How to locate the data or cluster directory in PostgreSQL and How to locate the directory that contains a database in PostgreSQL)

Each file on the hard drive will refer to only one relation. The maximum file size of a file is 1GB. If a relation is larger than 1GB he relation will be stored in more than one file. The relation's file name is stored in the relfilenode field in the pg_class table. The relations's name itself is stored in the relname field of the pg_class table.

The query to locate the relation's file name is as follows:

The table employee is therefore stored in a files called 433981 on the hard drive. The file will be located in the database's directory in the data directory.

If the table is larger than 1 GB the table will be stored in multiple files like 24765, 24765.1 and 24765.2.

How to locate the directory that contains a database in PostgreSQL

First of all you need to locate the data directory in which the databases are stored. If you do not know where to find the data directory you can read my blog entry: How to locate the data or cluster directory in PostgreSQL

In the data directory you will find a base directory and in the base directory there is a single directory per database. A database's directory name will always be a number.

The query

will display the Object ID and a database name for every database in the cluster.

Every database is stored in a single directory in the .../data/base directory. The database's directory name is the same as the databases's Object ID.

For example: The postgres database is stored in the 12603 directory.

How to log all the queries in PostgreSQL

The logging settings for PostgreSQL are contained in the postgresql.conf file in the data folder. (It is always a good idea to make a backup of the postgresql.conf file before you make any changes to it.)

Open the postgresql.conf file with a text editor. The logging options are after the following lines:

#------------------------------------------------------------------------------

# ERROR REPORTING AND LOGGING

#------------------------------------------------------------------------------

Change:

#log_min_duration_statement = -1

to:

log_min_duration_statement = 0

and change

#log_statement = 'none'

to:

log_statement = 'all'

Make sure that you remove the # before the changed lines.

Save the amended postgresql.conf file and restart the PostgreSQL service. Alternatively you can reload the postgresql.com file while the server is running with pg_ctl reload.

Change a column name in PostgreSQL, MySql and SQL Server

I am often asked how to rename a column in an existing (SQL) table.

First of all I want to warn that one should be careful when changing column names in existing tables as the changes can affect scripts, functions and procedures written for the database.

The queries to change column names are fairly easy to understand, but the syntax used for SQL Server, MySQL and PostgreSQL differs from each other..

The basic queries for the three database systems are as follows:

PostgreSQL

MySQL

SQL Server

Example:

The queries will be as follows:

PostgreSQL

MySQL

SQL Server

The result:

How to locate the data or cluster directory in PostgreSQL

If you used the one-click installer from EnterpriseDB to install a new version of PostgreSQL and left all the settings on their default settings the data directory will be as follows:

1.            On Windows 

1.            c:\Program Files\PostgreSQL\10\data or;

2.            c:\Program Files\PostgreSQL(x86)\10\data

2.            On Linux (CentOS and OpenSuse) /opt/PostgreSQL/10/data

You can however change the data folder while installing PostgreSQL(or even afterwards), which can make it difficult to locate the data directory at a later stage. You can use PostgreSQL's data dictionary to locate the data directory. To do this you need to run the following query from pgAdminIII or psql:

Alternatively you can use:

Deleting rows with duplicate values from a table

The following query can be used to delete rows with duplicate values from a table. This is very useful when the table is large and there are a lot of duplicate values.

The table must have a primary key or other unique field which can be used to identify the duplicate records. If the table does not have a primary key or other unique field it can be added. See my post Adding a primary key to a table.

Example:

In this example id is the primary key and student_no 000113, 000313, 000413. 000613 and 000913 are duplicated.

The following query will delete the duplicate rows:

The result: