Conditional INSERT

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Conditional INSERT

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

Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Michel Pelletier
Well, the obvious question is, why are you inserting data into your database you don't want?  It makes more sense to just not do the insert.

But, assuming perhaps you have no control over the client, you can create a BEFORE INSERT trigger that rejects the inserts that don't match your condition:




On Fri, Mar 15, 2019 at 10:55 AM basti <[hidden email]> 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');

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.

Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Michael Lewis
On Fri, Mar 15, 2019 at 10:55 AM basti <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Paul A Jungwirth
In reply to this post by basti-2
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              ~{:-)
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

basti-2
In reply to this post by Michael Lewis
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 <[hidden email]
>     <mailto:[hidden email]>> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Adrian Klaver-4
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 <[hidden email]
>>      <mailto:[hidden email]>> 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
[hidden email]

lup
Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

lup


On Mar 15, 2019, at 12:59 PM, Adrian Klaver <[hidden email]> 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
                                );
On 15.03.19 19:17, Michael Lewis wrote:
    On Fri, Mar 15, 2019 at 10:55 AM basti <[hidden email]
    <[hidden email]>> 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.



Does a check constraint not suffice in this situation?

Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Michel Pelletier
You're right it probably does, unless the constraint needs to do a sub-query to get the matching pattern, which would require a trigger.

On Fri, Mar 15, 2019 at 12:05 PM Rob Sargent <[hidden email]> wrote:


On Mar 15, 2019, at 12:59 PM, Adrian Klaver <[hidden email]> 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
                                );
On 15.03.19 19:17, Michael Lewis wrote:
    On Fri, Mar 15, 2019 at 10:55 AM basti <[hidden email]
    <[hidden email]>> 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.



Does a check constraint not suffice in this situation?

Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Andreas Kretschmer-3
In reply to this post by basti-2


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


Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Ken Tanzer
In reply to this post by Adrian Klaver-4
On Fri, Mar 15, 2019 at 11:59 AM Adrian Klaver <[hidden email]> 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.

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:  

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
(253) 245-3801

learn more about AGENCY or
follow the discussion.
Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Adrian Klaver-4
On 3/15/19 4:23 PM, Ken Tanzer wrote:

> On Fri, Mar 15, 2019 at 11:59 AM Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> 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/
> [hidden email] <mailto:[hidden email]>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:[hidden email]?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Ken Tanzer


On Fri, Mar 15, 2019 at 4:42 PM Adrian Klaver <[hidden email]> wrote:
> 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.

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.

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.

Cheers,
Ken
 
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> [hidden email] <mailto:[hidden email]>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:[hidden email]?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


--
Adrian Klaver
[hidden email]


--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.
Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Adrian Klaver-4
On 3/15/19 5:19 PM, Ken Tanzer wrote:
>
>
> On Fri, Mar 15, 2019 at 4:42 PM Adrian Klaver <[hidden email]
> <mailto:[hidden email]>> 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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Conditional INSERT

Tom Lane-2
In reply to this post by Ken Tanzer
Ken Tanzer <[hidden email]> 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