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
0
votes
1answer
16 views

How to rank an aggregation in each newly formed group?

The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL. Let's say I have a simplified ERD that looks as follows: This ...
0
votes
1answer
15 views

Create a foreign key to a table that doesn't exist in postgres

I'm trying to put together a solution where I have a bunch of script files that contain my schema creation. Something similar to Visual Studio's SQL Project, but for Postgres. My problem is that the ...
0
votes
3answers
25 views

How to prevent trigger from overwriting values that were explicitly set?

I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?): create temp table ...
0
votes
0answers
27 views

Sql, get data/rows for more selected ids in one condition, from file

I am using PostgreSQL 11.5 on Ubuntu Question How can I get data/rows for more selected ids in one where condition? Ideally from file with list of ids. Example |product_id |product_name ...
0
votes
1answer
29 views

How can I run a pgAgent job every 10 second?

I have a PostgreSQL 11.3 Database. I installed pgAgent for creating jobs. Now I want to run my job every 10 second, but in GUI there is possibility to write with precision of minutes, not with ...
0
votes
0answers
27 views

Postgres uses sequential scan with OR condition, but index scan with AND condition

We are doing a full text search on an object. Unfortunately, the object is normalized across different tables, and the full text search results in a condition with several OR, because the term can ...
0
votes
1answer
15 views

Postgresql - how to list indexes on a table that is partitioned?

This command works for non-partitioned tables: select * from pg_indexes But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition. ...
1
vote
1answer
24 views

Unable to alter partition table to set logged

I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command. Afterwards, I ran an alter table set logged ...
0
votes
1answer
20 views

Multiple versions of PostGIS and PgRouting on CentOS 7

I'm trying to install multiple versions of PostGis and PgRouting in the same instance of PostgreSQL v9.6, using CentOS 7.5. I've been fighting with dependencies and shared libraries, to make all ...
1
vote
1answer
127 views

Postgres table with 35m rows slow queries. how can i improve the performance?

SELECT count(c.id) as clickCount FROM clicks c LEFT JOIN links l on c.link_id = l.id LEFT JOIN user_agents ua on c.user_agent_id = ua.id AND ua.robot IS NULL WHERE l....
0
votes
0answers
25 views

Grant Privileges on Production Database Throws “Error: tuple concurrently updated”

I would like to grant read access to all tables in my schema GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXX; While it works on our alpha environment, it fails with our production database: [...
0
votes
1answer
60 views

How to pivot multiple columns in PostgreSQL 9.5?

I have the sample data as follows and want to find if I can use crosstab function in this case. Sample Data: ITEM | CD | TYPE |PARTS | PART | CNT | Item 1 | A | AVG |2 | 1 | 10 | Item ...
2
votes
2answers
29 views

Summing the count of two columns in postgres that contain the same value

I want to sum the total number of occurrences of one value across two columns in the same table. So, an example would be: id |node1 |node2 1 | 111 | 123 2 | 122 | 124 3 | 111 | 125 4 ...
0
votes
2answers
33 views

When to ANALYZE/VACUUM/REINDEX when adding primary keys and indexes

I have a recurring job that creates a Postgres (v11) database from a template and loads it with data. The scale of the data is on the order of 10s or 100s of millions of rows, so for performance ...
0
votes
1answer
18 views

Postresql dynamic index creation for many tables

How can I create an index in Postgres dynamically over many tables but always with different names. Lets assume table x has a column y with all table names of hundreds tables which need to get an ...
0
votes
1answer
23 views

Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2) [closed]

