date:20160314

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 amounts I'd right-justify the values and thus
> cause the decimals to line up.

I do format with the .00 too, just wanted to point that the lining up
is easy if you just substitute '.00$' or '\.$' with the correct amount
of space, something like:

s=> select val, tc, '"'||tc||'"' as quoted,
regexp_replace(tc,'\.00$','   ') as replaced from (select val,
to_char(val::decimal(6,2),'999,999D99') as tc from (values
(1),(1.05),(0)) as v(val)) as w;
 val  | tc  |quoted |  replaced
--+-+---+-
1 |1.00 | "   1.00" |1
 1.05 |1.05 | "   1.05" |1.05
0 | .00 | ".00" |
(3 rows)

Although I dislike 0 as space, so I normally use '0':

s=> select val, tc, '"'||tc||'"' as quoted,
'"'||regexp_replace(tc,'\.00$','   ')||'"' as replaced from (select
val, to_char(val::decimal(6,2),'999,990D99') as tc from (values
(1),(1.05),(0)) as v(val)) as w;
 val  | tc  |quoted |   replaced
--+-+---+---
1 |1.00 | "   1.00" | "   1   "
 1.05 |1.05 | "   1.05" | "   1.05"
0 |0.00 | "   0.00" | "   0   "
(3 rows)

And, if you want to use FM but make them line up on the right is doable too:

s=> select val, tc, '"'||tc||'"' as quoted,
'"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
(1),(1.05),(0)) as v(val)) as w;
 val  |  tc  | quoted | replaced
--+--++--
1 | 1.   | "1."   | "1   "
 1.05 | 1.05 | "1.05" | "1.05"
0 | 0.   | "0."   | "0   "
(3 rows)

But a right-aligning string output routine needs to be used.


cdrs=> select val, tc, '"'||tc||'"' as quoted,
'"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
(1234),(1.05),(0)) as v(val)) as w;
 val  |   tc   |  quoted  |  replaced
--++--+
 1234 | 1,234. | "1,234." | "1,234   "
 1.05 | 1.05   | "1.05"   | "1.05"
0 | 0. | "0." | "0   "
(3 rows)

Summarising, any combination can be easily done with a single round of replace.

Francisco Olarte.


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

[GENERAL] Unexpected result using floor() function

2016-03-14 Thread Frank Millman

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] Unexpected result using floor() function

2016-03-14 Thread Pujol Mathieu


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


--
Mathieu PUJOL
Ingénieur Réalité Virtuelle
Tel : 05.81.33.13.36
REAL FUSIO - 3D Computer Graphics
9, rue Paul Mesple - 31100 TOULOUSE - FRANCE
mathieu.pu...@realfusio.com - http://www.realfusio.com

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

2016-03-14 Thread Vick Khera

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 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] Unexpected result using floor() function

2016-03-14 Thread Merlin Moncure

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 peculiarities of floating point math.

merlin


-- 
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] Unexpected result using floor() function

2016-03-14 Thread Adrian Klaver


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://www.postgresql.org/docs/9.4/interactive/functions-math.html

FunctionReturn Type
power(a dp, b dp)   dp  
power(a numeric, b numeric) numeric 

So just doing:

test=> select floor(4.725 * power(10, 2.0) + 0.5);
 floor
---
   473
(1 row)


works.



Mathieu

Le 14/03/2016 15:11, Frank Millman a écrit :

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


--
Mathieu PUJOL
Ingénieur Réalité Virtuelle
Tel : 05.81.33.13.36
REAL FUSIO - 3D Computer Graphics
9, rue Paul Mesple - 31100 TOULOUSE - FRANCE
mathieu.pu...@realfusio.com  -http://www.realfusio.com




--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Unexpected result using floor() function

2016-03-14 Thread Merlin Moncure

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) work as
>> expected
>
> I don't think this is a bug -- just peculiarities of floating point math.

To be more clear: the problem is neither with pow() or floor().  The issue is:

postgres=# SELECT (4.725 * 100.0 + 0.5 ) = 473;
 ?column?
──
 t
(1 row)

Time: 0.387 ms
postgres=# SELECT (4.725 * 100.0::FLOAT8 + 0.5 ) = 473;
 ?column?
──
 f

The workaround is to use fixed point or build in epsilon tolerances in
any case where you are using decimal numbers and expect precise
results.

merlin


-- 
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] 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 with zero pennies, to the same precision.
> > Typically when displaying such amounts I'd right-justify the values and
> thus
> > cause the decimals to line up.
>
> But a right-aligning string output routine needs to be used.
>
>
> cdrs=> select val, tc, '"'||tc||'"' as quoted,
> '"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
> to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
> (1234),(1.05),(0)) as v(val)) as w;
>  val  |   tc   |  quoted  |  replaced
> --++--+
>  1234 | 1,234. | "1,234." | "1,234   "
>  1.05 | 1.05   | "1.05"   | "1.05"
> 0 | 0. | "0." | "0   "
> (3 rows)
>
> Summarising, any combination can be easily done with a single round of
> replace.
>
>
See also:

