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

learn more… | top users | synonyms

9
votes
1answer
35 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=# ...
0
votes
2answers
53 views

PSQL not executing SQL command - Powershell

I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the ...
8
votes
2answers
179 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 ...
0
votes
1answer
26 views

PostgreSQL to MS Access

Is it possible to create a database in PostgreSQL move it into other computer and use this database with MS Access? How to do that?
0
votes
2answers
728 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
1
vote
1answer
70 views

Update staging data with production data in Postgresql

I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a ...
-1
votes
3answers
85 views

Why does connecting to '127.0.0.1' work whilst 'localhost' fails?

I have noticed that although the listening host in postgresql.conf is localhost connecting to a database via psql fails unless the host is changed 127.0.0.1. I have also noticed MySQL display the ...
1
vote
1answer
18 views

How can I filter databases by owner using psql?

I know I can list my postgres databases with the following command: # list only the databases psql -U openerp -q -t -c "select datname from pg_database;" template1 Or # show a full listing of ...
0
votes
1answer
52 views

Cannot rename database (the user in --username is the owner)

I connect with psql and list my databases: List of databases Name | Owner | Encoding | Collate | Ctype | Access ...
6
votes
1answer
686 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 ...
0
votes
0answers
94 views

Import SQL & export CSV using Golang - how to escape backslash?

I'm trying using Golang for import SQL from /home/z/Documents/data.sql and export to CSV /tmp/filename.csv Since out of the box Golang access from inside psql, the import SQL command will be: \i ...
4
votes
3answers
289 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).
0
votes
2answers
60 views

How to use Italian locale for Postgres messages

On Oracle I have set: export NLS_LANG=Italian_ITALY.UTF8 .. on Unix shell and now I get status and error message in Italian. How to set in Postgres shell (psql)? I have set: SET lc_messages TO ...
1
vote
1answer
24 views

psql with '-c' option or '-f' option is not terminating properly

When I run a command such psql -d database -c 'select * from table' or psql -d database -f sqlcommands psql does not terminate cleanly. Interestingly piping the the contents of the file instead of ...
2
votes
1answer
4k views

Drop multiple databases in one script

I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. ...
3
votes
1answer
182 views

send jobs via psql asynchronously

I want to backup a huge number of records and delete them, but I don't have complete access to database server so I can't run my script on the server. because it takes so long I need to submit ...
0
votes
1answer
58 views

Safe way to perform DB script

If I am reading the documentation correctly, issuing this command psql -v ON_ERROR_STOP=1 database < script.sql where script.sql starts with BEGIN BEGIN; ALTER TABLE... some other DDL... ...
1
vote
0answers
43 views

Does psql print command results to standard error?

I am running COPY command through psql 9.1.15 as a cronjob to append data in csv file to table1: psql -q -h $DB_HOST -d $DB_NAME -p $DB_PORT -U $DB_USER <<EOF copy table1 from ...
0
votes
1answer
113 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 ...
4
votes
1answer
739 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. ...
0
votes
1answer
89 views

Display only table names in psql

How can I print only table names from a specified database in psql? That is, if I have tables called "table1" and "table2" then the output will be: table1 table2 and only that. I've been trying ...
1
vote
1answer
32 views

Using psql with string variable that has two periods

psql -d mydb -U me -h localhost -f db_log.sql -v db_user_string='Me' -v version="1.7.3" into the script begin; create table db_log ( db_owner varchar(255) not null, db_user varchar(255) ...
5
votes
3answers
7k views

psql: Show current database

In psql I can use \c <database_name> to connect to a given database. How can I see what database I am currently connected to? When I do: my_db> current_database(); I get: ERROR: ...
0
votes
1answer
53 views

dynamic postgres inside shell script not working

PG_CMD="psql -d portal -U portal -c " PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c " abc() { $PG_CMD " DO \$$ BEGIN select * from customer; ...
0
votes
1answer
73 views

Postgres installation on MacOSX - problem with psql

I'm currently trying to install Postgres (using the installation package from http://postgresapp.com), i.e. downloading, unpacking, dragging the app into the Applications folder, launching the app. So ...
0
votes
0answers
9 views

Problem with Postgres installation on MacOSX - psql does not work [duplicate]

I'm currently trying to install Postgres (using the installation package from http://postgresapp.com) on OSX Mavericks (10.9), i.e. through downloading, unpacking, and dragging the Postgres app into ...
3
votes
1answer
567 views

Dropped database still shows but causes errors PostgreSQL

I had dropped a database a couple weeks ago but it still shows with \l. I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was ...
69
votes
2answers
30k 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?
0
votes
0answers
169 views

psql: how to turn off context messages at raise notice

If I use raise notice, I get context messages at each execution. Can I turn them off and just get the NOTICE? NOTICE: d = [2,1] ,tn: (1,2,"{NULL,NULL}","{{-1,4},{-1,-1}}",1) CONTEXT: SQL ...
7
votes
2answers
7k 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 ...
364
votes
7answers
652k 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 ...
18
votes
1answer
30k 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} ...
3
votes
1answer
134 views

Rollback on \include error in psql

I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this: \include ...
0
votes
2answers
293 views

Using pg_dump and psql to transfer table with timestamp into another Db

I am using the following pg_dump and psql command lines through the terminal in order to transfer my table from one database to another. pg_dump -U postgres -t public.table1 database1 | psql -U ...
1
vote
1answer
95 views

script for restore of postgres database localy from dump given on stdin - ambiguous redirect

Runing this command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -U foo some_db" runs perfectly fine. It returns on stdandard output the dump of db. Then I would like to restore new_db based on dump ...
0
votes
1answer
581 views

psql: Run commands in batch mode

Consider a .sql file with a few selects and updates. I would like to run this file from a script using the psql utility: psql -h whatever.rds.amazonaws.com -U user dbname -f commands.sql The ...
3
votes
2answers
9k 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 ...
1
vote
1answer
1k views

Can I use pg_restore to restore data to subset of tables?

I am trying to restore data in to select tables of a database (9.3.5). This is a data refresh from production to test, while maintaining some table data in the test database (like application users ...
2
votes
1answer
9k 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 ...
4
votes
4answers
5k 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 ...
4
votes
4answers
323 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 ...
0
votes
1answer
119 views

How can I time SQL-queries using psql from command line?

I am sorry, I cannot comment, so I am posting a new question. There is already a question "How can I time SQL-queries using psql?" but I am missing answer how to do that from command line. How to run ...
1
vote
2answers
3k views

PostgreSQL cannot start after replacing contents inside its Data folder

When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the ...
2
votes
1answer
3k 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 ...
21
votes
9answers
56k 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 ...
0
votes
1answer
39 views

Can PostgreSQL results include the query?

I run the following command to run 2 queries in a sql file. Could be 1, could be 50 queries. Depends on the deployment. psql -U <username> -h <servername> -d <databasename> -f ...
1
vote
1answer
67 views

Understanding postgres prompts

What is the difference between postgres=# and postgres-#? I tried googling it and looking on the official site but could not get a hit for the symbols.
0
votes
1answer
562 views

Restore postgres in command line on ubuntu failed

This question may be asked many times. We are trying to move a postgres db from windows (psql 9.3) to ubuntu runing postgres 9.3. Here is what we did: In windows, create backup for db cis with ...
2
votes
1answer
286 views

Is there a way to define macros/alias in psql?

It is somewhat annoying to type EXPLAIN (ANALYZE, BUFFERS, VERBOSE) in psql tool every time you tune queries. So, is there a way to define an aliases (autocompletable ones would be very nice) in psql ...
1
vote
1answer
71 views

How do I automatically set meta commands when starting psql?

When I start a psql session, I would like certain meta commands to already be set. For instance: \x auto \set ON_ERROR_STOP on I was hoping to find an rc or config file but couldn't. The command ...