Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [psql]

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

psql is a terminal-based client to PostgreSQL, that's packaged with the software. It lets you edit queries interactively, issue them to PostgreSQL, and see the results. You can read and write to / from files and use it for scripting.

Additionally, psql provides a set of meta-commands and other shell-like features to facilitate writing scripts and automating a wide range of tasks.

378 questions
1592
votes
9 answers

How do I list all databases and tables using psql?

When I log in with psql --username=postgres, how do I list all databases and tables? I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, so I know they should be listed.
Jonas
  • 33,945
  • 27
  • 62
  • 64
355
votes
3 answers

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?
Jonas
  • 33,945
  • 27
  • 62
  • 64
314
votes
10 answers

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 using the psql command line tool? I'm on Ubuntu…
pedrosanta
  • 3,463
  • 3
  • 17
  • 7
219
votes
6 answers

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

Using \c 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: syntax error at or near "current_database" LINE 1:…
Amelio Vazquez-Reina
  • 2,315
  • 2
  • 15
  • 8
145
votes
7 answers

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} -d ${DBNAME} -c "REINDEX INDEX…
Majid Azimi
  • 2,351
  • 3
  • 23
  • 24
118
votes
11 answers

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: psql -bash: psql: command not found Do I have to…
Jonas
  • 33,945
  • 27
  • 62
  • 64
57
votes
4 answers

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 above \pset tuples_only will turn both header and…
skong
  • 671
  • 1
  • 5
  • 3
52
votes
3 answers

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 | …
jkj
  • 856
  • 1
  • 6
  • 8
42
votes
3 answers

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 SCHEMA doc FROM PUBLIC ; ip_spotlight-# REVOKE…
nskalis
  • 1,721
  • 4
  • 15
  • 12
38
votes
4 answers

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
andilabs
  • 697
  • 2
  • 6
  • 11
37
votes
3 answers

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 psql against that included the other files…
xenoterracide
  • 2,921
  • 5
  • 31
  • 33
34
votes
14 answers

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 someone can tell me how to troubleshoot this. I am…
Meltemi
  • 777
  • 3
  • 9
  • 14
28
votes
1 answer

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 search. When I hit control-r in bash, I get the…
user664833
  • 1,969
  • 3
  • 21
  • 19
20
votes
2 answers

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 encoding "LATIN1" Following this SO answer, I…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
20
votes
5 answers

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…
Sanx
  • 301
  • 1
  • 2
  • 3
1
2 3
…
25 26