How to create a huge string in Postgresql

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?

Answer

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?