Postgresql:Generate Sequence

Question

The query below generates a line of DNA sequence

prepare dna_length(int) as
with t1 as (select chr(65) as s union select chr(67) union select chr(71) union select chr(84) ),

 t2 as ( select s, row_number() over() as rn from t1),

 t3 as ( select generate_series(1,$1) as i,round(random() * 4 + 0.5) as rn ),

 t4 as ( select t2.s from t2 join t3 on (t2.rn=t3.rn))

select array_to_string(array(select s from t4),'') as dna ;

execute dna_length(20);

Result - dna CCCGGTTCTCCCCATGCAGG

I will like to generate 10 random DNA SEQUENCES

Gordon Linoff · Accepted Answer · 2020-06-24 18:54:04Z

1

Something like this?

select x, string_agg((array['A', 'C', 'G', 'T'])[1 + floor(random() * 4)], '')
from generate_series(1, 20, 1) gsn(n) cross join
     generate_series(1, 10, 1) gsx(x)
group by x

answered Jun 24, 2020 at 18:54

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Belayer · Accepted Answer · 2020-06-25 01:14:33Z

Having worked with DNA content databases for quite some, and the scientists, that like to play with them (sequences that is), I recommend a slight extension to the query by @GordonLinoff. Hide your implementation behind a function which takes 2 parameters: the length of the sequence and the number of sequences desired. You can then get any (reasonable) sequence length and any (reasonable) number of them.

create or replace 
function dna_sequence(
         sequence_length     integer
        ,number_of_sequences integer default 1 )
 returns table (dna_strand text) 
 language sql
as $$ 
   select string_agg((array['A', 'C', 'G', 'T'])[1 + trunc(random() * 4)], '') 
     from generate_series(1, sequence_length , 1) gsn(n)  
    cross join generate_series(1, number_of_sequences, 1) gsx(x)
    group by x    
$$;

-- test
select *
  from dna_sequence(20,10) ;

select *
  from dna_sequence(250) ;
 
select *
  from dna_sequence(20,100) 
 where position ('AAA' in dna_strand) > 0;

NOTE: The query in the above function is quite literally copy/paste Gordon Linoff's original. Then modified with only 2nd parameter changed in each generate series to the appropriate parameter value.

Collectives™ on Stack Overflow

Postgresql:Generate Sequence

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related