Questions tagged [psql]
psql is the primary interactive terminal for PostgreSQL. (DO NOT USE THIS TAG for questions targeting PostgreSQL!)
317
questions
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 ...
- 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?
- 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 ...
- 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: ...
- 1,807
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} ...
- 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:
...
- 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 ...
- 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
--------+-----------...
- 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 ...
- 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
- 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 ...
- 2,782
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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, ...
- 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 ...
- 483
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 ...
- 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. ...
- 367
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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. ...
- 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=# \...
- 233
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=...
- 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. ...
- 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 ...
- 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/...
- 1,039
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 ...
- 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 ...
- 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).
- 241
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 ...
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:
...
- 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....
- 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....
- 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 ...
- 83
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 "...
- 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 ...
- 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 ...
- 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 ...
- 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
----------------+--------...
personne3000
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 ...
- 153k
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 ...
- 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
...
- 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 ...
- 293
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) ...
- 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 ...
- 4,144
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:
...
- 605