Re: [GENERAL] Unexpected result using floor() function

> I am running PostgreSQL 9.4.4 on Fedora 22. > > SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected. > > SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising. > > Please can someone explain the anomaly. I think I have a solution to my problem, but I

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 at 3:51 PM, drum.lu...@gmail.com >>> < >>> drum.lu...@

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On 3/14/2016 6:41 PM, drum.lu...@gmail.com wrote: So I'm doing: CREATE EXTENSION "uuid-ossp"; INSERT INTO junk.wm_260_billables2 (account_id, code, info) SELECT account_id, code || '_' || nextval('uuid_generate_v4()')::text, info FROM junk.wm_260_billables1; Getting the error:

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

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 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 >>> >> >

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 missed my prior email. > > You cannot COPY directl

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 15 March 2016 at 11:44, Brent Wood wrote: > Not best practice but perhaps viable... > > > In the target table add a serial datatype column as part of the unique > constraint. > > > Do not populate this column explicitly on insert, but have the db do it > for you. It will allocate an incrementa

Re: [GENERAL] Re: how to switch old replication Master to new Standby after promoting old Standby

On Mon, Mar 14, 2016 at 11:08 PM, John Lumby wrote: > And indeed in its debug I found > received chunk for file "postgresql.conf", offset 0, size 16482 > received chunk for file "postgresql.conf.20160314114055", offset 0, size 16464 > > And I now see in its description in the Doc that it intends t

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

> > > > On the target table, I've got a CONSTRAINT: > >> ALTER TABLE dm.billables >> ADD CONSTRAINT uc_billable_code_unique_per_account UNIQUE("account_id", >> "code"); > > > So I'm importing a CSV file with repeated values on the field "code" > Example: > >> 'Interpreting Normal/AH' >> 'Interpre

Re: [GENERAL] how to switch old replication Master to new Standby after promoting old Standby

On Mon, Mar 14, 2016 at 6:54 PM, Shulgin, Oleksandr wrote: > On Mon, Mar 14, 2016 at 6:28 PM, John Lumby wrote: >> 1. shut down both new Master and intended-to-be-new-Standby >> 2. on intended-to-be-new-Standby, remove the entire content of pg_xlog and >> the global/pg_control >> 3. from new Mast

[GENERAL] Re: how to switch old replication Master to new Standby after promoting old Standby

Many thanks Alex, I had not noticed pg_rewind before.   The name pg_rewind is not exactly very descriptive of what this utility does.    But it is exactly what I need,  thank-you. I tried it an eventually got it to work but did hit one strange problem - I ran it like this pg_rewind -D /mnt/bluebi

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On 15 March 2016 at 10:46, James Keener wrote: > Is a uuid a valid value in the application making use of the data? Why > can't you add the column to table b and then import, or use create the uuid > in the import select clause? I'm also having trouble understanding the > problem and why you've d

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

Is a uuid a valid value in the application making use of the data? Why can't you add the column to table b and then import, or use create the uuid in the import select clause? I'm also having trouble understanding the problem and why you've discounted the options you've not even told us you've c

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] BDR

Also, what did you run exactly (sanitized of course). On March 14, 2016 5:38:19 PM EDT, John R Pierce wrote: >On 3/14/2016 2:17 PM, Dustin Kempter wrote: >> However my instances are not on the same server and I attempted to >> simply add a host=(the ip) but that failed. Please help > >did you g

Re: [GENERAL] BDR

On 14-03-16 22:17, Dustin Kempter wrote: Hello all, I am attempting to set up BDR between 2 separate nodes. I have been following the guide and got to here http://bdr-project.org/docs/0.9.0/quickstart-enabling.html I am now stuck on this section "Then you run a function that identifies a BDR g

Re: [GENERAL] BDR

On 3/14/2016 2:17 PM, Dustin Kempter wrote: However my instances are not on the same server and I attempted to simply add a host=(the ip) but that failed. Please help did you get an error? if so what error, exactly? -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-gener

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

On 15 March 2016 at 10:29, David G. Johnston wrote: > On Mon, Mar 14, 2016 at 2:13 PM, drum.lu...@gmail.com < > drum.lu...@gmail.com> wrote: > >> Hi all, >> >> > favorite >> >> >> I've got 2 tables: >> >

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

[GENERAL] BDR

Hello all, I am attempting to set up BDR between 2 separate nodes. I have been following the guide and got to here http://bdr-project.org/docs/0.9.0/quickstart-enabling.html I am now stuck on this section "Then you run a function that identifies a BDR group that delineates a connection string

[GENERAL] Unique UUID value - PostgreSQL 9.2

