Re: jsonb_set() strictness considered harmful to data

On Tue, Oct 22, 2019 at 3:55 PM Peter J. Holzer wrote: > On 2019-10-20 13:20:23 -0700, Steven Pousty wrote: > > I would think though that raising an exception is better than a default > > behavior which deletes data. > > As an app dev I am quite used to all sorts of "APIs" throwing exceptions > a

Re: jsonb_set() strictness considered harmful to data

On 2019-10-22 09:16:05 +1100, raf wrote: > Steven Pousty wrote: > > In a perfect world I would agree with you. But often users do not read ALL > > the documentation before they use the function in their code OR they are > > not sure that the condition applies to them (until it does). > > I'm well

Re: jsonb_set() strictness considered harmful to data

On 2019-10-21 09:39:13 -0700, Steven Pousty wrote: > Turning a JSON null into a SQL null  and thereby "deleting" the data > is not the path of least surprises. But it doesn't do that: A JSON null is perfectly fine: wds=> select jsonb_set('{"a": 1, "b": 2}'::jsonb, '{c}', 'null'::jsonb); ╔

Re: jsonb_set() strictness considered harmful to data

On 2019-10-20 13:20:23 -0700, Steven Pousty wrote: > I would think though that raising an exception is better than a default > behavior which deletes data. > As an app dev I am quite used to all sorts of "APIs" throwing exceptions and > have learned to deal with them. > > This is my way of saying

Re: Upgrade mode will prevent the installer .... (pgAgent)

Boylan, Ross wrote: I can't interpret this message, or figure out whether I should select the option or not. My main problem is that my intuition is that checking "upgrade mode" will perform an upgrade, but the text of the message seems to say that checking upgrade mode will NOT perform an u

Upgrade mode will prevent the installer .... (pgAgent)

I can't interpret this message, or figure out whether I should select the option or not. My main problem is that my intuition is that checking "upgrade mode" will perform an upgrade, but the text of the message seems to say that checking upgrade mode will NOT perform an upgrade. Since I don't

Re: A question about sequences and backup/restore cycles

Oh it is the one we are working on. One of my team members brought up this issue from a job where we worked on a vendor designed one. I am convince we do not have an issue now. Thanks for your expertise. On Tue, Oct 22, 2019 at 4:42 PM Adrian Klaver wrote: > On 10/22/19 1:35 PM, stan wrote:

Re: Primary key definition?

