Thread: nvarchar notation accepted?

nvarchar notation accepted?

From
Jaime Casanova
Date:
Hi,

i migrate a ms sql server database to postgres and was trying some
queries from the application to find if everything works right...
when i was looking to those queries i found some that has a notation
for nvarchar (ej: campo = N'sometext')
i was expecting those to fail but this actually works, is that fine? i
know, we can use E'' strings but N'' ones are no where documented, so
can i rely on those or i have to change those strings?

"""
create table t1_nvarchar(col1 text);
insert into t1_nvarchar values (N'texto');
"""

--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL


Re: nvarchar notation accepted?

From
Takahiro Itagaki
Date:
Jaime Casanova <jaime@2ndquadrant.com> wrote:

> i migrate a ms sql server database to postgres and was trying some
> queries from the application to find if everything works right...
> when i was looking to those queries i found some that has a notation
> for nvarchar (ej: campo = N'sometext')

Do you have documentation for N'...' literal in SQLServer?
Does it mean unicode literal? What is the difference from U& literal?
http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html

PostgreSQL doesn't have nvarchar types (UTF16 in MSSQL), and only
have mutlti-tyte characters. So I think you can remove N and just
use "SET client_encoding = UTF8" in the cases.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center




Re: nvarchar notation accepted?

From
Tom Lane
Date:
Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> writes:
> Jaime Casanova <jaime@2ndquadrant.com> wrote:
>> i migrate a ms sql server database to postgres and was trying some
>> queries from the application to find if everything works right...
>> when i was looking to those queries i found some that has a notation
>> for nvarchar (ej: campo = N'sometext')

> Do you have documentation for N'...' literal in SQLServer?
> Does it mean unicode literal? What is the difference from U& literal?
> http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html

> PostgreSQL doesn't have nvarchar types (UTF16 in MSSQL), and only
> have mutlti-tyte characters. So I think you can remove N and just
> use "SET client_encoding = UTF8" in the cases.

Actually, the lexer translates N'foo' to NCHAR 'foo' and then the
grammar treats that just like CHAR 'foo'.  In short, the N doesn't do
anything very useful, and it certainly doesn't have any effect on
encoding behavior.  I think this is something Tom Lockhart put in ten or
so years back, and never got as far as making it actually do anything
helpful.
        regards, tom lane


Re: nvarchar notation accepted?

From
Jaime Casanova
Date:
On Thu, May 13, 2010 at 10:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Actually, the lexer translates N'foo' to NCHAR 'foo' and then the
> grammar treats that just like CHAR 'foo'.  In short, the N doesn't do
> anything very useful, and it certainly doesn't have any effect on
> encoding behavior.  I think this is something Tom Lockhart put in ten or
> so years back, and never got as far as making it actually do anything
> helpful.
>

so, the N'' syntax is fine and i don't need to hunt them as a migration step?

--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL


Re: nvarchar notation accepted?

From
Tom Lane
Date:
Jaime Casanova <jaime@2ndquadrant.com> writes:
> On Thu, May 13, 2010 at 10:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Actually, the lexer translates N'foo' to NCHAR 'foo' and then the
>> grammar treats that just like CHAR 'foo'.  In short, the N doesn't do
>> anything very useful, and it certainly doesn't have any effect on
>> encoding behavior.  I think this is something Tom Lockhart put in ten or
>> so years back, and never got as far as making it actually do anything
>> helpful.

> so, the N'' syntax is fine and i don't need to hunt them as a migration step?

As long as the implied cast to char(n) doesn't cause you problems, it's
fine.
        regards, tom lane


Re: nvarchar notation accepted?

From
Jaime Casanova
Date:
On Thu, May 13, 2010 at 10:13 PM, Takahiro Itagaki
<itagaki.takahiro@oss.ntt.co.jp> wrote:
>
> Jaime Casanova <jaime@2ndquadrant.com> wrote:
>
>> i migrate a ms sql server database to postgres and was trying some
>> queries from the application to find if everything works right...
>> when i was looking to those queries i found some that has a notation
>> for nvarchar (ej: campo = N'sometext')
>
> Do you have documentation for N'...' literal in SQLServer?
> Does it mean unicode literal? What is the difference from U& literal?
> http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html
>

nop, only thing i found is about NVARCHAR:
http://msdn.microsoft.com/en-us/library/ms186939.aspx but it has no
examples about the N'' notation although you can find examples of it
use here: http://msdn.microsoft.com/en-us/library/dd776381.aspx#BasicSyntax

> PostgreSQL doesn't have nvarchar types (UTF16 in MSSQL), and only
> have mutlti-tyte characters. So I think you can remove N and just
> use "SET client_encoding = UTF8" in the cases.
>

i don't want to remove it! i'm trying to understand if this is a bug
that will be removed if no i can safely tell my client to not look forthose queries so it has less work to do for the
migration

--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL


Re: nvarchar notation accepted?

From
Peter Eisentraut
Date:
On tor, 2010-05-13 at 23:52 -0400, Tom Lane wrote:
> Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> writes:
> > Jaime Casanova <jaime@2ndquadrant.com> wrote:
> >> i migrate a ms sql server database to postgres and was trying some
> >> queries from the application to find if everything works right...
> >> when i was looking to those queries i found some that has a notation
> >> for nvarchar (ej: campo = N'sometext')
> 
> > Do you have documentation for N'...' literal in SQLServer?
> > Does it mean unicode literal? What is the difference from U& literal?
> > http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html
> 
> > PostgreSQL doesn't have nvarchar types (UTF16 in MSSQL), and only
> > have mutlti-tyte characters. So I think you can remove N and just
> > use "SET client_encoding = UTF8" in the cases.
> 
> Actually, the lexer translates N'foo' to NCHAR 'foo' and then the
> grammar treats that just like CHAR 'foo'.  In short, the N doesn't do
> anything very useful, and it certainly doesn't have any effect on
> encoding behavior.  I think this is something Tom Lockhart put in ten or
> so years back, and never got as far as making it actually do anything
> helpful.

