Hot answers tagged psql
50
Please note the following commands:
\list lists all databases
\dt lists 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).
See the manual about psql.
11
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 ...
9
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 ...
8
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
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 ...
5
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
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 ...
5
postgres=> \l
Liste der Datenbanken
Name | Eigentümer | Kodierung | Sortierfolge | Zeichentyp | Zugriffsprivilegien
----------------+------------+-----------+--------------+------------+-----------------------
postgres | postgres | UTF8 | de_AT.utf8 | de_AT.utf8 |
template0 | ...
5
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)
4
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 ...
4
\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.
4
You tell the COPY command to look for commas as delimiters (DELIMITERS ','), but there are no commas in your CSV. Use the 'text' format instead (it's the default, so you don't have to specify it) and do not specify a delimiter:
The default is a tab character in text format.
(source)
4
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 ...
4
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.
3
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 | ...
3
Not really - there is pgAgent - part of pgAdmin package, that could be abused to do it, but it's not its main purpose.
Generally, to run such long queries/jobs, you need somewhere to have psql session to live until it will finish. Usually, psql on screen on some kind of server - database server, or app server, doesn't matter much.
3
DROP DATABASE is a very special command that cannot be undone. To my knowledge there is no way to drop a database inside a transaction. I quote the manual:
DROP DATABASE cannot be executed inside a transaction block.
Whenever you run two commands in a script, they are automatically wrapped into a transaction. You can explicitly begin and commit ...
3
I usually tackle this a bit differently: I collect up the SQL files/snippets I want to run and then execute a single psql session that issues a BEGIN then uses \i to include each file in turn, finally issuing a COMMIT. Eg (untested but the general idea):
psql -1 <<__END__
\i script1.sql
\i script2.sql
\i script3.sql
__END__
errstatus=$?
if ! ...
3
You can drop the table (and dependent DB objects) with the pg_dump parameter --clean, from the pg_dump manual:
-c, --clean
Output commands to clean (drop) database objects prior to
outputting the commands for creating them. (Restore might generate
some harmless errors.)
So in your case this should be
D:\PostgreSQL\8.4\bin\pg_dump -U ...
3
drwxr--r-- 6 peter peter 4096 2011-04-14 14:03 phm
postgres@dexter:/home/peter/PyPacks$ cd phm
bash: cd: phm: Permission denied
Directories need to be executable to be able to cd into them (or use files within them). All the sub-directory from / to the /home/peter/PyPacks/phm need to be executable by the user you want to use for this to work.
Try at ...
3
My solution is not quite turning off but rather discarding headers.
You can try to tail the query output:
\o | tail -n +2
With \o, you can redirect output to a file or a pipe, like in this case. This solution has its flaw, too: at least in my case, after execution of SELECT [...], I don't get back to a prompt unless I press a key. And the first ...
3
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.
3
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.
3
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.
3
A user having a password or not won't affect if you're asked for a password when trying to connect. That is controlled by the pg_hba.conf file.
If you're being asked for a password that indicates that a password is required. If you try to connect to an account which doesn't have a password set that will simply fail. You need to either set a password for ...
3
Looks like MB vs MiB, ie decimal SI vs binary megabyte:
regress=> SELECT round(25.17*1000*1000 / (1024*1024),1);
round
-------
24.0
(1 row)
You've done your conversion into megabytes using decimal SI units (MB) not binary megabytes (MiB). Divide by 1024 not by 1000, or use pg_size_pretty:
regress=> select pg_size_pretty( 3072 * 8192::bigint );
...
2
Depending on the full error message you receive (I have a hunch you didn't post it in full) you probably are trying to run the script by using a login role which is not superuser:
C:\Users\Administrator>c:\PostgreSQL\9.0\bin\psql.exe -U milen -f C:\PostgreSQL\9.0\share\contrib\pgcrypto.sql milen
SET
psql:C:/PostgreSQL/9.0/share/contrib/pgcrypto.sql:9: ...
2
According to the installation guide after the installation has finished there should be shortcuts for StackBuilder, pgAdmin3 and psql in the Application folder of Postgres:
You will also find additional shortcuts to run pgAdmin, the psql command line interface and to access the PostgreSQL documentation.
If there are such shortcuts check where the ...
2
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
