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
0answers
33 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
9 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
37 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
46 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
13 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
28 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 ...
1
vote
2answers
412 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
38 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
42 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
22 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
16 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
9 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
22 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
24 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
14 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
14 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
19 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
28 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
27 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
28 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 ...
0
votes
0answers
141 views

Unable to connect postgresql server from pgAdmin4, showing error - 'ServerManager' object has no attribute 'user_info'

I am trying to connect a remote postgresql database server(RHEL7.5) through pgAdmin4 from my windows10 pc, but it is showing an error 'ServerManager' object has no attribute 'user_info'. I have ...
0
votes
2answers
28 views

declare variable in bash script set var=“psql -c ”select * from someting" and execute var

I am searching for a way to integrate psql statements in bash scripts for devops reasons. The simplest way I would do that is: CMD='psql -c "DROP DATABASE restore_database;"' then use a bash ...
0
votes
2answers
62 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 ...
0
votes
0answers
6 views

how do I generate a series starting from the last id in the table, generate_series(last id, 10000)

how do I generate a series starting from the last id in the table, generate_series(last id, 10000)? Say the last id is 101 in the table I would like to generate ids 102-10000 in the table. Currently I ...
6
votes
1answer
141 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
105 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
42 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
244 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
58 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
15 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
36 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 ...
0
votes
2answers
495 views

Importing plain (raw) files into a postgres table (bytea and large objects)

What is a good way to insert/copy plain files into a postgres table, preferrably using the psql command-line? In my case the files are a bunch of eMails from Maildir archives, so I tried to use COPY: ...
0
votes
0answers
40 views

advice : restore a table with PostgreSQL in a database

(dba beginner) As an exercise, I test different methods to restore all or part of a database. I have 2 dumps of a table: the first one in sql format, the second one in custom format. So I use psql in ...
-1
votes
1answer
123 views

COPY … FROM STDIN command failing on null values exported as “\N”

I initiated a table dump and wherever the NULL value comes, the dump file has \N for that. During the restore I'm getting this error. invalid command \N The database encoding is UTF8. I took ...
0
votes
0answers
20 views

Postgresql - should character varying be limited if its going to be unique indexed?

For a column storing email, the length intended to be stored is never going to be over 254 characters. According to \d I currently have character varying (withOUT parentheses denoting a limit). Psql ...
0
votes
1answer
81 views

Postgresql equivalent of --i-am-a-dummy

MySQL's command line tool has a flag --i-am-a-dummy that sets execution limits: With --safe-updates, you can modify rows only by specifying the key values that identify them, or a LIMIT clause, or ...
0
votes
2answers
445 views

Exporting table data into csv format from server to local [closed]

I have connected to remote server and trying to export a table data to local using the command: copy finance.coa to 'localusername@172.16.1.44:/home/kedarisettyakhiles/akki.csv' DELIMITER ',' CSV ...
2
votes
0answers
206 views

Use or include external schema in search_path on Redshift

Narrative I have a sql script that creates a bunch of tables in a temporary schema name in Redshift. I don't want to repeat the schema name a bunch of times, so I would like to do something like the ...
0
votes
0answers
23 views

How to achieve this Nested Complex postgresql schema CRUD?

I'm trying to achieve this complex schema. { relation1_id : Number, ... ... item_caption: [ { caption: '', subcaption: '', products: [ { ...
0
votes
2answers
29 views

Postgresql - roles without linux accounts?

Basic question: what I'm reading describes how to create linux users in parallel with postgresql roles. However, I don't see the value in creating any of these users in linux - I just want to create ...
0
votes
0answers
20 views

Psql, cte twice for same thing return bad value

What I need. Same values for category tree as in first case, first case is for name (cte), second is for id (cte2). What I tried. with recursive cte as( select category_id,category_parent,...
0
votes
2answers
3k views

In PostgreSQL URL I can't use a password containing special characters

I have a postgresql database in Azure The passwd string has special characters like these: )mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU psql --dbname=postgresql://db_user@mydemoserver:)mvd99/iyH_=ag=...
2
votes
0answers
446 views

Postgres Service Not start using via HomeBrew

I have to install homebrew in my macOS then after I tried to install Postgres on my mac using the homebrew. but when I checked my Postgres status is it not running. /usr/local/Cellar/postgresql/11.4/...
0
votes
1answer
47 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 ...
3
votes
1answer
1k views

How to pass variable to PL/pgSQL code from the command line?

I am running a psql script from the command line with variables something like: psql ...... -v now_utc=$NOW_UTC Then I want to use this in my script like: $$ DECLARE _now_date timestamp := :...
0
votes
1answer
453 views

Moving a large table from Localhost to remote server

Postgres 11, Windows 10, pgAdmin 4 I have a large post-gis enabled table (29million rows, 20GB + a 5GB spatial index) stored on my local machine (localhost). I want to move this table to an AWS ...
0
votes
1answer
61 views

How can I import an old pgdump into an updated schema?

I'm DB newb doing a software upgrade, and my predecessor's original migration plan may not have been as well thought out as I thought... The Plan was to use our backup, created via pg_dump -U derp -...
0
votes
1answer
172 views

System-wide startup file for Postgres 10 on Ubuntu 18.04

When I log into postgres under my personal shell account, my .psqlrc file runs a series of meta commands to set up what is for me an optimum working environment. I'd like to set that same environment ...

1
2 3 4 5 6