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.

336 questions
1500
votes
8 answers

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 have tried \d, d and dS+ but nothing is listed. I…
Jonas
  • 31,495
  • 27
  • 59
  • 64
332
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
  • 31,495
  • 27
  • 59
  • 64
279
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,093
  • 3
  • 15
  • 7
184
votes
5 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
  • 1,967
  • 2
  • 13
  • 8
128
votes
6 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,091
  • 3
  • 22
  • 23
103
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
  • 31,495
  • 27
  • 59
  • 64
50
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
  • 601
  • 1
  • 5
  • 3
40
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
  • 736
  • 1
  • 5
  • 8
38
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,401
  • 4
  • 12
  • 12
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
32
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
  • 575
  • 1
  • 5
  • 10
31
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,811
  • 5
  • 29
  • 32
25
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,659
  • 1
  • 17
  • 19
16
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
  • 11,019
  • 29
  • 79
  • 94
16
votes
1 answer

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, temp_lo, temp_hi, prcp, date FROM weather; I should…
Alpagut
  • 263
  • 1
  • 2
  • 6
1
2 3
…
22 23