0

I have created an order-column and i want to update the current values that is in that table. 

CREATE TABLE public.publishroomcontacts
(
    id integer NOT NULL DEFAULT nextval('publishroomcontacts_id_seq'::regclass),
    publishroomid integer NOT NULL,
    contactorder integer NOT NULL,
    CONSTRAINT publishroomcontacts_pkey PRIMARY KEY (id),
    CONSTRAINT fk_publishroomcontacts_publishroom_id FOREIGN KEY (publishroomid)
        REFERENCES public.publishrooms (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)
WITH (
    OIDS = FALSE
);

What i want to do is to go through all rows, group by publishroomid and add a contactorder. So if there is 4 contacts with the same publishroomid whey will have contact order 1, 2, 3 and 4.

How do i do this?

EDIT:

enter image description here

Daniel Gustafsson
  • 1,725
  • 7
  • 37
  • 78
  • 1
    What is the ordering? What did you try? – Laurenz Albe Aug 14 '18 at 09:03
  • Do you need this [PostgreSQL: Documentation: 9.5: Sorting Rows](https://www.postgresql.org/docs/9.5/static/queries-order.html) ? In your example just add `ORDER BY publishroomid, contactorder` at the end of your `SELECT` query. – Chocolord Aug 14 '18 at 09:09
  • @LaurenzAlbe look at my edit! :) – Daniel Gustafsson Aug 14 '18 at 09:14
  • 1
    How about `ROW_NUMBER() OVER(PARTITION BY publishroomid, ORDER BY id) AS contactorder` ? And there is no need to store it, since you can always compute it, when needed. – joop Aug 14 '18 at 09:21
  • @joop can i update individual rows and move rows up and down in the order using that? :) – Daniel Gustafsson Aug 14 '18 at 09:23
  • What is the intended ordering? Why do you want to store it? – joop Aug 14 '18 at 09:25
  • So that i can change the order of a list in my GUI. @joop – Daniel Gustafsson Aug 14 '18 at 09:26
  • That is a nasty problem, with ugly solutions (search for `tabbing order`, or the like). Do you realise that this would make `{publishroomid, contactorder}` a candidate key, and that updates on the contactorder column would involve an update on a key field? – joop Aug 14 '18 at 09:31
  • there is no need for it to be a candiadate key since i have the ID column that is my unique id. – Daniel Gustafsson Aug 14 '18 at 09:33
  • https://stackoverflow.com/q/14085258/2235885 for a possible duplicate + solution, plus a list of possible duplicates. – joop Aug 14 '18 at 09:44

0 Answers0