Thread: INSERT INTO ... SELECT (PostgreSQL vs. MySQL)
I'm currently "porting" a smallish application from Postgres to MySQL [*]. I see that with MySQL it is not possible to perform INSERT INTO ... SELECT when the target table is the same as the source table, e.g. INSERT INTO foo (abc, xyz) SELECT abc, xyz FROM foo WHERE id = 1 MySQL says: ERROR 1066: Not unique table/alias: 'foo' This statement works as expected in both PostgreSQL (at least 7.3.x) and also in Oracle 8i. The MySQL manual says: "The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it'sforbidden in standard SQL to SELECT from the same table into which you are inserting. (The problem is that the SELECTpossibly would find records that were inserted earlier during the same run. When using subquery clauses, the situationcould easily be very confusing!)" ( http://www.mysql.com/doc/en/INSERT_SELECT.html ) Can anyone shed light on whether the above statement (especially the bit about "standard SQL") is correct? I can't get my head around MySQL being more standards compliant than Postgres here... [*] I have probably committed some very heinous deed in a previous life ;-) Ian Barwick barwick@gmx.net
Hello, I switch to PostgreSQL 7.3 and now I try to make some Triggers. But the triggers are differnt to Oracle. Where can I find a tutorial about using and creating triggers in PostgreSQL 7.3 I just want to update to fields on instert and update with the system time... Perhaps someone can help me. Greetings, Stefan Sturm
On Thu, 10 Apr 2003, Ian Barwick wrote: > > I'm currently "porting" a smallish application from Postgres > to MySQL [*]. I see that with MySQL it is not possible to perform > > INSERT INTO ... SELECT > > when the target table is the same as the source table, e.g. > > INSERT INTO foo (abc, xyz) > SELECT abc, xyz FROM foo WHERE id = 1 > > MySQL says: ERROR 1066: Not unique table/alias: 'foo' > > This statement works as expected in both PostgreSQL (at least 7.3.x) > and also in Oracle 8i. > > The MySQL manual says: > > "The target table of the INSERT statement cannot appear in the > FROM clause of the SELECT part of the query because it's forbidden > in standard SQL to SELECT from the same table into which you are > inserting. (The problem is that the SELECT possibly would find > records that were inserted earlier during the same run. > When using subquery clauses, the situation could easily be very > confusing!)" > > ( http://www.mysql.com/doc/en/INSERT_SELECT.html ) > > Can anyone shed light on whether the above statement (especially > the bit about "standard SQL") is correct? I can't get my head > around MySQL being more standards compliant than Postgres here... I'm guessing they're speaking of (13.8 leveling rules 1) a) The leaf generally underlying table of T shall not be gen- erally contained in the <query expression>immediately contained in the <insert columns and source> except as the <qualifier> of a <columnreference>. I think when they mention the spec. :) However, that's a leveling rule, so it's optional (if you don't support it you can't claim the full level, you can only claim Intermediate SQL).
> Can anyone shed light on whether the above statement (especially > the bit about "standard SQL") is correct? I can't get my head > around MySQL being more standards compliant than Postgres here... MySQL would have a tough time determining whether the tuple at the end of the table was a result of the current insert or not (possibly making a loop). PostgreSQL and most other major databases are perfectly capable of such acts, so they have extended the spec for obvious reasons -- it's a silly limitation to put in place for capable databases. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Ian Barwick <barwick@gmx.net> writes:
> The MySQL manual says:
> "The target table of the INSERT statement cannot appear in the
> FROM clause of the SELECT part of the query because it's forbidden
> in standard SQL to SELECT from the same table into which you are
> inserting. (The problem is that the SELECT possibly would find
> records that were inserted earlier during the same run.
> When using subquery clauses, the situation could easily be very
> confusing!)"
> Can anyone shed light on whether the above statement (especially
> the bit about "standard SQL") is correct?
As usual ;-), the MySQL guys shade the truth to suit themselves.
The body of the spec's description of INSERT INTO clearly allows this
operation. SQL92 says
3) The <query expression> is effectively evaluated before inserting any rows into B.
which SQL99 renders as
5) QT is effectively evaluated before insertion of any rows into T.
so they have a perfectly clear model of how it should work. MySQL's
explanation of why it's undefined is just an explanation of why their
implementation cannot support it.
It is true that this is considered an advanced feature. SQL92
classifies it as Full SQL, while SQL99 calls it Feature F781:
Leveling Rules
1) The following restrictions apply for Intermediate SQL:
a) The leaf generally underlying table of T shall not be gen- erally contained in the <query
expression>immediately contained in the <insert columns and source> except as the <qualifier>
ofa <column reference>.
resp.
Conformance Rules
1) Without Feature F781, "Self-referencing operations", no leaf generally underlying table of T shall
begenerally contained in the <query expression> immediately contained in the <insert columns and
source>except as the <table or query name> or <correlation name> of a column reference.
At least, I *think* this is what these restrictions are talking about.
I'm not sure what the "except" phrases purport to allow (in standard
SQL it's not meaningful to reference a table not mentioned in FROM,
so what are they giving permission for here??). Maybe these
restrictions are talking about something else entirely? But there
is no other part of the spec that could possibly be read MySQL's way.
regards, tom lane
> of such acts, so they have extended the spec for obvious reasons -- it's Not extended the spec, implemented non-basic parts. > a silly limitation to put in place for capable databases. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
> I switch to PostgreSQL 7.3 and now I try to make some Triggers. But the > triggers are differnt to Oracle. Where can I find a tutorial about using > and creating triggers in PostgreSQL 7.3 Follow the below link to learn howto write triggered procedures: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=plpgsql-trigger.html regards, bhuvaneswaran
Ian Barwick wrote: > > I'm currently "porting" a smallish application from Postgres > to MySQL [*]. I see that with MySQL it is not possible to perform > > INSERT INTO ... SELECT > > when the target table is the same as the source table, e.g. > > INSERT INTO foo (abc, xyz) > SELECT abc, xyz FROM foo WHERE id = 1 > > MySQL says: ERROR 1066: Not unique table/alias: 'foo' > > This statement works as expected in both PostgreSQL (at least 7.3.x) > and also in Oracle 8i. Microsoft SQL-Server 2000 has no problem with it either. > > The MySQL manual says: > > "The target table of the INSERT statement cannot appear in the > FROM clause of the SELECT part of the query because it's forbidden > in standard SQL to SELECT from the same table into which you are > inserting. (The problem is that the SELECT possibly would find > records that were inserted earlier during the same run. > When using subquery clauses, the situation could easily be very > confusing!)" I didn't find anything like that in 15.8 of the SQL3 draft. Must be in small print on the backside of the PostScript file I have. Sure, the spec can be a bit confusing if one looks at it for the first time. I didn't know that it confused the MySQL developer(s) that much. For sure is it another perfect example why one has to be very carefull when reading the MySQL documentation. Half of the reasons for why MySQL differs from the standard or does not support basic standard functionality are simply made up. The manual is full of lame excuses for missing features and full of wrong or even dangerous advices or workarounds every database professional can only shake his head over. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
I usually use plpgsql to program my triggers. The plpgsql manual is here: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=plpgsql.html Section 19.9 specifically talks about triggers. Anyway, here is an example of what you said you needed: --my table CREATE TABLE mytest (id SERIAL, name TEXT, lastchange TIMESTAMP); --function to set the value of the field 'lastChange' to current system tyme CREATE OR REPLACE FUNCTION mytest_set_lastChange() RETURNS TRIGGER AS ' BEGIN NEW.lastChange:=current_timestamp; RETURN NEW; END; ' LANGUAGE 'plpgsql'; --trigger that calls mytest_set_lastChange after inserts or updates CREATE TRIGGER mytest_tg1 BEFORE INSERT OR UPDATE ON mytest FOR EACH ROW EXECUTE PROCEDURE mytest_set_lastChange(); INSERT INTO mytest (name) VALUES ('peter'); SELECT * FROM mytest;id | name | lastchange ----+-------+---------------------------- 1 | peter | 2003-04-10 13:10:16.993779 (1 row) UPDATE mytest SET name='joe';id | name | lastchange ----+------+---------------------------- 1 | joe | 2003-04-10 13:11:10.787253 (1 row) hope it helps. On Wednesday 10 April 2024 11:58, you wrote: > t want to update to fields on instert and update with the system > time...
On Thursday 10 April 2003 16:27, Ian Barwick wrote:
> I'm currently "porting" a smallish application from Postgres
> to MySQL [*].
Many thanks for all your feedback on this. I've just had a very long and
frustrating afternoon coercing the app to work with MySQL ("it's what
the customers have") and was harbouring thoughts of committing unspeakable
acts of violence to inanimate objects. [ Incoherent rambling rant about
database software which happily and warninglessly accepts swathes of syntax it
has every intention of ignoring silently snipped].
Anyway, that's another point for my growing MySQL gotcha list.
Ian Barwick
barwick@gmx.net
On Wednesday 10 Apr 2024 3:58 pm, Stefan Sturm wrote: > Hello, > > I switch to PostgreSQL 7.3 and now I try to make some Triggers. But the > triggers are differnt to Oracle. Where can I find a tutorial about using > and creating triggers in PostgreSQL 7.3 > > I just want to update to fields on instert and update with the system > time... You've already got some answers pointing you to the relevant point in the documentation, but you might like to check out http://techdocs.postgresql.org/ - lots of good stuff there. Especially, it might be worth checking the cookbook (Roberto Mello) and the Postgresql Notes (me). I think I've got an example of this in the notes, although it's probably out of date. -- Richard Huxton