Stack Exchange Network

Stack Exchange network consists of 175 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 [postgresql]

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

1
vote
1answer
46 views

Combining separate columns into smallest possible related ranges

I'm trying to discard multiple records that may or may not overlap based on the smallest possible contiguous ranges. I thought of doing something similar to This, however the ranges are numeric ...
1
vote
3answers
25 views

Query “all of” across many-to-many relation

Imagine a setup of three tables, User, Group, and UserGroup, where UserGroup consists of simple a foreign key to each of User and Group tables. User ---- id name Group ----- id name UserGroup ------...
0
votes
1answer
85 views

Trigger on 2 tables : issue with INSERT and UPDATE

This is related to my previous question: Trigger to cascade updates to a second table. In my Postgres 9.6 DB, I had 2 triggers on emprise (formerly table1), supposed to insert or update some fields ...
8
votes
1answer
6k views

What are the compression options for storing large amounts of text in Postgres?

I need to store large amounts of text in Postgres, mainly command logs and the output of long running commands and need to know if there are some settings that could help compress the data ...
0
votes
0answers
16 views

Postgres default databases

I went through a re-installation of postgresql on a mac using brew and I'm not sure about some default vs user created databases. After the installation I see 2 users: postgres dan (being my osx ...
1
vote
1answer
570 views

How can I control how Pentaho DI formats integers coming from a text file?

I have a simple pipe-delimited text file with integers recorded as a sequence of numbers: 012345|00678 |various|text|fields There are some leading zeros, and some trailing white space, but no ...
4
votes
1answer
801 views

speed up PostgreSQL calculations via OLAP warehouse?

So I have asked these three questions: Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5 https://quant.stackexchange.com/questions/29572/building-financial-data-...
0
votes
1answer
19 views

Allow transparently altering View's underlying table

How to create a view that does not prevent altering its underlying table? There's a certain reason why I can't drop and re-create the view every time I need to change a column type or add a new column....
2
votes
0answers
31 views

TCP Resets when using psycopg2

I'm trying to do a simple health check for keepalived and other services via a python script and psycopg2 library. All seems to be alright, until I close the connection, at which point a packet with ...
0
votes
0answers
11 views

ERROR sbuf_send_pending: no dst sock? frequent error in pgbouncer

Recently, my Pgbouncer's log file grew to around 1 GB and when I saw the entire log file it was just below statement with different timestamp: ERROR sbuf_send_pending: no dst sock? What could be ...
0
votes
1answer
454 views

Does 'max_worker_processes' include background process from postmaster?

I am wondering that max_worker_process include background process from postmaster? I'm going to upgrade Postgres from 9.3 to 9.6 and planning to use parallel query. -bash-4.2$ ps auxf | grep ...
0
votes
0answers
7 views

Huge Heap Blocks number in postgres query planner

