Top 12 FAQs About PostgreSQL

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 command-line tool): psql \d + tablename span /code> 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: column_name , data_type , character_maximum_length INFORMATION_SCHEMA . COLUMNS table_name = ; class " language-sql" class "token keyword" select class "token punctuation" class "token punctuation" class "token keyword" from class "token punctuation" class "token keyword" class "token keyword" where class "token operator" class "token string" '&lt;name of table&gt;' class "token punctuation" It’s supported by many DB engines. 2. How to switch databases in PostgreSQL? Answer: In PostgreSQL, you can use the meta-command of the client tool psql: \connect \ connect class " language-sql" class "token keyword" /span> DBNAME \c DBNAME code class " language-sql" code Also, you can select the database when connecting with psql. This is handy when using it from a script: sudo - u postgres psql - c class " language-sql" class "token operator" span class "token operator" span class "token string" "CREATE SCHEMA test AUTHORIZATION test;" /span> test version ( class " language-sql" SELECT span class "token keyword" span class "token punctuation" span ) span class "token punctuation" span ; span class "token punctuation" span code Also, you can use the below version to check the server version and client version of PostgreSQL Server version: pg_config class --version Client version: psql --version code class " language-sh" code You can also use this method Using CLI: Server version: $ postgres - V postgres ( PostgreSQL ) $ postgres - V | awk $ postgres - V | egrep - o class " language-sql" class "token operator" class "token comment" # Or --version. Use "locate bin/postgres" not found. if class "token punctuation" class "token punctuation" class "token number" 9.6 class "token number" .1 class "token operator" class "token operator" class "token string" '{print $NF}' class "token comment" # Last column is version. class "token number" 9.6 class "token number" .1 class "token operator" class "token operator" class "token operator" class "token string" '[0-9]{1,}\.[0-9]{1,}' class "token comment" # Major.Minor version class "token number" 9.6 If having more than one installation of PostgreSQL, or if getting the “ ” error: postgres: command not found $ locate bin / postgres | xargs - i xargs - t - V / usr / pgsql - / bin / postgres - V postgres ( PostgreSQL ) / usr / pgsql - / bin / postgres - V postgres ( PostgreSQL ) class " language-sql" class "token operator" class "token operator" class "token operator" class "token operator" class "token string" '{}' class "token operator" class "token comment" # xargs is intentionally twice. class "token operator" class "token operator" class "token operator" class "token number" 9.3 class "token operator" class "token operator" class "token operator" class "token punctuation" class "token punctuation" class "token number" 9.3 class "token number" .5 class "token operator" class "token operator" class "token operator" class "token number" 9.6 class "token operator" class "token operator" class "token operator" class "token punctuation" class "token punctuation" class "token number" 9.6 class "token number" .1 If doesn’t help, try : locate find $ sudo find / - wholename &gt; &amp; - | xargs - i xargs - t - V / usr / pgsql - / bin / postgres - V postgres ( PostgreSQL ) class " language-sql" class "token operator" class "token operator" class "token string" '*/bin/postgres' class "token number" 2 class "token operator" class "token operator" class "token operator" class "token operator" class "token operator" class "token operator" class "token string" '{}' class "token operator" class "token comment" # xargs is intentionally twice. class "token operator" class "token operator" class "token operator" class "token number" 9.6 class "token operator" class "token operator" class "token operator" class "token punctuation" class "token punctuation" class "token number" 9.6 class "token number" .1 Although can also be used instead of , using is preferable because is a deprecated alias of . postmaster postgres postgres postmaster postgres Client version: As relevant, login as . postgres $ psql - V psql ( PostgreSQL ) class " language-sql" class "token operator" class "token comment" # Or --version class "token punctuation" class "token punctuation" class "token number" 9.6 class "token number" .1 If having more than one installation of PostgreSQL: $ locate bin / psql | xargs - i xargs - t - V / usr / bin / psql - V psql ( PostgreSQL ) / usr / pgsql - / bin / psql - V psql ( PostgreSQL ) / usr / pgsql - / bin / psql - V psql ( PostgreSQL ) class " language-sql" class "token operator" class "token operator" class "token operator" class "token operator" class "token string" '{}' class "token operator" class "token comment" # xargs is intentionally twice. class "token operator" class "token operator" class "token operator" class "token operator" class "token punctuation" class "token punctuation" class "token number" 9.3 class "token number" .5 class "token operator" class "token operator" class "token operator" class "token number" 9.2 class "token operator" class "token operator" class "token operator" class "token punctuation" class "token punctuation" class "token number" 9.2 class "token number" .9 class "token operator" class "token operator" class "token operator" class "token number" 9.3 class "token operator" class "token operator" class "token operator" class "token punctuation" class "token punctuation" class "token number" 9.3 class "token number" .5 Using SQL: Server version: SELECT version -------------------------------------------------------------------------------------------------------------- PostgreSQL x86_64 - unknown - linux - gnu , gcc ( GCC ) Red Hat = SHOW server_version ---------------- SHOW server_version_num -------------------- class " language-sql" = span class "token operator" span &gt; span class "token operator" span class "token keyword" /span> version ( span class "token punctuation" span ; span class "token punctuation" span class "token comment" span class "token number" 9.2 /span> .9 span class "token keyword" span class "token operator" span class "token operator" span class "token operator" span class "token punctuation" /span> compiled by span class "token punctuation" /span> 4.4 span class "token number" span class "token number" 20120313 /span> ( .7 span class "token operator" span 4 span class "token number" span ) span class "token punctuation" span , span class "token punctuation" span class "token number" 64 /span> - span class "token keyword" span class "token operator" span &gt; span class "token operator" span class "token keyword" /span> server_version ; span class "token number" 9.2 /span> .9 span class "token operator" span &gt; span class "token operator" span class "token keyword" /span> server_version_num ; span class "token number" 90209 /span> SHOW all Client version: For what it’s worth, a shell command can be executed within to show the client version of the executable in the path. Note that the running can potentially be different from the one in the path. psql psql psql \ ! psql - V psql ( PostgreSQL ) class " language-sql" = span class "token operator" span &gt; span class "token operator" span class "token operator" span class "token operator" span class "token punctuation" span class "token punctuation" /span> 9.2 span class "token number" span code 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 SCHEMA CREATE class " language-sql" DROP span class "token keyword" span class "token keyword" /span> public span class "token keyword" span ; span class "token punctuation" span class "token keyword" /span> SCHEMA span class "token keyword" span ; span class "token punctuation" span code If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants. GRANT ALL ON SCHEMA TO postgres ; GRANT ALL ON SCHEMA TO ; class " language-sql" class "token keyword" class "token keyword" class "token keyword" class "token keyword" class "token keyword" public class "token keyword" class "token punctuation" class "token keyword" class "token keyword" class "token keyword" class "token keyword" class "token keyword" public class "token keyword" class "token keyword" public class "token punctuation" You can write a query to generate a SQL script like this: Another way: || tablename || pg_tables ; class class select class 'drop table "' class class class '" cascade;' class from class Or: || tablename || pg_tables ; class class select class 'drop table if exists "' class class class '" cascade;' class from class 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: || tablename || pg_tables schemaname = ; -- or any other schema class " language-sql" class "token keyword" select class "token string" 'drop table if exists "' class "token operator" class "token operator" class "token string" '" cascade;' class "token keyword" from class "token keyword" where class "token operator" class "token string" 'public' class "token punctuation" class "token comment" And then run it. 5. How to start the PostgreSQL server on Mac OS X? Answer: The package manager includes launchctl plists to start automatically. For more information run . Homebrew 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: host all trust span class "token comment" span class "token keyword" /span> all span class "token number" span .0 span class "token number" span .1 span class "token number" span / span class "token operator" span 32 span class "token number" span /code> Check the listen_addresses and port in postgresql.conf: egrep 'listen|port' /usr/local/var/postgres/postgresql.conf #listen_addresses = # what IP address(es) to listen on; #port = # (change requires restart) code class " language-sql" class "token comment" 'localhost' class "token comment" 5432 code was most likely installed via , , , or the installer. Cleaning up Postgres Homebrew Fink MacPorts EnterpriseDB Check the output of the following commands to determine which package manager it was installed with: brew &amp;&amp; brew list | grep postgres fink &amp;&amp; fink list | grep postgres port &amp;&amp; port installed | grep postgres span class "token operator" span class "token operator" span class "token operator" span class "token operator" span class "token operator" span /code> Also if you want to manually start and stop PostgreSQL (installed via homebrew), the easiest way is: brew services start class " language-sql" class "token keyword" /span> postgresql services stop postgresql code " language-sql" brew code If you have a specific version, make sure to suffix the version, for example: brew services start postgresql @10 code class " language-sql" span class "token keyword" span span class "token variable" span code 6. How to change the PostgreSQL user password? Answer: For passwordless login: sudo -u user_name psql db_name code class "" code To reset the password if you have forgotten: user_name ; class ALTER USER WITH PASSWORD 'new_password' Option2 Type: $ sudo -u postgres psql code class "" code Then: \ class password postgres code Then to quit : psql \ "" q If that does not work, reconfigure authentication. Edit (the path will differ) and change: /etc/postgresql/9.1/main/pg_hba.conf class local all all peer code to: class local all all md5 code Then restart the server: $ class sudo service postgresql restart code 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 command. e.g. COPY ( * foo) CSV ; class Copy Select From To '/tmp/test.csv' With DELIMITER ',' HEADER – 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. This approach runs entirely on the remote server Client-side The other approach is to , 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. do the file handling on the client-side The underlying syntax for this is the command and graphical tools like pgAdmin will wrap it for you in a nice dialog. COPY TO STDOUT The has a special “meta-command” called , which takes all the same options as the “real” , but is run inside the client: psql command-line client \copy COPY \ ( * foo) CSV class copy Select From To '/tmp/test.csv' With Note that there is no terminating , because meta-commands are terminated by newline, unlike SQL commands. ; Your application programming language also have support for pushing or fetching the data, but you cannot generally use within a standard SQL statement, because there is no way of connecting the input/output stream. PHP’s PostgreSQL handler ( PDO) includes very basic and functions which copy to/from a PHP array, which may not be efficient for large data sets. may COPY FROM STDIN/TO STDOUT not pg_copy_from pg_copy_to 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 – enabling you to get. ssh postgres@host command 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 psql& ;SELECT * from users; psql& ;\ "" gt gt ',' gt gt '/tmp/output.csv' gt gt 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? Answer: 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: newdb originaldb dbuser; class CREATE DATABASE WITH TEMPLATE OWNER Still, you may get: " " class ERROR: source database originaldb is being accessed by other users code To disconnect all other users from the database, you can use this query: pg_terminate_backend(pg_stat_activity.pid) pg_stat_activity pg_stat_activity.datname = pid &lt;&gt; pg_backend_pid(); class SELECT FROM WHERE 'originaldb' AND Alternative Solution: To clone an existing database with Postgres you can do that pg_terminate_backend(pg_stat_activity.pid) pg_stat_activity pg_stat_activity.datname = pid &lt;&gt; pg_backend_pid(); TARGET_DB SOURCE_DB USER_DB; class /* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/ SELECT FROM WHERE 'SOURCE_DB' AND /* CLONE DATABASE TO NEW ONE(TARGET_DB) */ CREATE DATABASE WITH TEMPLATE OWNER It will kill all the connection to the source DB avoiding the error " " class ERROR: source database SOURCE_DB is being accessed by other users code 9. How to fix error “Fatal: role “username” does not exist”? Answer: Use the to create your database – as long as you haven’t set up a role with the necessary privileges that corresponds to your operating system user of the same name ( in your case): operating system user postgres database h9uest sudo -u postgres -i code class "" code As recommended or . here here Then try again. Type when done with operating as system user . exit postgres Or execute the single command as with createuser postgres sudo' The point is to use the operating system user matching the database role of the same name to be granted access via . is the default operating system user to have initialized the database cluster. ident authentication postgres The manual: 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 ) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named . In order to create more roles you first have to connect as this initial role. initdb postgres Read about and in the manual. database roles client authentication Also you can try this solution In short, running sudo -u postgres createuser owning_user code class "" code creates a role with name owning_user (in this case, h9uest). After that you can run from the terminal under whatever account name you set up without having to enter into the Postgres environment. rake db:create 10. How to fix error “Peer authentication failed for user “Postgres”? Answer: The problem is still your file ( ). pg_hba.conf /etc/postgresql/9.1/main/pg_hba.conf* This line: class local all postgres peer code Should be: class local all postgres md5 code If you can’t find this file, running locate should show you where the file is. pg_hba.conf 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 . official PostgreSQL docs on authentication methods 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 and get the pid values you want to kill, then issue to them. pg_stat_activity SELECT pg_terminate_backend(pid int) PostgreSQL 9.2 and above: pg_terminate_backend(pg_stat_activity.pid) pg_stat_activity pg_stat_activity.datname = pid &lt;&gt; pg_backend_pid(); class SELECT FROM WHERE 'TARGET_DB' -- ← change this to your DB AND PostgreSQL 9.1 and below: pg_terminate_backend(pg_stat_activity.procpid) pg_stat_activity pg_stat_activity.datname = procpid &lt;&gt; pg_backend_pid(); class SELECT FROM WHERE 'TARGET_DB' -- ← change this to your DB AND 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 column to . See . procpid pid this mailing list thread 12. How to Insert or perform multiple updates in PostgreSQL? Answer: PostgreSQL since version 9.5 has syntax, with with the following syntax (similar to MySQL) UPSERT ON CONFLICT clause. the_table (id, column_1, column_2) ( , , ), ( , , ), ( , , ) (id) column_1 = excluded.column_1, column_2 = excluded.column_2; class INSERT INTO VALUES 1 'A' 'X' 2 'B' 'Y' 3 'C' 'Z' ON CONFLICT DO UPDATE SET Searching PostgreSQL’s email group archives for “upsert” leads to finding : an example of doing what you possibly want to do, in the manual Example: Exceptions with UPDATE/INSERT This example uses exception handling to perform either UPDATE or INSERT, as appropriate: db (a , b ); merge_db(key , data ) $$ db b = data a = key; ; ; db(a,b) (key, data); ; ; ; ; $$ plpgsql; merge_db( , ); merge_db( , ); class CREATE TABLE INT PRIMARY KEY TEXT CREATE FUNCTION INT TEXT RETURNS VOID AS BEGIN LOOP -- first try to update the key -- note that "a" must be unique UPDATE SET WHERE 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 VALUES RETURN EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END END LOOP END LANGUAGE SELECT 1 'david' SELECT 1 'dennis' There’s possibly an example of how to do this in bulk, using CTEs in 9.1 and above, in the : hackers mailing list WITH foos AS ( ( (%foo[])).*) updated ( foo foo.a = foos.a ... foo.id) foo foos.* foos updated (id) updated.id ; SELECT UNNEST as UPDATE SET RETURNING INSERT INTO SELECT FROM LEFT JOIN USING WHERE IS NULL In Conclusion This is the list for most frequently asked questions with solutions about PostgreSQL. I 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. This article was also posted on DevPostbyTruemark .