Questions tagged [psql]

Primary interactive shell for PostgreSQL. NOT a synonym for PostgreSQL.

0
votes
1answer
17 views

psql cannot connect without -h flag

I am running psql from a Debian 8.7 terminal but cannot figure out why it needs the -h flag to execute. For example, if i run psql -U postgres on the terminal, I get the following error: psql: could ...
0
votes
2answers
52 views

With psql, how to make output of '\d {tableName}' remain in console?

On Ubuntu, running psql from command line, when I type in '\d {tableName}' it takes me to some viewer mode similar to vi where I have to then hit 'q' to go back. Then I lose the list of columns that I ...
1
vote
1answer
21 views

Changes doing in psql shell doesn't show up in Datagrip session

I have user postgres with database postgres. I connected to it from Datagrip, but what I do in datagrip doesn't show when I do psql commands, same holds vice-versa. I do sudo -su postgres psql and \c ...
0
votes
1answer
23 views

psql copy from read data from script

I am trying to export / import a set of tables from a PostgreSQL database. I am using psql's copy from with stdin from a script. I have read that data (formerly produced using copy to with stdout) ...
0
votes
0answers
7 views

How can a single column can be shown as a row with some relationship with other column values

I want to create a request that shows some specific values in a row showed as a column. - The original table: **ID** **Name** **FRUIT** **Description** **Place**<br/> 1 ...
-3
votes
2answers
38 views

how do i automate creating database in postgresql

I'm new to this, can anyone please help me writing a script to automate these lines using shell scripts? postgres=# CREATE DATABASE testdb;<br> postgres-#
0
votes
0answers
38 views

How to set the title in PostgreSQL with dynamic value

I am migrating from Oracle to PostgreSQL,I want to set the title in PostgreSQL. I tried to set the title in POstgresql using \pset title or \C . My oracle code is similar to : TTITLE LEFT 'tag1' ...
0
votes
0answers
12 views

How to format title given to the result of a select query in postgresql?

Is there a way to format the title given to the result of a select query in psql? Can we align table title to center, left or right? Ex: In Oracle SQL*Plus TTITLE LEFT 'Left Aligned Text' CENTER '...
0
votes
0answers
22 views

How to view a non-described column in postgreSQL table so I can add non-null values to it

I am trying to apply changes I made to a Django model (listed below): class Meme(models.Model): # user author = models.ForeignKey(User, on_delete=models.CASCADE, default=0) # img file ...
0
votes
0answers
17 views

Procedure to import data from a .txt file into the database

I have a .txt file with data for import, however this data is defined by substring and not by a delimiter, I am currently doing the following implementation via php to solve the problem: $...
0
votes
0answers
25 views

Created a role in postgres database and no way to delete db or connect

I installed postgresql on homebrew and I am unable to connect to db by running psql. The error I get is psql: FATAL: role "some-role-name" does not exist. This is not a newly installed postgres and I ...
0
votes
1answer
15 views

Create a file as one user and edit it as another user

Im writing a shell to automate a process this shell will be run as root or as another user but not as the postgres user (meaning the user will just run the script) What i did as a postgres user ...
0
votes
1answer
15 views

Having issues with PSQL automatically lower casing executed query

I am having issues executing the following query. Is there any way to maintain the upper case letters with the psql query? I have tried quotes which does not work and I have tried single quotes, in ...
0
votes
2answers
29 views

Psycopg2 ProgrammingError: syntax error at or near SELECT

Trying to pass a variable to a psql query. Code below. I'm ultimately trying to copy the results to a CSV file and an error occurs trying to execute the module cur.copy_expert. date1 = ('2019-05-06',)...
0
votes
0answers
26 views

CREATE USER produces 'NOTICE: empty string is not valid…' [duplicate]

I'm following the PostgreSQL docs at https://www.postgresql.org/docs/11/sql-createuser.html to create a user. psql hates me. # CREATE USER blah WITH ENCRYPTED PASSWORD '...
1
vote
1answer
25 views

Counting parents types where all children are only of a particular type

Project Accomplishment ------- --------------- id id project_name accomplishment_name project_status_id project_id ...
0
votes
0answers
54 views

pg_ctl: directory “/var/lib/postgresql/10/main” is not a database cluster directory

