Thread: Simple query question
I'm in the process of converting my site from MySQL to PGSQL and I've come across something odd that I can't find an explenation for in the documentation. All my MySQL queries use double quotes around the field value I am looking for. IE SELECT * FROM sessions WHERE session_name = "session_name" However, when I run that in PGSQL, I get an error message: ERROR: Attribute "session_name" not found But if I replace the double quotes with single quotes, the query runs fine. Where in the documentation can I find why that is? Also, in MySQL you could use \G after the query (instead of using a semi colon) so that the record set would be displayed with the columns vertical instead of horizontal. This is especially useful when you are using the command line. Does PGSQL have something like that? This is something else I couldn't find in the documentation. thnx, Chris
From: "Chris Boget" <chris@wild.net> To: "PGSql Novice" <pgsql-novice@postgresql.org> Subject: [NOVICE] Simple query question Date sent: Mon, 23 Dec 2002 07:25:03 -0600 > I'm in the process of converting my site from MySQL to PGSQL and > I've come across something odd that I can't find an explenation for in > the documentation. The section called basic sql commands, double quotes seem to be reserved for objects(relations/attributes) though are not required so if you double quote (or not quote) a value in a where clause pgsql will look for the corresponding object and most likely fail. Always enclose values in single quotes. > > All my MySQL queries use double quotes around the field value I am > looking for. IE > > SELECT * FROM sessions WHERE session_name = "session_name" > > However, when I run that in PGSQL, I get an error message: > > ERROR: Attribute "session_name" not found > > But if I replace the double quotes with single quotes, the query runs > fine. > Where in the documentation can I find why that is? > > Also, in MySQL you could use \G after the query (instead of using a > semi colon) so that the record set would be displayed with the columns > vertical instead of horizontal. This is especially useful when you are > using the command line. Does PGSQL have something like that? This > is something else I couldn't find in the documentation. In psql typing \x will toggle the display hope this helps Paul Butler > > thnx, > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
---------------- The section called basic sql commands, double quotes seem to be reserved for objects(relations/attributes) though are not required so if you double quote (or not quote) a value in a where clause pgsql will look for the corresponding object and most likely fail. Always enclose values in single quotes. ------- I believe Oracle requires singles for values too. That must be a standard. Sincerely, r.
"Reshat Sabiq" <sabiq@purdue.edu> writes:
> I believe Oracle requires singles for values too. That must be a
> standard.
It is. SQL92 contains
<double quote> ::= "
<quote> ::= '
<delimited identifier> ::=
<double quote> <delimited identifier body> <double quote>
<character string literal> ::=
[ <introducer><character set specification> ]
<quote> [ <character representation>... ] <quote>
[ { <separator>... <quote> [ <character representation>... ] <quote> }... ]
I can't believe that MySQL gets such a basic element of SQL syntax wrong
--- perhaps the OP is mistaken? If MySQL really does treat doublequote
as introducing a literal, how do they handle weird identifiers, like
names with embedded spaces?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote: > I can't believe that MySQL gets such a basic element of SQL syntax wrong > --- perhaps the OP is mistaken? If MySQL really does treat doublequote > as introducing a literal, how do they handle weird identifiers, like > names with embedded spaces? back-ticks... SELECT * FROM `table name with spaces` WHERE `key`="a"; MySQL = My personal interpretation of SQL... ;-) Regards, Michael Paesold
On Mon, 23 Dec 2002 13:01:58 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote: > <double quote> ::= " > <quote> ::= ' > >I can't believe that MySQL gets such a basic element of SQL syntax wrong s/My/MS/ and better believe it :-) I did this in 7.0: CREATE TABLE tst2 (id INT, x varchar(20)) INSERT INTO tst2 VALUES (1, "one") INSERT INTO tst2 VALUES (2, 'two') SELECT * FROM tst2 id x ----------- -------------------- 1 one 2 two This seems to be a common misconception among database vendors who consider themselves leading in one sense or another. Servus Manfred
Manfred Koizar <mkoi-pg@aon.at> writes:
> On Mon, 23 Dec 2002 13:01:58 -0500, Tom Lane <tgl@sss.pgh.pa.us>
>> I can't believe that MySQL gets such a basic element of SQL syntax wrong
> s/My/MS/ and better believe it :-) I did this in 7.0:
> CREATE TABLE tst2 (id INT, x varchar(20))
> INSERT INTO tst2 VALUES (1, "one")
> INSERT INTO tst2 VALUES (2, 'two')
Yeek. Well, now I know why this seems to be such a frequent source of
confusion ...
regards, tom lane