Re: new line in psotgres

On 10/11/13 09:18, Gavin Flower wrote:
> On 10/11/13 02:38, Alban Hertroys wrote:
>> On Nov 9, 2013, at 12:08, abdullatheef<latheefvkpadi(at)gmail(dot)com> wrote:
>>
>>> >you can include newline in postgre using literal E
>>> >
>>> >create table table (text varchar(50));
>>> >insert into Table (text) values (E'This is the first part \\n And this is
>>> >the second');
>> Or like this:
>> development=> begin;
>> BEGIN
>> development=> create table t1 (text text);
>> CREATE TABLE
>> development=> insert into t1 values ('This is line 1.
>> development'> This is line 2.');
>> INSERT 0 1
>> development=> select * from t1;
>> text
>> -----------------
>> This is line 1.+
>> This is line 2.
>> (1 row)
>>
>>
>> Alban Hertroys
>> --
>> If you can't see the forest for the trees,
>> cut the trees and you'll find there is no forest.
>>
>>
>>
>> -- Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
> The second methods have different characteristics...
>
> -- test.sql
> CREATE TABLE t1
> (
> id SERIAL PRIMARY KEY,
> payload text
> );
>
> -- method 1
> INSERT INTO t1
> (payload)
> VALUES
> (E'This is the first part \\n And this is the second');
>
> -- method 2
> INSERT INTO t1
> (payload)
> VALUES
> ('This is line 1.
> This is line 2.');
>
> TABLE t1;
>
> ######## results:
>
> gavin=> \i test.sql
> DROP TABLE
> psql:SQL.sql:8: NOTICE: CREATE TABLE will create implicit sequence
> "t1_id_seq" for serial column "t1.id"
> psql:SQL.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create
> implicit index "t1_pkey" for table "t1"
> CREATE TABLE
> INSERT 0 1
> INSERT 0 1
> id | payload
> ----+--------------------------------------------------
> 1 | This is the first part \n And this is the second
> 2 | This is line 1. +
> | This is line 2.
> (2 rows)
>
>
> Cheers,
> Gavin
I had another thought...

I redid the above, and included at test with 'E' & '\n' - just one slash
before the 'n'. The other changes are mostly cosmetic.

So now we have 2 methods that produce identical output (ignoring the id).

/*
* Split lines text values
*
* splitline01.sql
*/
DROP TABLE IF EXISTS t1;

CREATE TABLE t1
(
id SERIAL PRIMARY KEY,
payload text
);

-- method 1
INSERT INTO t1
(payload)
VALUES
(E'This is the first part \\nAnd this is the second');

-- method 2
INSERT INTO t1
(payload)
VALUES
(E'This is the first part \nAnd this is the second');

-- method 3
INSERT INTO t1
(payload)
VALUES
('This is the first part
And this is the second');

TABLE t1;

##################################

gavin=> \i splitline01.sql
DROP TABLE
psql:splitline01.sql:12: NOTICE: CREATE TABLE will create implicit
sequence "t1_id_seq" for serial column "t1.id"
psql:splitline01.sql:12: NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "t1_pkey" for table "t1"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
id | payload
----+--------------------------------------------------
1 | This is the first part \n And this is the second
2 | This is the first part +
| And this is the second
3 | This is the first part +
| And this is the second
(3 rows)

Cheers,
Gavin