http://www.postgresql.org/docs/9.5/interactive/functions-string.html


format(formatstr text [, formatarg "any" [, ...] ])

David J.

Re: [GENERAL] enum bug

2016-03-14 Thread Joshua D. Drake


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 way enum
values are currently displayed is confusing.

Enums are evil because of the difficulty working with them. They should
be used for a fixed set of valid values. Otherwise use a lookup table.


"Otherwise use a lookup table."

That is the solution to this problem.

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
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] enum bug

2016-03-14 Thread Alvaro Herrera

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. Sorry if they were confusing. But the way enum
> >values are currently displayed is confusing.
> >
> >Enums are evil because of the difficulty working with them. They should
> >be used for a fixed set of valid values. Otherwise use a lookup table.
> 
> "Otherwise use a lookup table."
> 
> That is the solution to this problem.

You know, I suspect this Elein person may know a bit about these
database thingies(*).

I don't think she is complaining because she cannot find her way around
designing a garment color table; rather it seems to me she is pointing
out actual problems in our design of the enum feature because she would
like to see it improve.  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 are missing the point.

(*) 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.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

-- 
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] enum bug

2016-03-14 Thread Joshua D. Drake

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_ranges only
worked on existing data. Sorry if they were confusing. But the way enum
values are currently displayed is confusing.

Enums are evil because of the difficulty working with them. They should
be used for a fixed set of valid values. Otherwise use a lookup table.

"Otherwise use a lookup table."

That is the solution to this problem.

You know, I suspect this Elein person may know a bit about these
database thingies(*).

I am fully aware of who Elein is.

I don't think she is complaining because she cannot find her way around
designing a garment color table; rather it seems to me she is pointing
out actual problems in our design of the enum feature because she would
like to see it improve.

And what I am saying, is just use a lookup table instead. If you want to
spend your valuable time on ENUM design, go for it. Nobody is stopping
you or suggesting *you* shouldn't.

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 are missing the point.

1. I thought ENUMS were not needed in the first place. I still do.

2. I never called anyone silly, nor was I derogatory or dismissive. I
offered, my solution to this problem because ENUMS are a pain in the
butt and have been since the original implementation. I have yet to see
a valid reason to use them instead of a lookup table. I have even
written articles about the three basic solutions:

https://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/

(*) 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 answer presumes some personal issue with Elein. I don't know why
that is. I gave a technical answer to a technical problem.

Sincerely,

--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

--
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] 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 I did to figure out that enum_ranges only
> > >worked on existing data. Sorry if they were confusing. But the way enum
> > >values are currently displayed is confusing.
> > >
> > >Enums are evil because of the difficulty working with them. They should
> > >be used for a fixed set of valid values. Otherwise use a lookup table.
> >
> > "Otherwise use a lookup table."
> >
> > That is the solution to this problem.
>
> You know, I suspect this Elein person may know a bit about these
> database thingies(*).
>
> I don't think she is complaining because she cannot find her way around
> designing a garment color table; rather it seems to me she is pointing
> out actual problems in our design of the enum feature because she would
> like to see it improve.  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 are missing the point.
>
>
I still cannot figure out what the actual problem is that a lookup table
is the solution.

We already have a way to show all the possible enum values for a given enum
independent of whether it is use in a table or not.

The fact that we requiring passing a null instance instead of an OID might
be unusual but our recent json_to_record stuff does the exact same thing so
unusually or not it is (now?) idiomatic PostgreSQL.

David J.

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 are missing the point.
>>
>
> 1. I thought ENUMS were not needed in the first place. I still do.
>
> 2. I never called anyone silly, nor was I derogatory or dismissive. I
> offered, my solution to this problem because ENUMS are a pain in the butt
> and have been since the original implementation. I have yet to see a valid
> reason to use them instead of a lookup table. I have even written articles
> about the three basic solutions:
>
>
> https://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/


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 carry around, or relink to,
a lookup table to get an ordering column.

Now, this is a bit overrated since you immediately lose that ability if
you export to a Spreadsheet program, or otherwise lose the ordering nature
during a convert-to-text operation.



David J.

Re: [GENERAL] enum bug

2016-03-14 Thread Joshua D. Drake


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 carry around, or relink
to, a lookup table to get an ordering column.

Now, this is a bit overrated since you immediately lose that ability if
you export to a Spreadsheet program, or otherwise lose the ordering
nature during a convert-to-text operation.



I do not suggest that ENUMS are useless just that there are more 
flexible and reasonable ways (as a whole) to do what ENUMS provide.


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
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] enum bug

