Thread: insert statements
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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
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
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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
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" <vev@michvhf.com> To: "Peter Eisentraut" <peter_e@gmx.net> Cc: <pgsql-hackers@postgreSQL.org> 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 >
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" <vev@michvhf.com> > To: "Peter Eisentraut" <peter_e@gmx.net> > Cc: <pgsql-hackers@postgreSQL.org> > 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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
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 statement> ::= INSERT INTO <table name> <insert columns and source> <insert columns and source> ::= [ <left paren> <insert column list> <right paren> ] <query expression> | DEFAULT VALUES <insert column list> ::= <column name list> <column name list> ::= <column name> [ { <comma> <column name> }... ] <column name> ::= <identifier> I'm not particularly excited about supporting non-SQL variant syntaxes that add no functionality. regards, tom lane -- Rod Taylor This message represents the official view of the voices in my head ----- Original Message ----- From: "Vince Vielhaber" <vev@michvhf.com> To: "Rod Taylor" <rbt@zort.ca> Cc: "Peter Eisentraut" <peter_e@gmx.net>; <pgsql-hackers@postgreSQL.org> Sent: Thursday, March 14, 2002 9:08 AM Subject: Re: [HACKERS] insert statements > 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" <vev@michvhf.com> > > To: "Peter Eisentraut" <peter_e@gmx.net> > > Cc: <pgsql-hackers@postgreSQL.org> > > 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 > ====================================================================== ==== > > > >
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 <column name> 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 statement> ::= > INSERT INTO <table name> > <insert columns and source> > > <insert columns and source> ::= > [ <left paren> <insert column list> <right paren> ] > <query expression> > | DEFAULT VALUES > > <insert column list> ::= <column name list> > > <column name list> ::= > <column name> [ { <comma> <column name> }... ] > > <column name> ::= <identifier> > > I'm not particularly excited about supporting non-SQL variant syntaxes > that add no functionality. > > regards, tom lane > -- > Rod Taylor > > This message represents the official view of the voices in my head > > ----- Original Message ----- > From: "Vince Vielhaber" <vev@michvhf.com> > To: "Rod Taylor" <rbt@zort.ca> > Cc: "Peter Eisentraut" <peter_e@gmx.net>; > <pgsql-hackers@postgreSQL.org> > Sent: Thursday, March 14, 2002 9:08 AM > Subject: Re: [HACKERS] insert statements > > > > 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" <vev@michvhf.com> > > > To: "Peter Eisentraut" <peter_e@gmx.net> > > > Cc: <pgsql-hackers@postgreSQL.org> > > > 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 > > > ====================================================================== > ==== > > > > > > > > > > Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
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" <vev@michvhf.com> To: "Rod Taylor" <rbt@zort.ca> Cc: "Peter Eisentraut" <peter_e@gmx.net>; <pgsql-hackers@postgreSQL.org> 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 <column name> 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 statement> ::= > > INSERT INTO <table name> > > <insert columns and source> > > > > <insert columns and source> ::= > > [ <left paren> <insert column list> <right paren> ] > > <query expression> > > | DEFAULT VALUES > > > > <insert column list> ::= <column name list> > > > > <column name list> ::= > > <column name> [ { <comma> <column name> }... ] > > > > <column name> ::= <identifier> > > > > I'm not particularly excited about supporting non-SQL variant syntaxes > > that add no functionality. > > > > regards, tom lane > > -- > > Rod Taylor > > > > This message represents the official view of the voices in my head > > > > ----- Original Message ----- > > From: "Vince Vielhaber" <vev@michvhf.com> > > To: "Rod Taylor" <rbt@zort.ca> > > Cc: "Peter Eisentraut" <peter_e@gmx.net>; > > <pgsql-hackers@postgreSQL.org> > > Sent: Thursday, March 14, 2002 9:08 AM > > Subject: Re: [HACKERS] insert statements > > > > > > > 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" <vev@michvhf.com> > > > > To: "Peter Eisentraut" <peter_e@gmx.net> > > > > Cc: <pgsql-hackers@postgreSQL.org> > > > > 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 > > > > > ====================================================================== > > ==== > > > > > > > > > > > > > > > > > > > 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 > ====================================================================== ==== > > > >
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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.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 <column name> 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) <regular identifier> ::= <identifier body> <identifier body> ::= <identifier start> [ { <underscore> | <identifier part> }... ] <identifier start> ::= !! See the Syntax Rules <identifier part> ::= <identifier start> | <digit> 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?
On Thu, 14 Mar 2002, Stephan Szabo wrote: > > <identifier start> ::= !! See the Syntax Rules > > <identifier part> ::= > <identifier start> > | <digit> > 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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Vince Vielhaber <vev@michvhf.com> 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 <column name> 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
<qualified name>, not just <identifier>.
> 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
On Thu, 14 Mar 2002, Tom Lane wrote: > Vince Vielhaber <vev@michvhf.com> 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 <column name> 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 > <qualified name>, not just <identifier>. > > > 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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Vince Vielhaber <vev@michvhf.com> writes: > There are really no other decent CMSs available that support > PostgreSQL. bricolage.thepirtgroup.com/ Mike.
> > > 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
Sorry for the previous sarcastic response.
But I *really* don't see the benefit of that <table>(<table>.<col>)
syntax. Especially when it cannot (?? we need a counterexample) lead to
any additional interesting beneficial behavior.
- Thomas
Vince Vielhaber <vev@michvhf.com> writes:
> On Fri, 15 Mar 2002, Thomas Lockhart wrote:
>> But I *really* don't see the benefit of that <table>(<table>.<col>)
>> syntax. Especially when it cannot (?? we need a counterexample) lead to
>> any additional interesting beneficial behavior.
> The only benefit I can come up with is existing stuff written under
> the impression that it's acceptable.
That's the only benefit I can see either --- but it's not negligible.
Especially not if the majority of other DBMSes will take this syntax.
I was originally against adding any such thing, but I'm starting to
lean in the other direction.
I'd want it to error out on "INSERT foo (bar.col)", though ;-)
regards, tom lane
On Fri, 15 Mar 2002, Tom Lane wrote: > Vince Vielhaber <vev@michvhf.com> writes: > > On Fri, 15 Mar 2002, Thomas Lockhart wrote: > >> But I *really* don't see the benefit of that <table>(<table>.<col>) > >> syntax. Especially when it cannot (?? we need a counterexample) lead to > >> any additional interesting beneficial behavior. > > > The only benefit I can come up with is existing stuff written under > > the impression that it's acceptable. > > That's the only benefit I can see either --- but it's not negligible. > Especially not if the majority of other DBMSes will take this syntax. > > I was originally against adding any such thing, but I'm starting to > lean in the other direction. > > I'd want it to error out on "INSERT foo (bar.col)", though ;-) So would I. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.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
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
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
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 atPop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
>> >> 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