I'm following this guide to do a replica server using postgres, I already made everything in a shell to automate it and seems fine, until i get to this command pg_basebackup -h 192.168.1.103 -D /var/...
-1
votes
0answers
15 views

Im trying to write a Shell script to append postgresql database size and one of the table size into a file with date and time stamp

SELECT pg_size_pretty( pg_database_size('activevos') ); SELECT pg_size_pretty( pg_total_relation_size('aeprocesslogdata') );
-2
votes
0answers
13 views

Postgres is giving me a role does not exist error when I know the role exists

When I try to use the import/export function in pgadmin I am getting "psql: FATAL: role "username" does not exist" but when I do a role check on my postgres server I can see that the role is there ...
0
votes
0answers
10 views

postgresql exclude tables when restoring a sql file using psql [duplicate]

Is there any way to exclude some specific tables while restoring a sql file like: psql -U postgres dbname < /path/to/file.sql i know that tables can be excluded while taking dump of a db but i ...
0
votes
2answers
19 views

Bash shell to connect to a DB and run a query from in PSQL gives an unknown user:

Im trying run from a shell a command without actually switching users or logging into the DB manually (so the shell doesnt get interrupted asking for the password) CREATE EXTENSION IF NOT EXISTS "...
0
votes
1answer
16 views

How do i fill a column with specific fixed value for a table using select

I want to select some data, but that data has a column that has NULL values. I want it to change it from null to a specific fixed value without changing the database i.e. V_fruits number | fruit ...
0
votes
1answer
38 views

How to access private instance through docker

I have 2 servers in a subnet (A & B). A and B can talk to each other though private IP. I have deployed a docker on server A, now I want to access Server B from docker though private IP how can I ...
0
votes
1answer
21 views

How do you add images in psql for ruby?

I don't know how to add images in my sql for my ruby web application. This is the code i've tried in my views index.erb <h1>Welcome to my blog page</h1> <% @posts.each do |post| %>...
0
votes
1answer
18 views

Order By (alias) then Order by second sequalize

