Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [psql]

psql is the primary interactive terminal for PostgreSQL. (DO NOT USE THIS TAG for questions targeting PostgreSQL!)

0
votes
0answers
47 views

Order of tables in pg_dump

I have a dump file, which I was create via pg_dump. The file contains following rows : copy a_schema.a_table (id, outer_id) from stdin; -- e.g. this line in file 10 (1, 1) \. The structure of ...
1
vote
0answers
28 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
40 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
12 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
47 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 ...
1
vote
2answers
25 views

Recommended Way to Store Logical Expressions

Say, I have the following SQL table: | ID | GameId | PlanId | MinAgeLimit | Amount | |:--------------|--------|-------------|-------:| | 1 | Cricket | P1 | 10 | 500 | | 2 | ...
2
votes
1answer
60 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 ...
1
vote
2answers
424 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
261 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
45 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
18 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
65 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
22 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
56 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.
2
votes
0answers
267 views

Postgres throws syntax error at CREATE SCHEMA [closed]

I'm trying to execute an sql file on postgresql with the following command: psql -U postgres -d postgres -a -f file.sql -v ON_ERROR_STOP=1 The following error is shown: CREATE SCHEMA IF NOT EXISTS ...
0
votes
1answer
273 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
32 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
37 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
0answers
39 views

How to see last created entry from every table in psql?

I need query for extract number of lines and date of last created entry from every table. I found numbers of lines in pg_stat_user_tables but I don't know how to find the date of last created entry. ...
0
votes
0answers
30 views

Using psql, echo copy and import a pipe deliminated file

I'm writing a batch script, and batch doesn't like |, heck nor does command line entries. The only way I can input this command is to initiate a psql login directly and then issue it, which ruins the ...
0
votes
1answer
746 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 ...
-4
votes
1answer
252 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 ...
0
votes
0answers
15 views

Postgres (permissions): Can connect to DB, can't see tables [duplicate]

I have one database called db. I've created a copy of this database called db_guest so my colleague can work with copy of the data but doesn't touch production. I've created a new ROLE to not have to ...
1
vote
0answers
401 views

Attempting to copy data from text file into a single column on postgres table - getting relation does not exist [closed]

Within my database I created a schema called "jobs" and within that schema I created a table called "listings." This table contains two columns "company" and "title". I checked that this relation/...
3
votes
1answer
111 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 ...
3
votes
1answer
353 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 ...
0
votes
1answer
160 views

Psql refusing connections after restart

I began receiving these errors consistently after running a script that collects and stores data in psql: Could not connect because of FATAL: remaining connection slots are reserved for non-...
1
vote
0answers
56 views

PostgreSQL is not working after installing Postgres-XC

I am using PostgreSQL 9.3 on Ubuntu 17. This set up was working fine, but it stopped working after installing Postgres-XC. I tried uninstalling Postgres-XC and reverting back the data directory to ...
1
vote
1answer
52 views

How to control psql displaying rows with (END)?

After my computer upgraded from Mac Sierra to High Sierra, my psql started exhibiting an annoying behaviour. Sometimes when it lists rows, it prints (END) instead of the prompt, and I have to press ...
2
votes
1answer
3k views

pg_restore ERROR: “Relation does not exist” and creating new database

I have made a backup of my specific tables that I want to restore into a new database using: call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public....
1
vote
1answer
117 views

How do I change the default external text editor in psql?

I want to change the external editor in psql. But the command select-editor does not work. How can I change it? How do I run the command?
1
vote
1answer
111 views

How do I synchronize parallel imports with COPY? [closed]

I have ~20 tables that get built at the same time but using different psql processes (via bash script) at the first stage with something like: create table foo_1 as (select ....); -- via psql process ...
0
votes
0answers
221 views

Recursive query to find shortest path in graph