2016-03-14 Thread Melvin Davidson

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_value" actually
>> 
>> gives you a meaningful order without having to carry around, or relink
>> to, a lookup table to get an ordering column.
>>
>> Now, this is a bit overrated since you immediately lose that ability if
>> you export to a Spreadsheet program, or otherwise lose the ordering
>> nature during a convert-to-text operation.
>>
>>
> I do not suggest that ENUMS are useless just that there are more flexible
> and reasonable ways (as a whole) to do what ENUMS provide.
>
>
> Sincerely,
>
> JD
>
> --
> Command Prompt, Inc.  http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


It's not that hard to get all the valid enum values for a particular enum
type.
Either of the queries below works just fine.

SELECT e.enumtypid,
   e.enumlabel,
   e.enumsortorder
  FROM pg_enum e
 WHERE e.enumtypid = {the enum oid}
 ORDER BY 1, enumsortorder;

SELECT e.enumlabel,
   e.enumsortorder,
   e.enumtypid
  FROM pg_type t
  JOIN pg_enum e ON e.enumtypid = t.oid
 WHERE t.typtype = 'e'
  AND t.typname = {the enum name}
 ORDER BY 1, enumsortorder;

However, IMHO, enums are archaic and Foreign Keys (Parent/Child) is the
better way to go.
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] enum bug

2016-03-14 Thread Andrew Sullivan

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 thinking at the time that
they were a bad idea and that you should do such things properly with
CHECK constraints and so on.  But people wanted them because lots of
(IMO poor) designs rely on them.  That seems like a good reason to me
to keep them around, and not to use them :)

Best regards,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
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] Unexpected result using floor() function

2016-03-14 Thread Pujol Mathieu




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 don't think this is a bug -- just peculiarities of floating point math.

merlin


I think also that it is a float precision issue but the weird thing is 
that both calls without floor return 273. Maybe the display method make 
a rounding ?


SELECT 4.725 * power(10, 2)::numeric + 0.5) => 273.000
SELECT (4.725 * power(10, 2) + 0.5) => 273





--
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] enum bug

2016-03-14 Thread Alvaro Herrera

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 answer presumes some personal issue with Elein. I don't know why that
> is.

Quite the contrary.  I hold the utmost respect for her and her work.  I
think she has something useful to say on this topic (and many others)
and the community is being myopic by saying that "enums are useless
anyway, so why improve on them, use lookup tables" which is essentially
what is being repeated over and over.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

-- 
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] Unexpected result using floor() function

2016-03-14 Thread Adrian Klaver


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 * power(10, 2)::numeric + 0.5)
work as
expected

I don't think this is a bug -- just peculiarities of floating point math.

merlin



I think also that it is a float precision issue but the weird thing is
that both calls without floor return 273. Maybe the display method make
a rounding ?

SELECT 4.725 * power(10, 2)::numeric + 0.5) => 273.000
SELECT (4.725 * power(10, 2) + 0.5) => 273


First I would say the results you are seeing are 473.000 and 473.

There are two version of power():

http://www.postgresql.org/docs/9.4/interactive/functions-math.html

The version you are using returns a float. In your first example you 
turn that into a numeric and the overall output becomes numeric, hence 
the trailing 0's. In your second example you leave it as float and the 
output is rounded to 473. The 473.000 is not equal to the 473. To borrow 
from Merlins example:


test=> SELECT 4.725 * power(10, 2)::numeric + 0.5 =473;
 ?column?
--
 t
(1 row)

test=> SELECT (4.725 * power(10, 2) + 0.5) = 473;
 ?column?
--
 f
(1 row)










--
Adrian Klaver
adrian.kla...@aklaver.com


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

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

2016-03-14 Thread John Lumby

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 <->Standby,
*and* want to avoid making another full base backup and rsyncing it across,
I have found it easy to promote old Standby to new Master with pg_ctl promote,
but very difficult to restart the old Primary as a new Standby *without* 
performing new base backup.

Assume current 9.5.1 and using streaming replication with a named replication 
slot if relevant

Second assumption - I am able to temporarily prevent any relational updates to 
the database before I start the switchover
e.g. the
default_transaction_read_only = on
setting together with being able to control what transactions do if anything

So I reach a point where both systems have postgresql running without any 
replication,
both have identical content in all databases (that I can control),
and I am willing to tolerate short restarts if need be and also to scp/rsync 
the contents of pg_xlog
and other small files but *not* the entire cluster directory or any database 
base directories.

What do I do next?

Here is what I have found seems to work but I am not sure it is robust:

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 Master , tar + scp the entire content of pg_xlog and the 
global/pg_control to intended-to-be-new-Standby
4. create intended-to-be-new-Standby's recovery.conf,
specifying recovery_target_timeline = 'latest'
(but I think it works with this setting omitted)
5. start new Master
6. start new Standby and up it comes
in its postgres log(**) I see
LOG: 0: database system was shut down at 2016-03-14 16:41:24 GMT
LOCATION: StartupXLOG, xlog.c:5936
LOG: 0: entering standby mode
LOCATION: StartupXLOG, xlog.c:6016
LOG: 0: consistent recovery state reached at C/48D0
LOCATION: CheckRecoveryConsistency, xlog.c:7493
LOG: 0: invalid record length at C/48D0
LOCATION: ReadRecord, xlog.c:3960
LOG: 0: started streaming WAL from primary at C/4800 on timeline 3
LOCATION: WalReceiverMain, walreceiver.c:358
LOG: 0: redo starts at C/48D0
LOCATION: StartupXLOG, xlog.c:6700

