Re: [GENERAL] How to ensure that a stored function always returns TRUE or FALSE?

On Wed, Mar 2, 2016 at 5:39 AM, Alexander Farber wrote: > Thanks Vitaly, but instead of inverting the IF-condition I would prefer to > make my function more robust, since it is kind of security-related and I > might forget about the special IF-condition later when using it elsewhere... > > ​Merli

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

On Wed, Mar 2, 2016 at 2:29 PM, Derek Elder wrote: > > 2016-03-02 14:58:09 EST [14366]: [8-1] LOG: could not resolve > "localhost": Name or service not known > 2016-03-02 14:58:09 EST [14366]: [9-1] LOG: disabling statistics > collector for lack of working socket > I'm reasonably certain the abo

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

On Wed, Mar 2, 2016 at 3:49 PM, Tom Lane wrote: > Derek Elder writes: > > That was indeed the root cause. The /etc/hosts file on the server had > > incorrect permissions which caused localhost to not resolve. > > It strikes me that this should not have been so hard to solve. The > stats collect

Re: [GENERAL] Postgresql upgrade 9.5

On Wed, Mar 2, 2016 at 4:03 PM, avi Singh wrote: > Guys if anyone of you upgraded Prod database to 9.5.1 have any feedback > please share. I have to plan an upgrade, still debating if i should wait > for 9.5.2 or not? Whats your take on that? > Upgrade from what?​ ​There is a somewhat big ​ (i.

Re: [GENERAL] Issue enabling track_counts to launch autovacuum in 9.4.5

On Wed, Mar 2, 2016 at 4:25 PM, Tom Lane wrote: > "David G. Johnston" writes: > > ​The fact that the first two are only LOG level and not WARNING would > seems > > like the easiest improvement to make. > > Unfortunately, that would be a disimprovement, becau

Re: [GENERAL] PLPythonu for production server

On Thu, Mar 3, 2016 at 12:35 PM, Adrian Klaver wrote: > On 03/03/2016 10:09 AM, Rémi Cura wrote: > >> Hey List, >> >> would it be considered safe to use plpythonu for a production database? >> What would be the limitations/ dangers? >> > > They are explained here: > > http://www.postgresql.org/do

Re: [GENERAL] Check constraints for varchar[] and varchar[][] columns in a table

On Fri, Mar 4, 2016 at 6:59 AM, Alexander Farber wrote: > Hello, > > for a Scrabble-like word game using PostgreSQL 9.5 as backend > I am trying to add CHECK constraints to the VARCHAR arrays: > > CREATE TABLE words_games ( > gid SERIAL PRIMARY KEY, > created timestamptz NOT NULL,

Re: [GENERAL] String literal doesn't autocast to text type

It would be nice if you'd follow the list convention of bottom-posting. Not doing that is much more annoying than the omission of version and o/s - especially the later since PostgreSQL purports to be generally o/s agnostic. On Fri, Mar 4, 2016 at 12:17 PM, Alex Ignatov > wrote: > >> Hello! >> W

Re: [GENERAL] multiple UNIQUE indices for FK

On Fri, Mar 4, 2016 at 6:30 AM, Rafal Pietrak wrote: > The problem is that once one accepts the requirement for a unique index > as FK target column "mandatory performance support", then I fail to see > real reazon, where *ENY* unique index shouldn't do that role too. They > are unique (within d

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 1:52 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > > >>> Now i execute the same in pgsql 9.4.5 and all is fine!!! >>> >>> The EXPLAINs are: >>> >>> - pgsql 9.5.1: >>> >>>Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) >>> ->

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 2:16 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > the output is: > > pba=# EXPLAIN (ANALYZE, TIMING, BUFFERS) SELECT idprodxintegrar FROM > _gc_tb a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ > b.arama ) > ; >

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < fjmolinabr...@gmail.com> wrote: > when i run without the ANALIZE the output is: > > pba=# EXPLAIN ( TIMING, BUFFERS) SELECT idprodxintegrar FROM _gc_tb a > LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama <@ b.arama ); > ERROR

Re: [GENERAL] query reboot pgsql 9.5.1

> > > 2016-03-04 15:58 GMT-06:00 David G. Johnston : > >> On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < >> fjmolinabr...@gmail.com> wrote: >> >>> when i run without the ANALIZE the output is: >>> >>> pba=# EXPLAIN (

Re: [GENERAL] query reboot pgsql 9.5.1

On Fri, Mar 4, 2016 at 3:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > >> 2016-03-04 15:58 GMT-06:00 David G. Johnston >> : >> >>> On Fri, Mar 4, 2016 at 2:56 PM, Felipe de Jesús Molina Bravo < >>> fjmolinabr...@gmail.com>

Re: [GENERAL] multiple UNIQUE indices for FK

On Fri, Mar 4, 2016 at 4:17 PM, Rafal Pietrak wrote: > > > > In other words the presence of absence of an FK constraint between two > > tables should not alter the results of any question. But since a > > It wouldn't. > > 3. FK is defined with partially-unique indes. This is new and ... would >

Re: [GENERAL] Does a call to a language handler provide a context/session, and somewhere to keep session data?

On Sun, Mar 6, 2016 at 10:21 PM, wrote: > Given that a language handler would be expected to be persistent, and to > support concurrent (and reentrant) calls within a single database, is there > a unique context or session identifier available? > > Is there a place to store data, to be retrieved

Re: [GENERAL] Inserting JSON via Java PreparedStatment

> pStmt.setString(11, dtlRec.toString()); > pStmt.setObject(11, dtlRec.toString()); > > Which produce a different error: > > Event JSON: {"New MbrID":29} > > SQLException: ERROR: column "evtjson" is of type json but expression is of > type character varying > > Hint: You will need to rewrite or cas

Re: [GENERAL] pg_restore man page question

On Mon, Mar 7, 2016 at 10:17 AM, Karsten Hilbert wrote: > The man page of pg_restore says > > --disable-triggers > This option is relevant only when performing a > data-only restore. It instructs pg_restore to execute > commands to temporari

Re: [GENERAL] Inserting JSON via Java PreparedStatment

On Mon, Mar 7, 2016 at 8:15 PM, Curt Huffman wrote: > Thanks Rob & David! > > I got it to work using the following: > > > String qry = "INSERT INTO event " > + "(spotid, qid, userid, persid, , " > + "evtvalue, evtdt, evtjson) " > + "VALUES(?,?,?,?,?,?,?,?,?,?,to_json(?::json));"; > > ​This is

Re: [GENERAL] INDEX ONLY scan with expression index

On Mon, Mar 7, 2016 at 11:33 PM, Tom Lane wrote: > James Sewell writes: > > Would anyone be able to shed some light on why expression based indexes > > can't be used for an index only scan? > > I've found a few comments saying this is the case, and I've proven it is > > the case in reality - but

Re: [GENERAL] Exclude pg_largeobject form pg_dump

On Tue, Mar 8, 2016 at 9:45 AM, Andreas Joseph Krogh wrote: > På tirsdag 08. mars 2016 kl. 17:38:04, skrev Joshua D. Drake < > j...@commandprompt.com>: > > On 03/08/2016 08:02 AM, Andreas Joseph Krogh wrote: > > På tirsdag 08. mars 2016 kl. 16:57:01, skrev Tom Lane > >

Re: [GENERAL] Plan to support predicate push-down into subqueries with aggregates?

We bottom (or inline) post on these lists. On Tue, Mar 8, 2016 at 3:37 PM, Adam Brusselback wrote: > ​ > On Tue, Mar 8, 2016 at 5:17 PM, Tom Lane wrote: > >> Adam Brusselback writes: >> > I was wondering if there were any plans to support predicate push-down >> > optimization for subqueries (a

Re: [GENERAL] Plan to support predicate push-down into subqueries with aggregates?

On Wednesday, March 9, 2016, rob stone wrote: > On Wed, 2016-03-09 at 12:25 -0500, Adam Brusselback wrote: > > Note that:- (a) I included header_description in the definition. > (b) Removed some lines as if you want the total you may as well > include it in your select from the view, and for

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

tion into the title. -- Forwarded message -- From: Michael Paquier Date: Thu, Jan 28, 2016 at 6:01 AM Subject: Re: [GENERAL] Request - repeat value of \pset title during \watch interations To: "David G. Johnston" Cc: Tom Lane , "pgsql-general@postgresql.org" &

Re: [GENERAL] enum bug

On Fri, Mar 11, 2016 at 2:36 PM, Elein wrote: > > > Bug/Improvement: > > > >Enums belong to types, not to column values. > > > >* Create a built in function, like enum_range( type ), independent of > tables > > that shows a type's enum values. > >create or replace function en

Re: [GENERAL] enum bug

On Fri, Mar 11, 2016 at 2:55 PM, Melvin Davidson wrote: > Enums are evil! > http://www.lornajane.net/posts/2010/is-enum-evil > > ​Using red background is evil :) I'll disagree on the premise but will agree that without a better implementation and handling of change our implementation makes using

Re: [GENERAL] enum bug

On Fri, Mar 11, 2016 at 4:19 PM, Elein wrote: > An unused (yet) enum type cannot display the enum ranges. An empty table > containing that type cannot display enum ranges. > ​Yes, it can. ​CREATE TYPE rainbow AS enum ('red','orange','yellow','blue','purple'); SELECT enum_range(null::rainbow); e

Re: [GENERAL] Q: extract database name from directory dump

On Sat, Mar 12, 2016 at 2:05 PM, Karsten Hilbert wrote: > On Sat, Mar 12, 2016 at 03:55:32PM -0500, Melvin Davidson wrote: > > > hmmm, let's see. You haven't specified PostgreSQL version or O/S as is > > common sense and courtesy, so I will choose one for you. > > :-) Sorry. I am on 9.5.1 on D

Re: [GENERAL] pg_restore fails

On Saturday, March 12, 2016, Karsten Hilbert wrote: > Hi, > > Debian Stretch > PG 9.5.1 > > I am trying to pg_restore from a directory dump. > > However, despite using > > --clean > --create > --if-exists > > I am getting an error because schema PUBLIC alre

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 4:32 PM, Adrian Klaver wrote: > On 03/12/2016 03:09 PM, Karsten Hilbert wrote: > >> Hi, >> >> Debian Stretch >> PG 9.5.1 >> >> I am trying to pg_restore from a directory dump. >> >> However, despite using >> >> --clean >> --create >>

Re: [GENERAL] pg_restore fails

On Saturday, March 12, 2016, Karsten Hilbert wrote: > On Sat, Mar 12, 2016 at 04:53:20PM -0700, David G. Johnston wrote: > > > The docs could probably use improvement here - though I am inferring > > behavior from description and not code. > > > > The create o

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 5:31 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > You probably should just drop the existing database and use --create by > itself. > > You can even use the dropdb command to avoid SQL in your script. > > ​This seems like it is the

Re: [GENERAL] pg_restore fails

On Sat, Mar 12, 2016 at 5:43 PM, Karsten Hilbert wrote: > On Sat, Mar 12, 2016 at 05:31:38PM -0700, David G. Johnston wrote: > > > > The reason being, of course, that I want to check the exit > > > code in a pg_restore wrapper script. > > > > > > >

Re: [GENERAL] pg_restore fails

On Saturday, March 12, 2016, Karsten Hilbert wrote: > On Sat, Mar 12, 2016 at 05:49:56PM -0700, David G. Johnston wrote: > > > I'd operate under the premise that all warnings and errors are fatal > > (i.e., keep --exit-on-error) until you cannot for some very specific &g

Re: [GENERAL] Suppress decimal point like digits in to_char?

On Sunday, March 13, 2016, Ken Tanzer wrote: > Hi. Is there a way with to_char to suppress a decimal point, like a > leading or trailing 0, so that integers will not have them, but non-ints > will? I'm hoping I'm missing something easy. Thanks. > > Ken > > SELECT val,to_char(val::decimal(6,2),

Re: [GENERAL] Suppress decimal point like digits in to_char?

On Sunday, March 13, 2016, Ken Tanzer wrote: > > On Mar 13, 2016 6:29 PM, "David G. Johnston" > wrote: > > > > On Sunday, March 13, 2016, Ken Tanzer > wrote: > >> > >> Hi. Is there a way with to_char to suppress a decimal point, like a >

Re: [GENERAL] Suppress decimal point like digits in to_char?

On Mon, Mar 14, 2016 at 3:31 AM, Francisco Olarte wrote: > Hi; > > On Mon, Mar 14, 2016 at 2:53 AM, David G. Johnston > wrote: > > On Sunday, March 13, 2016, Ken Tanzer wrote: > > > Typically if I'm going to format any currency amount with pennies I would &

Re: [GENERAL] enum bug

On Mon, Mar 14, 2016 at 8:48 AM, Alvaro Herrera wrote: > Joshua D. Drake wrote: > > On 03/11/2016 03:19 PM, Elein wrote: > > > > >An unused (yet) enum type cannot display the enum ranges. An empty table > > >containing that type cannot display enum ranges. > > > > > >The example selects were what

Re: [GENERAL] enum bug

On Mon, Mar 14, 2016 at 8:52 AM, Joshua D. Drake wrote: > > If improved enough, maybe we could get to a >> point where they could actually be used; otherwise why the heck did we >> let the feature in the database in the first place? I think all these >> "use a lookup table, you silly!" answers

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On Mon, Mar 14, 2016 at 2:13 PM, drum.lu...@gmail.com wrote: > Hi all, > > favorite > > > I've got 2 tables: > > Temp-Table > Table-A > > Need to copy all the data from *Temp-Table* to *Table-A*. But the

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On Mon, Mar 14, 2016 at 2:37 PM, drum.lu...@gmail.com wrote: > >> > I want to import data from table A to table B, but when doing it the > column "code" on table B has to have some unique random data. > > I could use UUID like: > insert into "TB" ("Id", "Title") values (uuid_generate_v4(), '111')

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On Mon, Mar 14, 2016 at 3:51 PM, drum.lu...@gmail.com wrote: > I just need to know how can I do all of this > ​You may have missed my prior email. You cannot COPY directly into the target table. You must copy to a staging table. You then insert from the staging table to the target table, list

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On Mon, Mar 14, 2016 at 4:05 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Mar 14, 2016 at 3:51 PM, drum.lu...@gmail.com < > drum.lu...@gmail.com> wrote: > >> I just need to know how can I do all of this >> > > ​You may have mis

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On Monday, March 14, 2016, drum.lu...@gmail.com wrote: > > On 15 March 2016 at 12:05, David G. Johnston > wrote: > >> On Mon, Mar 14, 2016 at 4:05 PM, David G. Johnston < >> david.g.johns...@gmail.com >> > wrote: >> >>> On Mon, Mar 14, 2016

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On Tue, Mar 15, 2016 at 2:01 PM, drum.lu...@gmail.com wrote: > >>> >> Not reading the documentation for functions you've never heard of makes >> the list. >> >> David J. >> > > INSERT INTO junk.test1 (account_id, code, info)SELECT account_id, > uuid_generate_v4(), infoFROM junk.test2; > > It wor

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On Tue, Mar 15, 2016 at 2:35 PM, drum.lu...@gmail.com wrote: > > > On 16 March 2016 at 10:30, David G. Johnston > wrote: > >> On Tue, Mar 15, 2016 at 2:01 PM, drum.lu...@gmail.com < >> drum.lu...@gmail.com> wrote: >> >>> >>>>> >

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On Tue, Mar 15, 2016 at 2:48 PM, drum.lu...@gmail.com wrote: > > >>> I wouldn't ask if I wouldn't have tested it! >>> >>> Will have a look. >>> >>> ​ >> I didn't asked if you tested what you did post. I asked if you tried >> anything else before asking to be fed the answer. If you did it w

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

On Thursday, March 17, 2016, Michael Paquier wrote: > On Fri, Mar 18, 2016 at 8:16 AM, Tom Lane > wrote: > > David Steele > writes: > >> On 3/17/16 7:00 PM, Tom Lane wrote: > >>> The message I saw was post-1-March. If it was in fact submitted in > >>> time for 2016-03, then we owe it a review.

Re: [GENERAL] Deleting schema - saving up space - PostgreSQL 9.2

On Wed, Mar 16, 2016 at 1:59 PM, drum.lu...@gmail.com wrote: > > 1 - The problem here is that a VACUUM FULL will lock all the DB to wirte, > am I right? My DB is 1.7 TB, so it will take a while and the System can't > be offline > >1. Migrate the files to the NFS server >2. Delete the sche

Re: [GENERAL] Deleting schema - saving up space - PostgreSQL 9.2

On Wed, Mar 16, 2016 at 2:27 PM, drum.lu...@gmail.com wrote: > > > On 17 March 2016 at 10:21, David G. Johnston > wrote: > >> On Wed, Mar 16, 2016 at 1:59 PM, drum.lu...@gmail.com < >> drum.lu...@gmail.com> wrote: >> >>> >>> 1 - The p

Re: [GENERAL] Insert data in two columns same table

On Wed, Mar 16, 2016 at 6:49 PM, Andreas Kretschmer wrote: > > > > "drum.lu...@gmail.com" hat am 17. März 2016 um > 02:34 > > geschrieben: > > > > > > I'm trying to insert data from TABLE A to TABLE B. > > > > 1 - Select billable_id from dm.billable > > 2 - Select mobiuser_id from ja_mobiusers >

Re: [GENERAL] Drop only temporary table

On Friday, March 18, 2016, Durumdara wrote: > > I want to avoid to remove any real table on resource closing (= dropping > of temporary table). > How to I force "drop only temporary"? Prefix, option, etc. > > If you have to explicitly drop a temporary table you are likely doing something wrong.

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

Figured out it had to be added to 2016-09...done On Wed, Mar 9, 2016 at 11:40 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > Adding -hackers for consideration in the Commitfest. > > Thanks! > > David J. > > >>>Original request by me > &

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

On Mon, Mar 21, 2016 at 8:03 AM, Robert Haas wrote: > On Sun, Mar 20, 2016 at 9:31 AM, Michael Paquier > wrote: > > And the patch attached gives the following output: > > With title: > > =# \watch 1 > > Watch every 1sSun Mar 20 22:28:38 2016 > > popo > > a > > --- > > 1 > > (1 row) > ​Thi

Re: [HACKERS] [GENERAL] Request - repeat value of \pset title during \watch interations

On Mon, Mar 21, 2016 at 10:14 AM, Tom Lane wrote: > Robert Haas writes: > > Well, the title isn't normally centered, but yeah, that is odd. Yeah, > > that is odd. Come to think of it, I think I might have expected the > > title to appear *above* "Watch every %s", not below it. That might > >

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

On Monday, March 21, 2016, Tom Lane wrote: > "David G. Johnston" > writes: > > I'd rather not omit sleep but removing "Watch every" is fine (preferred > > actually), so: > > Title Is Here Mon Mar 21 15:05:06 2016 (5s) > > Meh ... seem

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

On Monday, March 21, 2016, Robert Haas wrote: > On Mon, Mar 21, 2016 at 2:09 PM, David G. Johnston > > wrote: > > On Monday, March 21, 2016, Tom Lane > > wrote: > >> "David G. Johnston" > writes: > >> > I'd rather not omit sleep but

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

On Monday, March 21, 2016, Tom Lane wrote: > "David G. Johnston" > writes: > > I'll admit it's awkward because it's abbreviated but if someone enters > > \watch 5 and then sees (5s) in the title I think they can put two and two > > together. >

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

On Monday, March 21, 2016, Tom Lane wrote: > "David G. Johnston" > writes: > > On Monday, March 21, 2016, Tom Lane > > wrote: > >> What about just discarding the old format entirely, and printing one of > >> these two things: > >> > >

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

jd -h localhost; * create table foo (id text); * create role jd_role; * alter table foo owner to jd_role; * grant jd_role to boo; * \c jd boo * analyze foo; ​ On 02/29/2016 09:09 AM, David G. Johnston wrote: ​ Given these two things it seems the least difficult solution that doesn't make things

Re: [GENERAL] Only owners can ANALYZE tables...seems overly restrictive

On Thu, Mar 24, 2016 at 4:51 AM, Stephen Frost wrote: > David, > > * David G. Johnston (david.g.johns...@gmail.com) wrote: > > Which means that, aside from effort, the main blocking factors here are > > code complexity (which I understand) and limited grant "bits"

Re: [GENERAL] [HACKERS] Nested funtion

On Sun, Mar 27, 2016 at 9:14 PM, Sridhar N Bamandlapally < sridhar@gmail.com> wrote: > Hi > > Is there any way to create nested function? > > oracle to postgres migration required super function variable reference > into nested function without nested function parameter > > Oracle sample: > --

Re: [GENERAL] pg_restore casts check constraints differently

On Tue, Mar 29, 2016 at 2:45 PM, Tom Lane wrote: > Joshua Ma writes: > > This might not be a common case, but we're using pg_dump in a testing > > environment to check migrations - 1) we initialize the db from HEAD, > > pg_dump it, 2) we initialize the db from migration_base.sql, apply > > migra

Re: [GENERAL] pg_restore casts check constraints differently

On Tue, Mar 29, 2016 at 7:40 PM, Tom Lane wrote: > "David G. Johnston" writes: > > On Tue, Mar 29, 2016 at 2:45 PM, Tom Lane wrote: > >> It's not really different. What you're seeing is pg_dump (or actually > >> ruleutils.c) choosing to dump som

Re: [GENERAL] Plpsql connecting to more than one database?

On Wednesday, March 30, 2016, Francisco Reyes wrote: > Is it possible to have a plpsql program connect to 2 databases? > > This seems to imply it, > http://stackoverflow.com/questions/18257003/pl-sql-querying-a-table-on-multiple-databases > > However looking at the postgresql plpsql reference, >

Re: [GENERAL] Create trigger on Materialized View?

On Thu, Mar 31, 2016 at 9:30 AM, Matthew Syphus wrote: > I would like a trigger on a materialized view. Is this possible? I have > tried both an INSTEAD OF and BEFORE trigger with no luck. It responds > with: > > "project_milestone_mv" is not a table or view. > > It is absolutely present and sp

Re: [GENERAL] Non-default postgresql.conf values to log

On Thu, Apr 7, 2016 at 10:06 AM, Alex Ignatov wrote: > It matter when your pg host is down and all you have is log file. It is > very usual situation. You need historical info. ​I think this would be a good idea. Is it something you are thinking of writing now that you see PostgreSQL does not

Re: [GENERAL] Non-default postgresql.conf values to log

On Thu, Apr 7, 2016 at 10:37 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Apr 7, 2016 at 10:06 AM, Alex Ignatov > wrote: > >> It matter when your pg host is down and all you have is log file. It is >> very usual situation. You need historica

Re: [GENERAL] Bypassing NULL elements in row_to_json function

On Fri, Apr 8, 2016 at 8:53 AM, Raymond O'Donnell wrote: > On 08/04/2016 16:31, Michael Nolan wrote: > > I'm looking at the possibility of using JSON as a data exchange format > > with some apps running on both PCs and Macs. . > > > > The table I would be exporting has a lot of NULL values in i

Re: [GENERAL] 9.5 - Is there any way to disable automatic rollback?

On Saturday, April 9, 2016, durumd...@gmail.com wrote: > Dear Everybody! > > > See this sampe: > > StartTrans; > try > Update1; > Insert1; > Update2; // this cause error f.e. > Commit; > except > AnyChecks; > Rollback; > > When Update2 causes error, AnyChecks comes. > > In

Re: [GENERAL] Bypassing NULL elements in row_to_json function

On Sat, Apr 9, 2016 at 9:48 PM, Michael Nolan wrote: > > 2nd Followup: It turns out that loading a table from a JSON string is > more complicated than going from a table to JSON, perhaps for good reason. > There does not appear to be a direct inverse to the row_to_json() function, > but it wasn'

Re: [GENERAL] Bypassing NULL elements in row_to_json function

On Sun, Apr 10, 2016 at 7:49 AM, Michael Nolan wrote: > > > On Sun, Apr 10, 2016 at 2:30 AM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sat, Apr 9, 2016 at 9:48 PM, Michael Nolan wrote: >> >>> >>> 2nd Followup: It t

Re: [GENERAL] Really unique session ID - PID + connection timestamp?

On Sun, Apr 10, 2016 at 6:29 AM, durumd...@gmail.com wrote: > > Dear Alban! > > > 2016.04.10. 13:05 keltezéssel, Alban Hertroys írta: > >> On 10 Apr 2016, at 9:07, Durumdara wrote: >>> >>> Dear Adrian! >>> >>> Again. As I see the beginning blocks are removed by mailing system in >>> the code. >>

Re: [GENERAL] Bypassing NULL elements in row_to_json function

On Sun, Apr 10, 2016 at 8:39 AM, Michael Nolan wrote: > Here's what I did: > > \d gold1604_test > Table "uscf.gold1604_test" > Column | Type | Modifiers > +--+--- > data | json | > > Some sample data: > {"id":"1001","name":"MISNER, J > NATHAN","st":"NY","exp":"2012-0

Re: [GENERAL] Table seems empty but its size is in gigabytes

On Tue, Apr 12, 2016 at 9:11 AM, Juan Carlos Michaca Lucero < jc_mich2c...@yahoo.com.mx> wrote: > Hi PostgreSQL, > > I'm using PostgreSQL 9.3 running on Ubuntu Server. > > I have a complex function to populate a big table, in order to improve > performance; data is prepared in temporary tables bef

Re: [GENERAL] Table seems empty but its size is in gigabytes

On Tue, Apr 12, 2016 at 9:30 AM, John R Pierce wrote: > On 4/12/2016 9:16 AM, David G. Johnston wrote: > > Now my big table statistics shows tuples inserted and its size is in order >> of gigabytes, but a simple SELECT has no rows, is there any way to recover >> t

Re: [GENERAL] Freezing localtimestamp and other time function on some value

On Tue, Apr 12, 2016 at 8:37 AM, Alex Ignatov wrote: > > On 12.04.2016 18:01, Adrian Klaver wrote: > >> >> >>I do it by having the date be one of the function arguments and have > the default be something like current_date. When I test I supply a date to > override the default. This allows for te

Re: [GENERAL] Freezing localtimestamp and other time function on some value

On Tue, Apr 12, 2016 at 10:14 AM, Alex Ignatov wrote: > > > On 12.04.2016 19:45, David G. Johnston wrote: > > On Tue, Apr 12, 2016 at 8:37 AM, Alex Ignatov < > a.igna...@postgrespro.ru> wrote: > >> >> On 12.04.2016 18:01, Adrian Klaver wrote: >> &

Re: [GENERAL] SET prepared statement

On Wed, Apr 13, 2016 at 8:38 AM, Oliver Kohll wrote: > Hello, > > We currently use prepared statements for most of the work an app does, as > an SQL injection protection and for other reasons. > > There's one statement which can't be prepared: > > SET LOCAL myprefix.mysetting = 'my setting value'

Re: [GENERAL] Freezing localtimestamp and other time function on some value

On Wed, Apr 13, 2016 at 10:14 AM, Alex Ignatov wrote: > Some quick and dirty issue resolution is simple: > set search_path = my_time_schema on db layer. After that you dont need to > change any code. And can take for example freeze.fixed_date from config =) > where my_time_schema contains all tim

Re: [GENERAL] [HACKERS] sign function with INTERVAL?

On Wed, Apr 13, 2016 at 3:48 PM, Daniel Lenski wrote: > On Wed, Apr 13, 2016 at 12:35 PM, Tom Lane wrote: > > Jim Nasby writes: > >> Actually, after looking at the code for interval_lt, all that needs to > >> happen to add this support is to expose interval_cmp_internal() as a > >> strict funct

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, Apr 19, 2016 at 3:23 PM, drum.lu...@gmail.com wrote: > Hi all, > > I've got two tables: > > - users > - companies > > I'm trying to create a function that: > > >- if users.code is empty, it gives a default value >- And the increment_client_code in company should auto increment for

Re: [GENERAL] Enhancement Request

On Tue, Apr 19, 2016 at 5:59 PM, Melvin Davidson wrote: > > Enhancement or feature requests should probably go to *Custormer > Feedback * > https://postgresql.uservoice.com/forums/21853-general > ​I'm on the PostgreSQL home page: what serie

Re: [GENERAL] [ADMIN] how to upgrade PG

On Mon, Apr 18, 2016 at 10:19 PM, Huang, Suya wrote: > Hello, > > > > Our PG runs on Ubuntu boxes and it’s 9.3.4, thinking of upgrading to > 9.3.12 or 9.5.2. > > > > Is the normal approach of upgrade something like below, no matter if it’s > major version or minor version upgrade? My focus is on

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wednesday, April 20, 2016, Melvin Davidson wrote: > > > > > > > > *I am a bit confused. If this is the correct list for enhancement > requests, then why is that not explicitly stated in the list description? > Then again, none of the list descriptions mention they are are the correct > ones fo

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

than a year ago when you brought this up last time: If your application needs to maintain knowledge of aging it should record that information into user-space tables using whatever semantics it requires. ​Courtesy goes both ways and given your bare-bones request it is not surprising that I,

Re: [GENERAL] Function PostgreSQL 9.2

On Wed, Apr 20, 2016 at 2:51 PM, drum.lu...@gmail.com wrote: > 1 - The customer can add any value into users.code column > 2 - The customer can chose between *add or no**t* add the value on > users.code column > 3 - If users.code is null (because the customer's chosen not to add any > value in th

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 5:30 PM, Melvin Davidson wrote: > On Wed, Apr 20, 2016 at 8:01 PM, Adrian Klaver > wrote: > "I just cannot see that in the following: > pg_upgrade 9.4 --> 9.5" > The 9.5 database is the same as the 9.4 one." > > You are speaking of the case where relcreatedat did not/does

Re: [GENERAL] RLS policy dump/restore failure due to elided type-casts

On Wed, Apr 20, 2016 at 5:35 PM, Adrian Klaver wrote: > On 04/20/2016 05:18 PM, Karl Czajkowski wrote: > >> Our row level policies require very explicit casts in order to be >> accepted by the DB, but those casts are discarded in the dumped policy >> statements. Thus, an attempt to reload the dum

Re: [GENERAL] RLS policy dump/restore failure due to elided type-casts

On Wed, Apr 20, 2016 at 5:18 PM, Karl Czajkowski wrote: > > CREATE POLICY delete_stuff ON stuff > FOR DELETE USING ('example attribute value' = ANY ( ((SELECT > current_attributes()))::text[] )); > > The following (untested) structure should be immune to this problem...use the knowledge as yo

Re: [GENERAL] RLS policy dump/restore failure due to elided type-casts

On Wed, Apr 20, 2016 at 6:04 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Apr 20, 2016 at 5:18 PM, Karl Czajkowski wrote: > >> >> CREATE POLICY delete_stuff ON stuff >> FOR DELETE USING ('example attribute value' = ANY (

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 8:18 AM, Melvin Davidson wrote: > On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver > wrote: > >> On 04/21/2016 07:53 AM, Melvin Davidson wrote: >> >> >>> "Whether that is worthy or not is the point of your request and really >>> depends on more input." >>> Correct. And tha

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

On Thu, Apr 21, 2016 at 3:08 AM, Bráulio Bhavamitra wrote: > Hi all, > > I'm finally having performance issues with PostgreSQL when doing big > analytics queries over almost the entire database of more than 100gb of > data. > > And what I keep reading all over the web is many databases switching

Re: [GENERAL] Update field to a column from another table

Please don't top-post. > > -Original Message- > > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of > > drum.lu...@gmail.com > > Sent: Donnerstag, 21. April 2016 07:10 > > To: Postgres General > > Subject: [GENERAL] Update field to a col

Re: [GENERAL] Update field to a column from another table

On Thursday, April 21, 2016, drum.lu...@gmail.com wrote: > So when I run: > > UPDATE ja_jobs t2 >> SET time_job = t1.time_job >> FROM junk.ja_test t1 >> WHERE t2.id = t1.id >> AND t2.time_job IS DISTINCT FROM t1.time_job; > > > I get: > > UPDATE 2202 > > So I check the data by doing: > > select *

Re: [GENERAL] Proper relational database?

On Thu, Apr 21, 2016 at 1:36 PM, Guyren Howe wrote: > Anyone familiar with the issue would have to say that the tech world would > be a significantly better place if IBM had developed a real relational > database with an elegant query language rather than the awful camel of a > thing that is SQL.

Re: [GENERAL] Proper relational database?

On Fri, Apr 22, 2016 at 12:25 PM, Raymond Brinzer wrote: > So, let's just flat-out ask. > > Dear Important People: would the PostgreSQL project consider > supporting other query languages? Or creating a plug-in mechanism for > them, so that alternative interface languages could be added without

Re: [GENERAL] Proper relational database?

On Fri, Apr 22, 2016 at 12:54 PM, Guyren Howe wrote: > The SQL language is terrible but we can live with it. > ​If anything, and especially something as pervasive and esoteric as SQL, makes you question your desire to live please get help.​ > But the answer to "Are there any relational data st

[GENERAL] Is it possible to recover the schema from the raw files?

On Sunday, April 24, 2016, Karsten Hilbert > wrote: > On Sun, Apr 24, 2016 at 04:35:56PM +0100, Tomas J Stehlik wrote: > > >> Well if the corrupted raw files include the system information > >> then I think you are out of luck. > > > > Well, this topic is not about "luck". > > Surely, English isn'

Re: [GENERAL] Clarify "allow_system_table_mods"

On Monday, April 25, 2016, Melvin Davidson wrote: > I need clarification on allow_system_table_mods parameter > Per the documentation: > *Allows modification of the structure of system tables.* This is used by > initdb. This parameter can only be set at server start. > > However, attempting to mo

<    1   2   3   4   5   6   7   8   9   10   >