Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
11,684
questions
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 | ...