Is the invalid record length msg anything to worry about?

But this method is purely empirical. Is it robust? Anyone have any better 
recommendations?

Cheers, John Lumby

(**Note this log was from a pre-release 9.5, 9.5alpha2
I don't have 9.5.1 to hand at present

-- 
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] how to switch old replication Master to new Standby after promoting old Standby

2016-03-14 Thread Shulgin, Oleksandr

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 where no failure occurs but I want to
> interchange Master <->Standby,
> *and* want to avoid making another full base backup and rsyncing it across,
> I have found it easy to promote old Standby to new Master with pg_ctl
> promote,
> but very difficult to restart the old Primary as a new Standby *without*
> performing new base backup.
>
> Assume current 9.5.1 and using streaming replication with a named
> replication slot if relevant
>
> Second assumption - I am able to temporarily prevent any relational
> updates to the database before I start the switchover
> e.g. the
> default_transaction_read_only = on
> setting together with being able to control what transactions do if
> anything
>
> So I reach a point where both systems have postgresql running without any
> replication,
> both have identical content in all databases (that I can control),
> and I am willing to tolerate short restarts if need be and also to
> scp/rsync the contents of pg_xlog
> and other small files but *not* the entire cluster directory or any
> database base directories.
>
> What do I do next?
>
> Here is what I have found seems to work but I am not sure it is robust:
>
> 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 Master , tar + scp the entire content of pg_xlog and the
> global/pg_control to intended-to-be-new-Standby
>

That does seem like a very risky strategy to me.  Have you taken a look at
pg_rewind (which is now part of the distribution)?

--
Alex

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

2016-03-14 Thread Frank Millman

> 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 workarounds. I can carry on now :-)

Frank

Re: [GENERAL] enum bug

2016-03-14 Thread Elein



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 values ('green');
>> ERROR:  invalid input value for enum rainbow: "green"
>> LINE 1: insert into badinfo values ('green');
> 
> True, we could improve that, though it could easily get messy with large
> enums.
> 
> 
>>>   * Make enum_range to not be dependent on values in the target table.
>>> Remove/Obsolete enum_range( enum_column ) and replace with enum_range( 
>>> typein regtype )
>>> 
>>>   Workaround: define the enum_range( typein regtyp ) yourself.
> 
> Hmm, this is pretty clunky.  I don't think passing the OID of the enum
> itself is a lot better, but if you use a regtype cast then perhaps it's
> not that bad.  Perhaps we could have both functions.

I was thinking of this function in terms of the catalog functions like 
pg_get_functiondef which takes an oid. Obviosly all of those functions can take 
 reg type with the actual name. 
> 
> -- 
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 


-- 
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] 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-string.html
> format(formatstr text [, formatarg "any" [, ...] ])

You mean to use it to right align the replaced string ( i.e.
format('%12s',replace(...)) ) or is there a code I do not know off
which can be used to achieve the global result ( I use it but it seems
to be like a restricted sprintf which can not do the supress the zero
stuff ) ?

Francisco Olarte.

-- 
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] Suppress decimal point like digits in to_char?

2016-03-14 Thread Ken Tanzer

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:
>> 
>> > 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 amounts I'd right-justify the values and
>> thus
>> > cause the decimals to line up.
>>
>> But a right-aligning string output routine needs to be used.
>>
>>
>> cdrs=> select val, tc, '"'||tc||'"' as quoted,
>> '"'||regexp_replace(tc,'\.$','   ')||'"' as replaced from (select val,
>> to_char(val::decimal(6,2),'FM999,990D99') as tc from (values
>> (1234),(1.05),(0)) as v(val)) as w;
>>  val  |   tc   |  quoted  |  replaced
>> --++--+
>>  1234 | 1,234. | "1,234." | "1,234   "
>>  1.05 | 1.05   | "1.05"   | "1.05"
>> 0 | 0. | "0." | "0   "
>> (3 rows)
>>
>> Summarising, any combination can be easily done with a single round of
>> replace.
>>
>>
> See also:
>
> http://www.postgresql.org/docs/9.5/interactive/functions-string.html
>
> 
> format(formatstr text [, formatarg "any" [, ...] ])
>
> David J.
>
>
>
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.

In particular, one might reasonably choose a format string
like 'FM999,999D99' and not realize it will fail on whole numbers.  Is
there any particular reason the D is not suppressible in this case, either
by default or as an option?  It seems to me if the trailing 0s are
suppressed, the decimal should follow suit for whole numbers.

