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.

321 questions
1455
votes
7 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
  • 30,365
  • 26
  • 57
  • 64
321
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
  • 30,365
  • 26
  • 57
  • 64
258
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
  • 2,883
  • 3
  • 15
  • 7
171
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,837
  • 2
  • 12
  • 8
122
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
  • 1,991
  • 3
  • 21
  • 23
98
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
  • 30,365
  • 26
  • 57
  • 64
49
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
  • 591
  • 1
  • 4
  • 3
39
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
  • 726
  • 1
  • 5
  • 8
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
34
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,271
  • 4
  • 12
  • 12
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,802
  • 5
  • 27
  • 32
23
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,549
  • 1
  • 14
  • 19
16
votes
2 answers

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 REFERENCES reference.donor_type (type_id), …
dw8547
  • 817
  • 2
  • 9
  • 23
15
votes
1 answer

PostgreSQL psql - not working but no errors

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 does nothing. No error, just no results. I can use…
matadur
  • 551
  • 1
  • 5
  • 11
1
2 3
…
21 22