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
2answers
21 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
24 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
35 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
26 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
27 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
48 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
55 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
11 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
17 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
27 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
25 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
37 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
30 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
20 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
26 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
50 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
39 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
18 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
242 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
34 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
33 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
136 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
930 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
19 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
152 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
16 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
23 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
237 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
401 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
739 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 ...
0
votes
1answer
29 views

String search is very slow with OR statement

I have the following tables: product, product_stock, and product_offer I need to optimize the search when looking for a product that is available in product_stock or that is currently on offer. First, ...
0
votes
0answers
112 views

Pgadmin4 : unable to login.Getting error failed to create ssh tunnel

I am using CentOs7, On the command line I login to the unix server via ssh (sshpass -p $PASS ssh linux_user@linux_box_ip ;;) and then give the below command after logging to the linux box. $ psql -h &...
7
votes
1answer
213 views

Reckless behavior of psql \e

psql 13.2 executes the query buffer no matter what when exiting the editor, even after quitting it - in which case the buffer may hold the most recently executed SQL query now. To reproduce: Start ...
0
votes
0answers
106 views

CentOS 8 Unable to connect to PostgreSQL 12 Server

When I attempt to connect to postgres in from my linux command line, I receive the following error: [lloyd@localhost ~]$ sudo -u postgres psql could not change directory to "/home/lloyd": ...
0
votes
0answers
657 views

PostgreSQL 12 problem restoring from pg_basebackup on CentOS8

I have recently created a Postgres backup, and attempted to use this backup, at which point I recieve the following error, when I try to start the server with - start postgresql-12.service, I get : ...
3
votes
2answers
211 views

How to generates VALUES literal expression using a query?

During development and testing VALUES literal expressions are useful because they enable you to store data definition in your SQL query. WITH foobar(foo, bar) AS ( VALUES (1::integer,'a'::text), ...
0
votes
2answers
887 views

How to make psql restore of a database ignore the owner information?

My database is associated with different usernames on the server and on my local machine. Whenever I try to restore it on my local machine from the backup downloaded from the server using the command ...
1
vote
1answer
167 views

How to save database connection information when using `psql`?

I am learning to use psql to work with a postgres database on linux. I can connect using a long connection string, for example by running: psql postgresql://myusername:mypassword@localhost:5433/dbname ...
0
votes
1answer
634 views

In which timezone does psql display "timestamp without timezone"?

What I mean is the following: if I create a timestamp without timezone (timestamp) column and store there some value, internally microseconds are stored, but when issuing select this is converted to a ...
0
votes
0answers
109 views

How to use the \i command in SQL-Shell?

I'm new to PostgreSQL and I wanted to use or run a specific file on the SQL-Shell on windows but I`m getting some errors of what I think is not making sense from my knowledge. I ran the following... \...
0
votes
1answer
12 views

Select columns by file containing primary keys

I have a text file with primary keys. Each line contains one primary key. Now I want to select the rows based on the input file. I can not find the right option for psql to do this. What is the ...
0
votes
1answer
254 views

could not connect to server: can't connect postgres on the server to postico on my local

when I connect postgres on the server to postico on my local I get this error could not connect to server: Operation timed out Is the server running on host "http://159.65.65.203/" (92.242....
0
votes
0answers
625 views

Syntax error near & and : in psql

I am getting below errors in sql log of Native Postgres database : psql.bin:G_co-ac_G_grant_ivspc.sql:1: ERROR: 42601: syntax error at or near "&" 2 LINE 1: grant select on ...
0
votes
0answers
196 views

Import table data using multiple sql file in psql

Local Machine(l1): Windows 10 psql Remote Machine(R1): linux EDB(postgres) On my local I have 1000+ sql script containing insert statement . psql is also installed locally . I need to run this sql ...
0
votes
1answer
113 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 ...
4
votes
2answers
10k 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 (...
1
vote
0answers
144 views

PSQL console encoding

How can I have a working PSQL console using UTF8 encoding under Windows? I have a Windows server and client. The Postgres 12 database contains tables with content in multiple languages (ex: English, ...

1
2 3 4 5 6