Questions tagged [psql]
psql is the primary interactive terminal for PostgreSQL. (DO NOT USE THIS TAG for questions targeting PostgreSQL!)
337
questions
0
votes
2
answers
18
views
How can reset query buffer in psql's console?
I want to set a comment on some column ,in the sample database to add comment on table tablename's column field:
sample=# comment on column tablename.field is 'don\'t use it';
The console turn into ...
- 222
3
votes
2
answers
132
views
psql can't connect to existing db
i have problem with connecting to database via psql. I get this error:
postgres@server:/var/lib/pgsql # psql -d aisgdmp2
psql: error: FATAL: database "aisgdmp2" does not exist
DETAIL: The ...
- 33
1
vote
0
answers
7
views
Why can psql commands not be executed from PGAdmin "Query Editor"
From the PGAdmin "Query Editor" I have tried, in the spirit of this SE post, to embed psql commands in my SQL scripts: For instance,
\set myTempVariable 42;
SELECT * FROM mytable WHERE age= ...
- 111
0
votes
0
answers
26
views
POSTGRESQL: Can you set a path which psql uses for finding SQL files when executing command from file?
I'm coming from an Oracle background and was used to setting an environment variable called SQLPATH which works pretty much like the $PATH on Linux.
To my understanding, when using the "\i" ...
0
votes
0
answers
28
views
Why would one of our linux VMs have a lag in connecting to PostgreSQL and not the others?
We have several VMs connecting to multiple PostgreSQL database servers. All but one VM connect instantly to all of the database servers, while our newest VM sometimes has a noticeable (1-2 seconds) ...
- 15
1
vote
1
answer
72
views
Create user mapping without password how to configure authentication
I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says.
local_db=> select * from employee;
ERROR: could not connect to server "...
- 11
0
votes
1
answer
53
views
Why the new user can't login postgresql database?
postgres can login postgresql via my bash console:
psql -U postgres
I have created a new user with the such same password as postgres.
postgres=# CREATE USER debian PASSWORD 'xxxxxx';
CREATE ROLE
...
- 111
1
vote
0
answers
168
views
Getting error as "ERROR: permission denied for schema public" while executing a sql script in Postgres sql
I am using ubuntu 18.04 and running postgres sql in a docker container running on port 5432 on a VM powered by GCP.
First of all I entered sudo -u postgres psql to enter into the database as ...
- 11
0
votes
0
answers
7
views
How can keep all query result in the psql console window instead of disappearance? [duplicate]
The table sp500 contains 503 records.
select * from sp500;
The select query can list all of them,when i check it with my page down key or arrow key until the end of all records:
If i press esc key ,...
- 111
1
vote
0
answers
85
views
After creating a table in postgres with psql, \dt gives "Did not find any relations"
I'm working with postgres14 in a docker container on a remote Rocky Linux VM. After a little bit of pain I managed to access the database from outside the container, at which point I wanted to create ...
- 11
0
votes
0
answers
63
views
Refresh materialized views upon database restore
Our database (PG 12) uses a few materialized views, some depending on others. We use pg_dump for backups and psql to restore. And, we do this restore every once in a while, for example when upgrading ...
- 371
1
vote
0
answers
71
views
How to recover a view after accidentally dropping a table with cascade?
I'm using Postgres 13 & recently dropped a table with cascade on to drop all indexes, etc.
It turns out we had an old view that was tied to that table, and we need to recover the view or at least ...
- 13
3
votes
1
answer
282
views
Set PostgreSQL config from command line with ALTER SYSTEM
I need to write a shell/bash script that automatically changes some PostgreSQL configurations.
Here's the command that I wrote:
sudo -u postgres psql -U postgres -d postgres -c "
ALTER SYSTEM SET ...
- 513
1
vote
0
answers
144
views
Unable to initialize a DB cluster and if initialized, pg_ctl does recognize active PostgreSQL services?
I am running Debian@11.6 via wsl2 on Windows 11.
I got a few issues.
One - I am unable to initialize a PostgreSQL DB cluster
Below are my bash configurations that I edited using sudo nano.
~/.bashrc ...
0
votes
0
answers
40
views
How Update psql?
For some reason I have PostgreSQL version 13 installed, but my psql is version 10. How can I fix this?
[Tue Dec 27 22:09:13 user3@server_f ~] /usr/bin/psql --version
psql (PostgreSQL) 10.21
- 121
1
vote
1
answer
677
views
postgres pg_dump + .pgpass file : no password supplied
per PSQL docs I created a .pgpass file on macOS with 0600 mask.
a.b.com:32109:mydb:user1:password1
when I list the file:
$ ls -la .pgpass
-rw------- 1 user staff 212 Dec 21 12:56 .pgpass
export ...
- 121
0
votes
1
answer
2k
views
Run PSQL command from bash script
I would like to know how to run \du within a script, and output that to a file.
Something like the following:
# some_script.sh
psql <database connection> & \du > output_of_du.txt
I can ...
- 226
0
votes
0
answers
67
views
psql RAISE NOTICE vs EXCEPTION have line break differences
I am finding that, in my use case, RAISE NOTICE produces a blank line after the message, but RAISE EXCEPTION does not.
I have psql configured to limit its output with
\pset tuples_only on and
\set ...
- 329
4
votes
2
answers
392
views
How can I get a Postgresql error number in psql?
How can I access the numeric code of an error in psql?
For example, in this case I would like to get the value 23503 ("foreign_key_violation") as documented here:
https://www.postgresql.org/...
- 807
0
votes
2
answers
44
views
How to query 30k of entries from an external data source by script
I have a SQL query which works for single checks:
SELECT trans_id from schema.table where trans_id like '%<trans_id>%'
There're might be better approaches to query for, but that's not the point.
...
- 26
0
votes
1
answer
293
views
psql environment variables same as shell ones?
(I suspect the answer is yes, but I don't see it confirmed anywhere.)
In psql we can set environment variables using the \setenv {name} {value} command.
Are these the same environment variables as the ...
- 103
0
votes
1
answer
112
views
In postgres unable to create database
I installed the package using:
sudo apt install postgresql postgresql-contrib libpq-dev
but in postgresql mode:
it was not creating database
I am using ubuntu 22.04
I waited for an hour still it was ...
0
votes
1
answer
38
views
`.psqlrc` setting, but hide the debugging message that this setting has turned on
I have configured psql, the PostgreSQL command line client, with ~/.psqlrc file, and I have the line \timing, to show how long queries took. But psql will print a line telling me that it has applied ...
- 807
0
votes
0
answers
70
views
How do I retain command history for psql running within PowerShell?
I'm running the following version of psql
psql (12.11)
on Windows Powershell running on Windows 10. Within the same shell session, when I log in and out of psql, it doesn't retain the previous ...
- 563
0
votes
0
answers
82
views
Can't cancel own query in AWS Aurora PostgreSQL 10.18 using pg_cancel_backend
I'm trying to cancel queries in AWS Aurora PostgreSQL 10.18 when logged in as not the master user, using pg_cancel_backend.
However, when I run SELECT pg_cancel_backend(1234) say, then I get the error:...
- 149
0
votes
1
answer
72
views
Why is a Move and Delete Data (approx 1/3 of the table) using (CTEs) WITH statements taking hours on a 600m row table taking 3+ hours - best approach?
Am not sure if this is the best method of moving/deleting data. The play table has 700m+ rows and game table has over 600m+ rows.
I have 2 tables play and game, information from the play table is ...
- 389
0
votes
1
answer
330
views
psql escape backslash
I want to escape backslash by \b so I can later on process it normally.
So far I use a command that return tab separated result which coverts nulls into \N as well (for mysql load into):
PGPASSWORD=$...
- 473
0
votes
1
answer
125
views
What is the meaning/benefit of this command: export PGOPTIONS="-P"
I see https://www.postgresql.org/docs/current/sql-reindex.html has
$ export PGOPTIONS="-P"
$ psql broken_db
...
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q
I see the below ...
0
votes
1
answer
1k
views
In Postgres 12, how do I set a default schema when logging in using psql?
I'm logging in to my Postgres 12 (running on Windows 10) db using an alias, which resolves to
PGPASSWORD=$DB_PASSWORD psql -U${DB_USER} $DB_NAME
I would like to find a way to tell Postgres which ...
- 563
0
votes
1
answer
159
views
Syntax Error in Boolean Null values
I have a csv that contains Boolean variables (yes,no,blanks). How can I recode blanks so that it'd integrate in the data type. Or fo i have to change data type for blanks to be readable?
- 1
0
votes
0
answers
587
views
"NOTICE: identifier will be truncated" in psql when I try "ALTER SYSTEM SET"
PostgreSQL 12.9
When I try to run:
psql -c'alter system set archive_command="pg_probackup-12 archive-push -B /pg_probackup --instance=pgsql-n1 --wal-file-path=%p --wal-file-name=%f --remote-host=...
- 1
0
votes
1
answer
29
views
What will happen if I run rollabck without begin in PostgresSQL statement?
I have run below statement without begin then, show a warning message
(WARNING: there is no transaction in progress)
like below image. But actually that table was deleted. Why it is not roll backed? ...
0
votes
0
answers
626
views
Restoring DB from pg_basebackup I get 'role "postgres" does not exist' error on new DB
I do it for the first time. On the main server I've set archive_mode getting my WAL files and do base backups via cron with the following command:
pg_basebackup -h localhost -U <user> -Ft -z -D /...
- 1
3
votes
2
answers
644
views
psql autocomplete on windows
Is it possible to make autocomplete working in psql on Windows?
As I know from Linux users, they can make use pressing TAB to autocomplete, which is quite handy, I guess.
- 131
2
votes
0
answers
112
views
interpolation of variables in appears not to apply to psql --command. Should this be expected?
Is it to be expected that psql does not perform variable interpolation on strings supplied as value of --command/-c option?
Specifically, I was surprised to find that this is an error:
$psql -v x=1 -c ...
- 121
0
votes
0
answers
2k
views
postgres fatal external connect error no encryption
I want to external connect to my postgresql database. so I use psql -h IP -p Port -U username -d databasename but it shows me error psql error : connection to server at "IP", port 5432 ...
- 1
0
votes
1
answer
1k
views
How to use psql command with no password prompt? [duplicate]
I'm trying to write shell script to avoid entering password every time while login to psql.
- 1
2
votes
0
answers
153
views
Why is Postgres 9.6 using a sequential scan when an index would be more efficient
I have a function which is being used on our system. When you run the function direct on the server itself it takes milliseconds to run. However when it is run via the application it takes seconds (...
- 389
1
vote
1
answer
649
views
how to configure psql to use the a different unixsocket path
I changed the port of Postgres to 2345 and I have the socket in:
/tmp/.s.PGSQL.2345
If I run psql I get this error:
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: ...
- 149
0
votes
0
answers
27
views
How to list out User Constraints in PostgreSQL?
I'm looking for an equivalent in PSQL for the below command:
SELECT *FROM USER_CONSTRAINTS WHERE TABLE_NAME='<Table Name>';
I tried \dt+ <Table Name> but it doesn't list out the names of ...
0
votes
1
answer
976
views
Postgres 13: Subscription stuck at initialize state
Postgres version: 13.5
I have 2 sets of publication/subscription between my 2 DBs.
One of them is working fine and the other one is stuck at initializing state for all the tables.
sourcedb=# select * ...
- 61
-1
votes
1
answer
433
views
How to store date with only four digital format?
In postgresql the date format is yyyy-mm-dd by default.
create table sample(
quant int,year date);
The data file is stored data.csv in csv format such as:
quant,year
10,2019
12,2020
13,2021
Try to ...
- 222
1
vote
0
answers
246
views
Independently checking if PostgreSQL server and client are installed
How can I independently determine whether the PostgreSQL server and client are installed in the Unix-based system?
I believe running psql --version checks only for the PostgreSQL client.
Is postgres --...
- 115
1
vote
0
answers
164
views
Error "set-valued function called in context that cannot accept a set"
I have sql script which runs correctly on postgres 11.3 but not on 13.4
I have about 5 tables, used in script - code below
SELECT "enterprise_workplace"."id",
"...
2
votes
1
answer
2k
views
Transpose rows to column in postgresql
The following query gives me a list of 1222 distinct columns:
select distinct column_name from information_schema.columns where table_name like 'fea_var%';
I want to create one base table which has ...
- 31
0
votes
1
answer
1k
views
How do I escape characters when running COPY command on command line?
I'm using Postgres 14 on CentOS 7. I would like to run a copy command from a bash script. I first tried running the command on the command line
$ PGPASSWORD=$DB_PASS psql -U $DB_USER -d $DB_NAME -c '...
- 563
3
votes
1
answer
2k
views
pg_dump is not the same version as psql
I updated postgres to version 14 with sudo apt upgrade.
psql --version
psql (PostgreSQL) 14.1 (Ubuntu 14.1-2.pgdg20.04+1)
But pg_dump did not upgrade with everything else:
pg_dump --version
pg_dump (...
0
votes
0
answers
23
views
Getting the max ID of integer columns in databases in Postgres 9.6/12
I am looking to get the max ID of each integer column in our databases servers.
The function I currently use:
CREATE OR REPLACE FUNCTION intpkmax() RETURNS
TABLE(schema_name name, table_name name, ...
- 389
0
votes
1
answer
1k
views
Postgres list only user's database and keep other hidden?
I've created a postgres user 'tester' and allowed to readonly to a database A. This user has no permisssion on database B & C.
But when I do \l to list database. The database B and C are also ...
- 219
0
votes
1
answer
325
views
PostgreSQL - psql \watch command - how to interrupt on Windows binary distro (EDB)
The output of SELECT version();
------------------------------------------------------------
PostgreSQL 14.1, compiled by Visual C++ build 1914, 64-bit
(1 row)
and it's a binary distribution from ...
