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
6
votes
1answer
87 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
0answers
20 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
16 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
31 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
100 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
14 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
20 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
184 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
29 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
39 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
1answer
78 views

what does this connection failure message mean?

$ psql -h host db_name psql: could not fork new process for connection: Cannot allocate memory I think I have enough free RAM? Other conditions, when using jdbc: Caused by: java.lang....
0
votes
0answers
17 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
60 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
335 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 ...
0
votes
0answers
104 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
20 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
26 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
93 views

How to set psql command line arguments in a sql script?

The broad question is can you set psql command line arguments within a sql script? Something simliar to SET In particular, Postgresql psql has a -S option, which turns on --single-line. According to ...
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
1k 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
316 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
0answers
29 views

psql - pass arguments to sql script via \i command from _within_ psql shell

I'm aware about psql -v var=value -f script.sql format as well as \set var value from within psql What I need is dynamic variable creation while being within psql session via \i script.sql command I....
0
votes
1answer
37 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
773 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
309 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
41 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
120 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 ...
0
votes
1answer
41 views

PSQL incomplete backup : how to debug

I've been doing backups of my database " screen ". after connecting to PSQL and typing \l+ I'm getting (among other things): Name | Owner | Encoding | Collate | Ctype | ...
1
vote
1answer
205 views

psql does not require a password on macOS with Postgres.app [duplicate]

When opening the psql console, there is no password prompt. This behaviour is the same for all postgres users. I can basically do everything without a password. This seems rather unsafe. How do I ...
0
votes
0answers
494 views

Fail to load pg_hba.conf

I can't connect to server. Using psql -h 127.0.0.1 -U postgres -d mydb and pgAdmin, in both case: psql: could not connect to server: Connection refused Is the server running on host "127.0.0.1" ...
0
votes
0answers
43 views

postgresql - Disallow getting list of databases

How can I disallow getting list of databases on the PostgreSQL server? That can be done (for example) using \l in psql.
1
vote
3answers
66 views

How to create comment on table from query result

I want to use a query result as comment on the table. I have tried in psql: \set list select count(*) from department.master_data COMMENT ON TABLE department.master_data IS :list; But I got this ...
1
vote
1answer
2k views

connecting to Postgres running inside docker container 'posgres' does not exist

I want to connect to Postgres (running in a container) using psql What I've done: docker pull postgres docker run -p5432:5432 --name mydb -e POSTGRES_PASSWORD=pass123 -d postgres Trying to connect ...
0
votes
1answer
50 views

Postgres migration local to remote, issue with terminal command

I'm trying to use the first answer here to transfer a local postgre db to elephantSQL https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server The command given is: ...
1
vote
2answers
2k views

postges: psql csv import fails although file exists

Trying to import a csv file as follows: psql -h 127.0.0.1 -d jira_statistics -U admin -p 5432 -c "COPY bugs FROM '/home/centos/bugs.csv' delimiter '|' csv;" Password for user admin: ERROR: could ...
1
vote
0answers
2k views

Postgresql 10: Can not establish DB connection

I have created a new database and user in Postgresql 10 named 'reportingdashboardproject' and 'repadmin' respectively. I used the following commands to create and grant privileges: postgres=# create ...
1
vote
1answer
2k views

Query to get all schemas in a database in PostgreSQL?

I'm not looking for the command \dn of psql, but instead of a query in SQL language that selects all schemas in a given database.
0
votes
1answer
22 views

Get interactive psql shell after executing queries

I want to execute a set of commands before executing other queries using psql The following is what I want to execute psql << EOF \setenv PAGER 'pspg -s 0' \pset pager always EOF After ...
2
votes
1answer
324 views

Cannot run psql in postgresql 9.5

I am using Postgresql 9.5 on Ubuntu 16.04 LTS. I receive the below error when I type psql: a psql: could not connect to server: No such file or directory Is the server running locally and accepting ...
2
votes
1answer
1k views

Why doesn't \COPY work when called from a PL/pqSQL function? [closed]

I am trying to use the \COPY function to fill the database from text files on the client side. I am using the Postgres interactive terminal (psql) to do this task. I wrote the following script and ...
4
votes
2answers
8k views

GRANT ALL ON ALL TABLES IN SCHEMA does not allow user to see tables

I have a pretty simple script to create a new database and to create a new user to access this database. This is run by the default postgres user. After granting access to the database and schema to ...
2
votes
1answer
1k views

How to shutdown postgres through psql?

The recommended way to shutdown postgres is to send a signal representing the different shutdown modes (fast, smart, immediate) or using init managers (initd, systemd, etc.) or their equivalent on non-...
-1
votes
1answer
525 views

Copy in SQL with index vs without index

Recently I have received a data dump in csv format that I am trying to import in my PSQL database. The dump consists of 8 csv file each of size 7GB. I have found that using the Copy command on one ...
0
votes
1answer
35 views

VIM returns “Error detected while processing” and “Sorry, the command is not available in this version”

When I run VIM with \e inside of psql, I get Error detected while processing /home/ecarroll/.vimrc: line 11: E319: Sorry, the command is not available in this version: call plug#begin('~/.vim/...
1
vote
1answer
503 views

Implications of using PostgreSQL COPY command to stdout and then immeadiately reading it from stdin

When transferring data between two databases, I would like to know if there is any disadvantages in the following approach: psql -h [HOST1] -U postgres -d [DB1] -c [SQL1] | psql -h [HOST2] -d [DB2] -...
0
votes
1answer
50 views

Does psql \dt cache schema?

When I have a session running in psql it does not show newly created tables (from another connection but for the current owner role). If I re-login they show up. Do I need to refresh a psql cache ...
0
votes
1answer
463 views

With PostgreSQL getting ERROR: COPY format “CSV” not recognized

Whever I try to copy from test=# \COPY table FROM 'file.csv' WITH ( FORMAT 'CSV', HEADER true ); ERROR: COPY format "CSV" not recognized What's the reason for this? I know I've used this before.
0
votes
1answer
981 views

How to disable foreign key constraints in Postgres dump file?

I used pg_dump -U postgres -p 65432 --table=tableName --data-only --column-inserts DBName>fileName.sql to dump one particular table's rows. Now I need to insert them to another database but I get ...

1 2 3 4 5