> > > CREATE TABLE books ( > > id SERIAL PRIMARY KEY, > > > > Which has the advantage of not having to manually create the sequences. > Will > > this also enforce that the "internally created sequence" will be > initialized > > to a value above the maximum key in use on a pg_restore? >

SQL Error [0A000]: ERROR: nondeterministic collations are not supported for LIKE

Hello, I have a question about nondeterministic collations. I have created a new collation that is nondeterministic and created several columns which use this collation. Querying these columns works great until I use LIKE. When I do, I get the following error: SQL Error [0A000]: ERROR: nondet

Re: A question about sequences and backup/restore cycles

On 10/22/19 1:35 PM, stan wrote: On Tue, Oct 22, 2019 at 12:48:41PM -0700, Adrian Klaver wrote: On 10/22/19 10:48 AM, stan wrote: Please reply to list also: Ccing list. Sorry if my description was not clear. No, we do not mix test, and production data. Let me try to clarify the question. L

Re: A question about sequences and backup/restore cycles

On Tue, Oct 22, 2019 at 12:48:41PM -0700, Adrian Klaver wrote: > On 10/22/19 10:48 AM, stan wrote: > Please reply to list also: > Ccing list. > > > > > > Sorry if my description was not clear. > > > > No, we do not mix test, and production data. Let me try to clarify the > > question. Looking

Re: Primary key definition?

On 10/22/19 2:47 PM, stan wrote: I started doing the following to define my primary keys long ago and in a universe far away: CREATE TABLE employee ( employee_key integer DEFAULT nextval('employee_key_serial') PRIMARY KEY , WEE ran into a scenario, after a total db restore on a proje

Re: Primary key definition?

On 10/22/19 12:47 PM, stan wrote: I started doing the following to define my primary keys long ago and in a universe far away: CREATE TABLE employee ( employee_key integer DEFAULT nextval('employee_key_serial') PRIMARY KEY , WEE ran into a scenario, after a total db restore on a proj

Re: A question about sequences and backup/restore cycles

On 10/22/19 10:48 AM, stan wrote: Please reply to list also: Ccing list. Sorry if my description was not clear. No, we do not mix test, and production data. Let me try to clarify the question. Looking at a pg_dump, I see the following: CREATE SEQUENCE public.customer_key_serial START

Primary key definition?

I started doing the following to define my primary keys long ago and in a universe far away: CREATE TABLE employee ( employee_key integer DEFAULT nextval('employee_key_serial') PRIMARY KEY , WEE ran into a scenario, after a total db restore on a project where we got errors inserting ne

Re: Recovering disk space

On Thu, Oct 10, 2019 at 3:57 AM stan wrote: > However, my 50G disk is still 96% full. How can I recover the disk space I > seem to have used u doing this? > The bloated storage is likely not under PostgreSQL's control. Use the tools provided by your OS to figure out what is using the space. Ch

Re: Regarding db dump with Fc taking very long time to completion

On Fri, Aug 30, 2019 at 5:51 AM Durgamahesh Manne wrote: > Hi > To respected international postgresql team > > I am using postgresql 11.4 version > I have scheduled logical dump job which runs daily one time at db level > There was one table that has write intensive activity for every 40 seconds

An issue installing an extension

I have 3 machines that _should_ be nearly idnetical. On 2 of them, i have no issues installing an extension I copiled. On one I get the followign error message: icadb=# CREATE EXTENSION IF NOT EXISTS pg_libphonenumber; ERROR: could not load library "/usr/lib/postgresql/11/lib/pg_libphonenumber.

Re: An issue installing an extension

stan writes: > I have 3 machines that _should_ be nearly idnetical. On 2 of them, i have > no issues installing an extension I copiled. On one I get the followign > error message: > icadb=# CREATE EXTENSION IF NOT EXISTS pg_libphonenumber; > ERROR: could not load library > "/usr/lib/postgresql/1

Re: Lookup Primary Key of Foreign Server's Table

On Tue, Oct 22, 2019 at 9:16 AM Chris Morris wrote: > I'm looking for a system query that will lookup the primary key column on > a fdw table. It's possible we need to declare that part of the foreign > table's schema in the local (is that the right term?) database? > > Here's the foreign table -

Lookup Primary Key of Foreign Server's Table

I'm looking for a system query that will lookup the primary key column on a fdw table. It's possible we need to declare that part of the foreign table's schema in the local (is that the right term?) database? Here's the foreign table - I don't see anything showing a primary key, so my hunch is we

Re: existing dblinks

On 10/21/19 3:26 PM, Julie Nishimura wrote: Hello, is there any way to find if there are any dblink exist on the 9.6 postgresql server? Are looking for whether the extension was installed? If so then in psql: \dx will tell you. Or are you looking for code that uses dblink? AFAIK dblink cre

Re: Having more than one constraint trigger on a table

På tirsdag 22. oktober 2019 kl. 17:12:59, skrev Adrian Klaver < adrian.kla...@aklaver.com >: [snip] No. When I sort the triggers I get: test=# create table trg_str(fld_1 varchar); CREATE TABLE test=# insert into trg_str values ('trigger_1_update_fts'), ('t

Re: Having more than one constraint trigger on a table

On 10/22/19 7:54 AM, Andreas Joseph Krogh wrote: Hi. I have the following schema (question at bottom): == CREATE TABLE company(idSERIAL PRIMARY KEY, parent_idINTEGER REFERENCES company(id)DEFERRABLE INITIALLY DEFERRED ,name VARCHAR NOT NULL, duns_numberVARCHAR, fts_

Having more than one constraint trigger on a table

Hi. I have the following schema (question at bottom): == CREATE TABLE company(id SERIAL PRIMARY KEY, parent_id INTEGER REFERENCES company(id) DEFERRABLE INITIALLY DEFERRED , name VARCHAR NOT NULL, duns_number VARCHAR, fts_all tsvector, t_updated BOOLEAN); CREATE o

Re: FW: Re: A question about building pg-libphonenumber

Hello. You can take a look at GitHub guides - https://guides.github.com/activities/forking/#making-a-pull-request. Anyway if you would like to just contribute this one-timer, feel free to assign patch here and I'll open pull requests mentioning you as an author and linking this thread in there on

FW: Re: A question about building pg-libphonenumber

- Forwarded message from Pavel Stehule - Date: Tue, 22 Oct 2019 14:43:17 +0200 From: Pavel Stehule To: stan Cc: pgsql-general Subject: Re: A question about building pg-libphonenumber List-Id: ??t 22. 10. 2019 v 14:37 odes??latel stan napsal: > On Tue, Oct 22, 2019 at 02:19:15PM +02

Re: pg_hba & ldap

Greetings, * Diego (mrstephenam...@gmail.com) wrote: > I have a problem with ldap authentication, I have a ldap string like this: > > host all all 0.0.0.0/0 ldap ldapserver="10.20.90.251 > 10.20.90.252 10.10.90.251 10.10.90.252" ldapport=389... > > It is correct? if the f

Re: A question about building pg-libphonenumber

út 22. 10. 2019 v 14:37 odesílatel stan napsal: > On Tue, Oct 22, 2019 at 02:19:15PM +0200, Pavel Stehule wrote: > > Hi > > > > ??t 22. 10. 2019 v 14:15 odes??latel stan napsal: > > > > > OK, this seems a stupid question, but I do not see the answer. > > > > > > > > > I downloaded pg-libphonenum

Re: A question about building pg-libphonenumber

On Tue, Oct 22, 2019 at 02:19:15PM +0200, Pavel Stehule wrote: > Hi > > ??t 22. 10. 2019 v 14:15 odes??latel stan napsal: > > > OK, this seems a stupid question, but I do not see the answer. > > > > > > I downloaded pg-libphonenumber, and am going to try to build it for PG > > version 11. Looks

Re: A question about building pg-libphonenumber

Hi út 22. 10. 2019 v 14:15 odesílatel stan napsal: > OK, this seems a stupid question, but I do not see the answer. > > > I downloaded pg-libphonenumber, and am going to try to build it for PG > version 11. Looks like the last build was against version 9.6. > > It builds fine, the issue comes in

A question about building pg-libphonenumber

OK, this seems a stupid question, but I do not see the answer. I downloaded pg-libphonenumber, and am going to try to build it for PG version 11. Looks like the last build was against version 9.6. It builds fine, the issue comes in when I try to do a make install. It wants to install in the app

pg_hba & ldap

Hi all! I have a problem with ldap authentication, I have a ldap string like this: host all all 0.0.0.0/0 ldap ldapserver="10.20.90.251 10.20.90.252 10.10.90.251 10.10.90.252" ldapport=389... It is correct? if the firs server is down, pg will go to the next one to co

A question about sequnces and pg_restore

On Mon, Oct 21, 2019 at 05:24:37PM -0700, Adrian Klaver wrote: > On 10/21/19 5:13 PM, stan wrote: > > > > I typically design a system with primary keys defined, like this: > > > > > > CREATE TABLE employee ( > > employee_key integer DEFAULT nextval('employee_key_serial') > >

Re: Too many SET TimeZone and Application_name queries

Hi Adrian, Thanks a lot for the right pointer. Setting -Duser.timezone=UTC has solved the problem. Now, we don't see any more queries related to *SET TimeZone.* Thanks again for your time and valuable suggestion. @Jeff : These queries were sent by the JDBC Driver latest changes, nothing to do w