Re: Conditional INSERT

Ken Tanzer 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 e

Re: Permission Read Only User

On 3/15/19 6:44 PM, Adrian Klaver wrote: On 3/15/19 4:37 PM, Sathish Kumar wrote: Hi All, I have created a read only user to perform select statements on our database but whenever we create new tables on the database this user is unable to view it unless I grant select again for this table. I

Re: Conditional INSERT

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

Re: Conditional INSERT

On Fri, Mar 15, 2019 at 4:42 PM Adrian Klaver 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.postgre

Re: Permission Read Only User

On 3/15/19 4:37 PM, Sathish Kumar wrote: Hi All, I have created a read only user to perform select statements on our database but whenever we create new tables on the database this user is unable to view it unless I grant select again for this table. Is there a way I can make select as defaul

Re: Conditional INSERT

On 3/15/19 4:23 PM, Ken Tanzer wrote: On Fri, Mar 15, 2019 at 11:59 AM Adrian Klaver mailto:adrian.kla...@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

Permission Read Only User

Hi All, I have created a read only user to perform select statements on our database but whenever we create new tables on the database this user is unable to view it unless I grant select again for this table. Is there a way I can make select as default permission for this user so that in future i

Re: Camel case identifiers and folding

We definitely *store* data case-sensitively, we just never want to *search* on it case-sensitively. That's what citext gives us. Many databases perform this way as a default. Postgres does not, but it offers alternatives. The OP is coming from MySQL which, if I remember correctly, treated non-bina

Re: Conditional INSERT

On Fri, Mar 15, 2019 at 11:59 AM Adrian Klaver 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 hos

Re: Camel case identifiers and folding

> On Mar 15, 2019, at 4:43 PM, Morris de Oryx wrote: > > The original question has already been answered really well, but it reminds > me to mention that Postgres text/varchar values are case-sensitive. Here's a > list of the times when I would like a case-sensitive text field: > >Never

Re: Camel case identifiers and folding

The original question has already been answered really well, but it reminds me to mention that *Postgres text/varchar values are case-sensitive*. Here's a list of the times when I would like a case-sensitive text field: Never Now here's the list of times I would like a case-blind text field:

Re: Conditional INSERT

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

Re: Facing issue in using special characters

On 3/15/19 11:59 AM, Gunther wrote: > This is not an issue for "hackers" nor "performance" in fact even for > "general" it isn't really an issue. As long as it's already been posted, may as well make it something helpful to find in the archive. > Understand charsets -- character set, code point,

Re: Conditional INSERT

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 wrote: > > > On Mar 15, 2019, at 12:59 PM, Adrian Klaver > wrote: > > On 3/15/19 11:54 AM, basti wrote: > > this

Re: Do all superuser processes count toward superuser_reserved_connections?

On 3/15/19 8:16 AM, Jeremy Finzel wrote: On Fri, Mar 15, 2019 at 9:48 AM Adrian Klaver > wrote: On 3/14/19 8:23 AM, Jeremy Finzel wrote: > I don't find a clear mention in the docs of superuser processes that are > exempt from counting toward su

Re: Conditional INSERT

> On Mar 15, 2019, at 12:59 PM, Adrian Klaver 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 hostn

Re: Conditional INSERT

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

Re: Conditional INSERT

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

Re: Conditional INSERT

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 `

Re: Conditional INSERT

> > On Fri, Mar 15, 2019 at 10:55 AM basti > 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 implemen

Re: Conditional INSERT

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:

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 us

Re: jsonb_set performance degradation / multiple jsonb_set on multiple documents

I don't know the details of jsonb_set, Perhaps the '||' operator will perform better for you, it will overwrite existing keys, so you can build your new values in a new object, and then || it to the original. postgres=# select '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"b": 4, "c": 5}'::jsonb;

Re: Facing issue in using special characters

This is not an issue for "hackers" nor "performance" in fact even for "general" it isn't really an issue. "Special characters" is actually nonsense. When people complain about "special characters" they haven't thought things through. If you are unwilling to think things through and go step b

jsonb_set performance degradation / multiple jsonb_set on multiple documents

Hi PostgreSQL Community. I tried to rewrite some plv8 stored procedures, which process in bulk JSONB documents, to PL/pgSQL. A SP usually has to delete/update/add multiple key with the same document and do it for multiple documents (~40K) in loop. When updating a single key PL/pgSQL wins against

Re: Do all superuser processes count toward superuser_reserved_connections?

On Fri, Mar 15, 2019 at 9:48 AM Adrian Klaver wrote: > On 3/14/19 8:23 AM, Jeremy Finzel wrote: > > I don't find a clear mention in the docs of superuser processes that are > > exempt from counting toward superuser_reserved_connections. So I would > > think that it's possible that postgres autov

Re: Do all superuser processes count toward superuser_reserved_connections?

On 3/14/19 8:23 AM, Jeremy Finzel wrote: I don't find a clear mention in the docs of superuser processes that are exempt from counting toward superuser_reserved_connections.  So I would think that it's possible that postgres autovac workers ought to count toward that.  Am I wrong about that?  I

Re: Camel case identifiers and folding

Steve Haresnape schreef op vr 15-03-2019 om 12:06 [+1300]: > I'm porting a sql server database to postgresql 9.6. My camelCase > identifiers are having their humps removed. This is disconcerting and > sad. the SQL ISO defines exactly this behaviour (as you note), so this is be expected suggestion

Re:Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

When I set max_parallel_workers_per_gather=0, the select statement can execute successfully. The problem has been solved. Thank you all very much. Alan At 2019-03-14 22:29:24, "Adrian Klaver" wrote: >On 3/13/19 10:54 PM, fuzk wrote: >> Dear Adrian, >> >> My setting is as following. >> >