Questions tagged [psql]

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

Filter by
Sorted by
Tagged with
1434 votes
7 answers
2.9m 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 ...
user avatar
  • 30k
320 votes
3 answers
188k 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?
user avatar
  • 30k
256 votes
10 answers
736k 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 ...
user avatar
  • 2,863
168 votes
5 answers
215k views

How to get the name of the current database from within PostgreSQL?

Using \c <database_name> in PostgreSQL will connect to the named database. How can the name of the current database be determined? Entering: my_db> current_database(); produces: ERROR: ...
user avatar
117 votes
6 answers
226k 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} ...
user avatar
  • 1,931
94 votes
11 answers
280k 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: ...
user avatar
  • 30k
47 votes
4 answers
56k 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 ...
user avatar
  • 571
38 votes
3 answers
35k views

How to wrap long lines when SELECTing SQL text columns?

I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length. From: SELECT * FROM test; test_id | text --------+-----------...
user avatar
  • 716
33 votes
14 answers
151k 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 ...
user avatar
  • 767
32 votes
4 answers
38k views

why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist

I am trying to run following command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db I get: failed: FATAL: database "new_db" does not exist
user avatar
  • 567
31 votes
3 answers
15k 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 ...
user avatar
30 votes
3 answers
131k views

postgres: permission denied for schema

I am new to postgresql and I would grateful if you could please advise on how-to resolve the following error.. I have issued the followed commands: ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN ...
user avatar
  • 1,171
23 votes
1 answer
8k 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 ...
user avatar
  • 1,499
16 votes
2 answers
38k views

Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

I have created the table donor in the schema reference as per: CREATE TABLE reference.donor ( donor_code smallint PRIMARY KEY, donor_name character varying NOT NULL, donor_type smallint ...
user avatar
  • 797
15 votes
6 answers
17k 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 ...
user avatar
  • 28.9k
15 votes
1 answer
26k 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 ...
user avatar
  • 541
15 votes
1 answer
10k views

Queries show data but not column names

I'm getting through the postgresql tutorial, I've created my db, 2 tables, weather and cities and added a few lines of data. When I query my db with this sql statement for example: SELECT city, ...
user avatar
  • 253
14 votes
3 answers
14k views

How to set a search_path default on a psql cmd execution?

I'm looking for an option to create an alias command for a psql console connection which allowed setting the search_path, but I am not finding any option on psql util, nor the option to execute a ...
user avatar
14 votes
6 answers
40k 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 ...
user avatar
  • 1,045
14 votes
2 answers
27k 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. ...
user avatar
13 votes
2 answers
44k 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 ...
user avatar
  • 10.4k
12 votes
3 answers
29k views

Homebrew installed PostgreSQL could not connect, database files are incompatible with server

Here's the error message I'm getting today when I attempt to log in to my localhost PosgreSQL databases using psql: psql: could not connect to server: No such file or directory Is the server ...
user avatar
  • 173
12 votes
2 answers
7k views

Why 't' and 'f' instead of TRUE and FALSE

Why does PostgreSQL return 't' and 'f' instead of TRUE and FALSE? The documentation itself advises us to use TRUE and FALSE when inserting boolean values or making comparisons, but returns a ...
user avatar
  • 123
12 votes
1 answer
57k 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 ...
user avatar
  • 223
12 votes
2 answers
19k 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. ...
user avatar
  • 263
12 votes
1 answer
1k views

When are privileges listed in \l and when not?

When are access privileges listed by \l, and when are they not? The access privileges listed by \l can change after a grant and revoke: $ createuser -EP my_readonly $ psql development development=# \...
user avatar
11 votes
4 answers
18k views

In PostgreSQL URL I can't use a password containing special characters

I have a postgresql database in Azure The passwd string has special characters like these: )mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU psql --dbname=postgresql://db_user@mydemoserver:)mvd99/iyH_=ag=...
user avatar
  • 111
