Re: [GENERAL] time series data

On Mon, Oct 2, 2017 at 10:27 AM, Khalil Khamlichi wrote: > we have records like this > > ccdb1=# select user_name, agent_status, event_time from cc_events ; > > user_name | agent_status | event_time > ---+--+- > user1 | ready| 2017-01-01 10

Re: [GENERAL] archive_command not being executed

Hi Eric, Thanks for using PostgreSQL! On Fri, Nov 10, 2017 at 9:26 AM, Paul Jungwirth wrote: > Oh this has happened to me before. :-) On SB1 you need to set > archive_mode to always (not on). Otherwise it is ignored when running as a > standby. It looks to me like this feature was not added unt

Re: [GENERAL] query by partial timestamp

is more likely to use an index (when available) so I suspect this is the way to go, but would an index based on extract(YEAR...) negate this difference? Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] query by partial timestamp

On Wednesday, January 9, 2013 at 04:42, Michael Nolan wrote: On 1/8/13, Gavan Schneider wrote: 2. SELECT ... WHERE '2011-01-01'::TIMESTAMP <= col_of_type_timestamp ANDcol_of_type_timestamp <= '2011-12-31'::TIMESTAMP; This won't

[GENERAL] >

On Saturday, January 12, 2013 at 04:49, Gavin Flower wrote: On 12/01/13 06:45, Bosco Rama wrote: Shouldn't the value for theta be: 2 * pi() * random() Bosco. Very definitely! :-) One could also ask if the value for theta shouldn't be: tau() * random() :-) Regard

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

our will be deemed to be too rusted in place for change, or if this proposal has too many adverse consequences, but hope springs eternal. :) Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

noise/confusion. Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

On Monday, January 21, 2013 at 12:06, Kevin Grittner wrote: Adrian Klaver wrote: [Actually Gavan Schneider wrote this, don't blame Adrian :] I see where my confusion lies. There are two proposals at work in the above: "Taking another tangent I would much prefer the default time t

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

On Monday, January 21, 2013 at 10:53, Steve Crawford wrote: On 01/21/2013 02:48 PM, Gavan Schneider wrote: Taking another tangent I would much prefer the default time to be 12:00:00 for the conversion of a date to timestamp(+/-timezone). Propose: '2013-12-25'::timestamp ==&g

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

just one of several scenarios where the date might get changed in ways that could be difficult to trace... caveat coder. Thanks again everyone for a lot more clarity in my thinking about dates times and timezones. Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

On 01/21/2013 07:40 PM, Gavan Schneider wrote: ... The points raised by Adrain have prompted some more research on my part and I am intrigued to learn that on one day of the year in many countries (e.g., Brazil) where daylight conversion happens over midnight the local-time version of

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

amed on a government, and, we can only hope ISO-8601 will prevent more examples being created. You sound as though you really need, and/or already have, a dedicated datatype... if only to stop 'the system' from 'fixing' such weirdness. Regards Gavan Schneider -- Sent

Re: [GENERAL] Throttling Streamming Replication

of the fixed-length WAL file) then compress-transmit-decompress the result you will get much better use of the available bandwidth between master and slave servers. Specifically you will only be sending information that is needed, and smaller data chunks are faster data chunks. Regards Gavan Sch

Re: [GENERAL] Cast double precision to integer & check for overflow

without fear of overflow. (And yes, I do feel stupid having to ask this question here.) If in doubt the Novice list is designed for those questions where feelings of impending stupidity lurk. Regards Gavan Schneider (who considers himself a novice) -- Sent via pgsql-general mailing lis

Re: [GENERAL] DEFERRABLE NOT NULL constraint

consider implementing non-standard behaviour, but if something is in the standard I can't see why it shouldn't be implemented, esp. when there is no compulsion for it to be used. Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

[GENERAL] Re: [NOVICE] Problems with ñ and tildes / CSV import problems in PostgreSQL 9.1

On Wednesday, February 6, 2013 at 11:56, 2jt6w5k...@sneakemail.com (Zach Seaman znseaman-at-gmail.com |pg-gts/Basic|) wrote: This a similar question to this one , so I have encoded a database with LATIN-1 as sugge

Re: [GENERAL] DEFERRABLE NOT NULL constraint