Cheers,
Ken




-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

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=decimal point, supressed if alone.

> In particular, one might reasonably choose a format string like 
> 'FM999,999D99' and not realize it will fail on whole numbers.  Is there any 
> particular reason the D is not suppressible in this case, either by default 
> or as an option?  It seems to me if the trailing 0s are suppressed, the 
> decimal should follow suit for whole numbers.

It does not fail, it just works in a diffrent way of what you would
like. Regarding supression, IMO it's a bad thing, it can lead to
misleading results. Imagine it is, and you do a right aligned print (
usual for numbers ) of prices 5.45, 1.20, 99.00, 2.40, and you end up
with ( using x for align )
price:
--
xx5.45
xx1.20
99
xx2.40

It would be misleading, I prefer to have xxx99., ugly but clearer IMO
( of course one never supress decimals in prices, so I would use
990D00, but anyway ).

Francisco Olarte.


-- 
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] Suppress decimal point like digits in to_char?

2016-03-14 Thread Ken Tanzer

>
>
> 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 there any
> particular reason the D is not suppressible in this case, either by default
> or as an option?  It seems to me if the trailing 0s are suppressed, the
> decimal should follow suit for whole numbers.
>
> It does not fail, it just works in a diffrent way of what you would
> like. Regarding supression, IMO it's a bad thing, it can lead to
> misleading results. Imagine it is, and you do a right aligned print (
> usual for numbers ) of prices 5.45, 1.20, 99.00, 2.40, and you end up
> with ( using x for align )
> price:
> --
> xx5.45
> xx1.20
> 99
> xx2.40
>
> It would be misleading, I prefer to have xxx99., ugly but clearer IMO
> ( of course one never supress decimals in prices, so I would use
> 990D00, but anyway ).
>
> Francisco Olarte.
>

I appreciate the comment and explanation.  But your example shows numbers
where the trailing 0s are not suppressed.   It seems to me that if you're
requesting suppression of trailing 0s, then you're accepting that your
numbers aren't going to align in the first place. And so it's hard for me
to see how, for example "99." is ever going to be desirable output if
suppression is what you're after.

And just as context on my end, the times I use to_char are generally to
merge numbers into a document or some fragment of English text.  For
anything going into a table, I'd usually just leave it as a number and case
it to the desired (fixed) number of decimals.  And as we've touched on, it
may just be different use cases colliding! :)

Cheers,
Ken

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

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 a that kind of
alignment which stronly suggest its .99. Of course it's not printed as
such, but visually it can trick you, that's why decimal points are
never supressed anc softwar has options to align coluns to the decimal
point.

> It seems to me that if you're requesting suppression of trailing 0s, then 
> you're accepting that your numbers aren't going to align in the first place. 
> And so it's hard for me to see how, for example "99." is ever going to be 
> desirable output if suppression is what you're after.

Never desirable for me, but I never use d9, I always do d0, but you are right.

> And just as context on my end, the times I use to_char are generally to merge 
> numbers into a document or some fragment of English text.

For the grouping ',' I see your point, I normally just use defaut
conversion for these as I dislike the grouping.

As I said, I could see a legitimitate case for Dd similar to the 09
stuff, but having so many replace options ( I think you can even do
, which is
easier on the eye but fails on locales, as the regexp does ( as a
note, in Spain they are inverted, dot for grouping comma for decimals
) ) I do not think it's a big deal, uglier things are coded by me
continuously nearly via muscle memory.

Francisco Olarte.

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

[GENERAL] ON CONFLICT DO NOTHING RETURNING

2016-03-14 Thread Peter Devoy

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 UPDATE SET
name='dog' RETURNING animal_id;

Is there a reason DO NOTHING was not developed for use with RETURNING?

Either way, upsert is great, I am glad we have it now.

Kind regards


Peter Devoy

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

2016-03-14 Thread Merlin Moncure

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 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 peculiarities of floating point math.
>>
>> merlin
>>
>>
> I think also that it is a float precision issue but the weird thing is that
> both calls without floor return 273. Maybe the display method make a
> rounding ?

Yeah.  One of the trickiest bits about floating point numbers with
postgres (as well as many other implementations) is that the textual
representation does not necessarily equate to the internal one.  This
can lead to some pretty weird situations.  For example, data that was
valid when dumped could fail to restore on duplicate key.  Anyone
doing work with floating point should be aware of this: it's compact
and fast but poor at representing precise numbers.

merlin

-- 
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] ON CONFLICT DO NOTHING RETURNING

2016-03-14 Thread Peter Geoghegan

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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2016-03-14 Thread Chris Mair


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_series(1,100);
   sum
--
 472.5001
(1 row)

vs.

chris=# select sum(4.725::numeric) from generate_series(1,100);
   sum
-
 472.500
(1 row)

Bye,
Chris.




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

