psql is the primary interactive terminal for PostgreSQL. (DO NOT USE THIS TAG for questions targeting PostgreSQL!)

learn more… | top users | synonyms

327
votes
7answers
592k views

How do I list all databases and tables using psql?

I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool. When I log in with psql --username=postgres, how do I list all databases and tables? I ...
60
votes
2answers
27k views

How can I time SQL-queries using psql?

I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?
21
votes
9answers
52k views

PostgreSQL is running locally but I cannot connect. Why?

Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
16
votes
1answer
27k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
13
votes
3answers
33k views

How to run psql on Mac OS X?

I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available. I get this message: ...
12
votes
2answers
35k views

List the database privileges using psql

I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL ...
7
votes
2answers
6k views

How to turn off header only in psql (postgresql)

I'm using PostgreSQL 9.1.X I am trying to build psql script to print results without a header but including a footer. http://www.postgresql.org/docs/9.1/static/app-psql.html From the document ...
6
votes
1answer
543 views

Is there a psql equivalent of bash's reverse-search-history?

I am very fond of bash's reverse-search-history (C-r) (command-line feature): Search backward starting at the current line and moving ‘up’ through the history as necessary. This is an incremental ...
5
votes
3answers
2k views

How to concatenate psql variables?

How can I concatenate two psql (PostgreSQL client) variables? I want to generate an absolute path by concatenating a directory path variable and a filename variable. I've tried this: \set path ...
5
votes
2answers
1k views

Span single transaction over several sequential psql sessions

I am running PostgreSQL 8.4 database server and psql terminal front-end on Ubuntu 10.04 Lucid Lynx and would like to span a single transaction over several sequential psql sessions. When I connect to ...
5
votes
1answer
5k views

How to insert an IP-address into an inet column in PostgreSQL?

I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
4
votes
3answers
5k views

psql: Show current database

In psql I can use \c <database_name> to connect to a given database. How can I see what database I am currently connected to? When I do: my_db> current_database(); I get: ERROR: ...
4
votes
1answer
2k views

Source several files at once with psql

I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran ...
4
votes
1answer
941 views

Getting SQLSTATE in psql error messages

I have the following problem. SELECT * FROM tgvbn(); ERROR: function tgvbn() does not exist LINE 1: SELECT * FROM tgvbn(); ^ HINT: No function matches the given name and ...
4
votes
1answer
538 views

PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

I am using PostgreSQL 9.1.5 and the psql to run a scipt containing a series of INSERT statements in various tables: psql -U usernamefoo databasenamefoo -f dml_script.sql The table constraints have ...
4
votes
3answers
267 views

How to prevent query execution after exiting the \e editor in psql?

Is there an option that can change the "exit external editor -> query runs automatically" behaviour of the psql \e switch ? (Other than intentionally mangling the query syntax).
4
votes
4answers
4k views

Passing arguments to psql

I'm running a plpgsql script in Postgres 8.3 - I would like to pass arguments to this script via psql. I'm currently executing the script like: psql -d database -u user -f update_file.sql I came ...
4
votes
1answer
385 views

Restoring plain-text pg_dump with psql and --disable-triggers

I had to perform some tests with a short script to update some "legacy" data in one of my tables. Cautious as I am, using an untested script, I decided to backup the relevant table before doing so. ...
4
votes
0answers
1k views

Is there a Postgres admin GUI that can execute a master file containing multiple sql files

I'm developing a really long script. I'd like to break the script into smaller, more manageable scripts and include each sql file in a master file, then just execute the master file. example: ...
3
votes
4answers
305 views

psql, getting a list of databases to be parsed by a script

I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them: psql -l -A -t but the output shows an obvious issue: the records are ...
3
votes
1answer
262 views

How to Substitute Variables in PostgreSQL

