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
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 ...
user avatar
  • 173
0 votes
1 answer
19 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?
user avatar
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=...
user avatar
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? ...
user avatar
0 votes
0 answers
108 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 /...
user avatar
  • 1
2 votes
1 answer
143 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.
user avatar
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 ...
user avatar
  • 111
0 votes
0 answers
265 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 ...
user avatar
0 votes
0 answers
35 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 ...
user avatar
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.
user avatar
  • 1
2 votes
0 answers
43 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 (...
user avatar
  • 173
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: ...
user avatar
  • 139
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 ...
user avatar
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 ...
user avatar
0 votes
1 answer
136 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 * ...
user avatar
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'; ...
user avatar
  • 126
-1 votes
1 answer
84 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 ...
user avatar
  • 126
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 --...
user avatar
  • 115
1 vote
0 answers
80 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", "...
user avatar
2 votes
1 answer
979 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 ...
user avatar
0 votes
1 answer
214 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 '...
user avatar
  • 605
2 votes
1 answer
590 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 (...
user avatar
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, ...
user avatar
  • 173
0 votes
1 answer
410 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 ...
user avatar
  • 209
0 votes
1 answer
91 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 ...
user avatar
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/...
user avatar
  • 173
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 ...
user avatar
  • 221
0 votes
2 answers
92 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 ...
user avatar
  • 126
-1 votes
1 answer
80 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 ...
user avatar
  • 113
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 \ -...
user avatar
  • 11
2 votes
1 answer
185 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....
user avatar
  • 31
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 ...
user avatar
  • 11
-1 votes
1 answer
399 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 ...
user avatar
  • 11
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 ...
user avatar
  • 1
1 vote
2 answers
4k 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 ...
user avatar
  • 143
0 votes
1 answer
103 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 ...
user avatar
0 votes
1 answer
40 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 ...
user avatar
0 votes
1 answer
1k 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=&...
user avatar
0 votes
0 answers
40 views

Change postgresql user password

I have PostgreSQL database installed on my server. The problem is that I do not have psql access to it because I do not remember the password. Even though my pg_hba.conf looks like (see image below), ...
user avatar
0 votes
1 answer
300 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 ...
user avatar
  • 73
0 votes
1 answer
22 views

Struggling to create an index for my PSQL Query

I'm running a query on a dataset that's got 30 million rows in and will only continue to grow, the table is customer_actions (Table size is 2416 MB) create table customer_actions ( id ...
user avatar
  • 3
0 votes
0 answers
104 views

psql connects to AWS RDS instance for co-worker, but not for me

We're on an EC2 VM (the same EC2 VM on the same subnet as the RDS instance) running vanilla PostgreSQL 12.5, and using psql to connect to an AWS RDS PostgreSQL 12.5 instance. My co-worker and I run ...
user avatar
  • 475
0 votes
1 answer
20 views

psql on WSL: Not listing all databases for owner postgres

I'm trying to setup Postgres on WSL (bash) to be able to use pg_dump (as an absolute beginner). I've followed the steps from here twice, once after purging the entire installation. At step 3: I used ...
user avatar
  • 1
0 votes
1 answer
24 views

Error with non-standard identifier in \copy command [closed]

Would like to be able to add characters like '-' in the schema name when running COPY command in PostgreSQL. Any way to get around this? psql -d postgres -c "\COPY (SELECT * FROM test-schema....
user avatar
  • 103
0 votes
0 answers
161 views

Dump all data to json files

I'm working on a script that has to dump app data from a schema to json files. Here is what I have thus far: export SCHEMA="citydb" export DB="kv_db" export PGPASSWORD=root psql -...
user avatar
  • 109
0 votes
1 answer
179 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 ...
user avatar
  • 429
1 vote
3 answers
1k views

How can I restore pg_dump file on vm

I created a dump with pg_dump using, sudo -u postgres pg_dump dbname > dbexport.psql I tried to restore that dump with, sudo -u postgres pg_restore -d dbname < dbexport.psql I got an error in ...
user avatar
  • 111
0 votes
1 answer
544 views

Why can't I use command \i on psql? It says no such file or directory [closed]

I am trying to use command \i 'C:\Users\Public\person.sql'; on windows on psql. And it says C:UsersPublicperson.sql: No such file or directory Although file does exist. I tried to relocate file on ...
user avatar
0 votes
0 answers
14 views

Connect to postgres11 using psql in a container

I'm looking for a way to connect to postgresql11 (azure PAAS instance) using psql tools installed on docker container. I would like to psql container installed on the same node where we have an ...
user avatar
  • 73
1 vote
1 answer
143 views

pg_dump while inserting data

I need to dump some tables of my postgres database and I am scared of getting inconsistent data.The database is verly large and the dump lasts some minutes. On my research I found a command to dump ...
user avatar
  • 13

1
2 3 4 5
7