Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #373 > unrolled thread

Damned postgres crap.

Started by"China Blue [Tor], Meersburg" <chine.bleu@yahoo.com>
First post2012-07-09 10:12 -0700
Last post2012-07-10 14:55 +0100
Articles 8 — 4 participants

Back to article view | Back to comp.databases.postgresql

Contents

  Damned postgres crap. "China Blue [Tor], Meersburg" <chine.bleu@yahoo.com> - 2012-07-09 10:12 -0700
    Re: Damned postgres crap. Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-07-09 21:42 +0200
      Re: PostgreSQL missing features (was rant about PostgreSQL) Hans Castorp <REWYRLXHEGHO@spammotel.com> - 2012-07-09 21:58 +0200
        Re: PostgreSQL missing features (was rant about PostgreSQL) Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2012-07-09 22:34 +0200
      Re: Damned postgres crap. "China Blue [Tor], Meersburg" <chine.bleu@yahoo.com> - 2012-07-09 13:45 -0700
        Re: Damned postgres crap. Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2012-07-09 22:14 +0100
          Re: Damned postgres crap. "China Blue [Tor], Meersburg" <chine.bleu@yahoo.com> - 2012-07-09 21:03 -0700
            Re: Damned postgres crap. Matthew Woodcraft <mattheww@chiark.greenend.org.uk> - 2012-07-10 14:55 +0100

#373 — Damned postgres crap.

From"China Blue [Tor], Meersburg" <chine.bleu@yahoo.com>
Date2012-07-09 10:12 -0700
SubjectDamned postgres crap.
Message-ID<chine.bleu-AE3031.10123209072012@news.eternal-september.org>
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.

-- 
My name Indigo Montoya.      |                     The Dude abides.
You flamed my father.        |       I'm whoever you want me to be.
Prepare to be spanked.       |  Annoying Usenet one post at a time.
Stop posting that!           |    At least I can stay in character.

[toc] | [next] | [standalone]

#374

FromLennart Jonsson <erik.lennart.jonsson@gmail.com>
Date2012-07-09 21:42 +0200
Message-ID<jtfc7l$b9i$1@dont-email.me>
In reply to#373
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

[toc] | [prev] | [next] | [standalone]

#375 — Re: PostgreSQL missing features (was rant about PostgreSQL)

FromHans Castorp <REWYRLXHEGHO@spammotel.com>
Date2012-07-09 21:58 +0200
SubjectRe: PostgreSQL missing features (was rant about PostgreSQL)
Message-ID<a60rfjFocqU1@mid.individual.net>
In reply to#374
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...

[toc] | [prev] | [next] | [standalone]

#376 — Re: PostgreSQL missing features (was rant about PostgreSQL)

FromLennart Jonsson <erik.lennart.jonsson@gmail.com>
Date2012-07-09 22:34 +0200
SubjectRe: PostgreSQL missing features (was rant about PostgreSQL)
Message-ID<jtff8k$tuk$1@dont-email.me>
In reply to#375
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

[toc] | [prev] | [next] | [standalone]

#377

From"China Blue [Tor], Meersburg" <chine.bleu@yahoo.com>
Date2012-07-09 13:45 -0700
Message-ID<chine.bleu-D5F931.13453909072012@news.eternal-september.org>
In reply to#374
In article <jtfc7l$b9i$1@dont-email.me>,
 Lennart Jonsson <erik.lennart.jonsson@gmail.com> wrote:

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

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

-- 
My name Indigo Montoya.      |                     The Dude abides.
You flamed my father.        |       I'm whoever you want me to be.
Prepare to be spanked.       |  Annoying Usenet one post at a time.
Stop posting that!           |    At least I can stay in character.

[toc] | [prev] | [next] | [standalone]

#378

FromMatthew Woodcraft <mattheww@chiark.greenend.org.uk>
Date2012-07-09 22:14 +0100
Message-ID<maq*XjT-t@news.chiark.greenend.org.uk>
In reply to#377
China Blue [Tor], Meersburg <chine.bleu@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-

[toc] | [prev] | [next] | [standalone]

#379

From"China Blue [Tor], Meersburg" <chine.bleu@yahoo.com>
Date2012-07-09 21:03 -0700
Message-ID<chine.bleu-DCF567.21034809072012@news.eternal-september.org>
In reply to#378
In article <maq*XjT-t@news.chiark.greenend.org.uk>,
 Matthew Woodcraft <mattheww@chiark.greenend.org.uk> wrote:

> China Blue [Tor], Meersburg <chine.bleu@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#PL
> > PGSQL-ERROR-TRAPPING
> 
> Is that the question you meant to ask?
> 
> In your first block, it's because you missed out the semicolon after BEGIN.

The quoted documentation leaves out a semicolon after every 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

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

-- 
My name Indigo Montoya.      |                     The Dude abides.
You flamed my father.        |       I'm whoever you want me to be.
Prepare to be spanked.       |  Annoying Usenet one post at a time.
Stop posting that!           |    At least I can stay in character.

[toc] | [prev] | [next] | [standalone]

#380

FromMatthew Woodcraft <mattheww@chiark.greenend.org.uk>
Date2012-07-10 14:55 +0100
Message-ID<ywj*o0W-t@news.chiark.greenend.org.uk>
In reply to#379
China Blue [Tor], Meersburg <chine.bleu@yahoo.com> wrote:
> Matthew Woodcraft <mattheww@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-

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web