[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 I need to create a Unique value.

*Example:*

Column Code|   Column Info   |
code_67493675936info_2016

*Question:*

How can I do that using PostgreSQL 9.2?

[GENERAL] BDR

2016-03-14 Thread Dustin Kempter

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 for other nodes to communicate with (for the first 
node, we will use port 5598) from the same SQL session as above on port 
5598:


SELECT bdr.bdr_group_create(
  local_node_name := 'node1',
  node_external_dsn := 'port=5598 dbname=bdrdemo'
);"

However my instances are not on the same server and I attempted to 
simply add a host=(the ip) but that failed. Please help


Thanks in advance!



--
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] 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 there is a
> Constraint that does not allow me to have duplicated items.
>
> So I need to create a Unique value.
>
> *Example:*
>
> Column Code|   Column Info   |
> code_67493675936info_2016
>
> *Question:*
>
> How can I do that using PostgreSQL 9.2?
>

You might want to try to restate the problem and question.  I'm having a
hard time trying to figure out what you want.

Reading your subject line I'll point you to:

http://www.postgresql.org/docs/9.2/interactive/datatype-uuid.html

specifically the extension that is mentioned.

Usually people figure out ways to accomplish their goals without using
UUID though.

David J.

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:
>>
>> 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 I need to create a Unique value.
>>
>> *Example:*
>>
>> Column Code|   Column Info   |
>> code_67493675936info_2016
>>
>> *Question:*
>>
>> How can I do that using PostgreSQL 9.2?
>>
>
> You might want to try to restate the problem and question.  I'm having a
> hard time trying to figure out what you want.
>
> Reading your subject line I'll point you to:
>
> http://www.postgresql.org/docs/9.2/interactive/datatype-uuid.html
>
> specifically the extension that is mentioned.
>
> Usually people figure out ways to accomplish their goals without using
> UUID though.
>
> David J.
> 
>
>
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');

but I'm doing:
INSERT INTO tableb (SELECT * FROM TABLEA)

So, how to use UUID using the SELECT above?

Re: [GENERAL] BDR

2016-03-14 Thread John R Pierce


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-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] BDR

2016-03-14 Thread Roland van Laar


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 group that delineates a 
connection string for other nodes to communicate with (for the first 
node, we will use port 5598) from the same SQL session as above on 
port 5598:


SELECT bdr.bdr_group_create(
  local_node_name := 'node1',
  node_external_dsn := 'port=5598 dbname=bdrdemo'
);"

However my instances are not on the same server and I attempted to 
simply add a host=(the ip) but that failed.

There are a couple of other factors:
- is postgres running on an external available ip?
- is there a replication user with a password?

Roland


Please help

Thanks in advance!







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

2016-03-14 Thread James Keener

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 get an error?   if so what error, exactly?
>
>
>
>-- 
>john r pierce, recycling bits in santa cruz
>
>
>
>-- 
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

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');
>
> but I'm doing:
> INSERT INTO tableb (SELECT * FROM TABLEA)
>
> So, how to use UUID using the SELECT above?
>
>
>
By explicitly listing column names instead of using "*" and then instead
of copying a column from A to B you omit the column from A and replace it
with a function call.

INSERT INTO tableb ("Id", "Title")
SELECT uuid_generate_v4(), "Title"
FROM tablea;

David J.

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

2016-03-14 Thread James Keener

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

On March 14, 2016 5:37:00 PM EDT, "drum.lu...@gmail.com"  
wrote:
>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:
>>>
>>> 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 I need to create a Unique value.
>>>
>>> *Example:*
>>>
>>> Column Code|   Column Info   |
>>> code_67493675936info_2016
>>>
>>> *Question:*
>>>
>>> How can I do that using PostgreSQL 9.2?
>>>
>>
>> You might want to try to restate the problem and question.  I'm
>having a
>> hard time trying to figure out what you want.
>>
>> Reading your subject line I'll point you to:
>>
>> http://www.postgresql.org/docs/9.2/interactive/datatype-uuid.html
>>
>> specifically the extension that is mentioned.
>>
>> Usually people figure out ways to accomplish their goals without
>using
>> UUID though.
>>
>> David J.
>> 
>>
>>
>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');
>
>but I'm doing:
>INSERT INTO tableb (SELECT * FROM TABLEA)
>
>So, how to use UUID using the SELECT above?

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

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 discounted the options you've not even told us
> you've considered.
>
>
>>>
>> 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');
>>
>> but I'm doing:
>> INSERT INTO tableb (SELECT * FROM TABLEA)
>>
>> So, how to use UUID using the SELECT above?
>>
>>
>>

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'
> 'Interpreting Normal/AH'
> 'Interpreting Normal/AH'
> 'Interpreting Normal/AH'
> 'Interpreting Normal/AH4'
> 'Interpreting Normal/AH'
> 'Interpreting Normal/AH6'
> 'Interpreting Normal/AH'


