Tag Info

Hot answers tagged

463

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.


103

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;


97

Just turn on timing by entering: \timing


41

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


35

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


29

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


29

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


23

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


19

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


16

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


13

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


11

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


9

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.


8

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 -


8

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)


8

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


8

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


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);


7

you can use "\conninfo" in 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

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


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


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.


5

Don't add a semicolon (;) and you are fine. I quote the manual about the \e or \edit meta-command here: ... if the query ends with (or contains) a semicolon, it is immediately executed. Otherwise it will merely wait in the query buffer; type semicolon or \g to send it, or \r to cancel.


5

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.


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

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


5

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


4

Since you state that you are open to other solutions I might suggest looking at terminal multiplexers such as screen or tmux. In my opinion tmux is a better choice due to its unique name (easier to get relevant hits in search engines). Essentially this kind of software allows you to detach from a shell and later resume the session.


4

Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like: #!/bin/sh psql_pipe=/tmp/psql$$ mkfifo -m 600 $psql_pipe psql < $psql_pipe & exec 3>$psql_pipe psql_pid=$! echo "> ...



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