straints all look very deferrable in this definition. If that's the case, why are we having this discussion if the requested functionality/compliance is already present? (As I have said already) I really must have missed something so am standing by for the 'gotcha'... please s

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Friday, February 8, 2013 at 19:34, Albe Laurenz wrote: Gavan Schneider wrote: Referring to: <http://www.postgresql.org/docs/current/static/sql-createtable.html> I really must have missed something so am standing by for the 'gotcha'... please supply :) Further down on th

Re: [GENERAL] Swapping volumes under tablespaces: supported?

mount/unmount/move tablespace a reality be my guest, but, be warned, there seem to be some very fundamental barriers. Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] Money casting too liberal?

cording to the situation, i.e., not just two decimal places... another setting. Personally I have ignored the money type in favour of numeric. Money seemed to do too much behind the scenes for my taste, but, that's me being lazy as well, I haven't spend much time trying to understan

Re: [GENERAL] Money casting too liberal?

d? :) Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Money casting too liberal?

. The actual values within the column remain as simple integers. This is mostly based on performance issues. If the MONRY type is to be used it has to offer real performance benefits over bespoke NUMERIC applications. Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] Money casting too liberal?

d to be thrown when it looks wrong 123,456.789.00 -- since this is likely to be corrupted data, and finally gets me back to the issue raised by OP. :) Hope this hasn't been too much of a ramble. Regards, and happy (Western) Easter to all, Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Money casting too liberal?

handled within established industry traditions and has special benefits with externally provided data... values will enter the dB with sign conventions properly observed. Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subs

Re: [GENERAL] Money casting too liberal?

being mentioned approaches one. Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Money casting too liberal?

On 30/3/13 at 12:58 AM, D'Arcy J.M. Cain wrote: On Sat, 30 Mar 2013 12:04:21 +1100 Gavan Schneider wrote: No MONEY column would be complete without the ability to specify whether it is normally DEBIT or CREDIT (or in my preferred case That seems extreme. What use case would there ev

Re: [GENERAL] Money casting too liberal?

On 31/3/13 at 5:20 AM, D'Arcy J.M. Cain wrote: On Sun, 31 Mar 2013 21:57:49 +1100 Gavan Schneider wrote: On 30/3/13 at 12:58 AM, D'Arcy J.M. Cain wrote: That seems extreme. What use case would there ever be for making a column always debit or always credit? I have a G/L syste

Re: [GENERAL] Using varchar primary keys.

an readable/meaningful so reports can be useful without always back joining the full name. Sequential integers rarely fulfil this role as implied by the original question. Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Why not cascade? (was: Using varchar primary keys)

into account, but what is the basis for such a general prohibition on a modern SQL dB? why not use the feature? Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BEFORE UPDATE trigger doesn't change column value

/www.postgresql.org/docs/9.2/static/sql-createtrigger.html> Is the function ever getting called? refer: <http://www.postgresql.org/docs/9.2/static/plpgsql-errors-and-messages.html> Regards Gavan Schneider -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To ma

Re: [GENERAL] How tö select a column?

On 2/18/17 at 3:33 AM, Egon Frerich wrote: I have a table with two columns with type money. If column 'a' has an amount > 0 then this amount is wanted else the amount from column 'b'. Examples in 4.2.14 SELECT CASE WHEN a > 0 THEN a ELSE b END FROM WHERE ;

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

excess leading zeros when building an integer value. (I don't think this breaks anything.) Other than that there really isn't a realisable consistent behaviour beyond the current strict bitwise interpretation. Specifically any behaviour which tries to promote or truncate some

Re: [GENERAL] Postgresql and github

Hi Poul, and thanks for using PostgreSQL! I've also been a very heavy user of Oracle and now a heavy user of PostgreSQL. I remember the days before Oracle acquired the RMAN software and bundled it with their database. Not so long ago, doing backups on Oracle wasn't so different from PostgreSQL;

[GENERAL] Window Function API Performance

able to explain why I get such bad runtimes? And even better, do you see a way to speed up WinGetFuncArgInPartition or replace it by something more performant? Would linear access be faster? Thank you for your support. Thilo Schneider P.S.: I know my question might be better suited the hackers

[GENERAL] Conversion of columns during CSV Import

Hello, is there any possibility to convert special columns during an CSV import via COPY? For example: HELLO;WORLD;;011001 should be converted to a character field 011001 to the date 10th January 2001 For the moment the only idea we have is, to import the CSV into a TEMP table and

Re: [GENERAL] [BUGS] Prepared Statement Name Truncation

ers, could give standards compliance in identifier length while still keeping the working tables compact. 3. (Wild speculation) There may be a "sweet spot" using even shorter identifiers than is the case now, with full disambiguation, which might improve overall perfor

Re: [GENERAL] ERROR: query has no destination for result data

On Friday, November 23, 2012 at 21:36, Peter Kroon wrote: Hello, I wish to return the SELECT statement. Ho can I achieve this? DO $$ DECLARE v_some_id int=14; BEGIN /* more queries here... */ SELECT 'this is text'; END

Re: [GENERAL] youtube video on pgsql integrity

build myself I have the ./configure output files, and will see if a core dump has turned up somewhere. Anything else to add? and, what is the next step? Regards Gavan Schneider PS. Of course I will smile bravely if this is the postgresql equivalent of being Rick rolled :) -- Sent via