So when importing it to the target table I got the error:

> ERROR:  duplicate key value violates unique constraint
> "uc_billable_code_unique_per_account"
> DETAIL:  Key ("account_id", "code")=(32152, 'Interpreting Normal/AH')
> already exists.

Command used to import the values:

> INSERT INTO dm.billables (SELECT billable_id, code, info FROM temptable)

OR directly through the CSV file:

COPY dm.billables (code, info, unit_cost, unit_price, account_id) FROM
> '/var/lib/pgsql/sql/lucas/charge_test.csv' WITH DELIMITER ',' QUOTE '"' CSV
> HEADER;


So. I determined that to do that without dropping the CONSTRAINT, I'll have
to generate a unique but random value to the "code" column.

*NOW:*
*COLUMN CODE|   COLUMN INFO*
'Interpreting Normal/AH'Travel1
'Interpreting Normal/AH1'trip2
'Interpreting Normal/AH2'test897
'Interpreting Normal/AH3'trip11
'Interpreting Normal/AH4'trave1

*NEW:*
*COLUMN CODE|   COLUMN INFO*
code_32152563bdc6453645Travel1
code_32152563bdc4566hhhtrip2
code_32152563b654645uuu   test897
code_32152563bdc4546uuitrip11
code_32152563bdc4db11aatrave1

How can I do that?

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

2016-03-14 Thread John Lumby

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/bluebild/pgrepl95 --source-server=host=10.19.0.21 port=5432 
user=postgres password=postgres -P --debug

after which my postgresql server would not start and I discovered that the 
rewind had actually copied the source's postgresql.conf,
replacing the one on the target (i.e. the old primary).   

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 to do this.
But why would it do that?


Maybe a note about it should be added to the wiki
https://wiki.postgresql.org/wiki/Streaming_Replication
    (not sure if I can)

Cheers,  and thanks,    John

> From: oleksandr.shul...@zalando.de 
> Date: Mon, 14 Mar 2016 18:54:11 +0100 
> Subject: Re: [GENERAL] how to switch old replication Master to new  
> Standby after promoting old Standby 
> To: johnlu...@hotmail.com 
> CC: pgsql-general@postgresql.org 
>  
> On Mon, Mar 14, 2016 at 6:28 PM, John Lumby  
> mailto:johnlu...@hotmail.com>> 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 where no failure occurs but I want  
> to interchange Master <->Standby, 
> *and* want to avoid making another full base backup and rsyncing it across, 
> I have found it easy to promote old Standby to new Master with pg_ctl  
> promote, 
> but very difficult to restart the old Primary as a new Standby  
> *without* performing new base backup. 
>  
> Assume current 9.5.1 and using streaming replication with a named  
> replication slot if relevant 
>  
> Second assumption - I am able to temporarily prevent any relational  
> updates to the database before I start the switchover 
> e.g. the 
> default_transaction_read_only = on 
> setting together with being able to control what transactions do if anything 
>  
> So I reach a point where both systems have postgresql running without  
> any replication, 
> both have identical content in all databases (that I can control), 
> and I am willing to tolerate short restarts if need be and also to  
> scp/rsync the contents of pg_xlog 
> and other small files but *not* the entire cluster directory or any  
> database base directories. 
>  
> What do I do next? 
>  
> Here is what I have found seems to work but I am not sure it is robust: 
>  
> 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 Master , tar + scp the entire content of pg_xlog and the  
> global/pg_control to intended-to-be-new-Standby 
>  
> That does seem like a very risky strategy to me.  Have you taken a look  
> at pg_rewind (which is now part of the distribution)? 
>  
> -- 
> Alex 
>  
  

-- 
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] how to switch old replication Master to new Standby after promoting old Standby

2016-03-14 Thread Michael Paquier

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 Master , tar + scp the entire content of pg_xlog and the
>> global/pg_control to intended-to-be-new-Standby

This is not robust and will corrupt your standby. Just take the case
of a relation data block modified on the to-be-new standby, and not
replayed since the last checkpoint before WAL forked: data will be
corrupted. Inconsistent pg_clog will likely break things.

> That does seem like a very risky strategy to me.  Have you taken a look at
> pg_rewind (which is now part of the distribution)?

pg_rewind has been designed for that, and ensures that the
soon-to-be-standby has a minimum recovery target sufficient. You had
better use it.
-- 
Michael


