How to purposely duplicate specific rows in SELECT statement?

Question

I want to duplicate a specific teacher so that they appear twice in the output of a SELECT statement.

Teacher	Grade	District
Mr. Smith	3rd	West
Mrs. John	4th	South
Mr. Cameron	2nd	North
Mr. Cameron	2nd	North
Kirk Horn	1st	West

Desired result, after duplicating 'Mr. Cameron':

Teacher	Grade	District
Mr. Smith	3rd	West
Mrs. John	4th	South
Mr. Cameron	2nd	North
Mr. Cameron	2nd	North
Mr. Cameron	2nd	North
Mr. Cameron	2nd	North
Kirk Horn	1st	West

What would a SELECT statement look like - without creating a new a table?

I want to do something like this but without the INSERT:
https://dba.stackexchange.com/questions/142414/easiest-way-to-duplicate-rows

What is your `select` statement? In your first data above, is that from a table or are you inserting that into a table? — Isolated, Oct 04 '22 at 20:15
@ErgestBasha `union all` and just add a where clause for specifically Mr. Cameron? I think I want to do something like this but without the insert statement since the table returned is the result of a select statement. https://dba.stackexchange.com/questions/142414/easiest-way-to-duplicate-rows — mike_mussini, Oct 04 '22 at 20:26
Why is Mr. Cameron being duplicated? what's the logic behind it? — DannySlor, Oct 04 '22 at 20:33
@mike_mussini added an answer with example. Something like that , but you need union all which doesn't remove duplicates — Ergest Basha, Oct 04 '22 at 20:35

Erwin Brandstetter · Accepted Answer · 2022-10-04T21:00:48.087

If the table is big, and the filter is not selective and backed by an index, then this "trick" avoids a second sequential scan over the table - using PostgreSQL:

SELECT t.*  -- or your list of columns
FROM   test t
     , generate_series(1, CASE WHEN t.teacher = 'Mr. Cameron' THEN 2 ELSE 1 END);

fiddle

It's short syntax for a LATERAL join. See:

What is the difference between a LATERAL JOIN and a subquery in PostgreSQL? fiddle

If you need more copies, just replace '2' above.

Postgres has a hard time estimating the number of rows to expect with this construct, which may confuse query planning. Stu's variant (doing the same) is slightly more expensive, but easier to estimate for query planning. Syntax needs to be adapted for Postgres:

SELECT teacher, grade, district
FROM   test t
JOIN   LATERAL (VALUES (1),(2)) x(v) ON v = 1 OR teacher = 'Mr. Cameron';

fiddle

Ergest Basha · Answer 2 · 2022-10-04T20:37:03.140

You could use UNION ALL

select Teacher,Grade,District
from test
union all
select Teacher,Grade,District
from test 
where Teacher='Mr. Cameron'
order by Teacher;

https://dbfiddle.uk/rBpSL8NW

If you want to force/predict how many duplicate values for Mr. Cameron you will add ,try below query which add only one duplicate value limit 1

(select Teacher,Grade,District
 from test
 )
union all
(
select Teacher,Grade,District
from test 
where Teacher='Mr. Cameron'
limit 1 
  );

https://dbfiddle.uk/uUX5QD3F

How to purposely duplicate specific rows in SELECT statement?

2 Answers2