11 votes
2 answers
19k views

psql: SELECT * ... except one column

I search for a simple way to select all columns except one in psql. With psql I mean the interactive command line. I would be happy with a tool that expands to * to a list of quoted column names. ...
user avatar
  • 1,331
10 votes
2 answers
17k 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 ...
user avatar
  • 30k
9 votes
3 answers
9k 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 '/tmp/...
user avatar
9 votes
2 answers
3k 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 ...
user avatar
  • 28.9k
9 votes
1 answer
6k views

Postgres privileges: psql \dp - what is about the plus-sign

I used psql-command "\dp" to find out the given grants. Versions on test-server: PostgreSQL 9.1.15 on armv7l-unknown-linux-gnueabi, compiled by gcc (Debian 4.6.3-14) 4.6.3, 32-bit Out of the ...
user avatar
  • 160
9 votes
3 answers
2k 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).
user avatar
9 votes
2 answers
4k 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 ...
user avatar
8 votes
1 answer
33k views

Display only table names with psql

How can I get the table names without a header or footer or anything else from a specified Postgres database with psql? That is, if I have tables called "table1" and "table2" then the output will be: ...
user avatar
  • 181
8 votes
1 answer
19k views

pg_restore ERROR: "Relation does not exist" and creating new database

I have made a backup of my specific tables that I want to restore into a new database using: call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public....
user avatar
  • 83
8 votes
1 answer
4k 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: master....
user avatar
  • 83
7 votes
6 answers
4k views

Reverse Byte-Order of a postgres bytea field

I'm currently working on a table that contains hashes, stored in bytea format. Converting the hashes to hex-strings however yields the wrong order of bytes. Example: SELECT encode(hash, 'hex') FROM ...
user avatar
7 votes
4 answers
54k 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 file "...
user avatar
  • 173
7 votes
1 answer
3k views

How do I use the input from a \prompt in a conditional (\if)?

Let's say I get value from a prompt? \prompt 'Write [Yes] or [No]\n' store Which does this, Write [Yes] or [No] Yes I can verify I have that works, \echo :store Yes But, I can't get it to work ...
user avatar
  • 56.2k
7 votes
2 answers
4k 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 ...
user avatar
  • 385
7 votes
1 answer
23k 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 ...
user avatar
  • 251
7 votes
1 answer
10k views

Cannot revoke default privileges from postgresql user

I am using the psql tool: Command: my_db=# \ddp Result: Default access privileges Owner | Schema | Type | Access privileges ----------------+--------...
user avatar
7 votes
1 answer
329 views

Reckless behavior of psql \e

psql 13.2 executes the query buffer no matter what when exiting the editor, even after quitting it - in which case the buffer may hold the most recently executed SQL query now. To reproduce: Start ...
user avatar
6 votes
4 answers
3k 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 ...
user avatar
  • 161
6 votes
2 answers
4k views

Add variable value in single quoted string. Expand variable within single quotes

I have tried different variations of the below commands but non work. I simply want to run the following command using a variable within the location string: CREATE TABLESPACE data OWNER appuser ...
user avatar
  • 339
6 votes
1 answer
4k views

Execute a series of commands in PSQL

I am looking for a solution to execute multiple commands in PSQL. Lets say I have a file with 2 name for database. 1. slave1 2. slave2 I already have a master template named "master_db". If I ...
user avatar
6 votes
1 answer
16k 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) ...
user avatar
  • 767
6 votes
1 answer
738 views

Cannot induce exit code 3 from psql

The docs page states (emphasis mine) Exit Status psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, file not found), 2 if the ...
user avatar
6 votes
2 answers
8k views

psql is not prompting me for a password, then denies access

I'm using Postgres 9.4 on Debian Linux. I created a database with a user, cindex with access to the database. Yet when I try and login at the command line, I'm not even prompted for a password: ...
user avatar
  • 605

1
2 3 4 5
7