-- 
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] 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'
>> 'Interpreting Normal/AH'
>> 'Interpreting Normal/AH'
>> 'Interpreting Normal/AH'
>> 'Interpreting Normal/AH4'
>> 'Interpreting Normal/AH'
>> 'Interpreting Normal/AH6'
>> 'Interpreting Normal/AH'
>
>
> So when importing it to the target table I got the error:
>
>> ERROR:  duplicate key value violates unique constraint
>> "uc_billable_code_unique_per_account"
>> DETAIL:  Key ("account_id", "code")=(32152, 'Interpreting Normal/AH')
>> already exists.
>
> Command used to import the values:
>
>> INSERT INTO dm.billables (SELECT billable_id, code, info FROM temptable)
>
> OR directly through the CSV file:
>
> COPY dm.billables (code, info, unit_cost, unit_price, account_id) FROM
>> '/var/lib/pgsql/sql/lucas/charge_test.csv' WITH DELIMITER ',' QUOTE '"' CSV
>> HEADER;
>
>
> So. I determined that to do that without dropping the CONSTRAINT, I'll
> have to generate a unique but random value to the "code" column.
>
> *NOW:*
> *COLUMN CODE|   COLUMN INFO*
> 'Interpreting Normal/AH'Travel1
> 'Interpreting Normal/AH1'trip2
> 'Interpreting Normal/AH2'test897
> 'Interpreting Normal/AH3'trip11
> 'Interpreting Normal/AH4'trave1
>
> *NEW:*
> *COLUMN CODE|   COLUMN INFO*
> code_32152563bdc6453645Travel1
> code_32152563bdc4566hhhtrip2
> code_32152563b654645uuu   test897
> code_32152563bdc4546uuitrip11
> code_32152563bdc4db11aatrave1
>
> How can I do that?
>


I could also drop the COSNSTRAINT, import all the data and then change the
"code" column to use UUID - *But how?*

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

2016-03-14 Thread Michael Paquier

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 to do this.
> But why would it do that?

To make its code more simple. This way there is no need to apply any
kind of file-based filters to decide if some files should be copied or
not, and it is not that much a big deal to copy the configuration
files of  the target node before performing the rewind.

> Maybe a note about it should be added to the wiki
> https://wiki.postgresql.org/wiki/Streaming_Replication
> (not sure if I can)

With a community account you could edit this page.
-- 
Michael


-- 
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] 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 incremental (unique) value automatically on
> insert.
>
>
> But I think your problem is more fundamental - if you genuinely have
> duplicate values in a column - there should not be a unique constraint on
> it. If it should be unique, then you should modify your insert data.
>
>
>
I Can't modify my insert data, because there's a PHP RANDOM CODE that does
exactly what I wanna do with the SQL - It generates a random but unique
value for the column "code" - So the customer will be able to have
duplicates values on that column

Today the PHP is already generating for example:

code_321525694417ad6b5f

So that is linked to another table (I can do that manually no problem)

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 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, listing
every single column, and replacing those columns you want to change with
some kind of expression.

Basically:

INSERT INTO targettable (col1, col2, col3)
SELECT col1, col2 || '_' || nextval('sequence_name')::text, col3
FROM stagingtable;

David J.

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 directly into the target table.  You must copy to a
> staging table.  You then insert from the staging table to the target table,
> listing every single column, and replacing those columns you want to change
> with some kind of expression.
>
> Basically:
>
> INSERT INTO targettable (col1, col2, col3)
> SELECT col1, col2 || '_' || nextval('sequence_name')::text, col3
> FROM stagingtable;
>
>
In theory an INSERT trigger might work too - but this is likely to be
simpler and faster.

David J.

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
>>>
>>
>> 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,
>> listing every single column, and replacing those columns you want to change
>> with some kind of expression.
>>
>> Basically:
>>
>> INSERT INTO targettable (col1, col2, col3)
>> SELECT col1, col2 || '_' || nextval('sequence_name')::text, col3
>> FROM stagingtable;
>>
>>
> In theory an INSERT trigger might work too - but this is likely to be
> simpler and faster.
>
> David J.
> 
>
>

Hi David... Thanks for you reply. I haven't seen it before.

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:

ERROR:  relation "uuid_generate_v4()" does not exist


But the extension is working:

> select uuid_generate_v4() as one;
>  one
> --
>  59ad418e-53fa-4725-aadb-8f779c1a12b2
> (1 row)


select * from pg_available_extensions;
> uuid-ossp  | 1.0 | 1.0   | generate
> universally unique identifiers (UUIDs)


Do you know what might I being doing wrong?

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2

2016-03-14 Thread John R Pierce


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:

ERROR:  relation "uuid_generate_v4()" does not exist


But the extension is working:

select uuid_generate_v4() as one;
 one
--
 59ad418e-53fa-4725-aadb-8f779c1a12b2
(1 row)


select * from pg_available_extensions;
uuid-ossp  | 1.0 | 1.0 | generate
universally unique identifiers (UUIDs) 



Do you know what might I being doing wrong?


nextval() takes a sequence name.   not a function like uuid_generate_v4().

if you insist on using UUID (very slow to generate, very bulky), then try...

INSERT INTO junk.wm_260_billables2 (account_id, code, info) SELECT 
account_id, code || '_' || uuid_generate_v4(), info FROM 
junk.wm_260_billables1;



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Unique UUID value - PostgreSQL 9.2