Re: [GENERAL] youtube video on pgsql integrity

Tom, thanks for the reply On Thursday, November 29, 2012 at 12:58, Tom Lane wrote: Gavan Schneider writes: pendari=> select now()/0; Bus error: 10 [ scratches head... ] I get the expected error report on my own OS X 10.7.5 machine (though it's just plain Lion not Server). As of

Re: [GENERAL] question about varchar

ems). There is no speed performance difference between them, but lots of design advantages in favour of text (it's much more 'future proof'). These ideas are much better explained, and tested here: <http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/>

[GENERAL] Set Returning C-Function with cache over multiple calls (with different arguments)

he object is by far the most expensive part in the desired function, it should be possible to create the cache only once over the whole query - using only the arguments in the array, which do not change over multiple calls. Is there a way around this problem? Another pointer I could use and do not know

Re: [GENERAL] Set Returning C-Function with cache over multiple calls (with different arguments)

PG_RETURN_NULL(); } while (0); // Of course, SRF_RETURN_DATUM has to be adapted the same way! } ------- snip --- Regards, Thilo Schneider -- Sent via pgsql-general mailing list (pg

[GENERAL] pg_restore

Hi, I have a dump that was generated with pg_dump in 8.0-beta5 and I'm trying to restore with pg_restore in 8.0-RC3. The pg_dump command was: # pg_dump -Ft -o -b database The pg_restore command was: # pg_restore -Ft -d database When I execute pg_restore I get this error: pg_restore: [archiver] un

Re: [GENERAL] pg_restore

database database.dump Using a dump file generated in pgadmin3, works fine... but pgadmin exports some strange things that are not related to the database. What can be wrong ? Cesar On Mon, 2005-01-17 at 18:42, Tom Lane wrote: > Cesar Schneider <[EMAIL PROTECTED]> writes: > > but is not

[GENERAL] dump 7.3 into 7.2?

I need to transfer a dump from a psql 7.3 database to a 7.2 database. Is there any way to do this? -- [ Russ Schneider (a.k.a. Sugapablo) ] [ http://www.sugapablo.com <--music ] [ http://www.sugapablo.net <--personal ] [ [EMAIL PROTECTED] <--

[GENERAL] Change onership of database and all tables?

In 7.2, how would you change ownership of a database and all its tables and sequences? Right now everything is owned by postgres and I want to change ownership to a regualar user. -- [ Russ Schneider (a.k.a. Sugapablo) ] [ http://www.sugapablo.com <--music ] [ h

Re: [GENERAL] several messages

On Fri, 26 Dec 2003, Russ Schneider wrote: > I need to transfer a dump from a psql 7.3 database to a 7.2 database. > > Is there any way to do this? In case anyone wants to know (or find out later searching the archives), I was able to do this with minimal effort. Here's what

[GENERAL] piping output to file

Is there an easy way to pipe a select statement's output to a file? -- [ Russ Schneider (a.k.a. Sugapablo) ] [ http://www.sugapablo.com <--music ] [ http://www.sugapablo.net <--personal ] [ [EMAIL PROTECTED] <--jabber IM ] ---(en

[GENERAL] Tuning queries on large database

for your help ! *Les points de vue exprimes sont strictement personnels et * * n'engagent pas la responsabilite de METEO-FRANCE. * **

Re: [GENERAL] [PERFORM] Tuning queries on large database

[PERFORM] Tuning queries on large database >thread-index: AcR6Iae9QRnQrjxYRJyInj9KrC3FYQAAOJgQ >From: "Merlin Moncure" <[EMAIL PROTECTED]> >To: "Valerie Schneider DSI/DEV" <[EMAIL PROTECTED]> >Cc: <[EMAIL PROTECTED]> >Content-Transfer-Encoding