12 Most Asked PostgreSQL Questions
Tags: postgres
Published: 2020-07-03 07:26:57

PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows, and OS X. it allows you to add custom functions developed using different programming languages such as C/C++, Java, etc. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. It is the default database for macOS Server.

As developers, we have answered the most frequently asked questions about PostgreSQL and also have confirmed that the solution works perfectly. So, here is the list for PostgreSQL questions and answers.

## Rundown For The 12 Most Asked PostgreSQL Questions

Below are the frequently asked questions about PostgreSQL.

### 1. How to perform “DESCRIBE TABLE” in PostgreSQL?

**Answer:**

You can try this (in the `psql` command-line tool):

```
\d+ tablename
```

**Also,**

In addition to the PostgreSQL way (\d ‘something’ or \dt ‘table’ or \ds ‘sequence’ and so on)

The SQL standard way, as shown here:

```
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = '<name of 
table>';
```

It’s supported by many DB engines.

### 2. How to switch databases in PostgreSQL?

**Answer:**

In PostgreSQL, you can use the `\connect` meta-command of the client tool psql:

```
\connect DBNAME
```

or in short:

```
\c DBNAME
```

Also, you can select the database when connecting with psql. This is handy when using it from a script:

```
sudo -u postgres psql -c "CREATE SCHEMA test AUTHORIZATION test;" test
```

### 3. Which version of PostgreSQL are we running?

**Answer:**

To check the current running version of PostgreSQL run this query from PostgreSQL:

```
SELECT version();
```

Also, you can use the below version to check the server version and client version of PostgreSQL

Server version:

```
pg_config --version
```

Client version:

```
psql --version
```

You can also use this method

**Using CLI:**

**Server version:**

```
$ postgres -V  # Or --version.  Use "locate bin/postgres" if not found.
postgres (PostgreSQL) 9.6.1
$ postgres -V | awk '{print $NF}'  # Last column is version.
9.6.1
$ postgres -V | egrep -o '[0-9]{1,}\.[0-9]{1,}'  # Major.Minor version
9.6
```

If having more than one installation of PostgreSQL, or if getting the “`postgres: command not found`” error:

```
$ locate bin/postgres | xargs -i xargs -t '{}' -V  # xargs is intentionally twice.
/usr/pgsql-9.3/bin/postgres -V 
postgres (PostgreSQL) 9.3.5
/usr/pgsql-9.6/bin/postgres -V 
postgres (PostgreSQL) 9.6.1
```

If `locate` doesn’t help, try `find`:

```
$ sudo find / -wholename '*/bin/postgres' 2>&- | xargs -i xargs -t '{}' -V  # xargs is intentionally twice.
/usr/pgsql-9.6/bin/postgres -V 
postgres (PostgreSQL) 9.6.1
```

Although `postmaster` can also be used instead of `postgres`, using `postgres` is preferable because `postmaster` is a deprecated alias of `postgres`.

**Client version:**

As relevant, login as `postgres`.

```
$ psql -V  # Or --version
psql (PostgreSQL) 9.6.1
```

If having more than one installation of PostgreSQL:

```
$ locate bin/psql | xargs -i xargs -t '{}' -V  # xargs is intentionally twice.
/usr/bin/psql -V 
psql (PostgreSQL) 9.3.5
/usr/pgsql-9.2/bin/psql -V 
psql (PostgreSQL) 9.2.9
/usr/pgsql-9.3/bin/psql -V 
psql (PostgreSQL) 9.3.5
```

**Using SQL:**

**Server version:**

```
=> SELECT version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

=> SHOW server_version;
 server_version 
----------------
 9.2.9

=> SHOW server_version_num;
 server_version_num 
--------------------
 90209
```

If more curious, try `=> SHOW all;`.

**Client version:**

For what it’s worth, a shell command can be executed within `psql` to show the client version of the `psql` executable in the path. Note that the running `psql` can potentially be different from the one in the path.

```
=> \! psql -V
psql (PostgreSQL) 9.2.9
```

### 4. How to drop all the tables in a PostgreSQL database?

**Answer:**

If all of your tables are in a single schema, this approach could work (below code assumes that the name of your schema is `public`)

```
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
```

If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.

```
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;
```

**Another way:**

You can write a query to generate a SQL script like this:

```
select 'drop table "' || tablename || '" cascade;' from pg_tables;
```

Or:

```
select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;
```

In case some tables are automatically dropped due to the cascade option in a previous sentence.

Additionally, you might want to filter the tables you want to drop by schema name:

```
select 'drop table if exists "' || tablename || '" cascade;' 
  from pg_tables
 where schemaname = 'public'; -- or any other schema
```

And then run it.

### 5. How to start the PostgreSQL server on Mac OS X?

**Answer:**

The <a href="https://brew.sh/">Homebrew</a> package manager includes launchctl plists to start automatically. For more information run `brew info postgres`.

