Hot answers tagged

679

Please note the following commands: \list or \l: list all databases \dt: list all tables in the current database You will never see tables in other databases, these tables aren't visible. You have to connect to the correct database to see its tables (and other objects). To switch databases: \connect database_name See the manual about psql.


145

This lists databases: SELECT datname FROM pg_database WHERE datistemplate = false; This lists tables in the current database SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;


126

Just turn on timing by entering: \timing


52

\l is also shorthand for \list. There are quite a few slash commands, which you can list in psql by using \?.


51

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


37

Timing can be turned on with \timing at the psql prompt (as Caleb already said). If you are on 8.4 or above, you can add an optional on/off argument to \timing, which can be helpful if you want to be able to set timing on in .psqlrc - you can then set \timing on explicitly in a script where plain \timing would otherwise toggle it off


36

You have three 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 ...


27

postgres=> \l Liste der Datenbanken Name | Eigentümer | Kodierung | Sortierfolge | Zeichentyp | Zugriffsprivilegien ----------------+------------+-----------+--------------+------------+----------------------- postgres | postgres | UTF8 | de_AT.utf8 | de_AT.utf8 | template0 | ...


25

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


23

Locate the psql binary. (In a terminal, run locate psql | grep /bin, and make note of the path. (In my case, it's /opt/local/lib/postgresql90/bin/, as it was installed using MacPorts.) Then, edit the .bash_profile file in your home folder (e.g. mate -w ~/.bash_profile assuming you've textmate), and add the needed line so it's in your path, e.g.: export ...


19

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


18

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.


16

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


15

Your script could look like this: BEGIN; \i file1.sql \i file2.sql COMMIT; Or you could do something like this: cat file1.sql file2.sql | psql -1 -f -


13

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


11

you can use "\conninfo" in psql


11

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


11

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


9

It seems pretty easy: postgres=# create table inet_test (address inet); CREATE TABLE postgres=# insert into inet_test values ('192.168.2.1'); INSERT 0 1 postgres=# insert into inet_test values ('192.168.2.1/24'); INSERT 0 1 postgres=# select * from inet_test; address ---------------- 192.168.2.1 192.168.2.1/24 (2 rows)


7

You can try sending psql to the background: psql -f your_sql_file.sql & Or, connecting to the local DB, you can use dblink to dispatch a query to the remote DB: SELECT dblink_connect('your_connection_name', 'your_connection_string'); SELECT dblink_send_query('your_connection_name', 'your_query'); Note that dblink_send_query can only send one query ...


7

\set path '/tmp/' \set file 'foo' \set pf :path:file \echo :pf /tmp/foo Why does this work? I quote the manual here: \set [ name [ value [ ... ] ] ] Sets the internal variable name to value or, if more than one value is given, to the concatenation of all of them. [...] Emphasis mine.


7

These variables are a feature of SQL*Plus. psql, the equivalent program in the PostgreSQL world, also has variables. Use \set variable 'value' and insert into mytable(mycolumn) values (:variable);


6

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


6

this has nothing to do with databases. its how you os resolves the domain name in this case 'localhost' you /etc/hosts file should have an entry for localhost as below if you don't have it , then adding this entry will resolve your issue 127.0.0.1 localhost localhost.localdomain


6

A plausible and typical explanation would be that the psql that comes with homebrew is in /usr/local/bin/psql which is different from the one that would be in your $PATH, like /usr/bin/psql (bundled with OS X). You may want to try with the full path: $ /usr/local/bin/psql -U rails -d myapp_development Also, there's something rather unusual in the ps ...


6

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


6

According to the pg_hba.conf snippet, a password is required if you connect from ::1, which is the IPv6 address for localhost. It may be that the box on which you have the problem has the resolver configured so that the name "localhost" refers to both 127.0.0.1 (IPv4) and ::1 (IPv6), so that the command psql -h localhost... may connect to one or the other ...


6

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


5

I don't see how your two solutions are different... Since it still has to actually CONNECT to a database instance to see what databases are there, I'm willing to bet the only different is HOW you are connecting. psql -l -A -t vs psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT ...


5

The -1 option to psql causes it to wrap a file specified by -f in a BEGIN..COMMIT block, making it a transaction. Otherwise, add the BEGIN and COMMIT commands to your script so that it becomes a single transaction.



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