Thread: Conditional INSERT

Hello,

I want to insert data into table only if condition is true.
For example:

INSERT into  mytable (domainid, hostname, txtdata)
  VALUES (100,'_acme.challenge.example', 'somedata');

The insert should only be done if Hostname like %_acme.challenge%.

How can it be done? I dont want that the user/script can insert any value.

Best regards.

On 3/15/19 10:55 AM, basti wrote:
> I want to insert data into table only if condition is true.
> For example:
> 
> INSERT into  mytable (domainid, hostname, txtdata)
>    VALUES (100,'_acme.challenge.example', 'somedata');
> 
> The insert should only be done if Hostname like %_acme.challenge%.

I would use `INSERT INTO ... SELECT` for this, instead of `INSERT INTO 
... VALUES`. For example:

     INSERT INTO mytable (domainid, hostname, txtdata)
     SELECT 100, '_acme.challenge.example', 'somedata'
     WHERE '_acme.challenge.example' LIKE '%_acme.challenge%'
     ;

(Presumably in the real code the hostname is parameterized so this isn't 
quite as pointless as it looks. :-)

If you are inserting a lot of rows at once you could also SELECT from a 
VALUES list:

     INSERT INTO mytable (domainid, hostname, txtdata)
     SELECT d, h, t
     FROM (VALUES
       (100, '_acme.challenge.example', 'somedata'),
       (200, 'bar.example.com', 'somedata'),
       (300, 'foo.example.com', 'somedata'),
       (400, '_acme.challenge.example', 'somedata')
     ) x(d, h, t)
     WHERE h LIKE '%_acme.challenge%'
     ;

I hope that helps!

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com

this is a dns database, and the client is update the _acme-challenge for
LE certificates. I don't want that the client can insert "any" txt record.
the client should only insert data if the hostname start with
_acme-challenge. i have no control on client.

i have try this rule but the server reject this with a endless loop:

CREATE RULE insert_acme AS ON INSERT TO t_dnsadmin_records_txt
    WHERE NEW.hostname like '_acme-challenge%'
    DO INSERT INTO t_dnsadmin_records_txt VALUES (
                                    NEW.domainid,
                                    NEW.hostname,
                                    NEW.txtdata
                                );


On 15.03.19 19:17, Michael Lewis wrote:
>     On Fri, Mar 15, 2019 at 10:55 AM basti <mailinglist@unix-solution.de
>     <mailto:mailinglist@unix-solution.de>> wrote:
> 
>         Hello,
> 
>         I want to insert data into table only if condition is true.
>         For example:
> 
>         INSERT into  mytable (domainid, hostname, txtdata)
>           VALUES (100,'_acme.challenge.example', 'somedata');
> 
>  
> Alternative to a trigger implementation, if you are generating that
> INSERT statement, you can change it to use a sub-select or CTE that
> contains no values if the domainid isn't what you like. If you want it
> to fail with error, you could add a check constraint. We might need more
> context on what you are doing and why to give good advice.

On 3/15/19 11:54 AM, basti wrote:
> this is a dns database, and the client is update the _acme-challenge for
> LE certificates. I don't want that the client can insert "any" txt record.
> the client should only insert data if the hostname start with
> _acme-challenge. i have no control on client.
> 
> i have try this rule but the server reject this with a endless loop:

To borrow a quote:

"I had a problem so I decided to use a rule, now I have two problems."

Do not use a rule. As suggested upstream use a BEFORE INSERT trigger, 
you will be a lot happier.

> 
> CREATE RULE insert_acme AS ON INSERT TO t_dnsadmin_records_txt
>      WHERE NEW.hostname like '_acme-challenge%'
>      DO INSERT INTO t_dnsadmin_records_txt VALUES (
>                                      NEW.domainid,
>                                      NEW.hostname,
>                                      NEW.txtdata
>                                  );
> 
> 
> On 15.03.19 19:17, Michael Lewis wrote:
>>      On Fri, Mar 15, 2019 at 10:55 AM basti <mailinglist@unix-solution.de
>>      <mailto:mailinglist@unix-solution.de>> wrote:
>>
>>          Hello,
>>
>>          I want to insert data into table only if condition is true.
>>          For example:
>>
>>          INSERT into  mytable (domainid, hostname, txtdata)
>>            VALUES (100,'_acme.challenge.example', 'somedata');
>>
>>   
>> Alternative to a trigger implementation, if you are generating that
>> INSERT statement, you can change it to use a sub-select or CTE that
>> contains no values if the domainid isn't what you like. If you want it
>> to fail with error, you could add a check constraint. We might need more
>> context on what you are doing and why to give good advice.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Am 15.03.19 um 18:55 schrieb basti:
> Hello,
>
> I want to insert data into table only if condition is true.
> For example:
>
> INSERT into  mytable (domainid, hostname, txtdata)
>    VALUES (100,'_acme.challenge.example', 'somedata');
>
> The insert should only be done if Hostname like %_acme.challenge%.
>
>

you can use a check-constraint:

create table mytable(hostname text, check(hostname like 
'%_acme.challenge%'));


I think you can see the idea...


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

