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

learn more… | top users | synonyms

0
votes
1answer
14 views

Restore Postgres Database on New Machine

Trying to restore a postgres database on a new Macbook Pro. I recreated the database using createdb dbname then tried to restore the old database using psql dbname < dbname_dump.bin but ended up ...
9
votes
1answer
36 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
56 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
181 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?
-1
votes
3answers
88 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
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
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
54 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 ...
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 ...
0
votes
2answers
63 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 ...
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
761 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
92 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) ...
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
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 ...
0
votes
1answer
76 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
171 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
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... ...
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
295 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 ...
0
votes
2answers
741 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
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
585 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 ...
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 ...
0
votes
1answer
123 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 ...
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 ...
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
568 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
287 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 ...
3
votes
1answer
334 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, ...
6
votes
1answer
689 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
93 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
58 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
30 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?
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: ...
2
votes
1answer
3k 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
159 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
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 ...
3
votes
1answer
83 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
277 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
180 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
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 ...
1
vote
1answer
131 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 ...
4
votes
0answers
1k 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: ...