**Start manually:**

`pg_ctl -D /usr/local/var/postgres start`

**Stop manually:**

`pg_ctl -D /usr/local/var/postgres stop`

**Start automatically:**

“To have launchd start PostgreSQL now and restart at login:”

`brew services start postgresql`

What is the result of `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start`?

What is the result of `pg_ctl -D /usr/local/var/postgres status`?

Are there any error messages in the server.log?

Make sure TCP localhost connections are enabled in pg_hba.conf:

```
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
```

Check the listen_addresses and port in postgresql.conf:

`egrep 'listen|port' /usr/local/var/postgres/postgresql.conf`

```
#listen_addresses = 'localhost'     # what IP address(es) to listen on;
#port = 5432                # (change requires restart)
```

**Cleaning up Postgres** was most likely installed via Homebrew, <a href="http://www.finkproject.org/">Fink</a>, <a href="https://www.macports.org/">MacPorts</a>, or the <a href="https://www.enterprisedb.com/node/16#osx">EnterpriseDB</a> installer.

Check the output of the following commands to determine which package manager it was installed with:

```
brew && brew list|grep postgres
fink && fink list|grep postgres
port && port installed|grep postgres
```

Also if you want to manually start and stop PostgreSQL (installed via homebrew), the easiest way is:

```
brew services start postgresql
```

and

```
brew services stop postgresql
```

If you have a specific version, make sure to suffix the version, for example:

```
brew services start postgresql@10
```

### 6. How to change the PostgreSQL user password?

**Answer:**

For passwordless login:

```
sudo -u user_name psql db_name
```

To reset the password if you have forgotten:

```
ALTER USER user_name WITH PASSWORD 'new_password';
```

Option2

Type:

```
$ sudo -u postgres psql
```

Then:

```
\password postgres
```

Then to quit `psql`:

```
\q
```

If that does not work, reconfigure authentication.

Edit `/etc/postgresql/9.1/main/pg_hba.conf` (the path will differ) and change:

```
    local   all             all                                peer
```

to:

```    
    local   all             all                                     md5
```

Then restart the server:

```
$ sudo service postgresql restart
```

### 7. How to save PL/pgSQL output from PostgreSQL to a CSV file?

**Answer:**

**Server-side**

If you want something easy to re-use or automate, you can use Postgresql’s built-in <a href="https://www.postgresql.org/docs/current/sql-copy.html">COPY</a> command. e.g.

```
Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;
```

**This approach runs entirely on the remote server** – it can’t write to your local PC. It also needs to be run as a Postgres “superuser” (normally called “root”) because Postgres can’t stop it doing nasty things with that machine’s local filesystem.

**Client-side**

The other approach is to **do the file handling on the client-side**, i.e. in your application or script. The Postgres server doesn’t need to know what file you’re copying to, it just spits out the data and the client puts it somewhere.

The underlying syntax for this is the `COPY TO STDOUT` command and graphical tools like pgAdmin will wrap it for you in a nice dialog.

The `psql` command-line client has a special “meta-command” called `\copy`, which takes all the same options as the “real” `COPY`, but is run inside the client:

```
\copy (Select * From foo) To '/tmp/test.csv' With CSV
```

Note that there is no terminating `;`, because meta-commands are terminated by newline, unlike SQL commands.

Your application programming language may also have support for pushing or fetching the data, but you cannot generally use `COPY FROM STDIN/TO STDOUT` within a standard SQL statement, because there is no way of connecting the input/output stream. PHP’s PostgreSQL handler (not PDO) includes very basic `pg_copy_from` and `pg_copy_to` functions which copy to/from a PHP array, which may not be efficient for large data sets.

There are several solutions:

**a. `psql` command**

`psql -d dbname -t -A -F"," -c "select * from users" > output.csv`

This has the big advantage that you can using it via `SSH, like ssh postgres@host command` – enabling you to get

**b. postgres `copy` command**

`COPY (SELECT * from users) To '/tmp/output.csv' With CSV;`

**c. psql interactive (or not)**

```
>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q
```

All of them can be used in scripts, but you can prefer #1.

**d. pgadmin but that’s not scriptable.**

### 8. How to create a copy of a database in PostgreSQL?

Postgres allows the use of any existing database on the server as a template when creating a new database. You should be able to execute the following in a query window if it doesn’t:

```
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
```

Still, you may get:

```
ERROR:  source database "originaldb" is being accessed by other users
```

To disconnect all other users from the database, you can use this query:

```
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();
```

**Alternative Solution:**

To clone an existing database with Postgres you can do that

```
/* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid <> pg_backend_pid();

/* CLONE DATABASE TO NEW ONE(TARGET_DB) */
CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;
```

It will kill all the connection to the source DB avoiding the error

