Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
6
votes
2answers
436 views

NULL value causes blank row in SELECT results for text concatenation

I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results: postgres=# SELECT ' (' || obj_id || ', ''' || ...
0
votes
1answer
27 views

Create gist index on cube array with 512 dimensions

I want to perform nearest neighbors search on db with 5000 entries of cube array with 512 dimensions. I referred to this answer and used docker image of postgresql with cube array limit set to 2048 ...
0
votes
0answers
22 views

Regarding Postgresql column length [migrated]

I am investigating an issue and the error log shows an error like this one: Error occurred (psycopg2.DataError) value too long for type character varying(8192) I could not find what causes the ...
0
votes
1answer
15 views

List database CONNECT privileges on PostgreSQL

How can I view database-level privileges like CONNECT of users? I found 2 ways already, but neither of them is perfect: Use function has_database_privilege(). This works, but I have to run it ...
0
votes
0answers
23 views

How to reset email/password pgadmin?

I'm running pgadmin4.3 and I do not remember what login/password I used. Tried to reinstall but it not worked. How I can reset it?
0
votes
2answers
192 views

PgAdmin4 unusable when database has lots of schemas

I have a production database with 60K database schemas. PgAdmin4 is unusable, I cannot even open "Query Tool" window. I suppose that PgAdmin tries to load database metadata, so that's the reason why ...
0
votes
1answer
16 views

Postgresql and jsonb: perform a like over all values of an array

Jsonb seems great but I can't figure out how to perform a partial search (like) over an array. In my case it's an array of email: ["email1@foo.com", "email2@foo.com", "email3@foo.com"] I want to be ...
0
votes
1answer
97 views

Looking for an efficient way to build some kind of “FIFO” table

I need to store measured data on a daily basis for a given period in the future, lets say 7 days. The current day should be always day 1. Basic Example: | id | day | value | |----|--------|-------|...
3
votes
1answer
44 views

Try catch equivalent in Postgres

Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.
0
votes
1answer
88 views

.pcppass not working

I run pgpool-II as postgres user so I can replicate exactly what I did in one sequence here in this post. -bash-4.2$ echo "pgpool:$(pg_md5 test)" > /etc/pgpool-II-11/pcp.conf -bash-4.2$ cat /etc/...
0
votes
1answer
21 views

Alternative defaults with a different table name

Say the original table has DEFAULT nextval('some_sequence'). I'm looking for a way to specify DEFAULT nextval('another_sequence') on a table view/alias so that I can just insert into the alias as ...
0
votes
2answers
97 views

A table with many null values VS many tables

Basically my level of DB knowledge enables me to create, select, delete and some simple things. When it comes to designing for performance, I have absolutely 0 knowledge. So my question here is let ...
-1
votes
2answers
30 views

How to get column values in where condition in sub query? [closed]

I am trying to get distinct list of values in where condition through sub query but it shows 0 rows in result set. Here is my query. SELECT a.topic_id, t.id as topicid, t.value as value, t.topic as ...
1
vote
2answers
168 views

Why isn't pg_restore --create working?

I backed up a database called app_data, on a 9.3 server, with this basic command: pg_dump -n public -F custom app_data > app_data.pg.dump Then I try to restore it on another server (running 9.4) ...
12
votes
1answer
11k views

pgAdmin 4, no built-in SSH tunnel config?

Just tried using pgAdmin 4 for the first time. It looks like there's no built-in SSH tunnel config for connection. I searched the docs but there's no mention of the feature. Is it gone for good?
0
votes
0answers
10 views

How can I connect to Denodo using Python Pandas and SQLAlchemy? [migrated]

I'm trying to connect to Denodo using Python SQLAlchemy, and create a DataFrame from a table. My environment is Cloudera Data Science Workbench. Can I use psycopg2 and the PostgreSQL connection string?...
0
votes
1answer
86 views

pg_replication_slots: how to determine how far behind a consumer is?

