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
1 answer
59 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 ...
  • 157
0 votes
0 answers
7 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
268 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
38 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
80 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
26 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
16 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
35 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 ...
  • 535
0 votes
0 answers
50 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
66 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 ...
  • 327
0 votes
1 answer
152 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=$...
  • 447
0 votes
1 answer
70 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
428 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 ...
  • 535
0 votes
1 answer
95 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
351 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
25 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
523 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
2 votes
1 answer
396 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.
1 vote
0 answers
75 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 ...
  • 111
0 votes
0 answers
1k 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
0 answers
208 views

Pg Cron and cron expression

I have a trigger that is executing an insert by pgcron, but I have a problem, I am not able to make the insert work with the following cron expression: */30 8-22 * * * I tested it with this ...
0 votes
1 answer
537 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
112 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 (...
  • 327
1 vote
1 answer
411 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
31 views

Errors messages when pusing data with psql on existing database

So I have a remote postgres database hosted on aws's rds, for a django application. This is still in dev (though obviously would like to start trying to do things right already). I need to upload ...
0 votes
0 answers
25 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
631 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
303 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 ...
  • 188
1 vote
0 answers
148 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
143 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
978 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 '...
  • 535
3 votes
1 answer
1k 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, ...
  • 327
0 votes
1 answer
965 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 ...
  • 209
0 votes
1 answer
204 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 ...
0 votes
1 answer
107 views

Postgres Upgrading Errors due to table not existing

I am trying to do an upgrade from Postgres 9.6 to 12, I have done this in the past but in this instance I am having difficulty. When running the pg_upgrade command followed by the parameters: /usr/...
  • 327
2 votes
1 answer
132 views

psql: change commandline editing mode?

psql, the PostgreSQL CLI, uses readline for it's user interface. Readline allows both emacs- and vi editing mode, and psql uses emacs mode by default. I, on the other hand, use vi for everything; is ...
  • 221
0 votes
2 answers
312 views

How can login postgres with new created Superuser?

I have created a new superuser with sudo su - postgres createuser --interactive --pwprompt The name is showkey and password is xxxxxx. postgres=# \du+ List ...
  • 188
-1 votes
1 answer
181 views

PostgreSQL: How to list all privileges of the built-in PUBLIC role?

The documentation says: Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC. How ...
  • 113
1 vote
1 answer
542 views

How to return values from psql to bash with custom session settings

I need to set the datestyle to "dd/mm/yyyy" before calling a procedure and getting it's return value back to bash. I tried the following: read return <<< $(psql \ -...
  • 111
2 votes
1 answer
375 views

Why can't I setup replication with pglogical? It says I need a primary key even though I have unique constraints

I'm trying to enable replication with pglogical on a database running Postgres 9.6.22, however I can't add one of my tables even though it has a unique constraint: db=> SELECT pglogical....
  • 31
0 votes
0 answers
74 views

How to get the functionality of sqlplus' "BREAK ON" in Postgres?

Is there a way to separate a query that has a group by attribute in Postgres in a fashion similar to sqlplus? Assuming I have branches of my company in every country, is there a way to have a separate ...
  • 111
-1 votes
1 answer
704 views

Sql plus equivalent in psql

I'm tasked of migrating some code from sqlplus to psql In oracle, I have a bash script that excutes a stored procedure and based on the result it calls a sqlplus that spools multiple files to dump ...
  • 111
0 votes
0 answers
42 views

postgresql database didn't restored in ubuntu and got blank

i try to restore databse form my pc(windows) that i send it from pscp i use this script : sudo -u postgres pg_restore -U postgres -f /home/abura/first_backup.sql and got blank, so i randomly click to ...
  • 1
2 votes
2 answers
8k views

\dt says "Did not find any relations."

I've been using Postgres 14 successfully, but now I'm stumped. I have a database and schema abc which has tables. \c abc \dt -- shows tables I made a new database/schema xyz, but I cannot see ...
  • 163
0 votes
1 answer
208 views

Made a plain-text backup (SQL script file format), how do I restore just the data (assuming the schema is valid)?

I use to backup my DB with pg_dump -cF p my_db > snapshot.sql. How can I restore only the data from one of those files (assuming the schema is valid)? Do I have to issue just and all the COPY ...
0 votes
1 answer
123 views

Postgres outputting an array in PSQL

I have the following function. It is something I am building and want to show the contents of stuff_list so I can prove it is working correctly. I can't however work out what output type I should be ...
1 vote
1 answer
4k views

How to pass parameters to sql script via psql \i command

The psql \i command is able to execute a given SQL script but I need a way to pass parameters to the script. Example: say you have this simple script select * from :table LIMIT 1; I've tried my_db=&...
0 votes
1 answer
502 views

psql (PostgreSQL client) tool install on RHEL

I would like to install just the PostgreSQL client tool (psql) on RHEL on a container to connect to an Azure database for PostgreSQL server. Can you please point me step by step instructions on how to ...
  • 83

1
2 3 4 5
7