Tag Info

Hot answers tagged

247

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.


62

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;


51

Just turn on timing by entering: \timing


28

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


22

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


19

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


18

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


16

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


11

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


9

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


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 -


7

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


7

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

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


7

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


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

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

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


6

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


5

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


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

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


4

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


4

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


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

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.


4

Yes, there is. It's stored in ~/.psqlrc More details in the manual: http://www.postgresql.org/docs/current/static/app-psql.html#AEN85369


4

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


4

re 1: that's the default value you define when you create the table: create table foo ( id integer default 42, last_modified timestamp default now() ) Details are in the manual: http://www.postgresql.org/docs/current/static/ddl-default.html http://www.postgresql.org/docs/current/static/sql-createtable.html re 2: an index can be defined on ...



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