Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Re: Damned postgres crap.

52 views
Skip to first unread message
Message has been deleted

Lennart Jonsson

unread,
Jul 9, 2012, 3:42:44 PM7/9/12
to
On 2012-07-09 19:12, China Blue [Tor], Meersburg wrote:
> 10:06:21.08586.03.0001. query: BEGIN
> .08586.03.0001.INSERT INTO config (name, value) VALUES (E'dbexp', E'10');
> .08586.03.0001.EXCEPTION WHEN unique_violation THEN
> .08586.03.0001.UPDATE config SET value=E'10' WHERE name=E'dbexp';
> .08586.03.0001.END error: fatal query: ERROR: syntax error at or near
> "INSERT"
> .08586.03.0001.LINE 2: INSERT INTO config (name, value) VALUES
> (E'dbexp', E'10');
> .08586.03.0001. ^
>
>
> 10:10:01.09104.03.0001. query: BEGIN;
> .09104.03.0001.INSERT INTO config (name, value) VALUES (E'dbexp', E'10');
> .09104.03.0001.EXCEPTION WHEN unique_violation THEN
> .09104.03.0001.UPDATE config SET value=E'10' WHERE name=E'dbexp';
> .09104.03.0001.END error: fatal query: ERROR: syntax error at or near
> "EXCEPTION"
> .09104.03.0001.LINE 3: EXCEPTION WHEN unique_violation THEN
> .09104.03.0001. ^
>
>
> At least Postgres retains its ideological purity by avoiding that terrible
> REPLACE command everyone else uses.
>

Everyone else who? You might want to have a look at the MERGE statement
which where introduced in SQL2003 and is supported by at least
PostgreSQL, Oracle, DB2 and SQL server.

/Lennart

Hans Castorp

unread,
Jul 9, 2012, 3:58:50 PM7/9/12
to
Lennart Jonsson wrote on 09.07.2012 21:42:
> Everyone else who?

Don't feed the trolls.

> You might want to have a look at the MERGE statement
> which where introduced in SQL2003 and is supported by at least
> PostgreSQL, Oracle, DB2 and SQL server.

Unfortunately MERGE is *not* supported by PostgreSQL...


Lennart Jonsson

unread,
Jul 9, 2012, 4:34:28 PM7/9/12
to
On 2012-07-09 21:58, Hans Castorp wrote:
[...]
>
> Unfortunately MERGE is *not* supported by PostgreSQL...
>

Ah, I see. Thanks for the correction. What I found was an article
discussing a *possible* implementation of MERGE in PostgreSQL. I jumped
to conclusions and continued googling for support in Oracle and SQL server.

/Lennart
Message has been deleted

Matthew Woodcraft

unread,
Jul 9, 2012, 5:14:44 PM7/9/12
to
China Blue [Tor], Meersburg <chine...@yahoo.com> wrote:
>>> 10:06:21.08586.03.0001. query: BEGIN
>>> .08586.03.0001.INSERT INTO config (name, value) VALUES (E'dbexp',
>>> E'10');
>>> .08586.03.0001.EXCEPTION WHEN unique_violation THEN
>>> .08586.03.0001.UPDATE config SET value=E'10' WHERE name=E'dbexp';
>>> .08586.03.0001.END error: fatal query: ERROR: syntax error at or
>>> near
>>> "INSERT"
>>> .08586.03.0001.LINE 2: INSERT INTO config (name, value) VALUES
>>> (E'dbexp', E'10');
>>> .08586.03.0001. ^
>>>
>>>
>>> 10:10:01.09104.03.0001. query: BEGIN;
>>> .09104.03.0001.INSERT INTO config (name, value) VALUES (E'dbexp',
>>> E'10');
>>> .09104.03.0001.EXCEPTION WHEN unique_violation THEN
>>> .09104.03.0001.UPDATE config SET value=E'10' WHERE name=E'dbexp';
>>> .09104.03.0001.END error: fatal query: ERROR: syntax error at or
>>> near
>>> "EXCEPTION"
>>> .09104.03.0001.LINE 3: EXCEPTION WHEN unique_violation THEN
>>> .09104.03.0001. ^
>>>
>>>
>>> At least Postgres retains its ideological purity by avoiding that terrible
>>> REPLACE command everyone else uses.

> I note that you don't explain why the example reduced from Postgres
> documentation gets a syntax error.
>
> http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Is that the question you meant to ask?

In your first block, it's because you missed out the semicolon after BEGIN.

In your second block, I think perhaps you're trying to use the example
from the PL/pgSQL documentation in direct SQL input. If that's what's
going on, you ought to be able to get it to work by wrapping your
queries up in a DO statement:

http://www.postgresql.org/docs/9.0/static/sql-do.html

-M-

Message has been deleted

Matthew Woodcraft

unread,
Jul 10, 2012, 9:55:04 AM7/10/12
to
China Blue [Tor], Meersburg <chine...@yahoo.com> wrote:
> Matthew Woodcraft <matt...@chiark.greenend.org.uk> wrote:
>> In your second block, I think perhaps you're trying to use the example
>> from the PL/pgSQL documentation in direct SQL input. If that's what's
>> going on, you ought to be able to get it to work by wrapping your
>> queries up in a DO statement:
>>
>> http://www.postgresql.org/docs/9.0/static/sql-do.html

>20:59:45.12147.03.0001. query: DO
> .12147.03.0001.BEGIN;
> .12147.03.0001.INSERT INTO config (name, value) VALUES (E'dbexp', E'10');
> .12147.03.0001.EXCEPTION WHEN unique_violation THEN
> .12147.03.0001.UPDATE config SET value=E'10' WHERE name=E'dbexp';
> .12147.03.0001.END error: fatal query: ERROR: syntax error at or near
>"DO"
> .12147.03.0001.LINE 1: DO
> .12147.03.0001. ^
>
>
>21:02:57.12672.03.0001. query: DO;
> .12672.03.0001.BEGIN;
> .12672.03.0001.INSERT INTO config (name, value) VALUES (E'dbexp', E'10');
> .12672.03.0001.EXCEPTION WHEN unique_violation THEN
> .12672.03.0001.UPDATE config SET value=E'10' WHERE name=E'dbexp';
> .12672.03.0001.END error: fatal query: ERROR: syntax error at or near
>"DO"
> .12672.03.0001.LINE 1: DO;
> .12672.03.0001. ^
>ERROR: syntax error at or near "DO" at character 1

You have to mark the beginning and the end of the code, usually with
dollar-quoting.

So this should be the right syntax:

DO $$
BEGIN
INSERT INTO config (name, value) VALUES (E'dbexp', E'10');
EXCEPTION WHEN unique_violation THEN
UPDATE config SET value=E'10' WHERE name=E'dbexp';
END
$$;

Note there's no semicolon after BEGIN inside PL/pgSQL code.

But if you want a fully reliable update-or-insert I think you need the
full loop, as shown in the documentation you linked to earlier (or else
use serialisable transactions with Postgresql 9.1).

I agree it's a shame that there isn't a more convenient way yet.

-M-
0 new messages