Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
19 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&...
toraritte's user avatar
  • 121
0 votes
0 answers
17 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 ...
Dan's user avatar
  • 1
0 votes
1 answer
29 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 ...
Afelium's user avatar
0 votes
0 answers
16 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: ...
Jim Stewart's user avatar
1 vote
1 answer
42 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 ...
Michal Charemza's user avatar
1 vote
0 answers
11 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 (:'...
Reetu Raj Dubey's user avatar
0 votes
0 answers
33 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 ...
mahdi's user avatar
  • 1
0 votes
1 answer
166 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 ...
s.k's user avatar
  • 372
1 vote
2 answers
82 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:...
Lugoom485's user avatar
0 votes
1 answer
49 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, ...
Cerin's user avatar
  • 1,365
1 vote
0 answers
132 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 ...
G_Hosa_Phat's user avatar
0 votes
0 answers
42 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. ...
Ken Ingram's user avatar
2 votes
1 answer
92 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 ...
Jice's user avatar
  • 23
0 votes
1 answer
203 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 ...
AStaUK's user avatar
  • 11
-1 votes
1 answer
254 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
  • 703
0 votes
1 answer
188 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
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 ...
Hassan ALi's user avatar
0 votes
1 answer
73 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
31 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
  • 355
0 votes
1 answer
49 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
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 ...
Ricardo's user avatar
0 votes
1 answer
237 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
138 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
41 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
247 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
52 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
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 ...
Bryce's user avatar
  • 35
0 votes
0 answers
253 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
4k 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
226 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
37 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
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: ...
frostzt's user avatar
  • 163
1 vote
1 answer
306 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
427 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
  • 334
3 votes
2 answers
885 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
73 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
766 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
1 vote
1 answer
555 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
352 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
873 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
1 vote
0 answers
644 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
477 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
  • 401
1 vote
0 answers
723 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
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 ...
collimarco's user avatar
1 vote
0 answers
853 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
161 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
3k 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
8k 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

1
2 3 4 5
8