I have a few tables and I am facing this issue most likely with the table "item". When I remove restaurant_id and category_id, it works fine. CREATE TABLE IF NOT EXISTS restaurant ( id ...
0
votes
1answer
36 views

Do database actions inside a transaction become slower as the transaction grows? [closed]

I have a PostgreSQL database running a transaction. Inside that transaction I process around 160 records from an Excel file. As it runs, I see the processing becomes slower with each record (0.05 secs ...
0
votes
2answers
37 views

How to count decimals without zeros

How can I count all decimal place up to the first zero For example I have a column named x with an entry of numeric datatype value 1.955000000 I want to count the decimal places without the zeros. ...
0
votes
0answers
24 views

CSV -> Python -> Pandas.DF -> PostgreSQL (UPSERT, THEN Cull data) [closed]

Problem Statement: I have multiple machines all running the same software, they output CSV files. I need to load this information into a database. The challenge is the user can, with permission, ...
0
votes
1answer
29 views

Schema and Query design for table with multiple languages [closed]

I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and ...
0
votes
1answer
24 views

How to insert values to one table from the table stored in another database in postgresql?

Here is the structure of table: `* Table 1 of database A contains mobile number and userId and both field cannot be blank I want to fetch data from table 1 of database A and insert that with some ...
1
vote
3answers
53 views

Take average of multiple nullable columns

I have an example table (year) as below in PostgreSQL 9.5: Name | 2010 | 2011 | 2012 ------------------------- A | 10 | | 40 B | 10 | 20 | 30 Now, if I write a simple query as ...
1
vote
2answers
41 views

postgresql - add the missing rows to each group of year/date

i have this table --> https://pasteboard.co/IrVXlRe.png and there is another table --> https://pasteboard.co/IrVXNPh.png what i need is to show ALL categories regardless of the quantity. if there ...
1
vote
1answer
22 views

sequence keys for static data

In my data model I have some static data that is added to over time as the system changes. For instance: create table region (id SERIAL,crname TEXT); customer has a link to the region, my system ...
1
vote
1answer
9 views

Postgresql replication slot used by barman has a long replay_lag

I set up barman using wal streaming, I just notice I have a hug replay_lag. I'd like to keep it down to 0 but have no idea about how to do this. I also have a database replica and it is working fine. ...
2
votes
1answer
31 views

Why does DELETE not work after an UPDATE in a CTE?

CREATE table test ( id integer, x integer ); INSERT INTO test VALUES (1, 1); SELECT works: WITH res AS ( UPDATE test SET x = 10 WHERE id = 1 RETURNING id ) SELECT id FROM test WHERE ...
0
votes
1answer
39 views

How to deal with different badges in Postgres Explain Visualizer?

I've been working with several DBMS during my career, but always Oracle/Sql Server/MySql. Now I have to deal with PostgreSQL and my first task is to tune several queries to speed them, as our DB is ...
-1
votes
1answer
13 views

DBD::Pg::st execute failed: ERROR: canceling statement due to user request

I am using postgresql with pgadmin, I am getting following error DBD::Pg::st execute failed: ERROR: canceling statement due to user request CONTEXT: SQL statement "TRUNCATE mystats_na_apple....
0
votes
2answers
33 views

Does enabling pg_stat_statements reduce disk space?

Recently I was informed that since enabling pg_stat_statements, the available disk space is dropping fast. I don't think it's caused by that, but I wonder: Does enabling pg_stat_statements reduce disk ...
0
votes
1answer
14 views

pg_restore fails with drop default on identity column

Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase. See below: pg_restore --verbose -...
1
vote
2answers
32 views

copy entire PostgreSQL cluster to another (identical) server

I'm looking to clone a PostgreSQL 10 cluster from server1 to server2, which is running the same Postgres version on identical hardware. The purpose is load-balancing and HA. Things to keep in mind: ...
1
vote
1answer
25 views

Why disable Postgres Autovacuum?

I've got a task to work with a 9.6 Postgres DB, and for some reason Autovacuum is disabled. The database was extremely slow, and after a manual VACUUM it could breathe again. The point is, why ...
1
vote
1answer
15 views

How I can find out the foreign wrapper that a foreign table belongs to?

I have found that the following query lists all my foreign tables in my PostgreSQL database: select * from information_schema."tables" where table_type='FOREIGN TABLE'; Now I want to find out the ...
1
vote
0answers
15 views

How to get db “state version seq” in postgres?

I need to run a script on every database change. The script works on diffs, so I need to know if I handled the previous state (the handler script could be down for some time for example). One way I ...
0
votes
1answer
27 views

How to Calculate Month over Month User Retention based on already active users (not based on user signup date)?

I have a Table which tracks User activity (i-e user started a session at what time ?). This table contains the data from Dec 2018 Till now. I need to calculate monthly retention (not based on signup ...
0
votes
0answers
22 views

Single metadata base table in Postgres

I am a fan of adding timestamps to tables in the form of created_at, updated_at and deleted_at. But it can be tedious to add these fields to every table. I was wondering, is it a bad design decision ...
0
votes
0answers
15 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
1answer
20 views

pg replication connection not returns wal events to java code client , it hangs some times

Describe the issue We are facing issue of pg replication slot not listen database wal events. Once pg database restarted and we attach Driver Version: 42.2.6 Java Version: JDK1.8 OS Version: Window ...
0
votes
1answer
20 views

postgres function definition not saving top whitespace

For reference I am running postgres 9.6 . I'd like to write functions inside of postgres where the top of the function saves. create or replace function apqp.apqp_list_gentex_tool_report_getlist(...
0
votes
1answer
14 views

How to reset object permissions to default?

I've just been added to a project using Postgresql 11.4 where database security has been an afterthought. As part of tightening things up, I've used ALTER DEFAULT PERMISSIONS to set sane ACLs on any ...
0
votes
1answer
20 views

pgadmin: can't insert jsonb[] values with strings

I have jsonb[] column and it allows me to insert some nested arrays, but only with number values like {{1,2,3}, {4,5,6}, {7,8,9}}. I'm not talking about inserting some nested objects like {{"one": "...
0
votes
1answer
18 views

How can I see what query is emitted by the query parser?

Consider this snippet: create table tbl_1(name text); create table tbl_2(name text); create function my_insert(_name text) returns text language sql as $$ insert into tbl_2(name) values (...
0
votes
1answer
43 views

Postgresql, escape character in string_agg function

Question, can I add escape character \ just for fields param_name and value for this? string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params", Result is. {name:"...
0
votes
2answers
39 views

PostgreSQL - getting Too many client repeatedly

I am running PSQL with PostGis extension. After the start of server, after about 3 weeks, I start getting fatal errors for connections with "too many clients already". When I restart the server, ...
0
votes
0answers
13 views

Postgresql, export to xml, <row> rename

When exporting to xml, I have this output. "<product><table xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns=""test""> <row> ... </row> <row> ... etc ...
0
votes
0answers
16 views

Reader rights by default in Postgres

My question is about database permissions and rights in Postgres. How can I create reader rights by default when objects are uploaded into PGadmin 4 schemata? I created the following schema in ...
0
votes
2answers
42 views

How to hide the partition child tables in PostgreSQL?

I am creating partition tables in postgreSQL. And the partition is on date column so its nearly high in number to show in schema. I want to show only my parent table for other users. I tried many ...
0
votes
0answers
18 views

How to prevent regular users to backup database, in Postgresql

How can I prevent/restrict regular users, that do have select rights granted on tables etc, to perform backup of Postgres database? Is there a way to enable users to connect, select, update etc. ...
0
votes
1answer
20 views

How to list all constraints of a table in PostgreSQL by schema?

Yes, I'm aware there is a question very similar to mine, the difference is that I want all constraints, including not null, which no answer is able to do.
1
vote
1answer
16 views

What does 'fully determines' mean in the context of postgres functional dependency statistics?

I am following the example of how to use 'create statistics' in postgresql from this page. CREATE TABLE tbl ( col1 int, col2 int ); INSERT INTO tbl SELECT i/...