Line Break Column Names

Question

I know that for text strings in PostgreSQL, line breaks are combined by adding an E or E character before the text:

 SELECT E'first\nsecond' 

leads to:

 first second 

But PostgreSQL also supports line breaks in column names - not sure why and how evil this practice is, but you can do the following:

 CREATE TABLE One("first\nsecond" text); CREATE TABLE Two("first second" text); 

If you are unsuccessful enough to work in one of them, you will find that while these queries work:

 SELECT "first\nsecond" from One; SELECT "first second" from Two; 

these do not:

 SELECT "first second" from One; SELECT "first\nsecond" from Two; 

My question is: is there a way in PostgreSQL that combines such differences, similar to the situation with column values?

I tried putting E in front of the column names "first\nsecond" , but it is not supported. Trying to put \r\n instead (I use Windows) gave me a third type of column name, which can only be requested as:

 SELECT "first\r\nsecond" FROM Third 

Answer 1

Column names are identifiers, and details about the syntax of identifiers are described below:

http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

TL DR : use the U&"..." syntax to enter non-printable characters into identifiers through their Unicode codes, and there is no way to unify CR,LF with LF only.

How to access a column in one row

We are allowed to use Unicode escape sequences in identifiers, so each of them works in the documentation:

 select U&"first\000asecond" from Two; 

if it's just a newline between two words.

What happens to the queries in the first table

The table is created using

 CREATE TABLE One("first\nsecond" text); 

Since the backslash character is not significant here, this column does not contain a new line. It contains first , followed by \ , then n , and then second . So:

  SELECT "first\nsecond" from One; 

works because it is the same as in CREATE TABLE

then

 SELECT "first second" from One; 

fails because there is a new row in this SELECT where the actual column name in the table has a backslash followed by n .

What happens to the queries in the second table

This is the opposite of the "One."

 CREATE TABLE Two("first second" text); 

A new row is taken verbatim and is part of the column. So,

 SELECT "first second" from Two; 

works because the new line exists exactly the same as in CREATE TABLE, with the new line embedded, whereas

 SELECT "first\nsecond" from Two; 

fails because earlier \n in this context does not mean a new line.

Carriage returns followed by Newline, or something strange

As mentioned in the comments and your editing, this could be a carriage return and a new line instead, in which case you should do the following:

 select U&"first\000d\000asecond" from Two; 

although in my test, pressing Enter in the middle of a psql column on Unix and Windows has the same effect: one new row in the column name.

To check what exact characters are in the column name, we can check them in hexadecimal format.

When you apply the create table from within psql to Unix for your example:

 CREATE TABLE Two("first second" text); select convert_to(column_name::text,'UTF-8') from information_schema.columns where table_schema='public' and table_name='two'; 

Result:

  convert_to ---------------------------- \x66697273740a7365636f6e64 

For more complex cases (e.g. non-ascii characters with multiple bytes in UTF-8), a more convenient query may help for readable code points:

 select c,lpad(to_hex(ascii(c)),4,'0') from ( select regexp_split_to_table(column_name::text,'') as c from information_schema.columns where table_schema='public' and table_name='two' ) as g; c | lpad ---+------ f | 0066 i | 0069 r | 0072 s | 0073 t | 0074 +| 000a | s | 0073 e | 0065 c | 0063 o | 006f n | 006e d | 0064