I am using sequalize.js, I am having trouble with an order statement, I want to first order by if id (if my id is inside that alias table), then another order.... order = [['alias', 'id'], /*[this ...
0
votes
0answers
12 views

Library Not Found: How do I connect psql to readline with homebrew?

I'm following the guild at https://olivierlacan.com/posts/migrating-homebrew-postgres-to-a-new-version/ to upgrade my psql thru homebrew from 9.5 to 9.6.6 but just doing psql --version returns ...
0
votes
0answers
20 views

Substraction bug [duplicate]

This happens on Google Sheets: This happens in javascript (chrome console): And something similar happens in psql, but I haven't been able to reproduce.
1
vote
2answers
56 views

Psql Docker: could not connect to server: No such file or directory

I created a container using following command: docker run -itd --name jkdb -e POSTGRES_PASSWORD=123 -v /home/lchang/jkrug/data:/var/lib/postgresql/data -p 5532:5432 -d postgres I checked in ...
0
votes
0answers
24 views

Django throws existOperationalError for DB

Running Django throws conn = _connect(dsn, connection_factory=connection_factory, **kwasync) does not existOperationalError: FATAL: database "saleor" I have used: CREATE USER saleor WITH PASSWORD '...
0
votes
1answer
27 views

How to check if .sql script execution succeeded? [duplicate]

I'm loading an exported database with the below commands: psql -c 'drop database database1' psql -c 'create database database1' psql database1 < script.sql Now I'm trying to check if the final ...
-1
votes
1answer
20 views

How to sanitize PSQL LIKE query against injection attacks - SQALCHEMY PYTHON

I am trying to sanitize an sql query using python when I pass it some variables that are taken from a form. I have the below: listOfBooks = db.execute("SELECT * from books WHERE " + searchParameter + ...
0
votes
0answers
12 views

create tablespace does not work but no error message [duplicate]

im trying to create a tablespace and seem to have no success. what i've tried: pablo@myserver:~$ sudo su - postgres postgres@myserver:~$ psql psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1)) Type "help" for ...
0
votes
0answers
33 views

“Django_content_type already exists” - after running migrate on server after database restore

I've migrated across to a server environment, and finally got everything working. The django project is rendering properly, but there seems to be a big problem with the migrations/database. When I ...
0
votes
0answers
16 views

psql: generated by default as identity

I'm following a psql tutorial on generating a table. However, I kept getting syntax error, even though I pasted in the exact code. thisdb=# create table t1 (a int generated by default as identity, b ...
0
votes
1answer
25 views

How to get output of a sql with additional special characters

Long story... I am trying to geenrate a crosstab query dynamically and run it as a psql script.. To achieve this, I want the last line of the sql to generated and appended to the top portion of the ...
0
votes
1answer
33 views

POSTGRES. error: extra data after last expected column. one double quote?

I have a massive database, and I found an error, when the migration is a single ", returns the error. ERROR: extra data after last expected column my data is ... 0,direccion N"16, 109, ...
0
votes
1answer
39 views

How to format or change a column heading without alter table structures in postgresql using command line

I am working on Oracle to PostgreSQL migration. Some .sql files are written to generate reports which is executing in Linux server. Below is code which has to be migrate, Looking for alternate in ...
-1
votes
0answers
17 views

RUN .sql file from command line syntax error [duplicate]

I written update query in .sql file in windows now trying to run that file from command line so it gives me syntax error  with this charcter C:\Users\ABC21>psql -h ABC21 -d testing-U myuser-p ...
1
vote
2answers
67 views

Ways to give SQL Query to PSQL Command line

I'm looking for different ways other than -c or --command= to give SQL queries to PSQL command line. What are alternatives to this: String command2 ="psql --dbname=postgresql://postgres:password@...
-1
votes
1answer
23 views

How do I replace (select current_timestamp) with a filename that houses this same select statement?

I am using PSQL. My command line is: $\copy (select current_timestamp) to '/home/myname/outputfile.txt' I would like to know, How do I replace "(select current_Timestamp)" with a filename that ...
0
votes
3answers
39 views

Pass value of variable to complex command

I am trying to pass value from string variable to complex bash command and I have problem with it. When I executing commands: files=($(ls abc*)) for file in "${files[@]}" do scp $file user@...
0
votes
1answer
28 views

Connect to a remote Database that live in Docker via Mac OS X

I'm on a Mac OS X, and have a docker-compose.yml version: '3' services: portalmodules: build: context: . dockerfile: Dockerfile ports: - 8010:8000 links: - ...
0
votes
1answer
42 views

Importing CSV file into a psql table

I have a csv file with 2 columns, one column is an id and the other one a name. No csv header. "5138334","Here's Where the Story Ends" "36615796","W31CZ-D" "10283436","Elliant" "8773661","Dobos ...
0
votes
0answers
16 views

Postgres gives no result or errors [duplicate]

I am completely new to postgresql, but I could not find similar questions on my problem. I downloaded my postgres data by heroku pg:backups:download I got my datadump (3.2M): latest.dump I restored ...
0
votes
1answer
20 views

Why is the format of the sql files affecting if they can run or not in PG?

I have placed a file in my docker-entrypoint-initdb.d/ directory. Here is what is in the file: CREATE TABLE user_test ( user_id INTEGER, name VARCHAR(100), email VARCHAR(...
3
votes
1answer
48 views

Is it possible to include timezones in list of roles?

Can a list of postgresql roles be generated that includes the role's timezone? I've learned, and tested, that a psql session will take on the timezone of the role used at login. Of course we can ...
0
votes
1answer
13 views

using psql environment in a function declaration

I am failing to use a parameter in function declaration. a SQL script like : CREATE OR REPLACE FUNCTION test_functon() RETURNS trigger AS $BODY$ DECLARE test int:=:SRID; BEGIN RETURN NEW; END; $...
1
vote
1answer
19 views

Concatenate result of a SELECT into an EXECUTE

I am trying to recreate a view from a backup of the definition using EXECUTE in an anonymous code block (DO $$ ... BEGIN... END$$), but I cannot get it to work. This is approximately what I have so ...
0
votes
1answer
26 views

How to execute psql from UNIX with variable for predicates?

I would like to know how to do the equivalent of the code below that I run in another RDBMS in Postgres. UNIX Script: call_rdbms_sql.sh Content: CONNDETAILS<<EOF select col1 from table1 ...
0
votes
1answer
36 views

Amazon EC2 and PostgreSQL: cannot connect due to time out error

I think I followed any possible guide before asking here. I am trying to remotely connect to a PostgreSQL db installed on a EC2 Linux instance (Ubuntu server 18.04, free tier if it matters). Here ...

http://mssss.yulina-kosm.ru