Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
1 vote
0 answers
11 views

PL/pgSQL select statement inside trigger returns null using where NEW

i'm creating a trigger that triggers on INSERT on a table, and i wish to log the structure of tables inserted so i wrote this Function CREATE OR REPLACE FUNCTION update_table_log_received() RETURNS ...
0 votes
1 answer
18 views

sorting multiple columns, using nulls first on one of the columns

Given a members table, having the following columns : accepted_at (datetime) updated_at (datetime NOT NULL) Given this query : select * from members orde by accepted_at DESC NULLS FIRST, updated_at ...
  • 165
0 votes
1 answer
32 views

Bad inner join performance on one-to-many relationship

I have a question regarding the postgres planner that I can't wrap my head around. I am trying to get the planner to look up a project ID by its unique key before running the other side of the query ...
-1 votes
1 answer
13 views

Postgres trigger has no effect despite running

I have a trigger that updates a count in table rssfeeds whenever an insert occurs on table articles. Here is what the function looks like: UPDATE rssfeeds SET total_articles = total_articles + 1 ...
4 votes
2 answers
78 views

Pair top-level key with nested value from jsonb document

Given the following data: create table datas (id int, data jsonb); insert into datas (id, data) values (1, '{"key1": {"foo": "bar1","some":"thing1&...
1 vote
1 answer
91 views

Postgres column limits

I have a PostgreSQL table which is quite large at the moment: 340 columns. This table is designed to hold the styling for a complex PDF. Over time I have made changes: dropped columns, added some new ...
  • 121
0 votes
0 answers
15 views

Enforce dynamic value correctly

I am working on quiz builder app with advanced features such as enforcing constraints on quizzes and/or quiz items. Here is the snippet of the ERD: violated_on represents the timestamp when this ...
  • 1
1 vote
2 answers
35 views

MySQL vs PostgreSQL for hash-based table partitioning

I'm planning an open-source a backend service whose primary job is to deliver sync messages between users' devices. I want the self-hosted and hosted versions of the service to share the same ...
0 votes
2 answers
9 views

Postgresql: Trigger is not working at times

I have a PostgreSQL trigger that is not firing sometimes, even though the status is always shown as "enabled". My trigger code is as follows: CREATE OR REPLACE FUNCTION audit_src_exhibit() ...
  • 1
0 votes
1 answer
21 views

Postgresql: Trigger is not working at times

I have a PostgreSQL trigger that is not firing sometimes, even though the status is always shown as "enabled". My trigger code is as follows: CREATE OR REPLACE FUNCTION audit_src_exhibit() ...
  • 1
0 votes
0 answers
20 views

Reduce the cost of query in postgresql partitioned table

I have a table t_dailylocationsdata in the PostgreSQL database with approx 1 billion rows. This table has date-wise data for the last 4-5 years for each location. The data for a given Location_id and ...
0 votes
0 answers
15 views

Postgresql stops logging in docker

I am using postgres 12.12 via the official docker image and it sometimes stops logging, seemingly randomly. I am trying to figure out what could be causing this. I'm running it on my development ...
  • 101
0 votes
0 answers
10 views

Postgres Search Bar filter Help

I am making a small personal project that is related to stocks. I basically want to make a search bar request that does the following. Lookup the database based on a search value that could be a ...
  • 101
0 votes
1 answer
17 views

Move timescaledb hypertable from a postgresql server to another one

I want to move a timescaledb hypertable that's on the terabytes order. I wanted to do something like the following: pg_dump -t "schema1"."table1" -h host1_ip -U username1 dbname1 | ...
  • 183
0 votes
0 answers
27 views

Index vs Partition vs Multiple Tables

I have a table with billions of records. So far we are indexing this table and we are thinking on partition this table based on a Key or creating multiple tables (one per each Key) to improve the ...
0 votes
1 answer
19 views

Why the new user can't login postgresql database?

postgres can login postgresql via my bash console: psql -U postgres I have created a new user with the such same password as postgres. postgres=# CREATE USER debian PASSWORD 'xxxxxx'; CREATE ROLE ...
  • 11
0 votes
0 answers
33 views

Choosing between Entity-Attribute-Value or JSONB representations

I must store multiple types of documents in my Postgres database. I know the schema of each particular document type, I know what fields will be present. So, in theory, I could create a separate table ...
0 votes
2 answers
45 views

What columns are needed to list the database, schema and table a record comes from?

I am writing queries which read identical tables across databases and schemas and I need the columns to include which schema and table a row comes from. While searching during the creation of this ...
  • 937
2 votes
1 answer
26 views

How to use window function over calculated column?

Table definition: Table "public.transactions" ┌────────────────────────────┬──────────────────────────┬───────────┬──────────┬───────────...
1 vote
0 answers
18 views

Enter psql without password requirement for user postgres

I have set PGPASSWORD in my .bashrc: export PGPASSWORD=mypass Now i can enter into psql with : psql -u postgres Set nopasswd in /etc/sudoers user ALL=NOPASSWD:/bin/su - postgres Then i toggle to ...
  • 11
1 vote
0 answers
22 views

PostgreSQL Logical Replication Trigger to add destination key column

I am using PostgreSQL Logical Replication to replicate data from multiple source databases into single one for analytical purposes. Data from the source database have the same schema and therefore ...
0 votes
1 answer
18 views

postgresl.conf vs conf.d/*: which has greater precedence

If the same parameter it's set on ./postgresql.conf and on ./conf.d/something.conf which one has preference or which one overwrites the other one?
  • 183
4 votes
1 answer
73 views

What are these single letter SQL keywords?

I am looking at list of SQL keywords here https://www.postgresql.org/docs/current/sql-keywords-appendix.html There are some single letter keywords there. I cannot find any information about them. ...
2 votes
1 answer
44 views

Avoiding deadlocks when locking multiple rows without using NOWAIT

In a couple of answers I've been advised that NOWAIT is necessary to prevent deadlocks, which has surprised me because it's not mentioned in the Postgres documentation on locking and deadlocks Given ...
  • 521
1 vote
0 answers
14 views

Getting error as "ERROR: permission denied for schema public" while executing a sql script in Postgres sql

I am using ubuntu 18.04 and running postgres sql in a docker container running on port 5432 on a VM powered by GCP. First of all I entered sudo -u postgres psql to enter into the database as ...
  • 11
0 votes
1 answer
23 views

PostgreSQL replication and index usage counts

Postgres collects index usage statistics in pg_catalog.pg_stat_all_indexes. Does anyone know if those statistics are identical across master and replica nodes when using “physical” wal replication?
  • 121
0 votes
1 answer
11 views

getting rows and adding a column to each with the timestamp of the next row, with postgres

I have the following object: CREATE TABLE IF NOT EXISTS {schemaName}.{tableName} ( id VARCHAR NOT NULL, ticker VARCHAR(16) NOT NULL, interval ...
  • 235
0 votes
0 answers
18 views

Create a group number for rolling month

Starting from a table with dates in descending order, starting from the current_date: date ---------- 2023-02-03 2023-02-02 2023-02-01 2023-01-31 ... I want to add a column that contains a group ...
  • 149
0 votes
0 answers
17 views

Copy a partition from one database to another in postgres

I have a range (day wise) partitioned table managed using pg_partman extension and replica of the same is created in a different database. I want to copy a partition specific of that day into another ...
0 votes
1 answer
18 views

Does it make sense to enlarge "shared_buffers" for writing Postgresql?

I'm using Postgresql-11, with a traditional HDD as the physical storing device. I need to optimize the performance for writing. I have changed the wal_buffers from -1 to 512MB, that means that ...
  • 203
0 votes
0 answers
12 views

Detailed error message from check constraints using exceptions?

I am trying to get a detailed error message when a check constraint is violated (in PostgreSQL). One way to achieve that is to define a check constraint using a UDF that throws exceptions. However, ...
0 votes
0 answers
8 views

Materialized view slow with Timescale

I have a table created like this: -- enable timescale plugin CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; -- create schema CREATE SCHEMA IF NOT EXISTS {schemaName}; -- create table CREATE ...
  • 235
1 vote
0 answers
18 views

Long-running postgres operation with temp tables accumulating locks over time

Hello people of the DBA stack exchange. I have a very large Postgres 12.2 database system I'm working on, lots of TB of data distributed between many machines. All main tables on these databases are ...
  • 11
0 votes
1 answer
17 views

How do I query only rows where multiple instances with the same ID exist with Postgres?

I have a table where I want to gather some statistics, but only for items that are repeated two or more times. To simply, here is how the table looks like: CREATE TABLE test ( id bigint, ...
-2 votes
0 answers
50 views

Can anyone comment on the validity of an optimization done on an upsert query by ChatGPT? [closed]

Here is the original query: INSERT INTO {schemaName}.{tableName} (id, ticker, interval, ts, price_top, ts_top, continuity_top, price_bottom, ts_bottom, continuity_bottom) VALUES (@id, @ticker, ...
  • 235
0 votes
1 answer
11 views

Refreshing development environment from backup that contains FDW in Postgres

Background: We recently added a foreign data wrapper from one of our Postgres (v14) databases to another Postgres (v14) database in a different system. It works fine in our production environment, but ...
  • 262
0 votes
0 answers
8 views

Ignore wrongly typed fields and update all possible fields in postgresql?

Is it possible to do a single query/insert statement that inserts a number of values - however the type of values might be wrong. If a type would be wrong it should just be "ignored". IE say ...
  • 423
2 votes
1 answer
27 views

PostgreSQL: How to use INOUT parameter of a Stored Procedure called in dynamic SQL?

I have a stored procedure with an INOUT parameter, which value is modified inside the SP. I need to call that SP, providing a variable as that parameter, and then use the modified variable. But the ...
0 votes
0 answers
11 views

Create a column containing group size in postgres

Given the following table: create table t(x integer); insert into t(x) values (1), (1), (2), (2), (2); I'd like to create an additional column which contains the size of the groups, the expected ...
  • 227
1 vote
0 answers
20 views

Time zone conversion in DMS from DB2 to Aurora Postgres

Challenge We are using AWS DMS to convert data from IBM DB2 into AWS Auroa Postgres IBM DB2 server is set to Eastern time (ET) zone. Timestamp related data inside DB2 is therefore in ET. Findings AWS ...
3 votes
1 answer
21 views

Will INSERT ON CONFLICT DO NOTHING lock the row in case of conflict?

In read-commited isolation level: If I understood correctly, in case of no pre-existing rows that would result in conflict, two concurrent transactions with INSERT ... ON CONFLICT DO NOTHING - which ...
  • 133
0 votes
1 answer
25 views

O(1) or O(log(n)) lookup time for records belonging to key on different table

I am looking for a way of accessing all appointments belonging to a particular user in an O(1) or an O(log(n)) fashion. I am currently using a where clause, to which my understanding runs in O(n). My ...
4 votes
1 answer
45 views

ALTER timestamptz column to timestamp without "converting" data?

It seems that altering a column from timestamp without time zone to timestamp with time zone converts the existing data based on the current session time zone at the time of the alter statement. See ...
-1 votes
0 answers
9 views

Allow SELECT, UPDATE Rights for a schema in a PSQL DATABASE

I have a Postgres DBMS with several databases. Among them there is one database called "store". Within this database there are several schemas and among them there is one called "sales&...
0 votes
0 answers
12 views

How to avoid new users to be able to access old and new databases in postgresql?

I am trying this query: ALTER DEFAULT PRIVILEGES IN SCHEMA public; REVOKE CONNECT ON DATABASE FROM PUBLIC; but I am unable to make it work in PostgreSQL. My goal is to avoid new users to be able to ...
  • 1
1 vote
1 answer
42 views

Optimize querying 100 million records- Postgres - indexes

Currently our queries are not performant. Table: (Item) 100 million rows. id: uuid user_id: bigInt item_type_id: uuid status: varchar start: date end: date Index on user_id, item_type_id Query: ...
0 votes
0 answers
19 views

Hierarchical query to select `children` from `children`

I have the following table mytable where id is a unique identifier at a given r (resolution) and children is an array of unique id's of r + 1. id r children[] 0 2 {0,2467972595799031808,...
  • 263
0 votes
0 answers
19 views

Formula issue in SQL Formula tab by using Postgrece syntax

I have given the formula as (0.9*0.1/CAST(SUB_AMOR_PER)) in Sql Formula tab. But it throws error as "syntax wrong". Can someone give the solution?
0 votes
0 answers
13 views

Aggregating unique values across 2 columns into one array (sql)

Imagine one table called exam in Postgres DB with 2 nullable columns called grade1 and grade2. Is it possible to aggregate all unique grades across both columns into one array without using UNION or ...
0 votes
1 answer
20 views

PostgreSQL INNER JOIN With Most Current Records

I am trying to create this VIEW: CREATE VIEW highlight_on_rss_feed AS SELECT highlight_on_article.title AS title, highlight_on_meta_description.meta_description AS description, concat('...

1
2 3 4 5
318