Hi all, down votefavorite I've got 2 tables: Temp-Table Table-A Need to copy all the data from *Temp-Table* to *Table-A*. But there is a Constraint that does not allow me to have duplicated items. So

Re: [GENERAL] Unexpected result using floor() function

Hi, maybe this is a late reply, but also note that 4.725 alone already cannot be represented in floating point exactly (and this has nothing to do with Postgres). Just sum it up 100 times to "see" the round off error becoming visible: chris=# select sum(4.725::double precision) from generate_s

Re: [GENERAL] ON CONFLICT DO NOTHING RETURNING

On Mon, Mar 14, 2016 at 12:28 PM, Peter Devoy wrote: > Is there a reason DO NOTHING was not developed for use with RETURNING? I don't know what you mean. It should work fine with RETURNING. -- Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

Re: [GENERAL] Unexpected result using floor() function

On Mon, Mar 14, 2016 at 11:54 AM, Pujol Mathieu wrote: > Le 14/03/2016 15:29, Merlin Moncure a écrit : >> >> On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu >> wrote: >>> >>> Hi >>> I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ >>> build >>> 1600, 64-bit" on Windows 8 >>> I

[GENERAL] ON CONFLICT DO NOTHING RETURNING

Hi all Is it possible to have ON CONFLICT DO NOTHING RETURNING or must I use DO UPDATE? E.g. if the 'name' column of my 'animals' table is unique it seems a little silly having to do an arbitrary update to get an ID from the row: INSERT INTO animals (name) VALUES ('dog') ON CONFLICT (name) DO UP

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

HI: On Mon, Mar 14, 2016 at 8:02 PM, Ken Tanzer wrote: >> price: >> -- >> xx5.45 >> xx1.20 >> 99 >> xx2.40 ... > I appreciate the comment and explanation. But your example shows numbers > where the trailing 0s are not suppressed. Yeah, my fault, but 5.45, 1.25, 99.00, 2.45 will create

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

> > > Well, this may be a good enhancement request, add something like > d=decimal point, supressed if alone. > > Yeah. Maybe that's all that need to be said. :) > > In particular, one might reasonably choose a format string like > 'FM999,999D99' and not realize it will fail on whole numbers. Is

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

Hi Ken: On Mon, Mar 14, 2016 at 7:33 PM, Ken Tanzer wrote: > Thanks for all the info and suggestions. I'll just observe that sure, you > can do it with a regex, but I'm still surprised that this can't be done with > to_char. Well, this may be a good enhancement request, add something like d=

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

On Mon, Mar 14, 2016 at 8:22 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > 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: >> >> > Typical

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

Hi David: On Mon, Mar 14, 2016 at 4:22 PM, David G. Johnston wrote: >> But a right-aligning string output routine needs to be used. ... >> Summarising, any combination can be easily done with a single round of >> replace. > See also: > http://www.postgresql.org/docs/9.5/interactive/functions-s

Re: [GENERAL] enum bug

Elein Mustain el...@varlena.com 510-637-9106 > On Mar 13, 2016, at 7:22 PM, Alvaro Herrera wrote: > > Elein wrote: > > Hi Elein, > >> * When an insert into an enum column fails give the person a hint as to >> valid values > >> -- Lousy message. Show enum list. >> insert into badinfo val

Re: [GENERAL] Unexpected result using floor() function

> I am running PostgreSQL 9.4.4 on Fedora 22. > > SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected. > > SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising. > > Please can someone explain the anomaly. Thanks for all the responses. Plenty of workaro

Re: [GENERAL] how to switch old replication Master to new Standby after promoting old Standby

On Mon, Mar 14, 2016 at 6:28 PM, John Lumby wrote: > > From: johnlu...@hotmail.com > To: pgsql-general-ow...@postgresql.org > Subject: how to switch old replication Master to new Standby after > promoting old Standby > Date: Mon, 14 Mar 2016 13:23:29 -0400 > > In a scenario involving replication

[GENERAL] how to switch old replication Master to new Standby after promoting old Standby

From: johnlu...@hotmail.com To: pgsql-general-ow...@postgresql.org Subject: how to switch old replication Master to new Standby after promoting old Standby Date: Mon, 14 Mar 2016 13:23:29 -0400 In a scenario involving replication where no failure occurs but I want to interchange Master <->Stand

Re: [GENERAL] Unexpected result using floor() function

On 03/14/2016 09:54 AM, Pujol Mathieu wrote: Le 14/03/2016 15:29, Merlin Moncure a écrit : On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu wrote: Hi I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build 1600, 64-bit" on Windows 8 I also notice that SELECT floor(4.725 * po

Re: [GENERAL] enum bug

