1

For testing / debugging purposes, I need to get an enormous string into a field for one of my records. Doesn't matter what the string is. Could be a million "*"s or the contents of Moby Dick.... whatever.

I'm not able to save a string so large via the app's UI because it crashes the browser. I'd like to write an SQL query to generate the massive string. Something like this:

UPDATE my_table SET text_field = <HUGE STRING CREATION> WHERE id = 42

The part I'm not sure how to do is <HUGE STRING CREATION>. I know I can concatenate strings with || but is there an SQL way to do that in a loop?

emersonthis
  • 31,843
  • 54
  • 200
  • 342

4 Answers4

7

Create your own lipsum function.

create or replace function lipsum( quantity_ integer ) returns character varying
    language plpgsql
    as $$
  declare
    words_       text[];
    returnValue_ text := '';
    random_      integer;
    ind_         integer;
  begin
  words_ := array['lorem', 'ipsum', 'dolor', 'sit', 'amet', 'consectetur', 'adipiscing', 'elit', 'a', 'ac', 'accumsan', 'ad', 'aenean', 'aliquam', 'aliquet', 'ante', 'aptent', 'arcu', 'at', 'auctor', 'augue', 'bibendum', 'blandit', 'class', 'commodo', 'condimentum', 'congue', 'consequat', 'conubia', 'convallis', 'cras', 'cubilia', 'cum', 'curabitur', 'curae', 'cursus', 'dapibus', 'diam', 'dictum', 'dictumst', 'dignissim', 'dis', 'donec', 'dui', 'duis', 'egestas', 'eget', 'eleifend', 'elementum', 'enim', 'erat', 'eros', 'est', 'et', 'etiam', 'eu', 'euismod', 'facilisi', 'facilisis', 'fames', 'faucibus', 'felis', 'fermentum', 'feugiat', 'fringilla', 'fusce', 'gravida', 'habitant', 'habitasse', 'hac', 'hendrerit', 'himenaeos', 'iaculis', 'id', 'imperdiet', 'in', 'inceptos', 'integer', 'interdum', 'justo', 'lacinia', 'lacus', 'laoreet', 'lectus', 'leo', 'libero', 'ligula', 'litora', 'lobortis', 'luctus', 'maecenas', 'magna', 'magnis', 'malesuada', 'massa', 'mattis', 'mauris', 'metus', 'mi', 'molestie', 'mollis', 'montes', 'morbi', 'mus', 'nam', 'nascetur', 'natoque', 'nec', 'neque', 'netus', 'nibh', 'nisi', 'nisl', 'non', 'nostra', 'nulla', 'nullam', 'nunc', 'odio', 'orci', 'ornare', 'parturient', 'pellentesque', 'penatibus', 'per', 'pharetra', 'phasellus', 'placerat', 'platea', 'porta', 'porttitor', 'posuere', 'potenti', 'praesent', 'pretium', 'primis', 'proin', 'pulvinar', 'purus', 'quam', 'quis', 'quisque', 'rhoncus', 'ridiculus', 'risus', 'rutrum', 'sagittis', 'sapien', 'scelerisque', 'sed', 'sem', 'semper', 'senectus', 'sociis', 'sociosqu', 'sodales', 'sollicitudin', 'suscipit', 'suspendisse', 'taciti', 'tellus', 'tempor', 'tempus', 'tincidunt', 'torquent', 'tortor', 'tristique', 'turpis', 'ullamcorper', 'ultrices', 'ultricies', 'urna', 'ut', 'varius', 'vehicula', 'vel', 'velit', 'venenatis', 'vestibulum', 'vitae', 'vivamus', 'viverra', 'volutpat', 'vulputate'];
    for ind_ in 1 .. quantity_ loop
      ind_ := ( random() * ( array_upper( words_, 1 ) - 1 ) )::integer + 1;
      returnValue_ := returnValue_ || ' ' || words_[ind_];
    end loop;
    return returnValue_;
  end;
$$;

And then use it, for example, to generate 50 words.

select lipsum( 50 );
Alejandro Salamanca Mazuelo
  • 1,063
  • 15
  • 18
4

You can use the repeat() function; quoting from the Postgres String Functions and Operators documentation:

Function: repeat(string text, number int)
Return Type: text
Description: Repeat string the specified number of times 

testdb=# SELECT REPEAT('SQL', 3);
  repeat
-----------
 SQLSQLSQL
(1 row)

You can also use a custom function to generate a random string with a defined lengh, for that refer to this question: How do you create a random string that's suitable for a session ID in PostgreSQL?

Martijn Pieters
  • 963,270
  • 265
  • 3,804
  • 3,187
Haytem BrB
  • 1,518
  • 3
  • 15
  • 23
0

Step 1.- Install https://postgresql-anonymizer.readthedocs.io/

Step 2.- For TEXT and VARCHAR columns, you can use the classic Lorem Ipsum generator:

anon.lorem_ipsum() returns 5 paragraphs
anon.lorem_ipsum(2) returns 2 paragraphs
anon.lorem_ipsum( paragraphs := 4 ) returns 4 paragraphs
anon.lorem_ipsum( words := 20 ) returns 20 words
anon.lorem_ipsum( characters := 7 ) returns 7 characters
Jhonny Ramirez Zeballos
  • 1,153
  • 9
  • 12
-3

I always prefer to use Lorem Ipsum text rather than complete garbage. I find it much easier to look at rather than some garbage or some word repeated numerous times. You can find many generators on line that can give you as long a block of text as you want. It isn't going to be clean and will include quotes and other things you may need to deal with which may or may not be a benefit for your testing.

For example: http://www.lipsum.com/

Evan Steinbrenner
  • 247
  • 1
  • 7