On 11/22/05, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> Jaime Casanova wrote:
> > >
> > > And yes merge CAN be used to do REPLACE (oracle uses their dummy table
> > > for this, we can use the fact that FROM clause isn't required in postgres).
> > >
> >
> > the FROM clause is required by default (starting with 8.1) unless you
> > change a postgresql.conf parameter.
> >
> > and i don't think that idea will have any fan...
>
> No, it is not, try SELECT 1. Oracle requires SELECT 1 FROM dual. The
> change in 8.1 is that SELECT pg_class.relname no longer works. You have to
> do SELECT relname FROM pg_class.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>

touche...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, Nov 22, 2005 at 11:57:48AM +0100, Martijn van Oosterhout wrote:
<excellent research snipped>

> Rather than trying to make MERGE do something it wasn't designed for,
> we should probably be spending our efforts on triggers for error
> conditions. Maybe something like:
>
> CREATE TRIGGER foo AFTER ERROR ON bar EXECUTE baz();
>
> Where baz would be passed NEW and OLD just like a normal trigger and if
> the trigger return NULL, the update is ignored. In the meantime the
> function can divert the insert to another table if it likes. This seems
> like a much more workable and useful addition.

I agree that we shouldn't try and distort MERGE into something fancy.
The AFTER ERROR trigger is a very interesting idea, since it could
handle many different cases. But I'm worried that people might not want
that behavior on by default for everything done against some table. I
think it'd be better to have some way to specify in a command that
you want to use some kind of error-handling trigger. Though presumably
the underlying framework would be same, so it shouldn't be hard to
support both.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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

http://archives.postgresql.org

Jaime Casanova wrote:
>
> the FROM clause is required by default (starting with 8.1) unless you
> change a postgresql.conf parameter.
>
> and i don't think that idea will have any fan...
>

Bruce already replied to your first statement so, what idea won't have
any fan ? It's not that we would change what MERGE does. Postgres just
does not requeire FROM clause in SELECT and second parameter of MERGE
can be SELECT which means you can do what REPLACE) does without problems
and without breaking something or violating standard and like I said you
can do the same in oracle using dual.

Btw about that keys, oracle gives error on many-to-one or many-to-many
relationship between the source and target tables.

--
Regards
Petr Jelinek (PJMODOS)


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Wed, Nov 23, 2005 at 12:24:24AM +0100, Petr Jelinek wrote:
> Btw about that keys, oracle gives error on many-to-one or many-to-many
> relationship between the source and target tables.

The standard has something called a "cardinality violation" if the
to-be-merged table doesn't match 1-1 with the rest of the statement. If
I had access to an Oracle I'd run two tests on MERGE:

1. Does the joining column have to have an index? For example, make a
column that's full of unique values but no unique index. According to
my reading of the the standard, this should still work (just slower).

2. Additionally, only the rows involved in the MERGE need to be
uniquely referenced, so if you add duplicate values but add a WHERE
clause to exclude those, it should also work.

My feeling is that requiring an index will limit it's usefulness as a
general tool.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDhDHFIB7bNG8LQkwRAq+3AJ4kprR3WbWOegJGf9JTVs NLa11/jQCfTmH8
6o2CsvPgg1g8uOnjbHoGMHo=
=D4Cr
-----END PGP SIGNATURE-----

Martijn,

Here is a quick test (Oracle 10.1.0.3/Linux):


SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

SQL> select * from merge_test_1;
ID NAME
---------- --------------------
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
1 xxx
6 rows selected.

SQL> select * from merge_test_2;
ID NAME
---------- --------------------
1 AAA
2 BBB
6 FFF

SQL> select index_name from user_indexes where table_name like
'merge_test%';
no rows selected

SQL> merge into merge_test_1 a1
2 using merge_test_2 a2
3 on (a1.id = a2.id)
4 when matched then
5 update set a1.name = a2.name
6 when not matched then
7 insert (id, name) values (a2.id, a2.name);
4 rows merged.

SQL> select * from merge_test_1;
ID NAME
---------- --------------------
1 AAA
2 BBB
3 ccc
4 ddd
5 eee
1 AAA
6 FFF
7 rows selected.



Regards,
Lubomir Petrov



Martijn van Oosterhout wrote:
> On Wed, Nov 23, 2005 at 12:24:24AM +0100, Petr Jelinek wrote:
>
>> Btw about that keys, oracle gives error on many-to-one or many-to-many
>> relationship between the source and target tables.
>>
>
> The standard has something called a "cardinality violation" if the
> to-be-merged table doesn't match 1-1 with the rest of the statement. If
> I had access to an Oracle I'd run two tests on MERGE:
>
> 1. Does the joining column have to have an index? For example, make a
> column that's full of unique values but no unique index. According to
> my reading of the the standard, this should still work (just slower).
>
> 2. Additionally, only the rows involved in the MERGE need to be
> uniquely referenced, so if you add duplicate values but add a WHERE
> clause to exclude those, it should also work.
>
> My feeling is that requiring an index will limit it's usefulness as a
> general tool.
>
> Have a nice day,
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq