Questions tagged [psql]

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

Filter by
Sorted by
Tagged with
4
votes
1answer
16k views

Copy from file: ERROR: invalid input syntax for integer: “\N”

My table column defined intcol integer; no NOT NULL constraint. In the load file most values are integers, but a few are null values labeled with the '\N' default as suggested by the copy manual. Can ...
5
votes
1answer
5k views

PostgreSQL PSQL client-side commands and new lines (\COPY)

The error message is the same with super user using COPY. The files are located on the same server as the postgres server. Saw many similar posting but not a single one answer my question. \copy ...
1
vote
0answers
481 views

bind psql client on specific network interface

I'm trying to test a postgresql database connectivity using psql command line client. There are several network interfaces on the server and several network interfaces on the client. Each network ...
4
votes
1answer
933 views

How to get only the first error with psql --single-transaction

I'm typing something like: psql --single-transaction --quiet my_db < my_file.sql When the script fails I get many lines saying: current transaction is aborted, commands ignored until end of ...
4
votes
1answer
4k views

psql is not prompting me for a password, then denies access

I'm using Postgres 9.4 on Debian Linux. I created a database with a user, cindex with access to the database. Yet when I try and login at the command line, I'm not even prompted for a password: ...
2
votes
1answer
49 views

Adding constraint on data

I created a table called customer, which consists of one attribute called email. Then I would like to add a constraint to email attribute. Following is my coding: Create table customer ( email ...
1
vote
1answer
2k views

What file formats can be restored using pg_restore on PostgreSQL?

first question here, fairly new to DB's and Data itself. My question is fairly simple, after playing around with Postgres, PGAdmin3, I realised I would like to access some public data, but I ...
2
votes
1answer
2k views

search_path set and seen but not used!

Even though I'm (apparently) setting the search_path correctly I still have to prefix everything with the schema. What's wrong here? How do I get a default schema from the search_path? psql blip ...
3
votes
2answers
9k views

Is there an easy way in PostgreSQL to escape newlines, carriage returns, and tabs?

Let's say I want to escape these to see a similar string SELECT * FROM ( VALUES (E'\r\n\t FOO BAR \t\n\r') ) AS t(str); How do I get the c-style escapes, \r\n\t FOO BAR \t\n\r back out? SELECT * ...
0
votes
3answers
88 views

Create a PK for a catalog w/o PK

I have a catalog-like huge table at my client's infrastructure, that I pull into my db: no pk, and I wasn't able to find a way to create a pk from the existing columns - the table has no restrictions ...
3
votes
2answers
17k views

How to show Large Objects (LOB) contents? [duplicate]

How could I get to have a look into LOB contents? \d ticketgrantingticket Table "public.ticketgrantingticket" Column | Type | Modifiers ----------...
2
votes
2answers
97 views

Erase psql database

I have psql installed in my Ubuntu system. There is a database named tmpdb in it. I want to delete it but get an error message: must be owner of database tmpdb". I open psql with $psql postgres ...
13
votes
2answers
72k views

postgres: permission denied for schema

I am new to postgresql and I would grateful if you could please advise on how-to resolve the following error.. I have issued the followed commands: ip_spotlight-# REVOKE EXECUTE ON ALL FUNCTIONS IN ...
4
votes
2answers
3k views

Extension created for public schema not available within database

So, I create the citext extension for schema public and I can see it's there, but then it's not available within the database in that schema. The database is in public as well, because it's created ...
1
vote
2answers
3k views

Postgresql - Map a Linux user to login

I want to map Linux's root user to Postgres user, like if I enter psql in root user it should go the PostgreSQL cli without asking any authentication. I tried to add an entry in pg_ident.conf and ...
8
votes
3answers
8k views

How to set a search_path default on a psql cmd execution?

I'm looking for an option to create an alias command for a psql console connection which allowed setting the search_path, but I am not finding any option on psql util, nor the option to execute a ...
1
vote
0answers
142 views

Understanding query variance in exec time in PostgreSQL?

I want to execute a set of queries many times through psql and to study running/execution time of each query. Of course, I want to run them many times to reduce effect of external factors through ...
1
vote
1answer
2k views

.psqlrc - setting keepalives_idle connection parameter

Is it possible to set keepalives_idle parameter in .psqlrc so that every connection I open uses it? I've finally found this option and it's a remedy for my connection dropping problems. I would ...
0
votes
1answer
29 views

Selecting union of results of dataset-producing function when applied element-wise to an array

This problem is somewhat an academic curiosity. I'm attempting to implement a SQL (i.e. not plpgSQL) function which takes an array of input data, transforms each entry to a set of zero or more rows ...
3
votes
2answers
10k views

Using Postgres COPY TO for data export without overwriting file

I'm running several queries with a script and psql. I am also exporting the results to a.csv using the COPY TO command. However, this overwrites the file a.csv when I would like to instead append the ...
5
votes
2answers
208 views

SQL Comments disappear with psql and `\e`

Is there anyway at all, ever to change the behavior of comments in psql. Take the query below. Execute it. CREATE TABLE foo AS SELECT x AS id, -- x AS id2, x AS id3 FROM generate_series(1,...
2
votes
0answers
888 views

How to transfer table from one database to another in postgresql

pg_dump -h host_ip -U postgres -d source_db -t aa_nqrgs.act_26022017 | psql -h destination_host_ip -U postgres -d destination_db I have tried this command to transfer a table from one server to ...
1
vote
1answer
481 views

Rate-limiting query execution in a batch process

I have a set of queries in my queries.sql file which I am feeding to psql for execution. But I don't want them to run instantly one after the other: I would like to set some sort of delay - I was ...
2
votes
1answer
79 views

Why does psql open two tcp connections to PostgreSQL?

If I for instance place a iproute, like this sudo iptables -I INPUT -p tcp --syn -m conntrack --ctstate NEW --ctstatus CONFIRMED --dport 5433 -j LOG I can see that there are two entries every time I ...
4
votes
1answer
2k views

PostgreSQL: Cast any boolean field to integer in SELECT * query [closed]

In PostgreSQL, I can cast individual boolean fields to integer when I want to get 0/1 output instead of f/t. SELECT some, thing, mybool::integer FROM mytable; But is there a way to ask PostgreSQL to ...
3
votes
1answer
3k views

Can I have two `psql` sessions use the same database connection?

Is it possible to open two psql sessions that use the same database connection? My goal is to show that a second user on the same connection can see uncommitted INSERTs, wherease a user on a ...
5
votes
2answers
497 views

Avoid output disappearing in psql

I open psql and write: mydb=> \dt and this list of tables is shown: List of relations Schema | Name | Type | Owner --------+-------------------------...
3
votes
1answer
126 views

PostgreSQL: translating user-defined calculations into executable calculation in trigger

I am looking to convert user defined machine readable string formulas into a set of queries and calculations, the result of which would then be saved in another non-normal table. The calcs would be ...
3
votes
2answers
840 views

PostgreSQL: trigger to autopopulate other fields with sub-strings of other field inserted

I am looking to insert 1 to 8 sub-string values from an inserted string value into other fields as integers using a trigger in PostgreSQL. The integer values have a string prefix to identify them ...
1
vote
0answers
39 views

Attach a psql session to a running transaction

Is it possible to open a psql session inside a currently existing transaction? I'd like to be able to do this for debugging purposes. The idea would be to break into a debugger in my program, attach ...
0
votes
2answers
5k views

Use psql --set variable in a function

I want to create a script that will create some custom named schema and within it some tables and functions. Like this: The example.sh file: #!/bin/bash # this is the only place I want to set the ...
2
votes
1answer
493 views

How do I create a psql alias for “\q”?

I'm using Postgres 9.5 on Ubuntu 14.04. Even after using Postgres for a year, I can't get used to typing \q to exit my command line session. My fingers just want to type "exit" or "quit". How can I ...
1
vote
2answers
139 views

Working of postgres remote connection

How does psql remote connection work (default configuration)? Does it make use of ssh or any other protocol? I am using linux machine (centos 6.8) and postgresql9.5 version.
3
votes
4answers
19k views

Relative path for psql \copy file?

I am attempting to create a collection of scripts to modify some tables in a Postgres 9.6 database. These scripts are stored in a variety of directories, and are in turn called from one master ...
5
votes
1answer
3k views

Execute a series of commands in PSQL

I am looking for a solution to execute multiple commands in PSQL. Lets say I have a file with 2 name for database. 1. slave1 2. slave2 I already have a master template named "master_db". If I ...
1
vote
1answer
1k views

PostgreSQL / pgAdminIII: is it possible to send command line commands via the UI

Using PostgreSQL / pgAdminIII, is it possible to send command line commands to PostgreSQL via the UI? Put another way: how can I access the command line via the pgAdminIII? As I am coming up to ...
2
votes
1answer
876 views

Wildcard for meta-command in postgresql

I am searching if there is a wildcard assigned to the \ meta-commandsSee description - meta-commands in Postgresql. for example.. postgresql=> \copy scheme.table FROM 'D:\path\to\%.csv' DELIMITER ...
6
votes
6answers
2k views

Reverse Byte-Order of a postgres bytea field

I'm currently working on a table that contains hashes, stored in bytea format. Converting the hashes to hex-strings however yields the wrong order of bytes. Example: SELECT encode(hash, 'hex') FROM ...
1
vote
1answer
1k views

psql: Could not connnect to the server: No such file or directory

I know that this is a question that has been posted repeatedly, but I have not been able to find an answer that suits me. PostgreSQL 9.5 was running fine, someone decided we needed to edit the pg_hba....
0
votes
2answers
5k views

How to execute multiple postgres script from command prompt

I have 100 script files which has insert scripts for the table. How can I execute these? Scrip1.sql , script2.sql, script3.sql .... script100.sql To execute single file, I am using the below ...
0
votes
1answer
932 views

How can I redirect database requests in my postgres server

So I have a list of(lot many) scrapers which push lot many data into one of my database location. Unfortunately I have a requirement that my database location have to be changed for some performance ...
1
vote
0answers
974 views

CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology crashes server

I am moving to a new Windows server and upgrading PostgreSQL to 9.6 and PostGIS to 2.3 while I am at it (old versions were 9.2.4 and 2.0). I used pg_dumpall to dump the entire server. Came to 318GB. ...
2
votes
1answer
670 views

How can I use `psql` without installing via brew

I want to use the psql utility standalone, without installing postgress via brew or something (I have my reasons). I use psql version 9.5.3 on OS X, which was distributed with PostgressApp. When I ...
1
vote
2answers
33 views

Can I get results from a Postgres query that I got disconnected from?

I started an hours-long Postgres query in my psql shell. Then, I got disconnected from the network and had to make a new psql shell. If I look in pg_stat_activity, I see that my query is still running....
2
votes
2answers
1k views

How do you use psql client to connect to a postgresql ipv6 host?

# postgresql.conf listen_addresses='::' and # pg_hba.conf hostssl webdb webserver ::0/0 cert The postgresql server is running on docker with pingable ipv6 address of "GlobalIPv6Address": "...
0
votes
0answers
3k 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
918 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
39 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 ...
1
vote
1answer
528 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 ...
4
votes
1answer
2k 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 ...