REFERENCES error message complaint, suggestion

[Andrew Sullivan]

Re: ERROR: function round(double precision, integer) does

On Thu, Mar 04, 2004 at 06:12:36PM -0800, Glen Parker wrote:[color=blue]
>
> This popped up because we're doing dev work against a 7.4 server but
> deploying against a 7.2 server.[/color]

Don't do that. You're going to have loads of trouble.
[color=blue]
> these queries are concerned. I'm considering Postgres to be broken. Am I
> wrong?[/color]

Yes, you are. These are different pieces of software, and they'll
have different behaviour. It's not like developers renumber the
versions for fun.

A

--
Andrew Sullivan | ajs@crankycanuc k.ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Enrico Weigelt]

Re: REFERENCES error message complaint, suggestion

* Tom Lane <tgl@sss.pgh.pa .us> [2004-03-04 11:24:11 -0500]:

<snip>[color=blue]
> Double quotes are for names (identifiers). Single quotes are
> for string literals (constants).[/color]

BTW: is this general SQL syntax or just PostgeSQL ?

mysql does no distinction (which is IMHO very unclean), and it gets
even worse since mysqldump's output does it exactly the wrong way:
identifiers in '' and string constants in "" ...


cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT services

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.d e
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL-Zugang ab 0 Euro. -- statische IP -- UUCP -- Hosting --
---------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Re: REFERENCES error message complaint, suggestion

Enrico Weigelt <weigelt@metux. de> writes:[color=blue]
> * Tom Lane <tgl@sss.pgh.pa .us> [2004-03-04 11:24:11 -0500]:[color=green]
>> Double quotes are for names (identifiers). Single quotes are
>> for string literals (constants).[/color][/color]
[color=blue]
> BTW: is this general SQL syntax or just PostgeSQL ?[/color]

This is SQL92 standard behavior. Relevant extracts from the standard:

<delimited identifier> ::=
<double quote> <delimited identifier body> <double quote>

<character string literal> ::=
[ <introducer><ch aracter set specification> ]
<quote> [ <character representation> ... ] <quote>
[ { <separator>.. . <quote> [ <character representation> ... ] <quote> }... ]

<double quote> ::= "

<quote> ::= '
[color=blue]
> mysql does no distinction (which is IMHO very unclean),[/color]

How can they have no distinction? Suppose I write

select 'col' from tab;
select "col" from tab;

If there is a column tab.col, what am I going to get in each case?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Rod K]

Re: REFERENCES error message complaint, suggestion

MySQL allows double quotes or single quotes around string literals. Back
ticks indicate identifiers.
[color=blue]
> -----Original Message-----
> From: pgsql-general-owner@postgresq l.org
> [mailto:pgsql-general-owner@postgresq l.org]On Behalf Of Tom Lane
> Sent: Friday, March 05, 2004 10:12 AM
> To: weigelt@metux.d e
> Cc: pgsql-general@postgre sql.org
> Subject: Re: [GENERAL] REFERENCES error message complaint, suggestion
>
>
> Enrico Weigelt <weigelt@metux. de> writes:[color=green]
> > * Tom Lane <tgl@sss.pgh.pa .us> [2004-03-04 11:24:11 -0500]:[color=darkred]
> >> Double quotes are for names (identifiers). Single quotes are
> >> for string literals (constants).[/color][/color]
>[color=green]
> > BTW: is this general SQL syntax or just PostgeSQL ?[/color]
>
> This is SQL92 standard behavior. Relevant extracts from the standard:
>
> <delimited identifier> ::=
> <double quote> <delimited identifier body> <double quote>
>
> <character string literal> ::=
> [ <introducer><ch aracter set specification> ]
> <quote> [ <character representation> ... ] <quote>
> [ { <separator>.. . <quote> [ <character
> representation> ... ] <quote> }... ]
>
> <double quote> ::= "
>
> <quote> ::= '
>[color=green]
> > mysql does no distinction (which is IMHO very unclean),[/color]
>
> How can they have no distinction? Suppose I write
>
> select 'col' from tab;
> select "col" from tab;
>
> If there is a column tab.col, what am I going to get in each case?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Bruce Momjian]

Re: REFERENCES error message complaint, suggestion

Enrico Weigelt wrote:[color=blue]
> * Tom Lane <tgl@sss.pgh.pa .us> [2004-03-04 11:24:11 -0500]:
>
> <snip>[color=green]
> > Double quotes are for names (identifiers). Single quotes are
> > for string literals (constants).[/color]
>
> BTW: is this general SQL syntax or just PostgeSQL ?[/color]

We follow the SQL standard on this, and I think most other vendors do as
well.
[color=blue]
> mysql does no distinction (which is IMHO very unclean), and it gets
> even worse since mysqldump's output does it exactly the wrong way:
> identifiers in '' and string constants in "" ...[/color]

That is pretty amazing.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.ph a.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Chris Boget]

Re: REFERENCES error message complaint, suggestion

> How can they have no distinction? Suppose I write[color=blue]
> select 'col' from tab;
> select "col" from tab;
> If there is a column tab.col, what am I going to get in each case?[/color]

select 'col' from tab;

+-----+
| col |
+-----+
| col |
| col |
| col |
+-----+
3 rows found in set (0.00 sec)

select "col" from tab;
+-----+
| col |
+-----+
| col |
| col |
| col |
+-----+
3 rows found in set (0.00 sec)

MySQL is definitely *not* 100% SQL92 compliant.

Chris






---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Michael Chaney]

Re: REFERENCES error message complaint, suggestion

On Fri, Mar 05, 2004 at 09:53:50AM -0600, Chris Boget wrote:[color=blue]
> MySQL is definitely *not* 100% SQL92 compliant.[/color]

Nah... can't be...

Not to be a jerk, but did you just figure this out?

Michael
--
Michael Darrin Chaney
mdchaney@michae lchaney.com

http://www.michaelchaney.com/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org