Questions tagged [psql]
psql is the primary interactive terminal for PostgreSQL. (DO NOT USE THIS TAG for questions targeting PostgreSQL!)
354
questions
-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 ...
- 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 ...
- 101
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 ...
- 103
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 ...
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 (...
- 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, ...
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 ...
- 1
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 ...
- 103
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 ...
- 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 ...
- 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&...
- 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 ...
- 103
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 ...
- 103
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 ...
- 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 ...
- 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 ...
- 11
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 ...
- 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 ...
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 ...
- 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: ...
- 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 ...
- 153
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 ...
- 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 ...
- 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= ...
- 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" ...
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) ...
- 15
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 "...
- 11
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
...
- 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 ...
- 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
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 ...
- 11
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 ...
- 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 ...
- 13
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 ...
- 563
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 ...
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
- 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 ...
- 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 ...
- 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/...
- 819
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.
...
- 26
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 ...
- 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 ...
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 ...
- 897
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 ...
- 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 ...
- 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=$...
- 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 ...
- 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 ...
- 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?
- 1
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=...
- 1