Hot answers tagged


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.


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 | ...


you can use "\conninfo" in psql


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 ------------+----------+----------+-------------+-----...


If you're using the psql command line tool, issue this command first: \pset format wrapped It should then wrap long lines to your terminal window like so: test_id | text --------+----------------------------- 1 | Lorem ipsum dolor sit amet,. |.consectetur adipiscing elit. |.. Mauris lorem You can also set the number of ...


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 ...


\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 ...


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 ...


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


Using psql meta-commands: 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 .... ...


When executing psql from shell you can use -t (prints tuples only) option: $ psql -t -c "SELECT version();" PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit Within psql use \t to turn off printing header and row count.


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 ;


this solves it: postgres=# \connect ip_spotlight You are now connected to database "ip_spotlight" as user "postgres". ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netops ; ip_spotlight=# GRANT USAGE ON SCHEMA doc,net TO netopsapp ; ip_spotlight=# GRANT SELECT ON ALL TABLES IN SCHEMA doc,net TO netops ; ip_spotlight=# GRANT SELECT ON ALL SEQUENCES IN ...


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 ...


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" ...


If you don't have any data, then it seems like a problem with Brew. Did you run brew upgrade, there are two answers on StackOverflow that address this, I've merged both of them together to try the best of both worlds. Backing up launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist sudo mv /usr/local/var/postgres /usr/local/var/....


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 ...


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 ...


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:


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-...


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


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 ...


I finally found it, its not a psql option, but it fits a oneliner: PGOPTIONS=--search_path=myschema psql -h myHost -U myUser -dmyDb -p myPort. If anybody improves it with a working version call for both unix and windows ill approve


I strongly recommend using 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/ If you want, you can add this path to your ...


From the manual: \t Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience.


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


Based on a_horse_with_no_name's comment, I started searching around psql and found the solution: \set VERBOSITY verbose SELECT * FROM tgvbn(); ERROR: 42883: function vfjkb() does not exist ... Now that goes into .psqlrc. Details and further options can be found in the psql documentation.


In short, you want either (clean existing): (note the database name is postgres) pg_restore -c -d postgres db.dump or (create new) pg_restore -C -d postgres db.dump or (create new explicitly) createdatabase the_database pg_restore -d the_database db.dump See what SCO said for more details.


In PostgreSQL (as described in documentation, The Information Schema): SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema='public'; For MySQL you would need table_schema='dbName' and for MSSQL remove that condition. Notice that "only those tables and views are shown that the current user has access to". Also, ...


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 ...

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