Joshua D. Drake wrote: > On 03/14/2016 08:48 AM, Alvaro Herrera wrote: > >(*) Yes, I'm being a bit sarcastic here, sorry about that. I actually > >learned quite a bit of database design and related topics by translating > >the "General Bits" column she used to write, many years ago. > > Your ans

Re: [GENERAL] Unexpected result using floor() function

Le 14/03/2016 15:29, Merlin Moncure a écrit : On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu wrote: Hi I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build 1600, 64-bit" on Windows 8 I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work as expected I

Re: [GENERAL] enum bug

On Sun, Mar 13, 2016 at 10:20:05PM -0400, Melvin Davidson wrote: > The point is, they are an archaic data type and it's a hell of a lot easier > to use Foreign Keys to insure integrity. So don't use them? Nobody, surely, is forcing you to use enums. I recall when enums were added. I recall thin

Re: [GENERAL] enum bug

On Mon, Mar 14, 2016 at 12:07 PM, Joshua D. Drake wrote: > On 03/14/2016 09:02 AM, David G. Johnston wrote: > > ​The one nice thing about enums is that you get two concepts in one >> column - a human readable label and a system used ordering. >> >> i.e., "SELECT enum_value FROM tbl ORDER BY enum_

Re: [GENERAL] enum bug

On 03/14/2016 09:02 AM, David G. Johnston wrote: ​The one nice thing about enums is that you get two concepts in one column - a human readable label and a system used ordering. i.e., "SELECT enum_value FROM tbl ORDER BY enum_value" actually ​ ​gives you a meaningful order without having to carr

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] 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 03/14/2016 08: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 I did to figure out that enum_range

Re: [GENERAL] enum bug

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 I did to figure out that enum_ranges only > >worked on existing data.

Re: [GENERAL] enum bug

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 I did to figure out that enum_ranges only worked on existing data. Sorry if they were confusing. But the w

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 > > format all values, even those

Re: [GENERAL] Unexpected result using floor() function

On Mon, Mar 14, 2016 at 9:29 AM, Merlin Moncure wrote: > On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu > wrote: >> Hi >> I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build >> 1600, 64-bit" on Windows 8 >> I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5

Re: [GENERAL] Unexpected result using floor() function

On 03/14/2016 07:21 AM, Pujol Mathieu wrote: Hi I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build 1600, 64-bit" on Windows 8 I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work as expected Seems to be related to the two forms of power(): http://ww

Re: [GENERAL] Unexpected result using floor() function

On Mon, Mar 14, 2016 at 9:21 AM, Pujol Mathieu wrote: > Hi > I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build > 1600, 64-bit" on Windows 8 > I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work as > expected I don't think this is a bug -- just peculi

Re: [GENERAL] Unexpected result using floor() function

100 is an integer power(10,2) is a double precision. Try this one: SELECT floor(4.725 * 100::double precision + 0.5); On Mon, Mar 14, 2016 at 10:11 AM, Frank Millman wrote: > Hi all > > I am running PostgreSQL 9.4.4 on Fedora 22. > > SELECT floor(4.725 * 100 + 0.5) returns 473, which is wh

Re: [GENERAL] Unexpected result using floor() function

Hi I reproduce your bug using "PostgreSQL 9.3.6, compiled by Visual C++ build 1600, 64-bit" on Windows 8 I also notice that SELECT floor(4.725 * power(10, 2)::numeric + 0.5) work as expected Mathieu Le 14/03/2016 15:11, Frank Millman a écrit : Hi all I am running PostgreSQL 9.4.4 on Fedora 2

[GENERAL] Unexpected result using floor() function

Hi all I am running PostgreSQL 9.4.4 on Fedora 22. SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected. SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising. Please can someone explain the anomaly. Thanks Frank Millman

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

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 > format all values, even those with zero pennies, to the same precision. > Typically when displaying such

Re: [GENERAL] MongoDB 3.2 beating Postgres 9.5.1?

On Mon, Mar 14, 2016 at 8:31 AM, Dmitry Dolgov wrote: > As far as I know there isn't much to do about caching. I don't know if it's > appropriate, but you can manually warm-up the cache (something like `cat > /var/lib/postgresql/9.5/main/base/*/* > /dev/null`). pg_prewarm may help as well. This ha

Re: [GENERAL] MongoDB 3.2 beating Postgres 9.5.1?

Hi, Paul I agree with Oleg, EDB benchmarks are strange sometimes. I did the same benchmarks several months ago. I never noticed the cache influence back then, so I tried to reproduce your situation now (on a 5*10^6 records although). I started to play with db cache (using `echo 3 > /proc/sys/vm/dr