Hot answers tagged psql
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.
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;
Just turn on timing by entering: \timing
\l is also shorthand for \list. There are quite a few slash commands, which you can list in psql by using \?.
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 ...
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 ...
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
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 ...
postgres=> \l Liste der Datenbanken Name | Eigentümer | Kodierung | Sortierfolge | Zeichentyp | Zugriffsprivilegien ----------------+------------+-----------+--------------+------------+----------------------- postgres | postgres | UTF8 | de_AT.utf8 | de_AT.utf8 | template0 | ...
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 ...
This is a dump file produced by pg_dump -Fc. To create a text file containing SQL commands from it, use pg_restore. The basic syntax is pg_restore file.dump > file.sql, that is, you don't specify a target database (notice the missing-d option).
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 | ...
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 ...
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 -
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)
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 ...
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 ...
\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.
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 ...
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);
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.
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.
pgScript is a local script extension of pgAdmin, which you most probably do not want here. pgAdmin is a GUI, not a console application - there is no stdin you could easily use. If you need stdin to stream your content, use psql, which is a console application - with the \copy meta-command of psql. If you have a file (which you obviously do), just use SQL ...
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.
The table should be create table cali ( id serial primary key, alk_from bigint, alk_to bigint, and_from int, and_to int ); And do \COPY cali (alk_from, alk_to, and_from, and_to) FROM '/home/.../data/output/id_cali.csv' (FORMAT CSV); So that it knows to not insert into the id serial column. The serial type is not a true type. From ...
SELECT set_config('search_path', 'fred,'||current_setting('search_path'), false); The false says it's not a transaction-LOCAL setting. For the bonus question, you can store the value in a custom setting: SELECT set_config('tmp.search_path', current_setting('search_path'), false); From version 9.2 on, you don't even have to define this setting in ...
\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 ...
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 ...
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)
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