PostgreSQL Naming Rules

On 28 October 2011 12:49, Robert Buckley <robertdbuckley@yahoo.com> wrote:

Hi,

according to this article
http://www.informit.com/articles/article.aspx?p=409471, the naming of
tables, and fields is restricted to 63 characters and must start with an
underscore or letter. Nothing is however said about in which character set.

Am I allowed to name a table field < Änderung_1 >. The Ä is a german letter
contained within the UTF8 character set.

yours,

Rob

The simplest answer is: just check it.

however this works for me:

create table "Änderung_1" (i integer);
select * from "Änderung_1";

regards
Szymon

Robert Buckley, 28.10.2011 12:49:

according to this article
http://www.informit.com/articles/article.aspx?p=409471, the naming of
tables, and fields is restricted to 63 characters and must start with
an underscore or letter. Nothing is however said about in which
character set.

Am I allowed to name a table field < Änderung_1 >. The Ä is a german
letter contained within the UTF8 character set.

It does work, but I have had some tools (JDBC based) that choked on those tables. And I had some troubles running pg_dump/pg_restore with those tables. But as I did that merely out of curiosity, I didn't bother to find a way on how to deal with them.

Thomas

----- Weitergeleitete Message -----
Von: Robert Buckley <robertdbuckley@yahoo.com>
An: Szymon Guz <mabewlun@gmail.com>
Gesendet: 13:23 Freitag, 28.Oktober 2011
Betreff: Re: [GENERAL] PostgreSQL Naming Rules

Thanks,

I tried importing a table and I got some errors regarding Character sets. The error suggested I should use Latin1 instead of UTF8. I tried both, but I still keep getting the error. I looked at the field names and found german letters in the names hence the question here in the forum.

So in principle it is not a field name problem, and I have to look elsewhere.

thanks,

rob

________________________________
Von: Szymon Guz <mabewlun@gmail.com>
An: Robert Buckley <robertdbuckley@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Gesendet: 13:15 Freitag, 28.Oktober 2011
Betreff: Re: [GENERAL] PostgreSQL Naming Rules

On 28 October 2011 12:49, Robert Buckley <robertdbuckley@yahoo.com> wrote:

Hi,

according to this article http://www.informit.com/articles/article.aspx?p=409471, the naming of tables, and fields is restricted to 63 characters and must start with an underscore or letter. Nothing is however said about in which character set. 

Am I allowed to name a table field < Änderung_1 >. The Ä is a german letter contained within the UTF8 character set.

yours,

Rob

The simplest answer is: just check it.

however this works for me:

create table "Änderung_1" (i integer);
select * from "Änderung_1";

regards
Szymon

Robert Buckley wrote:

according to this article http://www.informit.com/articles/article.aspx?p=409471, the naming of
tables, and fields is restricted to 63 characters and must start with an underscore or letter. Nothing
is however said about in which character set.

Am I allowed to name a table field < Änderung_1 >. The Ä is a german letter contained within the UTF8
character set.

You can, but it's a really bad idea to have non-ASCII names.

Yours,
Laurenz Albe

On Friday, October 28, 2011 3:49:58 am Robert Buckley wrote:

Hi,
according to this
article http://www.informit.com/articles/article.aspx?p=409471, the naming
of tables, and fields is restricted to 63 characters and must start with
an underscore or letter. Nothing is however said about in which character
set.

Am I allowed to name a table field < Änderung_1 >. The Ä is a german letter
contained within the UTF8 character set.

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

"SQL identifiers and key words must begin with a letter (a-z, but also letters
with diacritical marks and non-Latin letters) or an underscore (_). Subsequent
characters in an identifier or key word can be letters, underscores, digits
(0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers
according to the letter of the SQL standard, so their use might render
applications less portable. The SQL standard will not define a key word that
contains digits or starts or ends with an underscore, so identifiers of this form
are safe against possible conflict with future extensions of the standard. "

yours,

Rob

--
Adrian Klaver
adrian.klaver@gmail.com

On 10/28/2011 06:49 PM, Robert Buckley wrote:

according to this article
http://www.informit.com/articles/article.aspx?p=409471, the naming of
tables, and fields is restricted to 63 characters and must start with an
underscore or letter. Nothing is however said about in which character set.

Am I allowed to name a table field < �nderung_1 >. The � is a german
letter contained within the UTF8 character set.

It'll work, but sadly you will at some point run into badly written
tools that mangle anything but 7-bit-ascii text.

psql on the Windows command line needs some extra love to behave
correctly; you may have to issue a chcp command to change the console to
unicode before starting psql if you're working with data that cannot be
represented in the charset your version of Windows uses. That won't be
an issue for umlauts etc, but if you wanted (say) hebrew table names on
a German windows you'd probably have to change the codepage.

Other than that, PostgreSQL and its tools should be fine. You are likely
to run into badly written tools elsewhere that mangle anything that
isn't 7-bit ascii, though, so be careful.

In general, if PostgreSQL doesn't like a table identifier you can
"double quote" it to get it to accept it. Table identifiers in
double-quotes also have their case preserved rather than automatically
being lower-cased like unquoted identifiers. Because the downcasing
rules for anything except 7-bit ascii are less than clear, I'd recommend
double-quoting all identifiers that use characters outside that range.

--
Craig Ringer