On 3/15/19 4:23 PM, Ken Tanzer wrote:
> On Fri, Mar 15, 2019 at 11:59 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 3/15/19 11:54 AM, basti wrote:
>      > this is a dns database, and the client is update the
>     _acme-challenge for
>      > LE certificates. I don't want that the client can insert "any"
>     txt record.
>      > the client should only insert data if the hostname start with
>      > _acme-challenge. i have no control on client.
>      >
>      > i have try this rule but the server reject this with a endless loop:
> 
>     To borrow a quote:
> 
>     "I had a problem so I decided to use a rule, now I have two problems."
> 
>     Do not use a rule. As suggested upstream use a BEFORE INSERT trigger,
>     you will be a lot happier.
> 
>      >
>      > CREATE RULE insert_acme AS ON INSERT TO t_dnsadmin_records_txt
>      >      WHERE NEW.hostname like '_acme-challenge%'
>      >      DO INSERT INTO t_dnsadmin_records_txt VALUES (
>      >                                      NEW.domainid,
>      >                                      NEW.hostname,
>      >                                      NEW.txtdata
>      >                                  );
>      >
>      >
> 
> 
> Just curious, but wanted to follow up on whether rules are 
> across-the-board discouraged?  I've seen disparaging comments about 
> them, but I don't see any indication of that on the create rule page.

See here:
https://www.postgresql-archive.org/Deprecating-RULES-td5727689.html

The rumor crops periodically that they will be deprecated. I personally 
do not see that happening any time soon.

My issue with rules is this:

https://www.postgresql.org/docs/11/rules.html

If you can understand what really goes on in the above you are ahead of 
me. Given that my experience is that they do the unexpected as often as 
the expected so I stay away from them. Triggers I understand even when 
they error, which is the important part.

> 
> The other suggestion in this thread--a foreign key--will throw an 
> error.  Your suggestion of a before trigger might well be better (and if 
> so, why?), but is there anything particularly wrong or bad about using a 
> rule that would actually work?  Something along these lines:

The trouble is simple rarely stays simple and following logic is a lot 
easier in a trigger function then a rule. This is down mostly to the 
query rewrite that goes on in a rule. That is border line, if not 
outright, black magic. Try to follow what happens here:

https://www.postgresql.org/docs/11/rules-update.html

41.4.1.1. A First Rule Step by Step

> 
> CREATE RULE insert_acme AS ON INSERT TO t_dnsadmin_records_txt
> WHERE NOT NEW.hostname like '_acme-challenge%'
> DO INSTEAD NOTHING;
> 
> Thanks,
> Ken
> 
> 
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/15/19 5:19 PM, Ken Tanzer wrote:
> 
> 
> On Fri, Mar 15, 2019 at 4:42 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:

> OK, and thanks for the info. I've gleaned that rules are not 
> "deprecated" in the sense that they are slated for removal, but they are 
> rather discouraged.  Since that's the case, wouldn't it make sense to 
> warn users about this?  That might keep them away from rules, and ease 
> any eventual deprecation/transition issues you might have by lowering 
> the number of "rules" in the wild.

Well then there is this section:

https://www.postgresql.org/docs/11/rules-views.html

So as was mentioned in thread I posted, until someone comes up with a 
replacement they are not going away.

> 
> In the section on "Rules vs. Triggers" (41.7), it doesn't even hint at 
> this, and even says:
> 
> /"For the things that can be implemented by both, which is best depends 
> on the usage of the database."
> /
> 
> There _is_ a nice CAUTION box in 41.4 ("Rules on Insert, Update and 
> Delete").  I would suggest that something like that should go into Rules 
> vs. Triggers, and then have a short caution box at the top of perhaps 
> every Rule page (or at the very least for "CREATE RULE") that says 
> something like "Rules are discouraged.  You may be better off with 
> Triggers instead.  See <rules_v_triggers>."
> 
> Just my two cents, but that might help more people be aware of the issue 
> and avoid rules altogether.

I thought I remember there being something like in a older version of 
the docs. I can't find it now, so it might be just my mind playing 
tricks on me.

> 
> Cheers,
> Ken
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Ken Tanzer <ken.tanzer@gmail.com> writes:
> OK, and thanks for the info. I've gleaned that rules are not "deprecated"
> in the sense that they are slated for removal, but they are rather
> discouraged.  Since that's the case, wouldn't it make sense to warn users
> about this?

There's no plan to remove them, but we do encourage people to think of
triggers first.  That's why the triggers chapter appears first, and why
the "rules vs. triggers" section doesn't really read as evenhanded
(to me anyway).

> In the section on "Rules vs. Triggers" (41.7), it doesn't even hint at
> this, and even says:
> *"For the things that can be implemented by both, which is best depends on
> the usage of the database."*

You're ignoring the sentence immediately before that, which is

    Writing such triggers is often easier than writing rules, particularly
    if complex logic is required to perform the update.

as well as the one at the end of its (short) paragraph:

    However, the trigger approach is conceptually far simpler than the
    rule approach, and is easier for novices to get right.

The only case where we're really encouraging people to use rules is
where the overhead of a trigger is unacceptable.  Even then, this
whole section is written thinking of per-row triggers.  The performance
tradeoffs would likely be quite different if using a per-statement trigger
with transition tables.  But that's a very new feature, and I don't think
anyone's done serious performance comparisons of that vs. rules.

            regards, tom lane