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
8 views

Inheritence and Primary Key Constraints

I am currently trying to see how the object oriented features come in play in Postgres. During a few tests I noticed that when you insert in or delete from inheriting tables that those changes also ...
0
votes
0answers
9 views

Postgres memory usage grows over time, resets when clients restarted

I have a Postgres DB running on the same small server as some applications that use it. Immediately after restarting the applications, Postgres uses <100 MB memory. After a few days without ...
1
vote
1answer
23 views

PostgreSQL: duplicates despite having a unique constraint

We have a table in the production DB that looks like this: create table domains ( domain_id serial not null constraint domains_pkey primary key, domain_name text not null constraint ...
1
vote
1answer
19 views

optimizing Postgresql query with left join and indexes

I am trying to run the following query but it is taking forever to execute: Select Distinct ON (S1.sql_id) S1.msgdata as replymg, S1.time as replytme, S2.receiver, S2.time,...
0
votes
0answers
7 views

Query fails sometimes on casting error

I have a query that can run on the same data set, and sometimes it fails and sometimes it succeeds The query is generated by hive metadata service, and I can't modify it. This is a simplified ...
0
votes
0answers
11 views

Ensure integrity of other columns from foreign record

I have 3 tables: Race - id PK Racer - id PK - rank Winner - id PK - race_id FK(Race.id) - racer_id FK(Racer.id) - rank - prize UNIQUE CONSTRAINT (race_id, rank) Racer.rank is a non-unique ...
0
votes
0answers
14 views

In Postgres, what's the most optimal way to extract and use a numeric value from within a JSONB field?

Most sources (e.g. this answer) seem to indicate that the canonical way to extract a numeric data type from a JSONB column with values like, say, {"foo": 1.2345} is to do (col->>'foo')::numeric. ...
0
votes
1answer
16 views

Select from a table if conditions met in joined table

I have two tables members +-----------+ |id | name| +-----------+- | 1 | john | | 2 | adam | | 3 | sara | +-----------+- keys +-----------+ |id | color| +-----------+- | 1 | blue | ...
0
votes
0answers
14 views

Postgres Unnest or flatten a nested array inside of JSONB

I've got a table MY_TABLE that has the following columns: User Name Data Purchased Data is of the form : [{ "Id": 1, "Attributes": [\"[\\\"[\\\\\\\"[\\\\\\\\\\\\\\\"[\...
0
votes
1answer
14 views

Aurora vacuum not deleting frozen empty pages?

I recently ended up deleting most of the data in one of our tables, on purpose. Normally the table is append-mostly, with a short window during which updates might happen after the original insert. ...
0
votes
1answer
13 views

Adding a dropped check-constraint what queries the table

I'm using a Postgres to preist a hierarchical data set; the table I'm using is something like this: CREATE TABLE layers ( bucket text not null, uniq_id serial not null, name ...
1
vote
1answer
23 views

Will Postgres use a multi-column index when setting multiple colums not null?

Typically, when I set a column not null, if it doesn't have an index then I add it first, so that postgres can (hopefully) use the index while doing the table scan while locking the table, so that the ...
0
votes
1answer
22 views

Add Postgresql server to postgres-common

I've created postgresql cluster using initdb. Now I can't use it with common tools. Can i somehow add this cluster or if no, how can i delete it to create new one with pg_createcluster. I know there's ...
1
vote
1answer
30 views

Why is autovacuum running during a VACUUM FREEZE on the whole database?

I have a 14.7 TB Postgres(9.1) database that has to endure heavy writes for prolonged periods of time. I know that I need to closely manage my transaction ids to prevent db lockup. Recently I noticed ...
0
votes
2answers
28 views

How to update data with a trigger function and a join

I have two relations : CREATE TABLE relation_a (id_a int PRIMARY KEY, field_1 int); CREATE TABLE relation_b (id_b int PRIMARY KEY,fk_a int REFERENCES relation_a(id_a), field_1 int); INSERT INTO ...
0
votes
1answer
15 views

How to insert json into a table and have certain columns remain json [closed]

I want to insert data into a table where the first level keys are inserted as values and not as json. Although column "p_attribute" needs to store the nested json. For example, in case of Jane Doe, I ...
-1
votes
0answers
7 views

How to import data from AWS data lake to PostgreSQL DB?

We got already existing product which pushed data to AWS data lake and currently we are manually using that that data for some external processing. Now we are creating a product with PostgreSQL DB. We ...
0
votes
1answer
12 views

I am unable to extract numeric fields from a PostgreSQL database using Dart and the Postgres package

I have a simple, single row, PostgreSQL database table that includes a numeric(8,2) field, set to 120.00, and a character field set to 'N'. This is a Windows 10 setup. When I run the following Dart ...
0
votes
0answers
8 views

Losing connection to the server when using PG Routing function pgr_alphaShapes

I am running PG Routing v. 3.0 on Postgresql 12.2 and the pgr_alphaShapes function is giving me a lot of trouble. Following the documentation here, I passed a geometry and a spoon radius. SELECT * ...
1
vote
1answer
23 views

Improve PostgreSQL SELECT performance in a parent table (inheritance)

I have a PostgreSQL 9.2 database where each account has a schema, like below: My_Database |-> Schemas |-> AccountA |-> AccountB |-> AccountC |-> AccountD |-> ...
2
votes
2answers
653 views

How to add a day/night indicator to a timestamp column?

There is a timestamp column I use to indicate whether a row was created during day or night time. My code is the following, but for some reason I only get 'DAY' as outcome. Am I not formatting the ...
1
vote
2answers
50 views

Why xlog checkpoint was started?

Consider the following config shared_buffers = 20GB max_wal_size = 2GB min_wal_size = 1GB checkpoint_completion_target = 0.7 checkpoint_timeout = 5min and checkpoints' log 2020-05-25 16:28:37.128 ...
3
votes
3answers
173 views

An alternative to querying with multiple JOIN over the same table?

I have a Postgresql 11 database. Let's say I have a table called houses. It should have hundreds of thousands of records. CREATE TABLE houses ( pkid serial primary key, address varchar(255) NOT ...
-1
votes
1answer
22 views

How to inspect shared buffers in PostgreSQL?

I found this shared buffers how to look into usefull link. My tables Schema | Name | Type | Owner | Size | Description --------+---------------------------+--------...
0
votes
0answers
20 views

MS SQL to PostgresSQL replications [closed]

I have an MS SQL deployment and the data is growing. I have a PostgresSQL server and I drop the data moved from my MS SQL server every day. It takes many hours, and I want to have a replication from ...
0
votes
0answers
11 views

Is duplicating “short cut” foreign keys a good idea to avoid multiple joins?

I have 3 tables A, B and C. C belongs to B, and B belongs to A. 1 entity of A can have 0 or various entities of B, 1 entity of B can have 0 or various entities of C. 1 entity of C has 1 entity of B, ...
0
votes
0answers
18 views

High RAM usage during Postgres PITR

We're currently running Postgres 10 in our infrastructure with Barman managing weekly full backups and holding onto the WALs. Because of the size of our DB, the WAL volume, and resource constraints ...
0
votes
1answer
18 views

How to aggregate over a “multi source” with recursive, in postgres?

Imagine I have a table "departments" like this: name TEXT parent_department TEXT (nullable) I also have a "budgets" table like this: department_name TEXT budget INTEGER I need to "traverse" this ...
0
votes
0answers
16 views

Statistics collector: query in a date range and other questions

I want to use the Statistics Collector (https://www.postgresql.org/docs/current/monitoring-stats.html) in order to obtain the total database times during a certain period. Once enabled, I can ...
0
votes
1answer
8 views

PostgreSQL pg_rewind use cases

Let's say that I configured streaming replication between Server A (master) and Server B (slave). If server A goes down and Server B is promoted, would I be able to use pg_rewind, where Server B is ...
0
votes
0answers
13 views

Secure multi tenant login procedure

I have a multi-tenant solution today (client-server). Each database is separated from each other. Each db has its own login and own users. Now I am planning to take the move to a web application. This ...
0
votes
1answer
33 views

How to do Pagination with UUID v4 and Created Time on Concurrent Inserted Data?

Context: Out of curiosity, I'm doing load testing for my application. And then the result there's a lot of concurrent inserts happened. After doing the load testing on create-endpoint, I'm trying ...
0
votes
1answer
32 views

What happens if transaction rollback fails?

So failure while committing leads to rollback. But what if rollback also fails due to some reasons and the database has changed due to partial commitment. How is the database restored to its original ...
0
votes
0answers
15 views

PostgreSQL Streaming Replication with Switchover

I'm trying to provide streaming replication between a master (T1) and slave (T2) and swapping their roles when necessary (i.e letting T1 be a slave to T2). So far I am able to get this working if I'm ...
1
vote
1answer
40 views

How to prevent seek pagination from doing sequential scan over entire table

I have 3 tables and a materialized view: resource_categories contains all category names and metadata create table if not exists resource_categories ( category_id INT, title VARCHAR(255), ...
0
votes
1answer
31 views

Is it possible to set timezone for a schema - Postgresql

Is it possible to set the timezone for one schema? Do not want to set Postgresql(v.12) server level. Please suggest if any ways to this.
0
votes
1answer
23 views

How to import values of a JSON to PostgreSQL?

I have a JSON file gugu.json that looks like this: { "key1": { "value1": "normal", "value2": [ "PogU" ] }, "key2": ... } I want ...
0
votes
2answers
19 views

How to get the type of geometry column in postgres?

I need to determine the type of geometry column and doing it with the following query: SELECT type FROM geometry_columns WHERE f_table_schema = 'public' AND f_table_name = 'table_name' ...
0
votes
1answer
21 views

Create sequence starting with maximum id from a table [duplicate]

I'm trying to create a sequence in the following way: create sequence some_seq start with (select max(id) + 1 from some_table); But I'm getting the following error: ERROR: syntax error at or near "(...
0
votes
3answers
36 views

update one column based on query containing another (joined) table

Consider the following select query: SELECT s.id, s.event, s.column, e.oldcolumn FROM section as s INNER JOIN event as e ON s.event = e.id WHERE s.id = 1 Now I wish to update the section table so ...
0
votes
0answers
27 views

Is it possible to use the log of tracking changes to revert a table to a previous state in PostgreSQL?

This thread (Track all modifications to a PostgreSQL table) discussed how to generate a log to track changes in PostgreSQL. How to use the log to revert a table to its older version programmatically?
0
votes
1answer
20 views

Postgresql 4 table joins

I have one doubt in posgresql joins and filters. Please find below sql code snippets. 1) select count(*) FROM lfa1 a LEFT JOIN lfb1 b ON a.lifnr = b.lifnr and a.hvr_is_deleted = 0 and b....
0
votes
0answers
14 views

Removing postgres connection installed with EnterpriseDB on MacOS

I had earlier installed Postgres using Enterprise DB which is installed by default at location: /Library/PostgreSQL/10 However, because of some database issues I had to uninstall it. I followed all ...
1
vote
1answer
23 views

Create 7 day rolling average in PostGIS for each feature

I have a timeseries dataset that looks like this: uid | geom | date | count | ------------------------------------- 1 | FeatureA | 2016-02-01 | 1 | 2 | FeatureA | 2016-02-02 | 2 ...
0
votes
0answers
16 views

How to import multiple CSV files to a postgres table using pgadmin?

I'm looking to import a folder of over 100+ csv files into one table on PostgreSQL using pgadmin. I have the following script but is there a way to change the file path to all csvs? COPY schema.table ...
0
votes
1answer
18 views

Alter default privileges in postgres for other roles

Let's say I have a role called user1, user2 and postgres. Is it possible to alter the default privileges while logged in as postgres for objects created by user1 so that user2 can see them? This is ...
0
votes
1answer
31 views

Feasible to have thousands of foreign data wrappers in Postgres?

We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/...
1
vote
1answer
44 views

How to filter multiple many to many relationship based on multiple tags?

I've a scheme of two main tables: problem and tag and a relation (which is a many to many connector) table: problem_tags which excerpt of them is like: Problem table: ----+---------------------------...
0
votes
1answer
18 views

Count percentage of column with GROUP BY

Consider the following query: SELECT country , count(id) AS "count" FROM lead WHERE date_part('year', lead.since) = date_part('year', CURRENT_DATE) GROUP BY "country" ORDER BY "count" DESC ; ...
0
votes
0answers
18 views

PostgreSQL limiting number of rows for a certain condition

arena_stat_id | user_id | date_set | level | trophies ---------------+--------------------+----------------------------+-------+---------- 1 | ...

1
2 3 4 5
234