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
1
vote
0answers
18 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
0answers
33 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", "...
0
votes
1answer
177 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
1answer
24 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
1answer
120 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
0answers
15 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
1answer
47 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
1answer
33 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
1answer
32 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
1answer
20 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
2answers
55 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
1answer
25 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 ...
1
vote
1answer
48 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 \ -...
2
votes
1answer
46 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
0answers
26 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 ...
-1
votes
1answer
116 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 ...
0
votes
0answers
26 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 ...
0
votes
2answers
1k 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 ...
0
votes
1answer
46 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
1answer
37 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 ...
0
votes
1answer
108 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
0answers
32 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), ...
0
votes
1answer
123 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 ...
0
votes
1answer
19 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 ...
0
votes
0answers
57 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 ...
0
votes
1answer
13 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 ...
0
votes
1answer
18 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....
0
votes
0answers
57 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 -...
0
votes
1answer
63 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
3answers
136 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 ...
0
votes
1answer
65 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 ...
0
votes
0answers
10 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 ...
1
vote
1answer
36 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 ...
0
votes
0answers
42 views

Running long query for Aurora Postgresql in psql

Aurora PostgreSQL 12.4. I am working on a problem of archiving older table partitions data into s3. I am basically running this statement in a .sh script from a detached screen in a EC2 instance: ...
1
vote
1answer
53 views

Database is 300 GB, but I only see small tables?

I wonder why my database is so large. I type: SELECT pg_size_pretty(pg_database_size('defaultdb')); and it says defaultdb is 259 GB. That is huge. So I list my tables in psql with: \dt For each ...
0
votes
2answers
55 views

How to set user-based .psql_history in linux?

If use postgres in linux, it has a postgres user. So when use psql from this user, it will generate a .psql_history file under this user - /home/postgres/.psql_history. When set many users on the ...
-2
votes
1answer
19 views

POSTGRESQL Query and insert based on results [closed]

In this case I'm trying to perform a SELECT based query followed by an insert of new records into the same table based on those results. For example a simple table: Table "samples" Fields: ...
1
vote
1answer
574 views

How to list all sequences by psql with postgres?

I tried \ds didn't get any relations data. But I use SELECT * FROM information_schema.sequences; can get all the data. Why doesn't \ds work?
1
vote
1answer
17 views

How to edit current query without executing it?

I am aware of using \e in psql to edit the query in the buffer. But what if you are currently creating the query for the first time and want to jump to the editor? Or better, you go through history to ...
0
votes
1answer
40 views

return null as \N from psql

I'm trying to migrate some data from postgres to mysql. for that i'm doing a simple sql in postgres and load it to mysql. since format is a bit different the flag -AF $'\t' in psql makes it tab ...
0
votes
0answers
38 views

Cannot use psql anymore

I intend to use psql on Ubuntu. To authenticate, I used many things in the past: Simply write password when asking .pgpass file PGPASSWORD variable This was several weeks ago. I think my attempts ...
0
votes
1answer
215 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 ...
3
votes
1answer
2k views

How to pass environment variables to a sql file from bash shell (PostgreSQL)

I need to be able to pass environment variables, from a bash shell executing a .sql file using psql. The psql command I am running is: su postgres -c "psql -v ON_ERROR_STOP=1 -v dbname=example -v ...
1
vote
1answer
20 views

How to prevent message »Pager won't be used for less than 500 lines.«

I have \pset pager_min_lines 500 in my ~/.psqlrc. Now, each time I start psql, it shows the message »Pager won't be used for less than 500 lines.«. I don't need that message, I specificly asked for ...
0
votes
0answers
197 views

pg_cron connection refused with postgres login

I am trying to run a function periodically with pg_cron but every time it wants to execute it throws connection refused. the cron job is created using the postgres user. pg_hba.conf local all ...
0
votes
1answer
17 views

Measuring timing over many commands in psql

The \timing command finds time needed for each individual update/query. But I have a .sql file with 10000 inserts and I want to see how much time it takes. How can I achieve this?
0
votes
1answer
26 views

How to cat PostgreSQL's Help Menu

In PostgeSQL's psql interactive terminal, you can type \?+<enter> to produce a overview that shows a list of common commands. In Ubuntu Linux, is there a way that I can pipe that content to a ...
2
votes
2answers
452 views

Listing databases gives 'ERROR: invalid byte sequence for encoding “UTF8”: 0xe5 0xc6 0xf5'

I logged into one of our production databases today and tried listing the databases using \l and got: ERROR: invalid byte sequence for encoding “UTF8”: 0xe5 0xc6 0xf5 Since I am not touching any ...
0
votes
1answer
648 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' ...
1
vote
1answer
917 views

Ident authentication failed for user "postgres"

I am using PostgreSQL12 on Cent0S8, and I am able to connect to PostgreSQL using the command: sudo -u postgres psql However, when I attempt to connect using: psql -U postgres -h localhost I recieve ...

1
2 3 4 5
7