56 questions
2
votes
1
answer
97
views
Trigger update on Postgresql/PostGIS table
I am working in a Postgresql database with PostGIS.
I need to automatically populate a PostGIS column with the intersection of another column's geometry and a landmass polygon. The current trigger ...
-1
votes
1
answer
237
views
How to create a trigger in CloudSQL PostgreSQL that sends an HTTP POST request?
I have a table in a CloudSQL PostgreSQL database, and I want to create a trigger that sends an HTTP POST request whenever a new record is inserted into the table.
I know that PostgreSQL supports the ...
1
vote
0
answers
63
views
How would you ignore a trigger within another trigger with django-pgtrigger
I am updating tbla."field_2" from a trigger in tblb. Except tbla."field_2" is protected with a pgtrigger.ReadOnly trigger.
This works without the pgtrigger.ReadOnly trigger in TblA....
1
vote
2
answers
80
views
Retaining and inserting state into a postgres aggregation function
I have a case where I have a table "tab" that stores data (keyed by symbol and time) as well as a second table "summ" that stores the exponential weighted running average of the ...
- 1,502
3
votes
1
answer
946
views
The trigger with `BEFORE` or `AFTER` and `INSERT`, `UPDATE` or `DELETE` on a view doesn't work in PostgreSQL
I created, as shown below:
A table called person
A table called log, with 1 row inserted in it.
A view called my_v
A trigger function called my_func(), whose purpose is to increment num.log by 1.
A ...
- 42.8k
1
vote
1
answer
1k
views
Set default value of one column to another column [duplicate]
Is it possible in PostgreSQL to have a column's default value be equal to another column? This seems to be possible in SQLAlchemy and possibly other database frameworks, but ideally this would be done ...
- 703
0
votes
0
answers
20
views
PostgreSQL: deduplicate inserts, but preserve uniqueness for a column subset
I would like to keep a table of distinct places, with a restrictive UNIQUE (name, address) constraint.
When importing a new place, if it has a new (name, address) tuple I can insert it directly and ...
- 25.6k
-1
votes
1
answer
68
views
Why is the column value not persisting after Postgres function triggers after update?
I have a table called Projects where I need to set a certain column value after an update is executed and the record matches certain conditions. The function triggers correctly and there are no errors ...
- 53
1
vote
2
answers
435
views
PostgreSQL trigger race condition updating a balance table from transactions
I have a financial system where users have tokens and can add transactions. The system has to calculate the balance and mean acquisition price of each token. Data integrity is of utmost importance in ...
- 461
0
votes
2
answers
1k
views
Create notify trigger for specific JSON value in inserted row
I have a table with event entity
create table event_entity
(
id varchar(36) not null
constraint constraint_4
primary key,
details_json varchar(2550),
...
- 503
0
votes
1
answer
1k
views
How to select all inserted rows to execute an insert trigger with a stored procedure in postgresql?
I'm trying to set an "after insert" trigger that executes a procedure. The procedure would take all inserted rows in table A, group them by a column and insert the result in a table B. I ...
- 15
1
vote
1
answer
2k
views
Postgres statement-level trigger ERROR: transition tables cannot be specified for triggers with column lists
I have a table my_table with columns id (int), value (varchar), and modified (timestamp). I want to update the timestamp by the trigger each time I set a new value. I'm changing thousands of rows in ...
0
votes
2
answers
243
views
How to prevent recursion in trigger on UPDATE operation?
I have simple trigger:
CREATE OR REPLACE FUNCTION nmck_decrease_percent_calc() RETURNS TRIGGER AS
$BODY$
DECLARE
s_price integer;
BEGIN
SELECT "lotMaxPrice" into s_price FROM ...
- 9,977
0
votes
0
answers
527
views
PostgreSQL insert row with trigger function
I have a table (machines_data_paired) like this one:
timestamp
id
data
2022-07-01 17:23:13.437
1
{'foo': 'bar'}
2022-07-01 17:23:14.937
2
{'foo': 'bar'}
2022-07-01 17:23:15.437
1
{'foo': 'bar'}
When a ...
- 41
0
votes
0
answers
45
views
Creating SQL Functions
I've created the function, but have some mistakes. I understand the logic, but can't connect it.Maybe you will be able to help me to understand it.
Given database schema:
• Contacts (user ID, fname, ...
- 11
0
votes
2
answers
265
views
PostgreSQL OLD not working in after update statement level trigger
I'm trying to update course points by the sum of course's lessons points.
It is working perfectly if I do select particular course ID like this:
BEGIN
UPDATE course
SET points = (SELECT COALESCE(...
0
votes
1
answer
1k
views
PostgreSQL trigger function not working properly
I have a trigger function:
CREATE OR REPLACE FUNCTION Day_21_bankTriggerFunction()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
DECLARE
act VARCHAR(30);
BEGIN
SELECT account_number INTO act
DELETE FROM ...
- 23
0
votes
1
answer
630
views
Postgresql trigger not update - stack depth limit reached [duplicate]
everyone!
i want to do a very simple trigger that sees if some conditionals are 0 they set false on the same table, buy i am doing wrong and it s not updating
CREATE or REPLACE FUNCTION chequeo_datos()...
- 11
0
votes
2
answers
935
views
Postgres Trigger to INSERT UPDATE DELETE on similar derivative table
Description:
I am running postgresql 13
I have two tables under different schemas, t1 and t2.
t2 is derivative of t1 in the sense that they share all the same
columns and data, but t2 is always ...
- 81
0
votes
1
answer
353
views
How to execute same query in remote postgres server from inside "BEFORE TRIGGER"
I am planning to run exact same queries on the remote database server exactly when query executing on local server from inside BEFORE INSERT,UPDATE and DELETE TRIGGERs.
My use case is something like:
...
0
votes
1
answer
188
views
insert trigger to manipulate timestamps of existing rows
I have a table with 2 date columns
CREATE TABLE test
(
id serial PRIMARY KEY,
code integer NOT NULL,
eff_date timestamp default now(),
exp_date timestamp default '2025-12-31'
);
I want to ...
- 291
1
vote
1
answer
2k
views
Postgres: raise exception from trigger if column is in INSERT or UPDATE satement
I want to audit created_by, created_timestamp, modified_by, and modified_timestamp columns in my PostgreSQL table with triggers. Creating BEFORE INSERT and BEFORE UPDATE triggers to set these values ...
- 968
2
votes
2
answers
2k
views
How can I concat two strings to update a column in a postgres table using trigger function
I have user table and profile table. When a record is created in user table, it automatically create a record in profile table with the returned id.
The profile table is like:
create table app_public....
- 123
0
votes
1
answer
728
views
Setting NEW.column in "before update" trigger sets values from previous trigger call
I'm trying to create a before update trigger for a table. Basically, on each row update I need to set additional column on the same row on the same table.
However, for some reason, each trigger call ...
- 3,555
0
votes
1
answer
36
views
How to acheive a "stolen_at" field which is populated when the "status" field is updated? TRIGGER function and GENERATED ALWAYS AS attempts attached
How to update stolen_at such that it is populated with the current timestamp when status is updated from INSTOCK to STOLEN?
I've attempted a trigger based approached but it contains a bug:
SQL Error [...
- 9,035
0
votes
1
answer
240
views
Trigger placing a point in qgis to autocomplete a column by adding the next in an alphanumeric sequence in Postgresql
In QGIS, I have a table of points, and a table of areas. When I place a point in an area, I need the following to happen. Grab the ID of the area (called plat_page) the new point is being placed in. ...
- 135
1
vote
2
answers
454
views
Using ST_DWithin to get id of closest object within an area
I have a trigger function in pgAdmin4 that grabs the closest object to the start point and end point of a line on placement in QGIS. However, I want to not only grab the id of the closest object, but ...
- 135
0
votes
1
answer
168
views
Mixed SRID's in trigger stopping qgis from committing changes
I have a trigger that takes a line, grabs its ST_StartPoint and ST_EndPoint and then grabs the nearest point to those endpoints, and assigns a node_id to a column. This Fiddle shows the trigger as ...
- 135
1
vote
1
answer
166
views
Using Triggers in PGAdmin4 v5 to get the geom of a line being generated in QGIS
So I have a database of pipes that is visually coded in qgis. My goal is to add a trigger to PGAdmin4 v5 that uses ST_StartPoint() and ST_EndPoint() to get the end points of the polyline (the pipe) as ...
- 135
2
votes
1
answer
2k
views
How to insert stored functions and triggers in a Postgres container
I have a containerized Postgres database and I am trying to create a function and a trigger: a simple notification on a new row insertion.
It may seem stupid but I am unable to find how to write the ...
- 315
0
votes
1
answer
286
views
PostgreSQL trigger function to capture updated field
I am querying data from server to update fields in my local DB.
But, I have more than 100 columns hence I just want to know which column has new value(different than previous) so that I can use only ...
- 85
0
votes
0
answers
165
views
PostgreSQL Trigger on last deleted line
I created a trigger that updates the number of species counted from the "effectif" table to the "citation" table.
It works well except when I delete the last row, the calculation ...
- 13
0
votes
0
answers
161
views
PostgreSQL TRIGGER on WITH (UPDATE ..)UPDATE on 2 tables
I have an SQL query like this:
WITH whatever1 AS (
UPDATE
table1
SET
field1 = <some number>
WHERE
id = <id>
...
- 1,020
2
votes
1
answer
1k
views
How to write a generic update trigger for PostgreSQL view without known columns?
I'm using a PostgreSQL view to simulate a legacy table that has since been split into two separate tables, so we can maintain backwards compatibility with a range of services. The goal is essentially ...
- 6,189
2
votes
1
answer
3k
views
How to use the same trigger function for insert/update/delete triggers avoiding the problem with new and old objects
I am looking for an elegant solution to this situation:
I have created a trigger function that updates the table supply with the sum of some detail rows, whenever a row is inserted or updated on ...
- 1,337
0
votes
0
answers
260
views
Automatically copy schema changes to another schema in postgresql
I have a database that is not well designed. To design a new database, I came up with the idea of having 4 copies of each schema (A1, Backup_A1, History_A1, Test_A1). So if a row is added/updated in ...
- 11
0
votes
2
answers
947
views
PostgreSQL create trigger: Syntax error on insert action
I want to create a trigger in PostgreSQL.
I have a products table and a categories table. If a new tuple is inserted into the products table with a category_id, which does not exist yet, I want to ...
- 11
5
votes
1
answer
9k
views
Run Python script from PostgreSQL function
I'm facing a "little" problem regarding executing a Python script every time is an update or insert action on the PostgreSQL table.
This script will extract and write to a file updated or ...
- 73
2
votes
1
answer
2k
views
Get Table Name of PostgreSQL Event Trigger
I have multiple tables in a PostgreSQL 9.6 database which changes I want to monitor and handle in an extern application.
Handling only data changes wasn't hard, but now I want to monitor structure ...
- 183
0
votes
0
answers
175
views
Instead of Insert Trigger to prevent from duplicate record in my database
Actually my issue is that,
I have an Azure time trigger function that hitting an API on a daily basis and fetching data from that API and mapping that data through my model classes and inserting that ...
0
votes
1
answer
556
views
Postgres Function Trigger Sequence Dependency Error
I have the following functions and trigger with sequence setup:
I want to create a function and trigger that anytime I add a new row to STRATEGY_SITES table, the 'SITE_NUM' field will have the new ...
- 3
2
votes
3
answers
813
views
PostgreSQL copy to a new table with one extra column. How?
I am trying to make a forum database and in this database I have a trigger. After the Insert it should copy the values of the other table into the new one, but should also make a new column showing ...
- 23
0
votes
2
answers
40
views
How will it be in PostgreSQL? [closed]
i have code for MySQL but i need to PostgreSQL. Can you help me convert from MySQL to PostgreSQL?
Create TRIGGER date_reservation
BEFORE INSERT ON reservation
FOR EACH ROW
BEGIN
IF
NEW....
- 11
1
vote
1
answer
2k
views
Postgres - move rows to different table
Using postgres 11 I would like to automatically move rows from one table to another. I have setup a query, trigger function, and trigger but my test inserts fail with '0 0' when the trigger is enabled....
- 837
1
vote
1
answer
247
views
How to register trigger in PostgreSQL?
I have trigger and procedure. I want to be sure that string saved to database will have no '-' characters.
After executing
UPDATE interesant SET interesant_nip = '555-555-5555'
I get error
...
- 57
0
votes
2
answers
4k
views
Update another table through a trigger where new value is the result of a SELECT query
I have these tables:
Users
Skills (name - string, count - integer)
Has_skills (skill_id - skills.id, user_id users.id)
Has_skills is a many to many table between the first two through these FK:
...
- 101
4
votes
1
answer
2k
views
Modify OLD to be returned by DELETE in postgresql trigger function
I have a trigger function in postgresql which will insert rows in the audit table on INSERT, UPDATE and DELETE operations. In my tables, there is a column called audit_id and I need to write the ID of ...
- 43
0
votes
2
answers
21
views
Keep a table column at the earliest related value of a column in another table
I have two related tables. Lets call them event_types and occurrences with the following columns:
event_types
-------------
id VARCHAR(30)
name VARCHAR(50)
first_event Timestamp
and
occurrences
----...
- 607
0
votes
1
answer
67
views
PostgresSQL : Update Certain Column in Table-B After Certain Column in Table-B is Updated
I have 2 table in Postgres, let's say Category and Thread with relation one-to-many
Category
id | title | created_at | deleted_at
-----+-------+------------+-----------
Thread
id | category_id | ...
- 71
0
votes
0
answers
142
views
PostgreSQL: Execute a trigger after all associated rows are inserted/updated
I need to track data changes in DB and execute a trigger. The base records have a few associated (many-to-one) records in different tables. When any of the records (base or associated) are updated/...
- 7,084