[GENERAL] Removing null bytes from a json column

Does anyone have some tips on how to deal with an existing json type column that has some null bytes ( \u) in it? It seems like anything I do that touches any row with a null byte just errors. I'd love to just remove them if I could find some way to find them, but I'm having trouble even figur

[GENERAL] Creating a non-strict custom aggregate that initializes to the first value

Hi all, I'm trying to create a custom aggregate function that returns the value from the first row (possibly null). For ex. Table t a | b - 1 | A 2 | NULL SELECT my_first(b order by a) => A SELECT my_first(b order by a DESC) => NULL The straightforward way would seem to be something like

[GENERAL] Comment on extension issues with pg_dump after upgrading to 9.1.2

Hi All, We recently upgraded to 9.1.2 from 9.0.3 and ran into some issues with the process we've been using to dump and restore databases. We typically use a super user (but not the postgres user) to dump and restore databases, but in moving the 9.1.2 we've run into trouble with pg_dump outputing

Re: [GENERAL] Documentation of what schema modifications cause what level of table locking

Thanks, this is exactly what I was looking for. The listed commands that grab the ACCESS EXCLUSIVE lock are the ones we have to watch out for. Tim On Sat, Sep 24, 2011 at 2:23 PM, Adrian Klaver wrote: > On Friday, September 23, 2011 3:52:54 pm Timothy Garnett wrote: > > Hi all, >

[GENERAL] Documentation of what schema modifications cause what level of table locking

Hi all, I was wondering if there was some good documentation on what kinds of schema modifications block reads vs. which ones don't. For ex. we recently had an issue where someone ran as part of a migration ALTER TABLE tname ALTER COLUMN cname SET NOT NULL; on a large table that is not inserted

Re: [GENERAL] When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off)

2148,1000152147,1000152146,1000141594,1000141133,1000172483,1000191484,1000191485,1000196236,1000236337,1000241756,1000242921,1000256842,1000257993,1000270323,1000272820,1000281535,1000297033,1000297039,1000297446,1000301868,10003071961000316101,1000331822,1000334293,1000342550,1000352078,1000367699,1000372920,1000373959,1000383317,1000400498,1000405863,1000412281,1000420780,1000430861}'::integer[])) (7 rows) Total runtime: 47.137 ms On Tue, Aug 3, 2010 at

[GENERAL] When can postgresql use a partial (NOT NULL) index? Seems to depend on size of IN clause (even with enable seqscan = off)

Hi all, I'm debugging a performance issue that looks like it might actually be an issue/limitation/parameter/bug in the query planner, but since I couldn't find anything authoritative on when exactly postgresql is able to use partial not null indexes I'm not sure that that's the case and I was hop