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
3
votes
1answer
31 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
10 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
10 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
63 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
25 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
27 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
40 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
15 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
43 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
186 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
51 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
19 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
23 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
58 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
510 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
201 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
23 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
29 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
412 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
167 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
24 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
74 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
31 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
125 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
356 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
42 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
62 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 ...
0
votes
1answer
1k 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
36 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
1k 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 ...
0
votes
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
18 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
242 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
900 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
6k 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
923 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
419 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
30 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
421 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
47 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
381 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
826 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 ...
0
votes
0answers
56 views

Duration of consecutive timestamps by group in sql

I am new to psql window functions and would like to perform a query on test_table in order to find out every instance of inactive outages per mobile_id, country, and brand and most importantly the ...
0
votes
1answer
49 views

DB alert on values change

How to make notification when some conditions written by user are true. I have one table DATA which has 3 rows. name value ---- ----- a1 10 a2 20 a3 30 This table changes some values ...
0
votes
1answer
3k views

out of shared memory error when doing pg_dump

I have a postgres database with 3 schemas and around 1200 tables. When I try to take a schema only pg_dump of it using the command, pg_dump -h localhost -p 5432 -U postgres -d my_test_db -s, I ...
-2
votes
1answer
334 views

PSQL copy-paste tab-completion and inserting errant parenthesis

Note, I use tabs. Frequently, when I write answers on this site, I write them in vim with the assistance of vim-surround, especially when I'm migrating a fixed-width table/grid to a DDL. Here is an ...
3
votes
1answer
343 views

PSQL trying to connect to wrong database

I have noticed recently a weird (at least to me) behaviour of psql when trying to run psql -l. The essential steps to reproduce it are: Create a sample DB (I did it with docker and created a simple ...
4
votes
1answer
1k views

How do I use the input from a \prompt in a conditional (\if)?

Let's say I get value from a prompt? \prompt 'Write [Yes] or [No]\n' store Which does this, Write [Yes] or [No] Yes I can verify I have that works, \echo :store Yes But, I can't get it to work ...