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
votes
0answers
5 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 ...
-2
votes
0answers
8 views

psql its not responding on wrighting thre data base [closed]

psql (13.2) WARNING: Console code page (437) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details.
0
votes
1answer
13 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 ...
2
votes
1answer
33 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
13 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
33 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
14 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
19 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
72 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
45 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
232 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
23 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
25 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 &...
4
votes
1answer
114 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
50 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
304 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
155 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
132 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
37 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
204 views

in which timezone Psql displays timestamp without timezone (PostgreSQL)?

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
67 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
11 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
143 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
344 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
65 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
67 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
6k 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
83 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, ...
0
votes
1answer
46 views

Relational-JSON overlapping with association [closed]

I have a composed class called sensor that is linked to a main class Node such that: class node is linked with another node to make a communication that produce an event class sensor can as well ...
0
votes
1answer
119 views

psql command, how to enforce certificate check?

I have created a cockroachdb v20.1.0 cluster based on this: https://www.cockroachlabs.com/docs/stable/deploy-cockroachdb-on-premises.html I have also created a test user like this: CREATE USER test ...
0
votes
0answers
31 views

postgres install from homebrew not creating `postgres` user

So, I'm trying to fix my local postgreSQL install to save log4j2 entries. Because the former has been bungled, I decided to reinstall. I ran the following to uninstall: brew uninstall postgresql rm -...
0
votes
0answers
123 views

How do I temporarily set VERBOSITY in psql (where does psql store its variables)?

I'd like to be able to temporarily set VERBOSITY in a .sql script, execute a DO block, and then restore the VERBOSITY setting. However, this does not work: cat test-set.sql \echo VERBOSITY = :...
0
votes
1answer
31 views

possible to specify field enclosure in psql command line?

In psql if I have a field separator of ",", but there are commas in some fields, is it possible to specify a field enclosure character? I have looked through the documentation but I can't find it. ...
0
votes
0answers
25 views

Postgresql connects to database but with another schema owner

I have a database called sommos_development. Working normally, when I run the command \dn on the database, this is the result The Problem The thing is that sometimes, when I reboot my computer and I ...
0
votes
1answer
18 views

How to get latest timestamped result from time series in a join table [duplicate]

I have 3 tables node ---- id ip metric --- id key (str) node_id (FK on node) his_metric ---- id metric_id (FK on metric) value (float) datetime (datetime) Question How to write a query that will ...
0
votes
1answer
18 views

Column containing the word from another column

I am trying to select records in a postgresql where the service column contains usernames. Something like this but not 1 to 1: select service, cash from services where service not in (select name ...
0
votes
1answer
41 views

Query to update the JSON that does not contain a specific key

I want to query a table that has JSON data. The data of the column detail in the table details are as follows. id | 1 detail | { myCompany: [{ email: 'company@company.com', inProfit: true }], ...
0
votes
1answer
65 views

View operation timestamps for long running operations in PSQL console

When I do long running queries, I tend to use tmux and a psql console. It will not show me any output of when some operation was started or completed. How can I get the psql console to show some ...
2
votes
1answer
102 views

postgresql set variable not working with command option

Has anyone a solution for this issue (tested on postgres v12) I would like to use the psql variable option in conjunction with command like this: psql -v env=test -c "SELECT :'env';" but this ...
0
votes
1answer
336 views

Shell script - psql run multiple statements from a variable one by one in one transaction

Im preparing a shell script to generate analyze command and all the commands will go to a variable. echo $analyze_query analyze tbl1; analyze tbl2; analyze tbl3; Now I want to run one by one, and ...
1
vote
2answers
388 views

psql ^C takes long time to cancel query [closed]

I am using psql to query a large database. and (understandably) many queries that I type in take a long time. I typically want to cancel these queries and so I enter ^C, and psql responds with the ...
6
votes
1answer
295 views

Cannot induce exit code 3 from psql

The docs page states (emphasis mine) Exit Status psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own occurs (e.g. out of memory, file not found), 2 if the ...
0
votes
2answers
314 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
1answer
17 views

Fail to copy all PostgreSQL databases between servers although there is no error message

I want to copy all databases from PostgreSQL 9.3 on Server-1 (Windows 7) to an empty PostgreSQL 12.0 on Server-2 (Windows Server 2012 R2). The steps are as follows: On Server-1: C:\Program Files\...
0
votes
1answer
21 views

Constraints between tables on a variable

im aiming to add a constraint on the number of bicycles allowed to be stored in each terminal - the number differing between terminals. These are my original tables create table terminal( ...
0
votes
2answers
58 views

PostgreSQL dump distinct column without memory consumption

I'm trying to dump csv of distinct varchar(43) column to file, table has around billion rows, select distinct command either runs out of memory and command is aborted or server just closes all ...
0
votes
1answer
392 views

How to setup an ssh connection to a postgres 12 db using dbeaver

I am currently in the process of trying to connect to my local postgres 12 db. In the past (9.3/9.6) you were able to edit the .pg_hba file to modify which connections were allowed but that file doesn'...
4
votes
2answers
63 views

How can I rerun a statement from server logs without manual editing?

I have some statements in my logs like this: SELECT to_char("version_downloads"."date", $1), SUM(version_downloads.downloads) FROM "version_downloads" WHERE "version_downloads"."version_id" IN ($2, $...
1
vote
1answer
17 views

set -x equivalent for the Postgres cli/console/prompt/repl

Is there a "set -x" for the Postgres prompt/repl ? In bash when you want to execute a set of commands, and come back to check it after lunch you can do set -x command1 command2 command3 and bash ...
0
votes
0answers
73 views

Single-tenant to multi-tenant: restore old database to schema in new database

I have a Django app which uses Postgres as a database w/ the django-tenants package providing multitenant functionality. This is a migration from what was originally a single tenant database with just ...

1
2 3 4 5 6