Re: Damned postgres crap.

#1

On 2012-07-09 19:12, China Blue [Tor], Meersburg wrote:

Quote:

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

#2

Lennart Jonsson wrote on 09.07.2012 21:42:

Quote:

Everyone else who?

Don't feed the trolls.

Quote:

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...

#3

On 2012-07-09 21:58, Hans Castorp wrote:
[...]

Quote:

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

#4

China Blue [Tor], Meersburg <chine.bleu (AT) yahoo (DOT) com> wrote:

Quote:

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/s...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-

#5

China Blue [Tor], Meersburg <chine.bleu (AT) yahoo (DOT) com> wrote:

Quote:

Matthew Woodcraft <mattheww (AT) chiark (DOT) 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-

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Damned postgres crap.

comp.databases.postgresql comp.databases.postgresql

Re: Damned postgres crap. - 07-09-2012 , 02:42 PM

Re: PostgreSQL missing features (was rant about PostgreSQL) - 07-09-2012 , 02:58 PM

Re: PostgreSQL missing features (was rant about PostgreSQL) - 07-09-2012 , 03:34 PM

Re: Damned postgres crap. - 07-09-2012 , 04:14 PM

Re: Damned postgres crap. - 07-10-2012 , 08:55 AM