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

learn more… | top users | synonyms

1
vote
0answers
531 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 ...
0
votes
1answer
73 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... ...
4
votes
1answer
269 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 ../tables/...
0
votes
2answers
834 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 ...
2
votes
2answers
2k 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
183 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
1k 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
1answer
3k 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 ...
0
votes
1answer
619 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 ...
3
votes
1answer
8k 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 ...
0
votes
1answer
43 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 ...
3
votes
1answer
140 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
1k 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
523 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 ...
3
votes
1answer
100 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 ...
3
votes
1answer
617 views

How to Substitute Variables in PostgreSQL

The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL? INSERT INTO control_threshold ( threshold_id, group_name, description, sql, ...
8
votes
1answer
1k 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
vote
0answers
137 views

Postgres 9.2 on Windows

Simple question, but utterly infuriating. Trying to use psql from a command prompt, but can't access any of the previous commands with the up/down cursors. I could also really do with finding out ...
1
vote
1answer
74 views

Understanding the output of \d <table> in psql

Say I run the following command: \d mtb.big_table I get the output shown below. My questions are: What are column modifiers? (what does default now() mean?) Under Indexes, Why do some indices ...
0
votes
1answer
31 views

Saving output of \ commands to disk

When I try to run the following command: my_db> \copy \dt *. to /home/john/list.txt I get an error: \copy: parse error at "*" Why? How can I save the output to disk?
18
votes
3answers
19k 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: ...
2
votes
1answer
5k 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 ...
1
vote
1answer
224 views

Listing all databases in PostgreSQL [closed]

I have a basic question about PostgreSQL: When connecting to my database server via pgadmin3, I can see that there are multiple databases. For example, let's say I can see "test", "test1", and "...
4
votes
4answers
403 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 ...
3
votes
1answer
95 views

psql coloured prompt misbehaving

In theory, there is a possibility to use coloured prompts in psql. I decided (not the first time :) that I need them, so I thought I would give them a try. So I have in my .psqlrc the following: \...
0
votes
2answers
358 views

Storing data in PostgreSQL: One table or two?

I've just started using PostgreSQL 9.2 and my data consists of product prices at various points in time, usually a different price every month. Question: Because every product can have different ...
2
votes
1answer
222 views

Mismatch between size computed from pg_buffercahce Vs shared_buffers in postgresql?

I have a rather naive question regarding the size of shared_buffers computed from pg_buffercache and the shared_buffers size mentioned in postgresql.conf. Based on my understanding pg_buffercache ...
1
vote
2answers
6k 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 ...
1
vote
1answer
158 views

Hiding variable input in psql

I am attempting to prompt for a username and password when running a database creation script, and I want to hide any text input for my password prompt. In Oracle's sqlplus, I can use HIDE at the end ...
5
votes
0answers
2k 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....
1
vote
3answers
2k views

psql asks for password, CREATE USER didn't specify one

I used to have a user, say 'jack' in postgresql, and then I forgot what the password. I reassigned all its ownerships to user postgres, removed the user, and then created it again with CREATE USER ...
4
votes
2answers
946 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 ...
1
vote
1answer
116 views

There is an equivalent of login.sql on PostgresSQL?

When you log on Oracle database with SQLplus, it looks for a login.sql file in the current directory. If the file exists, it's executed. It's usefull to set preferences... There is an equivalent ...
4
votes
3answers
422 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).
1
vote
0answers
3k views

restore compressed dump query in postgresql asks password [closed]

I'm using ubuntu OS and I took backup using sudo -u postgres pg_dump dbname | gzip > filename.gz in the file path as... /home/administrator/Documents/DB_Backup# sudo -u postgres pg_dump dbname ...
6
votes
1answer
1k 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 ...
4
votes
1answer
661 views

PostgreSQL psql command line tool and SET CONSTRAINTS DEFERRED

I am using PostgreSQL 9.1.5 and the psql to run a scipt containing a series of INSERT statements in various tables: psql -U usernamefoo databasenamefoo -f dml_script.sql The table constraints have ...
4
votes
2answers
4k 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. ...
3
votes
1answer
3k 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 ...
4
votes
2answers
4k 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 ...
9
votes
2answers
11k 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 ...
4
votes
1answer
6k 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) ...
0
votes
1answer
614 views

PL/pgSQL function + client-side lo_import

I have a problem with importing documents into PostgreSQL. I have a plpgsql function, which simplified could look like this: create function add_file(flag integer, sth varchar) returns void as begin ...
26
votes
12answers
75k 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 ...
2
votes
1answer
6k 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. ...
7
votes
3answers
3k 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/...
5
votes
4answers
7k 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 ...
2
votes
1answer
22k views

How to import a.csv file into a postgresql database using \copy?

I am trying to import a .csv file into a postgresql database. I am using pgadmin III for that on a win7 machine. My query looks like that: \COPY pop_grid(GRID_ID, POP_TOT, YEAR, METHD_CL, CNTR_CODE, ...
3
votes
1answer
204 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 jobs(...
4
votes
2answers
2k views

psql won't list databases

I have a situation that I'm scratching my head over, but I'm new to postgresql, so I may be overlooking something obvious. I set up a testbed server and set access in pg_hba.conf: local all ...