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
2 votes
0 answers
16 views

Update all instances of key inside nested JSON array

I'm trying to update a complex JSON document stored in a table in CockroachDB. The CockroachDB query language is mostly Postgres compatible except for the lack of procedural code, so a Postgres answer ...
15 votes
6 answers
17k views

How to conditionally stop a psql script (based on a variable value)?

Let's consider the following example (from the start of a psql script): \c :db_to_run_on TRUNCATE the_most_important_table; -- tried to avoid similarities to anything that exists out there Now if ...
94 votes
11 answers
280k views

How to run psql on Mac OS X?

I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available. I get this message: ...
0 votes
0 answers
11 views

Postgres 12 Move and Delete Data using (CTEs) WITH statements causing parsing issues - best approach?

Am not sure if this is the best method of moving/deleting data. I have 2 tables play and game, information from the play table is referenced from the game table. I am trying to find all games that are ...
13 votes
2 answers
44k views

Postgresql: set default psql client encoding

When I connect to a Postgresql DB using psql, I often get these messages: => SELECT * FROM question_view ; ERROR: character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in ...
0 votes
1 answer
20 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
1 answer
1k views

postgresql copy comand with date in filename

I have a query and want the output in a .csv file with today's date and time in the name (I am using PostgreSQL v12). COPY (SELECT * FROM mytable) TO 'C:\Users\me\mylocation\filename_date_time.csv' ...
2 votes
1 answer
4k views

psql: how to turn off context messages at raise notice

If I use raise notice, I get context messages at each execution. Can I turn them off and just get the NOTICE? NOTICE: d = [2,1] ,tn: (1,2,"{NULL,NULL}","{{-1,4},{-1,-1}}",1) CONTEXT: SQL ...
2 votes
3 answers
5k views

Access column returned by psql from bash

I want to use value returned by psql in bash. This is the script: psql "connection parameters" -c "SELECT pg_database_size('dbname');" The output is like this: pg_database_size -...
0 votes
0 answers
46 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
426 views

PostgreSQL: managing the postmaster PID

I am running PostgreSQL on my localhost on MacOS. Every once in a while (~ every 5 times I reboot the computer), I face the following error when I try to connect to my local database instance: could ...
0 votes
1 answer
170 views

Unable to import shapefile into PostGisDatabase - problem of connection

I have been trying to import/load a shapefile into a PostGis database without any success despite many trials and searching an answer in the documentation. Configuration I'm running Windows 64 bits ...
0 votes
1 answer
19 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? ...
4 votes
3 answers
15k views

What does "psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)" actually mean?

I have a question regarding following output: [nikola@192 ~] $ sudo su - postgres [postgres@192 ~] $ psql dvdrental postgres psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (...
0 votes
1 answer
169 views

Query returns results on pgAdmin running on remote client but not directly through psql on db server

When I query my database using a role in pgAdmin on a remote client, I get the results of the query. However if I run the same query directly on the db server with the same role , I don't see the ...
0 votes
0 answers
112 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 /...
2 votes
1 answer
144 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
1 answer
110 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 \ -...
0 votes
1 answer
180 views

Postgresql: avoid referencing schema in trigger function

I have written function that I registered in a trigger that ensure referential consistency in non-normalized data (like we store agency_id + branch_id and I ensure that a referenced branch is inside a ...
1 vote
0 answers
18 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 ...
0 votes
0 answers
272 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
36 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
2 answers
655 views

barman & Dockerized PostgreSQL: check timeout: FAILED (barman check command timed out)

I am trying to configure barman to backup a PostgreSQL 11 database running inside a Docker container. barman v2.10 is installed directly in the Ubuntu 18.04.3 host machine. barman check pgsql gives ...
0 votes
1 answer
23 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.
2 votes
0 answers
44 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 (...
1 vote
1 answer
126 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: ...
0 votes
1 answer
138 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 * ...
0 votes
0 answers
19 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
18 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
0 answers
10 views

How can show all records in psql shell's window instead of part? [duplicate]

Enter into my xbrl database in postgresql: psql -d xbrl -U showkey Query records number with condition adsh='0000320193-21-000105': xbrl=# select count(*)from pre where adsh='0000320193-21-000105'; ...
-1 votes
1 answer
85 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 ...
32 votes
4 answers
38k views

why pg_restore ignores --create ? Error: failed: FATAL: database "new_db" does not exist

I am trying to run following command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -Fc -U foo some_db" | pg_restore --create --dbname=new_db I get: failed: FATAL: database "new_db" does not exist
1 vote
0 answers
35 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 --...
1 vote
0 answers
81 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
989 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 ...
256 votes
10 answers
734k views

List the database privileges using psql

I'm in the middle of a database server migration and I can't figure (after googling and searching here) how can I list the database privileges (or all the privileges across the server) on PostgreSQL ...
0 votes
1 answer
216 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 '...
2 votes
1 answer
596 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
18 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, ...
0 votes
1 answer
414 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 ...
0 votes
1 answer
93 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
41 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/...
2 votes
1 answer
56 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 ...
0 votes
2 answers
93 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 ...
-1 votes
1 answer
83 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 ...
4 votes
2 answers
17k views

PSQL not executing SQL command - Powershell

I have a simple script that should execute a select statement and output it to a CSV. My problem is that the script connects to the database and hangs waiting for my sql instructions (which are on the ...
2 votes
1 answer
186 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....
0 votes
0 answers
53 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 ...
0 votes
0 answers
31 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 votes
1 answer
403 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 ...

1
2 3 4 5
7