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

learn more… | top users | synonyms

1
vote
1answer
20 views

CREATE SEQUENCE using expressions with psql variables for parameters

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for ...
0
votes
0answers
4 views

How we can get all the PostgreSQL output(including the error) to a log file?

I have already tried in psql-shell the script with \o and \o opening and closing tags, but it only logs the result. I am looking for the errors also. I need to log all the results of our query ...
1
vote
0answers
9 views

FATAL: role “MYUSERNAME” does not exist

I am new to psql and am trying to set it up using the Postgres.app site on my Mac. After installation and opening psql, this is the message I get: Last login: Sat May 21 07:10:21 on ttys000 ...
25
votes
1answer
45k 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} ...
1
vote
1answer
19 views

Add variable value in single quoted string. Expand variable within single quotes

I have tried different variations of the below commands but non work. I simply want to run the following command using a variable within the location string: CREATE TABLESPACE data OWNER appuser ...
0
votes
1answer
42 views

How to concatenate psql variables in tablename?

I am trying to concatenate some fixed string with a variable to build a table name. This simple example demonstrates the problem. The variable "suffix" resolved properly, but "prefix" does not ...
1
vote
0answers
37 views

what are the options for the psql \copy command?

I was looking at the psql documentation: \copy { table [ ( column_list ) ] | ( query ) } { from | to } { 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [ [ with ] ( ...
1
vote
0answers
47 views

PSQL's \i vs psql -f

In the days when I used MySQL I was OK with using the mysql -u myuser mydb < file.sql to execute SQL script. For a while now I use PostgreSQL and there people (and docs) advice to use: psql ...
1
vote
1answer
82 views

Postgres \copy from CSV: how to format range variables?

Consider a Postgres table containing a range variable: CREATE TABLE my_table (t_range tsrange, field1 text); If I want to manually insert a row via psql, I can run: INSERT INTO my_table (t_range, ...
3
votes
1answer
59 views

How to paste chinese characters in psql terminal?

I am trying to paste chinese characters into psql terminal: $ psql => \encoding => UTF8 # Ctrl+v (艺术与设计) => ?????设计 When pasting into normal terminal (Max OS) it works fine: $ # Ctrl+v ...
3
votes
1answer
41 views

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, ...
2
votes
1answer
44 views

What is preventing DROP TABLE from removing a Postgres table? [duplicate]

I have a postgres database named aarons_db in ubuntu linux. I'm trying to delete a table called pnt within aarons_db. To do this, I am using the following command: DROP TABLE pnt Unfortunately, ...
0
votes
1answer
44 views

psql variable holding text with multiple lines

This works as expected, \set x '''2\n3''' but this doesn't \set x '''2 3''' Is there a workaround for later example so I don't have to use \n instead of newline?
24
votes
3answers
64k views

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 ...
4
votes
2answers
853 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 ...
0
votes
1answer
51 views

connect to psql and run SQL command from command line

I'm trying to write a quick line in bash that will use psql to connect to a database and delete all entries in two tables: \connect some_db DELETE from some_table DELETE from another_table I'd like ...
2
votes
1answer
19 views

Why does PSQL not allow hash indexes with Bitmapscan?

For doing a simple equality query such as EXPLAIN SELECT * FROM table where x = 1; I see that a Bitmap Index scan is done using the BTree indexes. Apparently, Bitmap Index scans disable Hash ...
0
votes
0answers
307 views

pgAgent not working on linux (Ubuntu 14.04)

I am running Postgres 9.3 on Ubuntu 14.04 and I am unable to get pgAgent daemon/service to run. I have confirmed that the daemon is not running using service --status-all. When trying to run ...
0
votes
0answers
39 views

Send Keyboard Interrupt in PostreSQL psql console?

Is it possible to send a keyboard interrupt signal to the PostgreSQL psql console that will take me back to a new console command line? Sometimes I'll type a command incorrectly and then hit enter ...
0
votes
0answers
34 views

Is my system configuration restricting me to psql:i386?

When I try to run psql on 64 bit sid I get this error message: /usr/lib/postgresql/9.4/bin/psql: symbol lookup error: /usr/lib/postgresql/9.4/bin/psql: undefined symbol: PQconninfo I needed to run ...
26
votes
11answers
71k 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
278 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 ...
0
votes
0answers
28 views

PGBouncer pausing hanging from ltm monitor

Currently I have the following setup: F5LTM --> PGBx2 --> DBx3 The PGBouncers are set up with priority groups on the F5 so in the event one fails or hangs traffic will still be sent to the database ...
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. ...
0
votes
1answer
43 views

Can I make something similar to a makefile for psql?

Basically I have a handful of .sql files that I regularly modify in structure that need to be run in a certain order: CREATE.sql, INSERT.sql, PLPSGSQL.sql, TEST.sql, DROP.sql Since every structural ...
0
votes
0answers
56 views

command-line arguments for postgresql

The following database restore command is failing because of too many command-line arguments (first is "151019_testbase.sql"). This is effected from a shell session within the directory of the ...
0
votes
0answers
15 views

Is there a way to set psql options in a file? [duplicate]

Is there a ".psql" file to set default config values for the psql utility? I'm particularly interested in setting \x by default.
0
votes
1answer
93 views

Count for many-to-many relationship

I have the following tables: surveys properties owners_properties owners ---+------------ ---------- ------------+--------- ------ id | property_id id ...
0
votes
1answer
23 views

SQL query with multiple many-to-one joins [closed]

I want to select a count of all surveys where the survey.property.address.city == "Garrison". I have the following tables (stripped for your convenience): surveys properties ...
1
vote
1answer
168 views

Deleting many rows on a live table in chunks - without locking for too long (PostgreSQL 9.3)

I am new to postgres, I have a 20 million row table that is on a live server - I need to remove most of the rows but not all. I want to do this without impact to other read/write processes accessing ...
548
votes
7answers
975k 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 ...
5
votes
2answers
330 views

psql: SELECT * … except one column

I search for a simple way to select all columns except one in psql. With psql I mean the interactive command line. I would be happy with a tool that expands to * to a list of quoted column names. ...
3
votes
1answer
1k views

Why 't' and 'f' instead of TRUE and FALSE

Why does PostgreSQL return 't' and 'f' instead of TRUE and FALSE? The documentation itself advises us to use TRUE and FALSE when inserting boolean values or making comparisons, but returns a ...
4
votes
1answer
393 views

Cannot revoke default privileges from postgresql user

I am using the psql tool: Command: my_db=# \ddp Result: Default access privileges Owner | Schema | Type | Access privileges ...
0
votes
1answer
31 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
60 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=# ...
2
votes
2answers
988 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
2k 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
64 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
vote
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
-2
votes
3answers
396 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
30 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
297 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 ...
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 ...
0
votes
0answers
336 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
399 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
458 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
38 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
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. ...
3
votes
1answer
201 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 ...