Hot answers tagged

128

The function current_database() returns the name of the current database: SELECT current_database(); It's an SQL function, so you must call it as part of an SQL statement. PostgreSQL doesn't support running functions as standalone queries, and has no CALL statement like some other SQL engines, so you just use SELECT to call a function.


111

postgres=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US....


109

In Postgresql these terminal commands list the databases available el@defiant$ /bin/psql -h localhost --username=pgadmin --list Or the command stated more simply: psql -U pgadmin -l Those commands print this on the terminal: List of databases Name | Owner | Encoding | Collate | Ctype | Access ...


108

You have four choices regarding the password prompt: set the PGPASSWORD environment variable. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-envars.html use a .pgpass file to store the password. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html use "trust authentication" for that ...


105

perhaps you mean listing users and their privileges for a database - I can't quite tell from the question: postgres=> \du List of roles Role name | Attributes | Member of -----------------+--------------+------------------------------------------------ dba | Create role | {util_user,...


79

You can do that by following: SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='mytable' This gives you this kind of output: mail=# select grantee, privilege_type from information_schema.role_table_grants where table_name='aliases'; grantee | privilege_type --------------+----------------- mailreader | ...


35

To gain more info on database and table list, You can do : \l+ to list databases List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ------------+----------+----------+-------------+-----...


33

From pg_Admin you can simply run the following on your current database and it will get all the tables for the specified schema: SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'public' ORDER BY table_type, table_name This will get you a list of all the permanent tables (generally the tables you're looking ...


32

you can use "\conninfo" in psql


29

I found that I had an extremely similar problem, namely that postgres was opening a socket in /var/pgsql_socket_alt where none of my software expects to look, but the solution to my problem was not only a problem with my $PATH. I had to create the directory /var/pgsql_socket, chown it to myself, and set unix_socket_directory in postgresql.conf (located in /...


26

\c prints something like You are now connected to database "foobar" as user "squanderer". Use this if you don't mind creating a new connection, because this is what happens. The \connect (shortened as \c) without all parameters will create a new connection identical to your current one. The current connection is closed. See the \connect command spec on ...


19

It is possible that you have inserted the tables into a schema that is not in your search path, or the default, ie, public and so the tables will not show up using \dt. If you use a schema called, say, data, you can fix this by running, alter database <databasename> set search_path=data, public; Exit and reenter psql and now \dt will show you the ...


17

A simple example with PGPASSWORD will be something like: PGPASSWORD=YOUR_PASSRORD psql -h YOUR_PG_HOST -U YOUR_USER_NAME Hope it helps.


17

This is because it is the way pg_restore works. pg_restore manual reads as follow : -C, --create Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it. When this option is used, the database named with -d is used only to issue the initial DROP DATABASE ...


15

Using psql meta-commands: https://www.postgresql.org/docs/current/static/app-psql.html Going over the page with Ctrl+F gives: \ddp [ pattern ] Lists default access privilege settings. \dp [ pattern ] Lists tables, views and sequences with their associated access privileges. \l[+] [ pattern ] List the databases in the server and show .... ...


15

\copy can use a temporary table. First I tested and confirmed this with version 9.0 at the command line. Then I created a file with SQL and psql meta command \copy using multiple temporary tables. That worked for me, too. CREATE TEMP TABLE tmp as SELECT * FROM tbl; \copy (SELECT * FROM tmp JOIN tbl USING (id)) TO '/var/lib/postgres/test1.csv'; Call: psql ...


15

Jumped on the IRC postgresql channel and had this answered in 30 seconds. Issue was that I was not ending my SQL commands with a semicolon. Doh!


14

PostgreSQL does not return these instead of boolean values. It is some clients (for example, psql and pgAdminIII) which represents TRUE with t and FALSE with f - try the same query in another client and you will see something else. See, for example, what DBVisualizer gives you: I guess the reason for showing t and f is simply sparing space in a command-...


13

There is an option in psql which stops executing commands on error, this is ON_ERROR_STOP. If we could raise an error somehow, this would do what we want. The problem is that we have to test the variable and produce an error somehow. Since one can't use control structures in psql (because there are none)*, my only idea was to use SQL for testing. Well, ...


13

A conversation about this on GitHub led to an interim "quick fix" solution: open ~/.editrc and add: bind "^R" em-inc-search-prev Apparently the bigger issue is that PostgresApp uses libedit instead of libreadline. And supposedly libedit lacks some command-line features of libreadline, so until PostgresApp is compiled against libreadline, just use ...


12

Undercovers psql uses the bellow query when you issue \du command. SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit, r.rolvaliduntil, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as ...


12

The time that \timing returns also includes the network latency, if you're connecting to a remote server. When you don't want that and don't need the query output too, better use EXPLAIN ANALYZE, which outputs the query plan with the planner estimates plus the actual execution times. for example, EXPLAIN ANALYZE SELECT foo from bar ;


11

Appears "the way" to install the client, if you want to use hombrew, is: $ brew install postgresql then psql (the client command line) will now be available to you (it also installs a local Postgres server/database, but you don't have to use that if all you want is the client). Apparently there's also a 'wrapper' to psql to make it more "user friendly" ...


11

I strongly recommend using Postgres.app from the Heroku team, which is also supported by them! It has a menubar icon and the menu has a psql item: You will also find psql included here if you want the same version of that as the server (path may vary by version): /Applications/Postgres.app/Contents/MacOS/bin If you want, you can add this path to your ...


11

The documentation on psql explains: Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name ...


10

A (possibly obvious) additional step is become the postgres user, otherwise you may get errors about roles not existing. sudo su - postgres psql -l or psql postgres=> \l


10

SELECT * FROM current_catalog; -- and SELECT current_catalog; ...both work as well (catalog is standard SQL for database)


10

Is there a way to permanently configure this setting, either in the .pgpass file or anywhere else Yes there is: it's ~/.psqlrc (or %APPDATA%\postgresql\psqlrc.conf in Windows) See the manual for details: http://www.postgresql.org/docs/current/static/app-psql.html#AEN88713


10

If you are using psql 9.6+, there is a very convenient command called \gexec: Sends the current query input buffer to the server, then treats each column of each row of the query's output (if any) as a SQL statement to be executed. [...] The generated queries are executed in the order in which the rows are returned, and left-to-right within each row ...


9

\set fullexplain EXPLAIN (ANALYZE, BUFFERS, VERBOSE) :fullexplain SELECT * FROM pg_stat;. Probably not the most elegant thing I admit. Also you could put this in .psqlrc.


Only top voted, non community-wiki answers of a minimum length are eligible