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
-1 votes
1 answer
34 views

How do I connect to a PostGres 14 server installed via Homebrew on Mac OS Big Sur?

I installed PostGreSQL 14 via Homebrew. I seem to be able to restart my local server ... $ brew services restart postgresql@14 Stopping `postgresql@14`... (might take a while) ==> Successfully ...
Dave's user avatar
  • 643
0 votes
1 answer
31 views

PostgreSQL UPDATE successfully executed but stopped making changes

I made some UPDATE-s to my PostgreSQL DB, and after some point, they all of a sudden stopped making changes, even though the result says that there were updates made. I tried doing it inside ...
user2340939's user avatar
0 votes
0 answers
12 views

How to achieve Economic Order Quantity in inventory

Hope you are doing well. I have a existing schema which maintains product batch and expiry date record and it sale short expiry products accordingly. Here is my existing schema I am also tracking ...
Hassan ALi's user avatar
0 votes
1 answer
35 views

Setup Citus extension in a ssh environment

I tried to do CREATE EXTENSION citus;. However, I was given the following error: ERROR: Citus can only be loaded via shared_preload_libraries HINT: Add citus to shared_preload_libraries ...
Disdainty's user avatar
0 votes
1 answer
14 views

psql meta-command \d is really slow

My basic question is: what affects the performance of \d? I have a separate (small) schema that I manage on a database server that I have otherwise no higher privileges on. The server holds a huge (...
Randall's user avatar
  • 345
0 votes
1 answer
21 views

How to add information about two same named databases on different hosts in PgBouncer config file?

I want to add details for databases in a config file of pgbouncer. I have two same database names but their hosts are different, as there is no information about server endpoints in the config file, ...
pratiksha ratnaparkhi's user avatar
0 votes
0 answers
15 views

Return nothing or all if overlaps fit somewhere PSQL

I have searched a bit and not come across anything comparable, so I'm asking this question here, hoping I haven't overlooked anything. In my situation, we store deliveries on the database, which ...
Ricardo's user avatar
0 votes
1 answer
90 views

Issues using a psql -v variable with jsonb_set

Pretty obscure situation and a couple hours of trying different things with no luck. I have a bash script which restores a production database backup locally. Part of the script runs some SQL to set ...
Luke Belbina's user avatar
1 vote
0 answers
90 views

PgAdmin4 cant find pgagent as an extension MacOS

I installed pgagent using EnterpriseDB's installer on MacOS. However, pgagent doesn't show up in pgadmin after the installation. Even when I try to manually add pgagent as an extension, I get the ...
champa's user avatar
  • 11
0 votes
1 answer
38 views

Using PostgreSQL

I have a batch file that can connect to the remote server without an issue. I now want it to read the sql script that works fine thru the gui and export the data to csv. The two pieces work perfectly ...
linda's user avatar
  • 11
0 votes
0 answers
113 views

PDT [95116] FATAL: could not access file "pg_stat_monitor": No such file or directory

Recently I accidentally added pg_stat_monitor as a shared preload library but now I can't start my postgres database server because of PDT [95116] FATAL: could not access file "pg_stat_monitor&...
champa's user avatar
  • 11
0 votes
1 answer
37 views

data modeling - alternatives to NULL foreign key?

I am currently working on a browser game. There is a map, each play has at least one village. Each spot on the map is either empty OR used by a village. I have a map table with a list of all position ...
Pierre-Alexandre's user avatar
0 votes
0 answers
20 views

design tables with multi prerequisites

I am working on a browser game and currently on the data modelling part. This is a browser game where every player has one or multiple village, with different buildings, and each building can be ...
Pierre-Alexandre's user avatar
0 votes
0 answers
23 views

Is there a way of calculating the schema that would result from applying a Postgres SQL script

I have a Postgres sql script with lots of CREATE TABLE and ALTER TABLE statements. CREATE TABLE IF NOT EXISTS admins ( id SERIAL NOT NULL, token TEXT NOT NULL UNIQUE, PRIMARY KEY ...
dcastro's user avatar
  • 109
2 votes
2 answers
590 views

psql: Preventing Idle Transactions by Default

To prevent idle transactions in psql by default, I thought I should just set an aggressive idle_in_transaction_session_timeout in my .psqlrc. This works initially, however, when the connection is ...
Bryce's user avatar
  • 35
0 votes
0 answers
122 views

Escape single quotes when passing command to psql -c

I need to run the following command in a BASH shell: sudo -u postgres /usr/pgsql-15/bin/psql -c "select count(*) from web_site where web_page like '%something%' " -d 'some_database' The ...
enrico_steez's user avatar
0 votes
1 answer
1k views

PostgreSQL-15, how to retrieve table owner by given database name and table name?

I have multiple PostgreSQL-15 databases with different names to fulfil our business operational data, such as: pilot, production and archive data in different purpose. I am trying to write a shell ...
cidy.long's user avatar
  • 133
0 votes
0 answers
136 views

Psql error when attempting to restore schema backup to Postgres database

I am trying to clone a schema in my remotely hosted Postgres 15 database, without affecting the original schema. I decided to do this in the method outlined here in the most-upvoted answer, i.e. using ...
user274130's user avatar
0 votes
0 answers
24 views

\dt in psql exits shell

I have created a brand new instance and database which I am connecting to with psql. I have successfully ran a CREATE TABLE command and now I want to list the tables, so I write \dt and the shell ...
PalBo's user avatar
  • 101
5 votes
1 answer
559 views

using "\dp table_name" in "psql" throws "operator is not unique"

Problem I tried to run \dp heroes in psql however it immediately threw the following! (heroes is the name of the table) ERROR: operator is not unique: unknown || "char" LINE 16: ...
frostzt's user avatar
  • 153
1 vote
1 answer
159 views

Set 'timing on' for the duration of the current `psql` session using command-line options

What is the equivalent of \timing on command in psql, using the psql command-line options? I am aware that this will enable timing for all psql sessions: echo '\\timing on' >> ~/.psqlrc I am ...
Timur Shtatland's user avatar
0 votes
2 answers
257 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 ...
showkey's user avatar
  • 284
3 votes
2 answers
531 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 ...
Misudka's user avatar
  • 33
1 vote
0 answers
49 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= ...
Owen's user avatar
  • 111
0 votes
0 answers
548 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" ...
Blockdump's user avatar
0 votes
0 answers
36 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) ...
Andy Norris's user avatar
1 vote
1 answer
417 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 "...
Aymen Rahal's user avatar
0 votes
1 answer
236 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 ...
newview's user avatar
  • 111
1 vote
0 answers
691 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 ...
Talib's user avatar
  • 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 ,...
newview's user avatar
  • 111
1 vote
0 answers
449 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 ...
skippynk's user avatar
0 votes
0 answers
342 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 ...
vektor's user avatar
  • 371
1 vote
0 answers
491 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 ...
Jose Lopez's user avatar
4 votes
1 answer
1k 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 ...
collimarco's user avatar
1 vote
0 answers
650 views

Unable to initialize a DB cluster and if initialized, pg_ctl does recognize active PostgreSQL services?

I am running [email protected] 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 ...
iCantFindaGoodUsername's user avatar
0 votes
0 answers
86 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
Rich_F's user avatar
  • 131
1 vote
1 answer
2k 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 ...
taitelman's user avatar
  • 121
2 votes
1 answer
6k 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 ...
baxx's user avatar
  • 296
4 votes
2 answers
664 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/...
Mark Harrison's user avatar
0 votes
2 answers
52 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. ...
dbalucas's user avatar
0 votes
1 answer
597 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 ...
Niels Bom's user avatar
  • 103
0 votes
1 answer
329 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 ...
vishnu kolluri's user avatar
0 votes
1 answer
146 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 ...
Amandasaurus's user avatar
0 votes
0 answers
118 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 ...
Dave's user avatar
  • 643
0 votes
1 answer
89 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 ...
rdbmsNoob's user avatar
  • 429
0 votes
1 answer
702 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=$...
Nir's user avatar
  • 513
0 votes
1 answer
219 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 ...
Vy Do's user avatar
  • 189
0 votes
1 answer
2k 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 ...
Dave's user avatar
  • 643
0 votes
1 answer
241 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?
Saransh's user avatar
0 votes
1 answer
1k 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=...
Anton P's user avatar

1
2 3 4 5
8