How to Insert a Line Break in a String PostgreSQL ?

In PostgreSQL, managing strings efficiently is crucial, especially when dealing with VARCHAR and NVARCHAR data types. One common challenge developers face is inserting line breaks within these strings. In this article, we’ll explore different approaches to tackle this issue, allowing us to format our strings effectively.

How to Insert a Line Break in PostgreSQL?

Inserting line breaks into VARCHAR or NVARCHAR strings in PostgreSQL poses a challenge due to the lack of a straightforward built-in function for this purpose. We can insert a line break into a string using various methods in PostgreSQL. We will discuss various methods along with the examples and so on. The Most Widely Used methods are:

• Method 1: Use Escape Strings
• Method 2: Put a Multiline String
• Method 3: Use the CHR function
• Method 4: Use the standard_conforming_strings flag

Before understanding the issue with inserting newline, let us learn about strings in PostgreSQL. Strings are collections of characters enclosed in single quotes and considered constant values. There are various data types for strings in PostgreSQL.

• CHAR
• VARCHAR
• TEXT

Setting Up Environment for insert a line break in a PostgreSQL

We will create a table representing animals in a zoo.

CREATE TABLE zoo(
  ID SERIAL,
  NAME VARCHAR(20),
  TYPE VARCHAR(20),
  ABOUT VARCHAR(50)
);

Note: Serial is used to define a column as an auto-increment column. Here the ID column will automatically increment by 1 each time a value is inserted.

Let’s insert some animals into the zoo.

INSERT INTO zoo(NAME, TYPE, ABOUT) 
VALUES 
  ('tel', 'Giraffe', 'Long Neck.'), 
  ('fol', 'Cow', 'It moos a lot.');
  
SELECT * FROM zoo;

Output:

Output

Explanation: So we have a zoo with two animals. Now let’s insert some more animals and see how to insert strings with newline in them.

1. Use Escape Strings

We can enable the use of backslash (\) denoted special characters in PostgreSQL using escape strings. Escape strings are denoted by putting the letter E(upper or lower case) in front of the quotes. Let’s insert a line break embedded string into the table.

Query:

INSERT INTO zoo (NAME, TYPE, ABOUT) VALUES 
  ('ulo', 'Frog', E'Green toad.\nEating mosquitoes');
  
SELECT * FROM zoo;

Output:

Output

Explanation: We inserted a row into the table with the about string having a newline in it.

Note: While Escape strings help to recognize backslash embedded special characters, we cannot simply put a backslash in it as it always try escape the next character. To put a backslash in an escape string, escape it by putting another backslash in front of it i.e. double backslash (//) will produce a single backslash in an escape string.

2. Put a Multiline String

We can put a newline in a string by pressing enter and making it multiline. This works fine and the final string inserted into the table as a multiline string. See the following example:

INSERT INTO zoo(NAME, TYPE, ABOUT) VALUES
  ('wic', 'Crow', 'A greyish one.
Is an omnivore.');

SELECT * FROM zoo;

Output:

Output

Explanation: Here the about column for the crow contains a multiline string with an embedded newline.

3. Use CHR Function

We can insert multiline strings into the table using CHR function as well. The CHR function produces a character from an ASCII code or the Unicode code point. The ASCII value of `\n` is 10. We will concat the character produced by the CHR function with the string to put a newline into it. Let’s see the following example:

INSERT INTO zoo(NAME, TYPE, ABOUT) VALUES
  ('bti', 'Snake', CONCAT('A black mamba.', CHR(10), 'It is very poisonous.'));
  
SELECT * FROM zoo WHERE id IN (1,2,5);

Output:

Output

Explanation: The record with snake has a multiline string in the about column. The CHR function produced a newline character which was concatenated with the string later on.

4. Use standard_conforming_strings Flag

Before version 9.1 this flag is was off by default. When this flag is off PostgreSQL, recognizes backslash in both normal and escape strings. We can manually turn this flag off and insert line-break into normal strings. Let’s see the following example:

SET standard_conforming_strings = off;
INSERT INTO zoo(NAME, TYPE, ABOUT) VALUES
  ('zeo', 'Elephant', 'Big Trunk.\nBig Tusks.');
  
SELECT * FROM zoo WHERE id IN (1,2,6);

Output:

Output

Explanation: This method is mainly for compatibility with legacy code that still run on the fact that we can directly put `\n` into normal strings. However, there is no harm in using this method and it will run on any version.

Conclusion

Overall in PostgreSQL we have learnt about how to insert a newline character into table. We cannot use the famous `\n` character to put newlines into strings in PostgreSQL. Instead, we need to use other methods to insert newlines into strings. The first is to use Escape quoted strings as they allow to use backslash to escape characters. The second is to use multiline strings and simply press enter to insert newline into the string. The third is to use the CHR and CONCAT function to insert newline by using its ascii value. The fourth is to set standard_conforming_strings flag to off and use escape in normal strings. In total, it is pretty easy to insert newline into a string in PostgreSQL and can be done by any of the methods above.