Postgresql Sequence Based on Another Column

Apply a sequence based on the value of another column

A quick example using dummy data:

create table age_seq_test(age int , fld_1 varchar, id integer);

insert into age_seq_test values (10, 'test'), (30, 'test2'), (20, 'test3');

select * from age_seq_test order by age;
 age | fld_1 |  id  
-----+-------+------
  10 | test  | NULL
  30 | test2 | NULL
  20 | test3 | NULL

BEGIN;

with t as 
   (select age, row_number() over (order by age) as rn from age_seq_test) 
update 
    age_seq_test AS ast 
set 
    id = t.rn + 999
from 
    t 
where  
    ast.age = t.age  ;

select * from age_seq_test order by age;
 age | fld_1 |  id  
-----+-------+------
  10 | test  | 1000
  20 | test3 | 1001
  30 | test2 | 1002


--COMMIT/ROLLBACK depending on what the SELECT shows.

PostgreSQL sequence connects to columns

Typically, sequences are created implicitly. With a serial column or (alternatively) with an IDENTITY column in Postgres 10 or later. Details:

• Auto increment table column

Sequences are separate objects internally and can be "owned" by a column, which happens automatically for the above examples. (But you can also have free-standing sequences.) They are incremented with the dedicated function nextval() that is used for the column default of above columns automatically. More sequence manipulation functions in the manual.

Details:

• Safely and cleanly rename tables that use serial primary key columns in Postgres?

Or you can use ALTER SEQUENCE to manipulate various properties.

Privileges on sequences have to be changed explicitly for serial columns, while that happens implicitly for the newer IDENTITY columns.

List down values that match a specific sequence in other column in PostgreSQL

you can use string_agg:

select Name , string_agg(Destination, '-' order by date) journey
from table
group by name 
having journey ilike '%Thailand-Japan-Canada%'

Updating postgres column with sequence of integers for unique pairs values

It is good that you looked for a similar question. But what did you actually try on your issue, you cannot just copy an answer from there and expect it to even relate to what you need. That just provides a format that may be applicable.



What you need is to identify a unique column or a unique set of columns to identify the specific rows, from there use row_number to assign the desired value for each unique value/set, finally match the generated/extracted identifier to get the desired result column. In the below the CTE used columns user_pk, team, assigned_at to identify specific rows and the targeted new column value. The main part then matched those column for update. (see demo)

with ord_num( user_pk, team, assigned_at, order_seq)  as 
     ( select user_pk, team, assigned_at
            , row_number() over (partition by  user_pk, team 
                                     order by  user_pk, team, assigned_at
                                )
         from user_team 
      )
update user_team ut
   set order_seq = 
       (select order_seq 
          from ord_num os 
         where (ut.user_pk, ut.team, ut.assigned_at) = 
               (os.user_pk, os.team, os.assigned_at)
       ) ;

NOTE: It is extremely poor practice to user order as a column name. It is both a Postgres and SQL Standard reserved word. While you can get away with it, the best outcome is just confusion, at worst processed fail, by doing the incorrect thing and not providing any message. Resulting in complete data corruption. Also applies to any reserved word.

Pivoting a column based on sort order but with same name in PostgreSQL

You can use row_number() and conditional aggregation. The column names need to be different though:

with mycte as (...) -- your cte here
select
    id,
    max(link) filter(where rn = 1) link1,
    max(link) filter(where rn = 2) link2,
    max(link) filter(where rn = 3) link3,
    max(link) filter(where rn = 4) link4
from (
    select c.*, row_number() over(partition by id order by position) rn
    from mycte c
) t
group by id

---

Related Topics