Skip to main content

All Questions

Filter by
Sorted by
Tagged with
2 votes
2 answers
559 views

How to update a foreign key with random values from the referenced table?

In order to generate test data for a Postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'. How to do it? I ...
Edilson Vieira's user avatar
4 votes
1 answer
461 views

Concurrent update on two different tables fail

I'm using PostgreSQL with the following database schema: CREATE TABLE plans ( slug VARCHAR(500) PRIMARY KEY ); CREATE TABLE users ( id VARCHAR(16) PRIMARY KEY, org_id VARCHAR(16) NOT NULL ...
Valentin Nasraty's user avatar
3 votes
2 answers
13k views

Update with subquery referencing the main table

I'm trying to understand how to filter my subquery in context to the main query. Ultimately I'm trying to get the MAX value from the latest record BEFORE the date of the current record. Here is where ...
4Cast's user avatar
  • 31
0 votes
2 answers
425 views

Store value of subquery in update for later use

Trying to calculate % change of price. Query below works, but was hoping there was a way that I didn't have to call the same subquery first? I don't think WITH works here unless I'm mistaken. update ...
Nicole Staline's user avatar
4 votes
1 answer
6k views

Unexpected unique constraint violation during update

In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records ...
orange's user avatar
  • 153
0 votes
1 answer
41 views

Need help with correlated subquery

I have two PostgreSQL tables that each contain a column called date_of_incident. They each also have a column called incident_id and this is how the tables are related. I want to update all rows of ...
two4two's user avatar
1 vote
1 answer
475 views

Update rows in table with id from row before in PostgreSQL

Say I have a table like this: | id | grp | time_added | previous_id | |----|-------|------------|-------------| | 1 | 1 | 5 | null | | 2 | 1 | 8 | null | | ...
Hans's user avatar
  • 113
1 vote
1 answer
516 views

Update "oldest" row

I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n). I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
Tallboy's user avatar
  • 223
1 vote
2 answers
9k views

How to access row value from UPDATE sub query function?

I would need to use UPDATE like this: update offer set (bank, bank_id) = (cost, gid) from ( select cost, osm_points.gid from pgr_kdijkstraCost( 'SELECT gid as id, source, target, walk_cost as cost ...
Milan Muňko's user avatar