The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL? INSERT INTO control_threshold ( threshold_id, group_name, description, sql, ...
3
votes
2answers
1k views

psql won't list databases

I have a situation that I'm scratching my head over, but I'm new to postgresql, so I may be overlooking something obvious. I set up a testbed server and set access in pg_hba.conf: local all ...
3
votes
2answers
9k views

psql denies access to file when using COPY FROM

When I do this: COPY "mytable" FROM '/my/file.csv' WITH DELIMITER AS ',' CSV; On this: -rw-r--r-- 1 peter peter 54819176 2011-07-21 13:17 file.csv psql tells me this: ERROR: could not open ...
3
votes
2answers
2k views

How to conditionally stop a psql script (based on a variable value)?

Let's consider the following example (from the start of a psql script): \c :db_to_run_on TRUNCATE the_most_important_table; -- tried to avoid similarities to anything that exists out there Now if ...
3
votes
1answer
1k views

PostgreSQL psql - not working but no errors [closed]

I'm a complete noob to PostgreSQL, so I'm probably missing something completely obvious. Connected to my local db via psql on terminal. Any command I run to make db changes, or even a simple select ...
3
votes
1answer
177 views

send jobs via psql asynchronously

I want to backup a huge number of records and delete them, but I don't have complete access to database server so I can't run my script on the server. because it takes so long I need to submit ...
3
votes
1answer
558 views

Dropped database still shows but causes errors PostgreSQL

I had dropped a database a couple weeks ago but it still shows with \l. I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was ...
3
votes
1answer
541 views

Variable substitution in psql when using \copy

I'm using the psql command \copy and I would like to pass a variable to it from the shell (for table name) like I've done when scripting queries. I've read in the documentation for psql that: The ...
3
votes
1answer
119 views

Rollback on \include error in psql

I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this: \include ...
2
votes
2answers
2k views

How to execute a query from psql without waiting for the result?

My query (to create a new table from existing table) takes a very long time. So I've set up a remote database in my office - more RAM there. I can connect to my database from home as usual with psql. ...
2
votes
1answer
18k views

How to import a.csv file into a postgresql database using \copy?

I am trying to import a .csv file into a postgresql database. I am using pgadmin III for that on a win7 machine. My query looks like that: \COPY pop_grid(GRID_ID, POP_TOT, YEAR, METHD_CL, CNTR_CODE, ...
2
votes
1answer
8k views

Postgres client copy (\copy) command doesn't have access to a temporary table?

I'm generating a list of SQL commands to export some data that I ultimately run using psql -f. The queries all get the same subset of data, so I thought I'd factor the qualifications out and put a ...
2
votes
1answer
2k views

Postgresql: set default psql client encoding

When I connect to a Postgresql DB using psql, I often get these messages: => SELECT * FROM question_view ; ERROR: character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in ...
2
votes
1answer
246 views

Is there a way to define macros/alias in psql?

It is somewhat annoying to type EXPLAIN (ANALYZE, BUFFERS, VERBOSE) in psql tool every time you tune queries. So, is there a way to define an aliases (autocompletable ones would be very nice) in psql ...
2
votes
1answer
72 views

psql coloured prompt misbehaving

In theory, there is a possibility to use coloured prompts in psql. I decided (not the first time :) that I need them, so I thought I would give them a try. So I have in my .psqlrc the following: ...
2
votes
1answer
173 views

Mismatch between size computed from pg_buffercahce Vs shared_buffers in postgresql?

I have a rather naive question regarding the size of shared_buffers computed from pg_buffercache and the shared_buffers size mentioned in postgresql.conf. Based on my understanding pg_buffercache ...
2
votes
1answer
4k views

PostgreSQL: Change default port used by utilites like psql, createdb, etc

I have 2 instances of PostgreSQL running on some servers. One on the default port (5432) and the other instance on port 5433. Some users (and processes) only need access to the second instance (5433) ...
2
votes
1answer
2k views

PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

I have to restore a DB table every day from a postgresql 8.4 to 8.1. For the backup, I use this code (windows server): D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t my_table myDB > ...
2
votes
1answer
3k views

Cannot start psql with user postgres “Could not change directory to /home/user ”

I did a silly mistake. I wanted to set postgres permissions to a folder for exporting data from my database. So I set something like sudo chmod 777 -u postgres /home/user and then my home partition ...
2
votes
1answer
2k views

How do I install pgcrypto on Windows?

I would like to install pgcrypto in Windows. I have a file C:\Program\PostgreSQL\9.0\share\contrib\pgcrypto.sql but when I try to run it in psql I get an error: C:: Permission denied How do I solve ...
1
vote
1answer
67 views

How do I automatically set meta commands when starting psql?

When I start a psql session, I would like certain meta commands to already be set. For instance: \x auto \set ON_ERROR_STOP on I was hoping to find an rc or config file but couldn't. The command ...
1
vote
1answer
55 views

Understanding the output of \d <table> in psql

Say I run the following command: \d mtb.big_table I get the output shown below. My questions are: What are column modifiers? (what does default now() mean?) Under Indexes, Why do some indices ...
1
vote
3answers
1k views

psql asks for password, CREATE USER didn't specify one

I used to have a user, say 'jack' in postgresql, and then I forgot what the password. I reassigned all its ownerships to user postgres, removed the user, and then created it again with CREATE USER ...
1
vote
1answer
90 views

There is an equivalent of login.sql on PostgresSQL?

When you log on Oracle database with SQLplus, it looks for a login.sql file in the current directory. If the file exists, it's executed. It's usefull to set preferences... There is an equivalent ...
1
vote
1answer
63 views

Understanding postgres prompts

What is the difference between postgres=# and postgres-#? I tried googling it and looking on the official site but could not get a hit for the symbols.
1
vote
2answers
3k views

PostgreSQL cannot start after replacing contents inside its Data folder

When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the ...
1
vote
1answer
4k views

Drop multiple databases in one script

I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. ...
1
vote
1answer
21 views

psql with '-c' option or '-f' option is not terminating properly

When I run a command such psql -d database -c 'select * from table' or psql -d database -f sqlcommands psql does not terminate cleanly. Interestingly piping the the contents of the file instead of ...
1
vote
1answer
27 views

Using psql with string variable that has two periods

psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string='Me' -v version="1.7.3" into the script begin; create table db_log ( db_owner varchar(255) not null, db_user varchar(255) ...
1
vote
1answer
78 views

script for restore of postgres database localy from dump given on stdin - ambiguous redirect

Runing this command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -U foo some_db" runs perfectly fine. It returns on stdandard output the dump of db. Then I would like to restore new_db based on dump ...