Questions tagged [psql]
psql is the primary interactive terminal for PostgreSQL. (DO NOT USE THIS TAG for questions targeting PostgreSQL!)
370
questions
0
votes
1
answer
24
views
Cannot connect postgresql server
i ran some error today with my postgresql maybe someone can help. I install Postgresql in windows server. I connect using PgAdmin. Usually i just enter my master password, then the next step i enter ...
0
votes
0
answers
19
views
How do I change the console font for psql in PostgreSQL 16.3?
I don't know how this has happened (I may have changed something in my PostgreSQL / psql settings) but now everytime I open the psql console I get the following error:
"WARNING: Console code page ...
- 1
0
votes
1
answer
39
views
Locking write to tables while Postgres procedure is executing
I have created a procedure in Postgres that will create a table with the contents of another three tables (SELECT with JOINS). Then the resulting table will be altered adding two columns that didnt ...
- 103
0
votes
0
answers
87
views
Cannot connect PSQL to local Postgres database
PSQL is failing to connect to Postgres 16 running on the local Fedora 40 Linux server.
I can connect from a remote server or through PGAdmin using pqsl, but not locally. I get the same error when ...
- 1
0
votes
1
answer
34
views
How to input a variable from an insert statement into the json value in PSQL?
I want this statement to insert the variable data in my psql statement but I do not know how to initialize the data within the json statement.
INSERT INTO audit_log (
action,
result,
...
0
votes
2
answers
35
views
How to sort the output of meta / slash commands in `psql`?
For example, how to sort the output below by the values in "Column" in the psql shell itself?
my_db=> \d auth_user
Table "public.auth_user&...
- 121
0
votes
0
answers
35
views
Postgresql 16.2: ERROR: could not find hash function for hash operator 607
I've problem with my database running on postgresql 16.2. My application is running fine, it can query data, but I'm not able to create a backup using pg_dump or inspect database via psql.
Running ...
- 1
0
votes
1
answer
36
views
Lookup tables: check by type id or type name?
TLDR: Should I check an entity's type by lookup_table.id or lookup_table.type_name?
I've learned that lookup tables are almost always preferrable to Enums due to their flexibility. However, I have ...
- 1
0
votes
0
answers
33
views
Postgres SELECT in form suitable for copy-paste to INSERT in psql
In psql, I'd like to SELECT a single record in a format that's suitable for copy-pasting as an INSERT in another unrelated/disconnected database with the same schema.
For example, I want to do this:
...
- 163
1
vote
1
answer
59
views
What does psql do on connect (that could be taking 1 to 10 minutes)?
In some cases psql is taking ~1 to 10 minutes to connect to PostgreSQL, with no queries that it seems to be running while doing so (according to pg_activity that I think queries pg_stat_activity)
I'm ...
- 263
1
vote
0
answers
15
views
Command Line argument in PLPGSQL declaration
I am running this script which takes two command line arguments and it gives me error about syntax being wrong.
ERROR: syntax error at or near ":"
LINE 1: INSERT INTO variables values (:'...
- 11
0
votes
0
answers
48
views
Why load test on postgres database getting worse result while changing default parametes to get better performance?
I want to have a load test on my postgresql database named dvdrental.While using postgres default parameters in postgresql.conf file , my database performance is pretty better than other situations.My ...
- 1
0
votes
1
answer
565
views
Execute gexec in a psql command
I'm trying to pass the -c option of psql a query using \gexec but it's not working as I would like:
psql -d postgres://postgres@localhost:5432/postgres -c "SELECT format( 'ALTER TABLE %I.%I ADD ...
- 384
1
vote
2
answers
185
views
Merging information from multiple rows into one row
I have three tables event, person and event_person_map
event:
id integer PRIMARY KEY
event_name text
event_date date
person:
id integer PRIMARY KEY
full_name text
email_address text
event_person_map:...
- 35
0
votes
1
answer
223
views
Unable to revoke privileges in Postgres even though revoke statements succeed
How do you revoke all privileges for a user in Postgres and then drop the user?
The docs say it should be as simple as:
REVOKE ALL PRIVILEGES ON DATABASE database FROM user;
DROP USER user;
However, ...
- 1,385
1
vote
0
answers
286
views
Why is the data in a varchar field exceeding the size limit?
I have an application that is copying data from one PostgreSQL database to another. For the most part, that application works exactly as expected. However, there is a comments column in one of the ...
- 445
0
votes
0
answers
74
views
Postgresql refuses connect from Windows psql
I've been fiddling with these for 3 hours now and I can't figure out why my PostgreSQL server on a Debian server accepts connections from my Ubuntu server but not from my
Windows 10 or 11 machines.
...
- 161
2
votes
1
answer
160
views
Chain 2 queries with \gexec in psql
I'd like to produce a data dictionary from the catalog tables of a Postgres DB. I wanted to have the name of the table on one line and then the list of columns.
I've tried to do it with \gexec:
\set ...
- 23
0
votes
1
answer
296
views
PostgreSQL 16, pg_dumpall Restore
I'm trying to move a DB from a server running PostgreSQL 9.6 (CentOS) to another running PostgreSQL 16.1 (Ubuntu), I've used pg_dumpall --clean (from the 16.1 install) to output the DB's to a file ...
- 11
-1
votes
1
answer
324
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 ...
- 723
0
votes
1
answer
434
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
13
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
102
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
50
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 (...
- 355
0
votes
1
answer
64
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
17
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
338
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
152
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
42
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
331
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
60
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
26
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
2k
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
394
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
6k
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
302
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
53
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
6
votes
1
answer
1k
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: ...
- 163
1
vote
1
answer
409
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 ...
0
votes
2
answers
568
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 ...
- 334
3
votes
2
answers
995
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 ...
- 45
1
vote
0
answers
113
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
895
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" ...
1
vote
1
answer
671
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
472
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
1k
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
1
vote
0
answers
766
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
556
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 ...
- 411
1
vote
0
answers
864
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
2k
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 ...
- 623