Re: literal vs dynamic partition constraint in plan execution

On Thu, Aug 29, 2019 at 4:45 PM Luca Ferrari wrote: > > Ok, the title is a little buzz, however I've got a partitioned table > and one "leaf" has a set of checks against a timestamp field to ensure > that tuples within such table belongs to the year and month: Of course, all the siblings have sim

Exclusion constraints on overlapping text arrays?

Hi. Using 9.6.14, I was setting up a table with this: EXCLUDE using gist (EXCLUDE using gist (grant_number_codes with &&) Where grant_numbers is a varchar[]. I get this error: ERROR: data type character varying[] has no default operator class for access method "gist" HINT: You must specify a

Re: How to log 'user time' in postgres logs

On 8/28/19 11:36 AM, francis picabia wrote: The other possibility was it just didn't log the slower times, but I have seen larger numbers in the 10,000 ms range in the night when some backups and housekeeping happens. All  log related settings: checkpoint_segments = 12 logging_collector

Re: How to log 'user time' in postgres logs

On 8/28/19 11:36 AM, francis picabia wrote: The server was running Moodle.  The slow load time was noticed when loading a quiz containing multiple images.  All Apache log results showed a 6 seconds or a multiple of 6 for how long it took to retrieve each image. Interestingly, if I did a wget

Re: Query using 'LIKE' returns empty set [FIXED]

On Thu, 29 Aug 2019, Rob Sargent wrote: Are you sure that particular file has the search string? Rob, I'm suitably embarrased: that's the wrong file name. I must be seriously under cafinated. The proper file is fish_data.sql so grep and postgres return 1409 instances. My apologies to all, R

Re: Query using 'LIKE' returns empty set

You need the wildcard character at front and back. select * from fish_counts where stream_tribs ilike '*%*Nehalem*%*';

Re: Query using 'LIKE' returns empty set

On 8/29/19 10:39 AM, Rich Shepard wrote: Next problem is one I've not before encountered. The .sql file used to import data to the fish_counts table has rows such as this one: ('1237796458250','0','17174','Buchanan Creek','Buchanan Creek trib to North Fork Nehalem River','0-3.25','161980'

Re: How to log 'user time' in postgres logs

On Wed, Aug 28, 2019 at 4:51 PM Tom Lane wrote: > francis picabia writes: > > The server was running Moodle. The slow load time was noticed when > loading > > a quiz containing > > multiple images. All Apache log results showed a 6 seconds or a multiple > > of 6 for how long > > it took to ret

Query using 'LIKE' returns empty set

Next problem is one I've not before encountered. The .sql file used to import data to the fish_counts table has rows such as this one: ('1237796458250','0','17174','Buchanan Creek','Buchanan Creek trib to North Fork Nehalem River','0-3.25','161980','Unknown','Jack or subadult','Peak live & dead

Re: Selecting rows having substring in a column [RESOLVED]

On Thu, 29 Aug 2019, Rich Shepard wrote: Aha! I thought of 'like' but forgot about ilike. That's exactly what I need. 'thought' is the wrong word. I should have written that I once knew of like and had forgotten it. Rich

Re: Selecting rows having substring in a column

On 8/29/19 8:47 AM, Rich Shepard wrote: On Thu, 29 Aug 2019, Gary Cowell wrote: Look at the 'LIKE' function Gary, Yes, I thought of like but didn't think to look for it in the postgres manual. Thank you very much, Rich I've given up on the "likes" in favour of the ~ (tilde) and ~* (til

Re: Selecting rows having substring in a column

On Thu, 29 Aug 2019, Gary Cowell wrote: Look at the 'LIKE' function Gary, Yes, I thought of like but didn't think to look for it in the postgres manual. Thank you very much, Rich

Re: Selecting rows having substring in a column [RESOLVED]

On Thu, 29 Aug 2019, Adrian Klaver wrote: test=# select * from like_test where fld_1 ilike '%Winding River%'; fld_1 Adrian, Aha! I thought of 'like' but forgot about ilike. That's exactly what I need. Thanks very much, Rich

literal vs dynamic partition constraint in plan execution

Ok, the title is a little buzz, however I've got a partitioned table and one "leaf" has a set of checks against a timestamp field to ensure that tuples within such table belongs to the year and month: testdb=# \d respi.y2019m08 ... Partition of: respi.y2019 FOR VALUES IN ('8') Check constraints:

Re: Selecting rows having substring in a column

Look at the 'LIKE' function select * from Fishes where stream_trib like '%Winding River%'; You need to 'bookend' your string with '%' for 'LIKE' function, if the string could match anywhere. If it could only be at the end, you could use '%Winding River' If case is an issue, wrap it with upper (o

Re: Selecting rows having substring in a column

On 8/29/19 7:13 AM, Rich Shepard wrote: Using postgres-11.1 here. My SQL knowledge needs expanding and my web searches have not found a satisfactory answer. I hope to learn the correct approach here. A table (Fishes) has an attribute column stream_trib with values such as Small Creek trib t

Selecting rows having substring in a column

Using postgres-11.1 here. My SQL knowledge needs expanding and my web searches have not found a satisfactory answer. I hope to learn the correct approach here. A table (Fishes) has an attribute column stream_trib with values such as Small Creek trib to Winding River Roaring River

Re: implicit transaction changes trigger behaviour

Willy-Bas Loos writes: > I currently have a fairly complex use case to solve and one thing i tried > was a deferred constraint trigger. I'm not sure if this solution is the way > to go, but anyway: As i was testing my code, i noticed that the trigger > behaves differently depending on whether or n

Re: implicit transaction changes trigger behaviour

On Thu, Aug 29, 2019 at 2:16 PM Willy-Bas Loos wrote: > delete from b; > --DELETE 3 Here the trigger is fired 3 times (for each row), and on every single test it finds a row in 'a', that is your variable n_b_type1 is always 1, that causes the trigger (fired on each row) to not abort. If you delet

RE: Rename a column if not already renamed.?

Hi, Finally resolved this. Bottom-line some stupidity-bad analysis on my part. Scenario was - changes were ported from trunk back to a branch and then rolling that branch back into trunk. Altering the rename_column fx to check that old and new name did not exist was a necessary for merge proc

Re: Security patch older releases

Hi, I was a bit afraid of this.. And of course I want all the latest security improvements, it's not about that.. it was about the other release sandbox. Maybe they can then first patch their sandbox, issue solved ;-) Thanks for all the replies & have a nice end of week! Erika On Thu, 29 Aug 20

Re: Security patch older releases

On Thu, Aug 29, 2019 at 2:05 PM Erika Knihti-Van Driessche wrote: > So, I have postgres 9.6.9 and customer wants it to be updated to 9.6.11, > because that is their current testing "sandbox" version, which was not > installed by me and is also another linux distro. Binary packages are always at

Re: Security patch older releases

Thu, Aug 29, 2019 at 2:05 PM Erika Knihti-Van Driessche < erika.kni...@gmail.com> wrote: > Hi, > > I think I've seen this question pass by earlier, but don't immediately > find it back.. > > So, I have postgres 9.6.9 and customer wants it to be updated to 9.6.11, > because that is their current t

implicit transaction changes trigger behaviour

Hi, I currently have a fairly complex use case to solve and one thing i tried was a deferred constraint trigger. I'm not sure if this solution is the way to go, but anyway: As i was testing my code, i noticed that the trigger behaves differently depending on whether or not i explicitly use BEGIN a

Security patch older releases

Hi, I think I've seen this question pass by earlier, but don't immediately find it back.. So, I have postgres 9.6.9 and customer wants it to be updated to 9.6.11, because that is their current testing "sandbox" version, which was not installed by me and is also another linux distro. Now, the new

Re: Question about password character in ECPG's connection string

On Thu, Aug 29, 2019 at 1:08 PM Egashira, Yusuke wrote: > According to my tests, I think that the ECPG's connection_option seems not to > accept '&' character as password anyhow... > ECPG CONNECT's connection_option seems to have some restricted characters. As far as I understand from

RE: Question about password character in ECPG's connection string

> > Perhaps it helps to URL-encode the & in the password as %26? > > The OP already did without success. Yes, I already get failed with URL-encode the &. > Could it be needed to escape the & with the backslash or single ticks? Thanks. I tested with "pass\&word" and "'pass&word'". However, them

Re: [External] Re: wal_level logical for streaming replication

Thanks Laurenz, Regards, Vijay On Thu, Aug 29, 2019 at 2:07 AM Laurenz Albe wrote: > > On Wed, 2019-08-28 at 21:44 +0530, Vijaykumar Jain wrote: > > If I change wal_level back to replica, will it corrupt wal? coz it > > will then be having diff information ( r format of data ?) > > That's why