I am trying to tune a complex SQL query(Can't post here due to organization policy). While going through query plan i found out that huge 'Heap scans' value spiking up query execution time and ...
0
votes
0answers
8 views

How to update an element in json array with where clause?

I came across this question on how to update nth element of array, but it has nested json values in it instead of a plain array of strings. Here are some sample json values from jsonb column of the ...
5
votes
1answer
1k views

How to exclude tables when using pg_dumpall with postgresql?

If use pg_dump, it has a --exclude-table-data option to ignore special table. But pg_dumpall command doesn't has that option. So how to exclude several tables when dump all databases?
1
vote
1answer
31 views

LEFT JOIN and handling NULL Values [on hold]

I have two tables. Table 1: A B ------ 1 'A' 2 'B' 3 'C' Table 2: A C ---------- 2 15 3 20 I'm using the following query: SELECT table1.A, table1.B, table2.C FROM table1 ...
0
votes
0answers
11 views

how to calculate the exact time taken to do autovacuum in postgres

it say’s it took 179 hours but how it is possible to spent 179 hours from yesterday ? Do i am missing anything ? 2019-04-22 07:40:34 CEST [30320]: [4-1] user=,db=,app=,client= LOG: automatic vacuum ...
0
votes
1answer
20 views

How to set up signed SSL Root and Leaf certificates on a PostgreSQL 11 database on Ubuntu and Windows 10 Power BI plus PostgreSQL?

I have Power BI and PostgreSQL 11 installed on Windows 10 and my primary PostgreSQL 11 database on Ubuntu 18.10 Linux. I edited the postgresql.conf and pg_hba.conf files correctly to the IP Adresses ...
3
votes
1answer
84 views

Group by array overlapping

I have a PostgreSQL table with id and clusters like this: CREATE TABLE w (id bigint, clst int); INSERT INTO w (id,clst) VALUES (1,0), (1,4), (2,1), (2,2), (2,3), (3,2), (4,2), (5,4), ...
0
votes
0answers
22 views

Temp table performance issue

We are seeing wildly varying times while create temporary tables against a Postgres 11 database. For example, a statement like the following took 13.6 seconds: CREATE TEMPORARY TABLE foo ( id int ...
1
vote
1answer
410 views

How to access PostgreSQL Functions from Oracle through DBLink?

I'm having two DB server (PostgreSQL, Oracle). I'm able to access the table in PostgreSQL from Oracle through DB-Link. I've use the below query to access the table. select "Cust_Id", "Mobile_No" ...
0
votes
0answers
31 views

work_mem setting for huge queries

I am querying some data from a view using copy (select * from tbl where id in (n) order by 1) command (the table's in the view are ~600GB, almost all the columns included in the view) and it generates ...
1
vote
0answers
25 views

Accessing Postgresql from web via REST or gRPC? [on hold]

Standard REST server using Golang is rather simple to understand and get up and running. But a gRPC server that do the same thing seems to be complicated and full of dead ends as you mix JSON and ...
1
vote
2answers
39 views

Removing values from array in postgreSQL

Lets say I have a table called users, who looks something like the following: users table: |user_id | name | 1 Bob 2 Alice 3 Joe 4 Tom And ...
1
vote
0answers
25 views

PostgreSQL query report with dates

I'm trying to build report from my access control database system in order to count work hours duration of each employee. So, there are two tables: CREATE TABLE log ( id integer, logtime ...
0
votes
2answers
5k views

psql: FATAL: password authentication failed for user “postgres”

I am running postgresql-9.6 on Rhel7. I know that the default/root user doesnt have a password, however i will like to use phpPgAdmin to do my work. the problem is I have edited my pg_hba.conf file to ...
1
vote
2answers
18 views

uniqueness in array column type throughout all records

There is a table id serial primary key, names varchar(20)[] it contains such record 1, {"one", "two", "three"} Then, next record is inserting (notice one array element already exists in existing ...
-1
votes
0answers
16 views

how to add specific dates to the below script [on hold]

SELECT * FROM ( SELECT couponcreatedate AS "Issue Date", count(*) AS "Issue Count", round(sum(cast(couponmaxvalue AS numeric)) / 100, 2) AS "Max Value" FROM coupons AS c GROUP ...
111
votes
12answers
133k views

How do I find PostgreSQL's data directory?

I forgot how I last time started postgres (it was months ago) and I don't remember where the data directory is located at. The postgres command seems to require to location. I'm on MacOsX if that ...
0
votes
1answer
29 views

Should you create separate tables for fixed options for a column?

As shown below, in multiple places in my DB Design I am creating tables for "options" if you will. Example, RFP Stages. Which would hold things like "Complete", "Bidding", etc. I'm doing this ...
0
votes
0answers
9 views

Connecting PostgreSQL database created on Ubuntu to pgAdmin [on hold]

I have created a PostgreSQL database on Ubuntu 18.04 LTE and am trying to connect to the database using pgAdmin. How can I set the server ready for the remote connection? And how can I set the ...
1
vote
1answer
20 views

How does postgres host based authentication work?

This is a duplicate of: https://stackoverflow.com/questions/55804806/how-does-postgres-host-based-authentication-work (I was asked to shift this query to DBA.Stackexchange) I am installing DSPACE ...
1
vote
1answer
20 views

Temporary role/privileges

Is it possible in PostgreSQL to temporary grant roles/privileges to a user? I'm thinking about this as a solution for the situation when there is a need to troubleshoot an issue and an user may need ...
3
votes
0answers
49 views
+100

Update column without dropping dependancies

I have a table with many dependancies such as: functions, views and materialised views. I'd like to update a column from an int to a bigint. The issue I have is that doing so requires dropping a ...
7
votes
1answer
556 views

PostgreSQL partial index unused when created on a table with existing data

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: https://stackoverflow.com/a/...
1
vote
1answer
2k views

How to assign privileges on a postgresql schema to a user?

pg_hba.conf contains: ... host 10.10.10.10 service_db admin md5 ... Logged in as root, executed the following commands: ###CREATE DB ADMIN sudo -u postgres psql -p 5432 -d postgres -c "CREATE USER '...
-1
votes
0answers
16 views

One long table vs many smaller

Our plugin-style platform has several plugins ( like forum, group, simple mail/chat, etc.) which all have share common features : file and event_log. Their structure are rather simple and can serve ...
1
vote
2answers
192 views

what is “local block” in pg_stat_statements?

https://www.postgresql.org/docs/9.4/static/pgstatstatements.html local_blks_hit bigint Total number of local block cache hits by the statement local_blks_read bigint Total number of local ...
4
votes
1answer
415 views

Disallowing table creation in the public schema

Postgres version: 9.3.16 Let's say we have two users, luser and editor. I'd like to make it so that luser (or any other non-superusers) cannot create any tables under the public schema, except editor....
0
votes
0answers
27 views

Clustered Index in Postgresql [on hold]

I know that, PostgreSQL does not nominate indices as clustering indices by default. If I want to have clustered index, I can manually cluster a table along an index. But I have question about that. ...
0
votes
0answers
16 views

Build, analyze & visualize database [on hold]

I am embarking to build a database that would ultimately store close to a Million data rows. It will be a relational database with connections. I intend to currently build this using MySql or ...
1
vote
1answer
32 views

Unique sets of IDs from table

I want to represent mixture of arbitrary numbers of chemicals. Since the relationship between chemicals and mixtures here is many-to-many, I thought I'd implement it like this (simplified): CREATE ...
2
votes
1answer
67 views

Improving performance of count where only result > 0 matters

I have following query: select count(*) -- rp.tag from submission inner join post rp on submission.id = rp.submission_id where user_name = ?1 and lower(rp.tag) like ('%verified%') and not lower(rp....
0
votes
1answer
24 views

Another - ERROR: column “da2.dependency_device_name” must appear in the GROUP BY clause or be used in an aggregate function

I am attempting to aggregate dependency_device_name into one string, "Dependency List". Here is the error: ERROR: column "da2.dependency_device_name" must appear in the GROUP BY clause or be ...
0
votes
1answer
28 views

Visual Studio Code Postgres SQL Connection

I would like to use Visual Studio Code to preform my PostgreSQL queries, and general DB maintenance as an alternative to pgAdmin4. I have read the documentation included with the appropriate extention ...
1
vote
1answer
668 views

Table design for conversation

I want to create simple conversation module on my site. So I need a list of users who had conversation with current user. I need to show amount of new messages in each conversation. I think about next ...
3
votes
2answers
894 views

How to aggregate transactional records by month year but based on subscription period not calendar months?

I have a table containing transactional events for customers: table: events | id | cust_id | date | amount | |------|----------|----------------------------|--------| | 78 ...
1
vote
1answer
532 views

How to get lexemes array from Postgresql ts_vector, sorted by lexems positional information?

Postgresql full text search to_tsvector() function returns a sorted array of lexemes. I need exactly the same function, but returning unsorted array of lexemes. Is there any such postgresql function ...
5
votes
1answer
67 views

Show current row “win streak”

I need to show the running win/loss streak per row in a query, so given the table below, the query should return the "expected" column. I've tried some approaches with window functions, but no success....
11
votes
2answers
6k views

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it ...
1
vote
1answer
32 views

Postgesql non-deterministic order of result with LIMIT and ORDER BY

I understand that Postgresql may return results in non-deterministic order when the query parameters are non-unique. This is especially a problem when we use LIMIT since this may return entirely ...