Re: insert statements
Re: insert statements
От:
"Rod Taylor" <rbt@zort.ca>
Дата:
Why not send in your changes to PostNuke along with the appropriate section from the SQL specs? Surely they'll apply a reasoned patch which improves conformance to the SQL standard and doesn't break anything in the process. I'd suspect both SyBase, and MySQL can also take insert into foo (a) as well. -- Rod Taylor This message represents the official view of the voices in my head ----- Original Message ----- From: "Vince Vielhaber" To: "Peter Eisentraut" Cc: Sent: Thursday, March 14, 2002 8:29 AM Subject: Re: [HACKERS] insert statements > On Wed, 13 Mar 2002, Peter Eisentraut wrote: > > > Vince Vielhaber writes: > > > > > For example: > > > > > > insert into foo(foo.a) values(1); > > > > > > fails because the table name is used. Update statements also include the > > > table name. Both fail. Does anyone know of a workaround? > > > > Completely loudly to whomever wrote that SQL. It's completely > > non-standard. > > > > (The implication I'm trying to make is that there's no way to make > > PostgreSQL accept that statement. Adding this as an extension has been > > rejected in the past.) > > I'm now wondering why it was rejected. I couldn't try this last nite > so I just tried it now. Here's with Sybase 11.0.3.3 : > > 1> create table foo(a int) > 2> go > 1> insert into foo(a) values(1) > 2> go > (1 row affected) > 1> insert into foo(foo.a) values(2) > 2> go > (1 row affected) > 1> > > And I suspect more than just mysql and sybase accept either syntax. > Right now I'm modifying postnuke but that's only a short term solution, > and I wouldn't want to add it to PostgreSQL either 'cuze if it remains > rejected that would hamper upgrades. ROCK --> ME <-- HARD PLACE :) > There are really no other decent CMSs available that support PostgreSQL. > > Vince. > -- > ====================================================================== ==== > Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > Online Campground Directory http://www.camping-usa.com > Online Giftshop Superstore http://www.cloudninegifts.com > ====================================================================== ==== > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Re: insert statements
От:
"Rod Taylor" <rbt@zort.ca>
Дата:
As snipped from:
http://archives.postgresql.org/pgsql-bugs/2000-10/msg00030.php (All
my stuff is in paper form)
What's your definition of "other dbs"? The above statement is quite
clearly in violation of the SQL92 and SQL99 specifications:
::= INSERT INTO Re: insert statements
От:
"Rod Taylor" <rbt@zort.ca>
Дата:
Out of curiosity, does SyBase allow you to qualify it with schema.table.column? -- Rod Taylor This message represents the official view of the voices in my head ----- Original Message ----- From: "Vince Vielhaber" To: "Rod Taylor" Cc: "Peter Eisentraut" ; Sent: Thursday, March 14, 2002 9:39 AM Subject: Re: [HACKERS] insert statements > On Thu, 14 Mar 2002, Rod Taylor wrote: > > > As snipped from: > > http://archives.postgresql.org/pgsql-bugs/2000-10/msg00030.php (All > > my stuff is in paper form) > > What's your definition of "other dbs"? The above statement is quite > > clearly in violation of the SQL92 and SQL99 specifications: > > And nowhere does it say that cannot be qualified with > the table name in front of it. Looking at the entire message noted > above the list of other dbs that support it is now Oracle, Sybase, > MS-SQL and mysql. If "other dbs" ends up the equivilent of "everything > but PostgreSQL" then which one is non-standard? > > > > > > > > ::= > > INSERT INTO
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
Vince Vielhaber writes: > On Fri, 15 Mar 2002, Thomas Lockhart wrote: >> But I *really* don't see the benefit of that
Re: insert statements
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Vince Vielhaber writes: >> What's your definition of "other dbs"? The above statement is quite >> clearly in violation of the SQL92 and SQL99 specifications: > And nowhere does it say that cannot be qualified with > the table name in front of it. Au contraire, that is EXACTLY what that bit of BNF is saying. If they'd meant to allow this construction then the BNF would refer to , not just . > Looking at the entire message noted > above the list of other dbs that support it is now Oracle, Sybase, > MS-SQL and mysql. If "other dbs" ends up the equivilent of "everything > but PostgreSQL" then which one is non-standard? Out of curiosity, what do these guys do if I try the obvious insert into foo (bar.col) ... regards, tom lane
Re: insert statements
От:
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Дата:
On Thu, 14 Mar 2002, Vince Vielhaber wrote:
> On Thu, 14 Mar 2002, Rod Taylor wrote:
>
> > As snipped from:
> > http://archives.postgresql.org/pgsql-bugs/2000-10/msg00030.php (All
> > my stuff is in paper form)
> > What's your definition of "other dbs"? The above statement is quite
> > clearly in violation of the SQL92 and SQL99 specifications:
>
> And nowhere does it say that cannot be qualified with
> the table name in front of it. Looking at the entire message noted
AFAICS periods are not valid in identifiers that are not double
quoted (section 5.2 has the rules on regular identifiers and delimited
ones)
::=
::= [ { | }... ]
::= !! See the Syntax Rules
::= |
identifier start is a simple latin letter, a letter in the character
repertoire that's in use, a syllable in the repertoire or an ideograph in
the repertoire.
identifier is defined as either a regular identifier or a delimited one
(ie double quoted). So column name cannot contain periods.
That being said, is this something that's worth adding due to general
usage by other systems?
Re: insert statements
От:
Thomas Lockhart <thomas@fourpalms.org>
Дата:
> > > Looking at the entire message noted
> > > above the list of other dbs that support it is now Oracle, Sybase,
> > > MS-SQL and mysql. If "other dbs" ends up the equivilent of "everything
> > > but PostgreSQL" then which one is non-standard?
The one(s) that intentionally violate or gratuitously extend published
language standards? ;)
> Looks like Sybase ignores the bar:
:)
So would you like to write the specification for this "standard
behavior"? We'll submit it for SQL200x :)
- Thomas
Re: insert statements
От:
Thomas Lockhart <thomas@fourpalms.org>
Дата:
Sorry for the previous sarcastic response. But I *really* don't see the benefit of that
Re: insert statements
От:
Fernando Nasser <fnasser@redhat.com>
Дата:
Vince Vielhaber wrote: > > Looks like Sybase ignores the bar: > > 1> create table foo(a int) > 2> go > 1> insert into foo(bar.a) values(1) > 2> go > (1 row affected) > 1> select * from foo > 2> go > a > ----------- > 1 > > (1 row affected) > 1> > This looks like a parser error to me. It probably only takes the last bit of the name and ignores all the qualifiers... -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Re: insert statements
От:
Fernando Nasser <fnasser@redhat.com>
Дата:
Tom Lane wrote: > > I'd want it to error out on "INSERT foo (bar.col)", though ;-) > And on "INSERT foo (bar.foo.col)" as well. This means we will have to take this check down to the analyze phase (where the schema where foo is located is finally known, if it was not specified explicitly). We could easily take "INSERT bar.foo (bar.foo.col)" but the above one is trouble. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Re: insert statements
От:
Fernando Nasser <fnasser@redhat.com>
Дата:
Vince Vielhaber wrote: > > On Thu, 14 Mar 2002, Rod Taylor wrote: > > > Out of curiosity, does SyBase allow you to qualify it with > > schema.table.column? > > Just tried it... Yes. > What if you give it a bogus schema name? Does it error out or just ignore it? -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Re: insert statements
От:
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Дата:
>> >> I'd want it to error out on "INSERT foo (bar.col)", though ;-) >> > > And on "INSERT foo (bar.foo.col)" as well. Why accept above at all ? Seems much too error prone, I would eighter accept table with schema or without schema, mixing both cases seems unnecessarily confusing and error prone to me. If at all, I would allow: INSERT bar.foo (bar.foo.col) INSERT foo (foo.col) Would that be enough for the initial problem case ? Andreas
Re: insert statements
От:
Michael Alan Dorman <mdorman@debian.org>
Дата:
Vince Vielhaber writes: > There are really no other decent CMSs available that support > PostgreSQL. bricolage.thepirtgroup.com/ Mike.
insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
I'm trying to use latest version of PostNuke which uses adodb for the database layer. The problem is in the insert and update statements. For example: insert into foo(foo.a) values(1); fails because the table name is used. Update statements also include the table name. Both fail. Does anyone know of a workaround? Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
On Wed, 13 Mar 2002, Peter Eisentraut wrote: > Vince Vielhaber writes: > > > For example: > > > > insert into foo(foo.a) values(1); > > > > fails because the table name is used. Update statements also include the > > table name. Both fail. Does anyone know of a workaround? > > Completely loudly to whomever wrote that SQL. It's completely > non-standard. > > (The implication I'm trying to make is that there's no way to make > PostgreSQL accept that statement. Adding this as an extension has been > rejected in the past.) Yeah, that's kinda what I expected. There's just under 1700 insert and update statements but only 1200 selects. Neither option sounds good at this point. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
On Wed, 13 Mar 2002, Peter Eisentraut wrote: > Vince Vielhaber writes: > > > For example: > > > > insert into foo(foo.a) values(1); > > > > fails because the table name is used. Update statements also include the > > table name. Both fail. Does anyone know of a workaround? > > Completely loudly to whomever wrote that SQL. It's completely > non-standard. > > (The implication I'm trying to make is that there's no way to make > PostgreSQL accept that statement. Adding this as an extension has been > rejected in the past.) I'm now wondering why it was rejected. I couldn't try this last nite so I just tried it now. Here's with Sybase 11.0.3.3 : 1> create table foo(a int) 2> go 1> insert into foo(a) values(1) 2> go (1 row affected) 1> insert into foo(foo.a) values(2) 2> go (1 row affected) 1> And I suspect more than just mysql and sybase accept either syntax. Right now I'm modifying postnuke but that's only a short term solution, and I wouldn't want to add it to PostgreSQL either 'cuze if it remains rejected that would hamper upgrades. ROCK --> ME <-- HARD PLACE :) There are really no other decent CMSs available that support PostgreSQL. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
On Thu, 14 Mar 2002, Rod Taylor wrote: > Why not send in your changes to PostNuke along with the appropriate > section from the SQL specs? > > Surely they'll apply a reasoned patch which improves conformance to > the SQL standard and doesn't break anything in the process. I'd > suspect both SyBase, and MySQL can also take insert into foo (a) as > well. Look below, I showed both syntaxes with Sybase. Since I don't have a copy of the SQL specs I can't send them the appropriate section or I would have already. Care to forward that appropriate section? > -- > Rod Taylor > > This message represents the official view of the voices in my head > > ----- Original Message ----- > From: "Vince Vielhaber" > To: "Peter Eisentraut" > Cc: > Sent: Thursday, March 14, 2002 8:29 AM > Subject: Re: [HACKERS] insert statements > > > > On Wed, 13 Mar 2002, Peter Eisentraut wrote: > > > > > Vince Vielhaber writes: > > > > > > > For example: > > > > > > > > insert into foo(foo.a) values(1); > > > > > > > > fails because the table name is used. Update statements also > include the > > > > table name. Both fail. Does anyone know of a workaround? > > > > > > Completely loudly to whomever wrote that SQL. It's completely > > > non-standard. > > > > > > (The implication I'm trying to make is that there's no way to make > > > PostgreSQL accept that statement. Adding this as an extension has > been > > > rejected in the past.) > > > > I'm now wondering why it was rejected. I couldn't try this last > nite > > so I just tried it now. Here's with Sybase 11.0.3.3 : > > > > 1> create table foo(a int) > > 2> go > > 1> insert into foo(a) values(1) > > 2> go > > (1 row affected) > > 1> insert into foo(foo.a) values(2) > > 2> go > > (1 row affected) > > 1> > > > > And I suspect more than just mysql and sybase accept either syntax. > > Right now I'm modifying postnuke but that's only a short term > solution, > > and I wouldn't want to add it to PostgreSQL either 'cuze if it > remains > > rejected that would hamper upgrades. ROCK --> ME <-- HARD PLACE > :) > > There are really no other decent CMSs available that support > PostgreSQL. > > > > Vince. > > -- > > > ====================================================================== > ==== > > Vince Vielhaber -- KA8CSH email: vev@michvhf.com > http://www.pop4.net > > 56K Nationwide Dialup from $16.00/mo at Pop4 Networking > > Online Campground Directory http://www.camping-usa.com > > Online Giftshop Superstore http://www.cloudninegifts.com > > > ====================================================================== > ==== > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
On Thu, 14 Mar 2002, Rod Taylor wrote: > As snipped from: > http://archives.postgresql.org/pgsql-bugs/2000-10/msg00030.php (All > my stuff is in paper form) > What's your definition of "other dbs"? The above statement is quite > clearly in violation of the SQL92 and SQL99 specifications: And nowhere does it say that cannot be qualified with the table name in front of it. Looking at the entire message noted above the list of other dbs that support it is now Oracle, Sybase, MS-SQL and mysql. If "other dbs" ends up the equivilent of "everything but PostgreSQL" then which one is non-standard? > > ::= > INSERT INTO
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
On Thu, 14 Mar 2002, Rod Taylor wrote: > Out of curiosity, does SyBase allow you to qualify it with > schema.table.column? Just tried it... Yes. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
On Thu, 14 Mar 2002, Stephan Szabo wrote: > > ::= !! See the Syntax Rules > > ::= > > | > identifier start is a simple latin letter, a letter in the character > repertoire that's in use, a syllable in the repertoire or an ideograph in > the repertoire. > > identifier is defined as either a regular identifier or a delimited one > (ie double quoted). So column name cannot contain periods. > > That being said, is this something that's worth adding due to general > usage by other systems? In an odd way, I guess that's what I'm asking. At what point is it us that's non-standard? Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
On Thu, 14 Mar 2002, Tom Lane wrote: > Vince Vielhaber writes: > >> What's your definition of "other dbs"? The above statement is quite > >> clearly in violation of the SQL92 and SQL99 specifications: > > > And nowhere does it say that cannot be qualified with > > the table name in front of it. > > Au contraire, that is EXACTLY what that bit of BNF is saying. If > they'd meant to allow this construction then the BNF would refer to > , not just . > > > Looking at the entire message noted > > above the list of other dbs that support it is now Oracle, Sybase, > > MS-SQL and mysql. If "other dbs" ends up the equivilent of "everything > > but PostgreSQL" then which one is non-standard? > > Out of curiosity, what do these guys do if I try the obvious > > insert into foo (bar.col) ... Looks like Sybase ignores the bar: 1> create table foo(a int) 2> go 1> insert into foo(bar.a) values(1) 2> go (1 row affected) 1> select * from foo 2> goa----------- 1 (1 row affected) 1> Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
On Fri, 15 Mar 2002, Tom Lane wrote: > Vince Vielhaber writes: > > On Fri, 15 Mar 2002, Thomas Lockhart wrote: > >> But I *really* don't see the benefit of that
Re: insert statements
От:
Vince Vielhaber <vev@michvhf.com>
Дата:
On Mon, 18 Mar 2002, Fernando Nasser wrote: > Vince Vielhaber wrote: > > > > On Thu, 14 Mar 2002, Rod Taylor wrote: > > > > > Out of curiosity, does SyBase allow you to qualify it with > > > schema.table.column? > > > > Just tried it... Yes. > > > > What if you give it a bogus schema name? Does it error out or just > ignore it? If I get a few mins before I leave I'll try it, but I would guess that it ignores it because when I tried INSERT INTO foo(bar.a), bar didn't exist and Sybase still accepted it. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Re: insert statements
От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
Vince Vielhaber writes: > For example: > > insert into foo(foo.a) values(1); > > fails because the table name is used. Update statements also include the > table name. Both fail. Does anyone know of a workaround? Completely loudly to whomever wrote that SQL. It's completely non-standard. (The implication I'm trying to make is that there's no way to make PostgreSQL accept that statement. Adding this as an extension has been rejected in the past.) -- Peter Eisentraut peter_e@gmx.net
