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
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) ...
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 "...
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 ...
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 ...
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 ...
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/...
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. ...
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 ...
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:...
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?
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=...
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.
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 ...
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 * ...
-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 ...
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 ...

1
2 3 4 5
7