Given the replication api exposed in postgres 9.6.6, for a given replication slot and a consumer, what system catalogs or view could inform how far behind the consumer was the latest state of the wal?
-3
votes
1answer
32 views

How to copy data from a table to another in O(1) time complexity?

I have a table Customer with fields id, account_balance, etc. At the end of every month at midnight I want to copy the whole list of customers with their account balance to another table(month, year, ...
0
votes
2answers
43 views

Postgresql, commas in string_agg function [closed]

Need FIELD like this, with ' and without , at the end of whole field. {name:'color',value:'red'},{name:'width',value:'300 ml'} Tried string_agg(distinct '{name:\'' || param_name || '\',value:\'' ||...
2
votes
1answer
21 views

Gather values from different related rows with JSONB fields

Say I have the following table structure: jobs +----+------------+ | id | some_field | +----+------------+ | 1 | some_val | | 2 | some_val | | 3 | some_val | +----+------------+ events +----...
1
vote
1answer
14 views

Unable to Run psql in Windows Command Prompt

I have just installed postgreSQL and have set up the user postgres and its password. In the psql Shell I am able to login as normal however I can't login in Windows Command Prompt. When I run the psql ...
1
vote
1answer
20 views

Run out of disc space on Debian server due to postgresql database

I recently run out of disc space no my server due to implementing some audit tools on Postgresql database. Two tables holding data about changes made by users quickly reached 16GB each, and totally ...
0
votes
2answers
38 views

Storing the groupwise most recent related record

I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the "...
0
votes
0answers
7 views

How to find the master instance of a read replica in AWS RDS PostgreSQL?

I have an RDS Postgres instance whose role is Replica. I do not see the master in the same region and probably is in another region of the AWS account. How do I find the master for this replica? ...
1
vote
1answer
34 views

How can I generate fake data that adheres to the PRIMARY KEY-FOREIGN KEY constraints of my schema?

I have a schema for which I would like to generate fake data. I can generate fake data using Python for each of the tables separately, but I don't know how to make sure the fake data generation ...
0
votes
1answer
91 views

How to easily show very long “canonical” name for each column in all reports?

Related: Does the length of a table name affect performance? For query writing purposes I want to have nice short actual column names. However, from my actual data source, I have an extremely long "...
1
vote
1answer
65 views

Hotel Booking Database design problem

I am currently designing (as an assignment for the course at the university) a database for online booking of hotels worldwide and I have stumbled into a problem. Here's is the conceptual design: ...
2
votes
0answers
31 views

Postgres wait_event_type Extension

I have a function that was written in Language SQL (not plpgsql) that is scheduled to be called nightly from pgAgent. It was running fine until last night. The function usually takes under 3 minutes ...
0
votes
1answer
23 views

How to set minimal token length for to_tsvector() function?

I parse xml documents with to_tsvector() function, and, sometimes, it produces tokens, which are shorter than 3 characters: '1':89,91 '2019':14 '25':4 I know, that to_tsvector([ config regconfig, ]...
3
votes
1answer
877 views

Postgres: permission denied for relation for read_only user [closed]

I am trying to set up a new read only user in postgres. I did the following steps to create the new user: sudo -u postgres createuser --interactive name: read_only_user superuser: n ...
0
votes
1answer
24 views

Does a read replica help if the problem are heavy queries?

The application that our team is responsible for is having ever more often DB performance problems. The application querying it doesn't throw lots of concurrent queries, the problem we have are rather ...
1
vote
1answer
675 views

Relation does not exist while scanning schema

My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS ...
0
votes
0answers
23 views

Can I see the remote IP address for failed PostgreSQL logins?

We're running PostgreSQL on Heroku, with all the application logs going to Logentries, and I'm seeing around 4,000 failed login attempts per hour for a specific set of credentials: 2019-07-30T09:08:...
1
vote
1answer
18 views

List Postgresql foreign data wrappers without the use of the `psql` command

As seen in a stackoverflow answer I can use the psql command and the \dew[+] command in order to list the foreign wrappers. But the use of universal database tools such as DBEaver, DataGrid, etc ...
0
votes
1answer
89 views

PostgreSQL pg_basebackup from several port number

I have one slave server which used as a replication server (let's called it slave1) from several master servers. The `slave1' server is set up to receive replication from several postgresql server, ...
0
votes
2answers
103 views

Postgres file increment size

I'm COPY-ing 300 tables / 400G to PG10@win2016server using a few threads to speed things up, but because the table files are incremented in 64-kb units (or something there-about) they get fragmented ...
0
votes
0answers
23 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 ...
1
vote
1answer
18 views

Running .sql files in postgres (structured programs)

In oracle I could create .sql files and run them with a structure (like a stored procedure) with begin declare end; I did some tests, because I need to run scripts to fix problems, not is ...
1
vote
1answer
33 views

max_stack_depth error in postgresql

I am fairly new to PostgreSQL and would appreciate any help and recommendations. I'm running the following function to update a table: $BODY$ DECLARE commercial_period date := (SELECT periods....
2
votes
1answer
95 views

Postgresql import user from linux

I install postgresql 9.2 on a Ubuntu 12.10 machine I tried to import linux's user to postgresql but only way I can figure out is CREATE USER Jack WITH PASSWORD '1234'; and do it 10 times (if ...
1
vote
0answers
30 views

Brand new postgres setup - what to check first?

My sys ops team have recently handed over a brand new set of postgres clusters (prod, stage, test etc). We don't have any DBAs and they are under resourced. Mistakes in config have been found before. ...
0
votes
1answer
14 views

Insert on conflict return false otherwise true in single query in stored function

At now I use code like that create function something() returns boolean as $$ begin if exists (select 1 from some_table where some_cond) then return false; end if; insert into ...
-1
votes
0answers
22 views

Restore PGAdmin 3 into PGAdmin 4

Is there an easy way to restore databases from PGadmin 3 into Pgadmin 4? Right now data on both, and I want to gather all my data on the same server. The port on PGadmin 3 is 5432 and 5433 on PGadmin ...
1
vote
2answers
393 views

How to use pgloader to transfer sqlite to postgres with table name in Uppercase

I can use pgloader to transfer my sqlite database to postgres as described in this quickstart page. But the result is all my tables' names are lowercases. e.g. TestName -> testname Is there any way ...
0
votes
1answer
136 views

Postgres 11 declarative partitioning by relative time

I'm working on a system that uses PostgreSQL 11 as it's main data store, and I'm looking at how to most efficiently query a large table of events (up to 120 million rows, so not 'big data' big, but ...
0
votes
1answer
22 views

pg_trgm index not used with commutator

PostgreSQL 9.6, on a table with ~100000 rows of text up to 3200 characters long. I have the trigram indexes: CREATE INDEX trgm_idx_gin ON test_trgm USING gin (t gin_trgm_ops); CREATE INDEX ...
2
votes
1answer
88 views

Getting Postgres CREATE TABLE statements

I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me ...
0
votes
2answers
33 views

How do I find a list of customers that have not placed an order in the past 1 year?

I'm having trouble figuring out how to write this query: We have a sales table but the sales table only represents customers that have purchased. We have a customers table that represents all of the ...
0
votes
1answer
26 views

Using WHERE / AND in Joins

Can someone elaborate on how the following two queries would provide different results? That is, how the query is processed differently using WHERE or AND. select orders.*, accounts.* from ...
1
vote
1answer
84 views

Slow PostgreSQL 9.6.5 query with LIMIT, but only for tsquery with no results

Table a has > 3 million rows and this index: CREATE INDEX idx_a_txt ON a USING gin (txt); This query takes 5 seconds if I search for something that doesn't exist, and 31 milliseconds if I ...