I am trying to update a text field in a table of my postgresql database.
UPDATE public.table SET long_text = 'First Line' + CHAR(10) + 'Second line.' WHERE id = 19;
My intended result is that the cell will look like this:
First Line Second line
The above syntax returns an error.
You want chr(10) instead of char(10).
Be careful with this, because that might be the wrong newline. The "right" newline depends on the client that consumes it. Macs, Windows, and Linux all use different newlines. A browser will expect <br />.
It might be safest to write your update using an escape string for PostgreSQL 9.1+. But read the docs linked below.
UPDATE public.table
SET long_text = E'First Line\nSecond line.'
WHERE id = 19;
The default value of 'standard_conforming_strings' is 'on' in 9.1+.
show standard_conforming_strings;
Use a literal newline (if standard_conforming_strings = on, i.e. you're on a recent PostgreSQL):
UPDATE public.table
SET long_text = 'First Line
Second line.'
WHERE id = 19;
or you can use an escape:
UPDATE public.table
SET long_text = E'First Line\nSecond line.'
WHERE id = 19;
PostgreSQL:
varchar + varchar = ERROR. \ r and \ n is not anywhere that works, for greater compatibility use:
Corret:
UPDATE public.table SET
long_text = concat('First Line',CHR(13),CHR(10),'Second line.')
WHERE id = 19;
In my version of postgres, \n didnt work for line break and i used \r\n instead, like this:
UPDATE public.table
SET long_text = E'First Liner\r\nSecond line.'
WHERE id = 19;
The above solutions are working find but by just entering "Enter" also you can achieve this.
UPDATE public.table
SET long_text = 'First Line
Second line.'
WHERE id = 19;
