Question 88979 in PostgreSQL Question posted in
The official documentation can be found here.

Return string column with appended row number – Postgresql

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.

Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Below are two approaches to achieving the described results. The first uses a recursive common table expression (CTE) and is a close analog to loops in procedural languages. When working with databases, it is useful to break away from procedural thinking and instead embrace set based approaches. A set based approach is demonstrated in the second query.

    WITH RECURSIVE parms(field1, cnt) AS (
  VALUES ('Car', 4),
         ('Truck', 3)
),
cte(field1, cnt, n) AS (
  SELECT parms.field1, parms.cnt, 1 AS n
    FROM parms
   WHERE parms.cnt > 0
  UNION ALL
  SELECT cte.field1, cte.cnt, cte.n + 1 AS n
    FROM cte
   WHERE cte.n < cte.cnt
)
SELECT cte.field1 || '|' || cte.n::text AS result
  FROM cte
 ORDER BY cte.field1, cte.n;

    WITH parms(field1, cnt) AS (
  VALUES ('Car', 4),
         ('Truck', 3)
),
c AS (
  SELECT generate_series(1, MAX(parms.cnt)) AS n
    FROM parms
)
SELECT parms.field1 || '|' || c.n::text AS result
  FROM parms
    JOIN c ON c.n <= parms.cnt
 ORDER BY parms.field1, c.n;
    Login or Signup to reply.
  3. 0

    Let’s use a temporary table with integers until …. well basically the max number that will be needed, like (SELECT max(cnt) FROM mytable;), but let’s stick to 10 for now:

    with recursive abc as (
    select 1 as x 
    union all 
    select x+1 
    from abc 
    where x<10) 
select * from abc;

    Then you can do an INNER JOIN:

    with recursive abc as (
    select 1 as x 
    union all 
    select x+1 
    from abc 
    where x<10) 
select * 
from mytable
inner join abc on abc.x <= mytable.cnt
order by field1, x;

    or, if we really need the desired output:

    with recursive abc as (
    select 1 as x 
    union all 
    select x+1 
    from abc 
    where x<10) 
select CONCAT(field1,'|',x) as result 
from mytable
inner join abc on abc.x <= mytable.cnt
order by field1, x;

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search