This should maybe changed to just ignoring the N and treating N'' like
''.



Re: nvarchar notation accepted?

From
Florian Pflug
Date:
On May 14, 2010, at 5:56 , Jaime Casanova wrote:
> On Thu, May 13, 2010 at 10:13 PM, Takahiro Itagaki
> <itagaki.takahiro@oss.ntt.co.jp> wrote:
>>
>> Jaime Casanova <jaime@2ndquadrant.com> wrote:
>>
>>> i migrate a ms sql server database to postgres and was trying some
>>> queries from the application to find if everything works right...
>>> when i was looking to those queries i found some that has a notation
>>> for nvarchar (ej: campo = N'sometext')
>>
>> Do you have documentation for N'...' literal in SQLServer?
>> Does it mean unicode literal? What is the difference from U& literal?
>> http://developer.postgresql.org/pgdocs/postgres/sql-syntax-lexical.html
>>
>
> nop, only thing i found is about NVARCHAR:
> http://msdn.microsoft.com/en-us/library/ms186939.aspx but it has no
> examples about the N'' notation although you can find examples of it
> use here: http://msdn.microsoft.com/en-us/library/dd776381.aspx#BasicSyntax

Without using the N prefixed versions of CHAR, VARCHAR and string literals, MS SQL Server refuses to process characters
otherthan those in the database's character set. It will replace all those characters with '?'. 

Note that this is not an encoding issue - it will even do so with protocol versions (everything >= 7.0 I think) that
useUTF16 on-wire, where those characters can be transmitted just fine. 

best regards,
Florian Pflug



Re: nvarchar notation accepted?

From
Jaime Casanova
Date:
On Thu, May 13, 2010 at 11:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jaime Casanova <jaime@2ndquadrant.com> writes:
>> On Thu, May 13, 2010 at 10:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Actually, the lexer translates N'foo' to NCHAR 'foo' and then the
>>> grammar treats that just like CHAR 'foo'.  In short, the N doesn't do
>>> anything very useful, and it certainly doesn't have any effect on
>>> encoding behavior.  I think this is something Tom Lockhart put in ten or
>>> so years back, and never got as far as making it actually do anything
>>> helpful.
>
>> so, the N'' syntax is fine and i don't need to hunt them as a migration step?
>
> As long as the implied cast to char(n) doesn't cause you problems, it's
> fine.
>

Is this something we want to document? Maybe something like:
"""
For historical reasons N'' syntax is also accepted as a string literal.
"""

or we can even mention the fact that that is useful for sql server migrations?

--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL


Re: nvarchar notation accepted?

From
Peter Eisentraut
Date:
On sön, 2010-06-06 at 21:13 -0500, Jaime Casanova wrote:
> On Thu, May 13, 2010 at 11:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Jaime Casanova <jaime@2ndquadrant.com> writes:
> >> On Thu, May 13, 2010 at 10:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >>> Actually, the lexer translates N'foo' to NCHAR 'foo' and then the
> >>> grammar treats that just like CHAR 'foo'.  In short, the N doesn't do
> >>> anything very useful, and it certainly doesn't have any effect on
> >>> encoding behavior.  I think this is something Tom Lockhart put in ten or
> >>> so years back, and never got as far as making it actually do anything
> >>> helpful.
> >
> >> so, the N'' syntax is fine and i don't need to hunt them as a migration step?
> >
> > As long as the implied cast to char(n) doesn't cause you problems, it's
> > fine.
> >
> 
> Is this something we want to document? Maybe something like:
> """
> For historical reasons N'' syntax is also accepted as a string literal.
> """
> 
> or we can even mention the fact that that is useful for sql server migrations?

I don't think it's a historical reason, at least not unless all reasons
are to some degree historical.  The N'' syntax is in the SQL standard,
and so if our implementation matches that, it should be documented as a
supported feature, and if it doesn't match it, we should fix it, and
perhaps leave it undocumented until we have figured out what we want it
to do.  (I have not done that analysis.)



Re: nvarchar notation accepted?

From
Jaime Casanova
Date:
On Mon, Jun 7, 2010 at 2:23 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
>
> The N'' syntax is in the SQL standard,
>

I didn't know that, do you know what paragraph is it? i can't find it

--
Jaime Casanova         www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL


Re: nvarchar notation accepted?

From
Peter Eisentraut
Date:
On mån, 2010-06-07 at 12:56 -0500, Jaime Casanova wrote:
> On Mon, Jun 7, 2010 at 2:23 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> >
> > The N'' syntax is in the SQL standard,
> >
> 
> I didn't know that, do you know what paragraph is it? i can't find it

Look for <national character string literal>.



Re: nvarchar notation accepted?

From
Bruce Momjian
Date:
Peter Eisentraut wrote:
> On m?n, 2010-06-07 at 12:56 -0500, Jaime Casanova wrote:
> > On Mon, Jun 7, 2010 at 2:23 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> > >
> > > The N'' syntax is in the SQL standard,
> > >
> > 
> > I didn't know that, do you know what paragraph is it? i can't find it
> 
> Look for <national character string literal>.

I have moved this from the open items list to the main TODO list.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + None of us is going to be here forever. +