Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
15,861
questions
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 ...
- 11
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
...
- 99
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&...
- 43
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 ...
- 113
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"
┌────────────────────────────┬──────────────────────────┬───────────┬──────────┬───────────...
- 155
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 ...
- 163
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.
...
- 142
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,
...
- 123
-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 ...
- 131
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 ...
- 11
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:
...
- 13
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 ...
- 1
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('...
- 155