have an assignment to find the shortest possible path between two people in a table. The table is Company(company_name, job, name), all of them not null, but none of them unique (primary key is the ...
3
votes
1answer
2k views

psql, I try to connect to a database and I get “psql: FATAL: Peer authentication failed for user ”, why?

I'm using psql (9.2.23) on my Dell Latitude 3540 laptop running Linux CentOS 7. I want to do a simple thing: create a database username and log in as that username. My operating system username is "...
2
votes
1answer
50 views

How do I stop psql from displaying executed SQL?

When I execute SQL, I see the query echoed back at me. =# \d LOG: statement: SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' ...
0
votes
1answer
362 views

selecting and Inserting data from two different servers

I have two server mysql and psql. I am fetching data from mysql server database table and inserting it into my psql server. The way I am doing is by selecting all the column from mysql server and ...
1
vote
1answer
691 views

Unique index on multiple columns vs single column index

I have a table in the following structure. Now from a data integrity standpoint I want to add a joint uniqueness index on product_id and item_id. Now there can be around 100K product_id and 50k ...
1
vote
1answer
34 views

Joining temporal and spatial data

I have two tables in psql that I am trying to join based on certain attributes - they are very large - 17 M rows and 2.7 M rows respectively mydb=> SELECT reltuples::bigint AS estimate FROM ...
1
vote
0answers
410 views

psql: Connection refused. pqAdmin: the database system is starting up

Windows Server 2012 Postgres 9.6 I made a replication with the master server. On the slave server service postgres is running, but in log files slave's server "FATAL: the database system is starting ...
0
votes
1answer
71 views

psql's \set command unable to save json file's content in a variable

I am trying to below set command inside bash: #!/bin/bash psql -X -h localhost -U <dbuser> -d <dbname> <<EOF CREATE TABLE cardtable(id SERIAL PRIMARY KEY, charcolumn VARCHAR(20) ...
0
votes
1answer
22 views

JSONB: Finding an element with a null attribute in array of objects

With the following table in psql 10: CREATE TABLE logs(id serial, data JSONB); INSERT INTO logs VALUES (1, '[{"name":"test1","date":"2018-01-09"},{"name":"test2","date":"2018-01-10"}]'), (2, '[{"...
0
votes
1answer
259 views

Insert PBKDF2 hash into PostgreSQL container

I am using the PostgreSQL docker image with the following Dockerfile: FROM postgres:10.1 COPY init-db.sh /docker-entrypoint-initdb.d/init-db.sh and an init-db.sh script with the following content: ...
1
vote
1answer
1k views

COPY FROM STDIN from client app

I have a client appconsole. I want to execute a SQL command "copy from stdin" and send data from client STDIN to Postgres server to fill a table. Is it possible without psqlclient and just via ...
0
votes
1answer
25 views

Get increasing records over time

I have a table of car ids - id and their battery level - battery, which is collected at ten-minute intervals. My goal is the following output: total count of unique car ids whose batteries ...
2
votes
3answers
3k views

Using COPY in psql script does not write to file

I have a script with multiple queries I am triying to write to a csv file: copy ( select /* very long query */ ) to '/tmp/report.csv' with csv header; And I run the script like this psql -d ...
0
votes
1answer
916 views

Postgres 10 and partitioning by week [closed]

i'm trying to understand how partitioning works, and main manual doesn't help me a lot. Also, PostgreSQL 10 supports partitioning natively... but what does it mean? is it possible to declare table:...
3
votes
3answers
6k views

Homebrew installed PostgreSQL could not connect, database files are incompatible with server

Here's the error message I'm getting today when I attempt to log in to my localhost PosgreSQL databases using psql: psql: could not connect to server: No such file or directory Is the server ...
1
vote
2answers
45 views

Can I log psql time and statements to a text file (without the data)?

I am currently comparing performance of PostgreSQL with several other SQL systems. I am aware of the \timing option to turn on timing queries. However, I would very much like to automate the process ...
0
votes
2answers
29 views

Numeric variables in psql

I am running a number of queries in psql using for a time interval where a part of the query goes like where g2.datetime between g1.datetime+'19 minutes' and g1.datetime+'21 minutes' i.e. I want an ...
2
votes
1answer
6k views

Copy from file: ERROR: invalid input syntax for integer: “\N”

My table column defined intcol integer; no NOT NULL constraint. In the load file most values are integers, but a few are null values labeled with the '\N' default as suggested by the copy manual. Can ...