```
ERROR:  source database "SOURCE_DB" is being accessed by other users
```

### 9. How to fix error “Fatal: role “username” does not exist”?

Use the operating system user `postgres` to create your database – as long as you haven’t set up a database role with the necessary privileges that corresponds to your operating system user of the same name (`h9uest` in your case):

```
sudo -u postgres -i
```

As recommended <a href="https://serverfault.com/questions/359856/what-is-the-difference-between-sudo-i-and-sudo-su">here</a> or <a href="https://serverfault.com/questions/601140/whats-the-difference-between-sudo-su-postgres-and-sudo-u-postgres">here</a>.

Then try again. Type `exit` when done with operating as system user `postgres`.

**Or**

execute the single command `createuser` as `postgres` with `sudo'`

The point is to use the operating system user matching the database role of the same name to be granted access via `ident authentication`. `postgres` is the default operating system user to have initialized the database cluster. 

<a href="https://www.postgresql.org/docs/current/database-roles.html">The manual:</a>

In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a “superuser”, and by default (unless altered when running `initdb`) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named `postgres`. In order to create more roles you first have to connect as this initial role.

Read about <a href="https://www.postgresql.org/docs/current/user-manag.html">database roles</a> and <a href="https://www.postgresql.org/docs/current/client-authentication.html"client authentication</a> in the manual.

**Also you can try this solution**

In short, running

```
sudo -u postgres createuser owning_user
```

creates a role with name owning_user (in this case, h9uest). After that you can run `rake db:create` from the terminal under whatever account name you set up without having to enter into the Postgres environment.

### 10. How to fix error “Peer authentication failed for user “Postgres”?

**Answer:**

The problem is still your `pg_hba.conf` file (`/etc/postgresql/9.1/main/pg_hba.conf*`).

This line:

```
local   all             postgres                                peer
```

Should be:

```
local   all             postgres                                md5
```

If you can’t find this file, running `locate pg_hba.conf` should show you where the file is.

After altering this file, don’t forget to restart your PostgreSQL server. If you’re on Linux, that would be `sudo service postgresql restart`.

These are brief descriptions of both options according to the <a href="https://www.postgresql.org/docs/9.3/auth-methods.html">official PostgreSQL docs on authentication methods.</a>

**Peer authentication**

The peer authentication method works by obtaining the client’s operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

**Password authentication**

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

If you are at all concerned about password “sniffing” attacks then md5 is preferred. A plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then the password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).

Sample location for `pg_hba.conf`:

`/etc/postgresql/9.1/main/pg_hba.conf`

### 11. How to drop a PostgreSQL database if there are active connections to it?

**Answer:**

This will drop existing connections except for yours:

Query` pg_stat_activity` and get the pid values you want to kill, then issue `SELECT pg_terminate_backend(pid int)` to them.

**PostgreSQL 9.2 and above:**

```
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND pid <> pg_backend_pid();
```

**PostgreSQL 9.1 and below:**

```
SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
  AND procpid <> pg_backend_pid();
```

Once you disconnect everyone you will have to disconnect and issue the DROP DATABASE command from a connection from another database aka not the one you’re trying to drop.

Note the renaming of the `procpid` column to `pid`. See <a href="https://www.postgresql.org/message-id/E1T6wSQ-0005kN-LD@wrigleys.postgresql.org">this mailing list thread</a>.

### 12. How to Insert or perform multiple updates in PostgreSQL?

**Answer:**

PostgreSQL since version 9.5 has <a href="https://www.postgresql.org/docs/devel/sql-insert.html">UPSERT</a> syntax, with <a href="https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT">ON CONFLICT</a> clause. with the following syntax (similar to MySQL)

```
INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
      column_2 = excluded.column_2;
```

Searching PostgreSQL’s email group archives for “upsert” leads to finding <a href="https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE">an example of doing what you possibly want to do, in the manual</a>:

**Example: Exceptions with UPDATE/INSERT**

This example uses exception handling to perform either UPDATE or INSERT, as appropriate:

```
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        -- note that "a" must be unique
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');
```

There’s possibly an example of how to do this in bulk, using CTEs in 9.1 and above, in the <a href="https://www.postgresql.org/message-id/BANLkTi=AFampn19+VrbHa7EbL45XvjUgiQ@mail.gmail.com">hackers mailing list</a>:

```
WITH foos AS (SELECT (UNNEST(%foo[])).*)
updated as (UPDATE foo SET foo.a = foos.a ... RETURNING foo.id)
INSERT INTO foo SELECT foos.* FROM foos LEFT JOIN updated USING(id)
WHERE updated.id IS NULL;
```

**In Conclusion**

This is the list for most frequently asked questions with solutions about PostgreSQL. Hope this blog helped you with your problem if you haven’t found what you are looking for. Please feel free to comment if you need any help. We will get in touch with you as soon as possible.