Search code examples
sqlpostgresqlrow-number

Return string column with appended row number

I have a need to get the max number of records from a table and concatenate that number at the end of the field name, starting at 1 and ending at the max number of records:

field1 cnt
Car 4
Truck 3

The final results need to be exportable in the format:

Car|1
Car|2
Car|3
Car|4
Truck|1
Truck|2
Truck|3

I cant use RAISE as I need more than a message in this instance. It has to be data output so the results can be used downstream.

Solution

  • If that's your table (matching your description):

    CREATE TABLE tbl1 (
  tbl_id serial PRIMARY KEY
, field1 text
);

    Then row_number() works:

    SELECT field1 || '|' || row_number() OVER (PARTITION BY field1)
FROM   tbl1;

    If that's your table (your sample data):

    CREATE TABLE tbl2 (
  tbl_id serial PRIMARY KEY
, field1 text
, cnt int
);

    Then generate_series() does the job:

    SELECT field1 || '|' ||  g
FROM   tbl2, generate_series(1, cnt) g;

    fiddle

    Neither loop nor recursion are needed either way.