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.
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 <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');
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 Fri, Mar 15, 2019 at 10:55 AM basti <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 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
On Mar 15, 2019, at 12:59 PM, Adrian Klaver <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
);
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.
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 <robjsargent@gmail.com> wrote:
On Mar 15, 2019, at 12:59 PM, Adrian Klaver <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
);
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.Does a check constraint not suffice in this situation?
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 Fri, Mar 15, 2019 at 11:59 AM Adrian Klaver <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.
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;
WHERE NOT NEW.hostname like '_acme-challenge%'
DO INSTEAD NOTHING;
Thanks,
Ken
--
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
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 Fri, Mar 15, 2019 at 4:42 PM Adrian Klaver <adrian.klaver@aklaver.com> 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/
> 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
![](http://agency-software.org/demo/client/images/agency_logo_small.png)
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
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