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

learn more… | top users | synonyms

0
votes
0answers
3 views

psql multiline commands: How to edit previous lines?

When writing a query across several lines in psql, sometimes I realize I've made a mistake in one of the lines previously written. In the following example, select * form ( etc, etc... there is a ...
1
vote
0answers
24 views

Windows Domain User with Admin privileges cannot access a drive with full permissions in psql

I have installed the standard PostgreSQL 9.5 from Enterprise DB with a domain-level postgres user who has domain-level admin group membership. I have explicitly added full permissions for this user to ...
3
votes
1answer
29 views

Require JSON to have an element

When working with the json datatype, is there a way to ensure the input JSON must have an element? I don't mean primary, I want the JSON that gets inserted to at least have the id, name element. It ...
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 ...
1
vote
1answer
21 views

Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?

Let's say I have 20 different schemas on a Postgres database. I create a new user theUser and now I want to grant only the SELECT and INSERT privileges for this user on all schemas. Is there an easy ...
0
votes
0answers
12 views

Windows Powershell - multiline indirect for psql connection

I have a script that goes like psql << EOF sql 1 sql 2 EOF I want to do the same in Windows powershell. I learned about the script block, but tried passing it in Invoke-command, but hitting a ...
1
vote
1answer
69 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
6 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
21 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 '/...
26
votes
1answer
49k 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
25 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
46 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
45 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
73 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 -...
2
votes
1answer
124 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
74 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
43 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
69 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?
27
votes
3answers
70k 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
935 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
56 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
420 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
43 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
40 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 ...
2
votes
1answer
310 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
30 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
68 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
70 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
118 views

Count for many-to-many relationship

I have the following tables: surveys properties owners_properties owners ---+------------ ---------- ------------+--------- ------ id | property_id id ...
0
votes
1answer
24 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
250 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 ...
579
votes
7answers
1.0m 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
393 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
480 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
32 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
67 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
1k 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
3k 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
94 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?
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
votes
3answers
491 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
31 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
342 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 ...
4
votes
3answers
417 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).