Re: [GENERAL] PSQLRestore
On 09/09/2011 01:25, Bob Pawley wrote: > > > -Original Message- >> From: Raymond O'Donnell > Sent: Thursday, September 08, 2011 3:23 PM To: Bob Pawley Cc: Adrian > Klaver ; Postgresql Subject: Re: [GENERAL] PSQLRestore > On 08/09/2011 23:02, Bob Pawley wrote: >> The problem seems to be in this code which I am using to transfer from >> the opendialogue to PSQLRestore. >> >> FileRestore := OpenDialog1.FileName; >> PSQLRestore1.RestoreFromFile(FileRestore, ' '); >> >> I'm not sure what string is expected. > > Hello Bob, > > According to the docs, the second argument should either be a TStrings > object, into which log messages are placed, or else the name of a file > where the log output can be written. > > Ray. > > Hi Ray > > Is there any chance you could send me a short example of what that means. Hello Bob, This is off the top of my head - I haven't used that component (yet) myself, and haven't tested the following, but at a guess it would be something like this: EITHER: var LogMessages: TStringList; begin LogMessages := TStringList.Create; PSQLRestore1.RestoreFromFile(FileRestore, LogMessages); ... and then later you could display the contents of LogMessages, if you need to. OR: PSQLRestore1.RestoreFromFile(FileRestore, 'c:\logfile.txt'); I hope this helps. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] integer instead of 'double precision'?
take any table and run Query - select 1/3 from storage limit 1 Result - ?column? integer 0 Expected Result - ?column? double precision 0.3... Question - Since there is no column type to begin with as this is a made-up column, shouldn't postgres know it is double precision due to the remainder? I thought perhaps I could cast it as double precision as noted on http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html though doing the following: select float8(1/3) from storage limit 1 results in: float8 double precision 0 any ideas on how to get this type of a manufactured column (not sure the right term for it) to show the double precision result?
Re: [GENERAL] integer instead of 'double precision'?
On Fri, 2011-09-09 at 10:42 -0400, Henry Drexler wrote: > take any table and run > > Query > - > select > 1/3 > from > storage > limit 1 > > > Result > - > ?column? > integer > 0 > > > Expected Result > - > ?column? > double precision > 0.3... > > > > Question > - > Since there is no column type to begin with as this is a made-up column, > shouldn't postgres know it is double precision due to the remainder? > You divide an integer with an integer, that should give you an integer. And that's exactly what it does. > I thought perhaps I could cast it as double precision as noted on > http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html > > though doing the following: > > select > float8(1/3) > from > storage > limit 1 > > results in: > > float8 > double precision > 0 > You still divide an integer with an integer. 1/3 as integers has a result of 0. You then cast it to float which gives you the value 0 in double precision. > any ideas on how to get this type of a manufactured column (not sure the > right term for it) to show the double precision result? Sure, do select 1./3 from... or select float8(1)/3... -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] integer instead of 'double precision'?
Henry Drexler writes: > [ "1/3" yields zero ] Yeah, it's an integer division. > I thought perhaps I could cast it as double precision as noted on > http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html > though doing the following: > float8(1/3) That's casting the result of the division to float, which is way too late. You need to cast one or both inputs to non-integer, for instance 1.0/3 1/(3::float8) etc etc. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL Upgrade Procedure
I've had PG 9.0 installed and working fine however it's Friday and I'm running updates on the server & see that 9.1 is available. I know when I upgrade, I will now have two instances of PostgreSQL installed under /etc/postgresql: Code: slave:~# cd /etc/postgresql slave:/etc/postgresql# ls -l total 8 drwxr-xr-x 3 postgres postgres 4096 Aug 31 13:02 9.0 drwxr-xr-x 3 postgres postgres 4096 Sep 9 10:08 9.1 EndCode: My question is what is the official procedure for removing the old version and then running 9.1 only on my system. I don't want to leave 9.0 config files or directories so can someone please point me into the clean and correct way of properly upgrading from 9.0 to 9.1? I did do a pg_dump on my two databases & the default 'postgres' database and backed them up so I can import them into the new 9.1 instance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hiring PostgresSQL admin
Merchantcircle.com (acquired by Reply!) is hiring. Please send your resume to me. Thanks, Claire
Re: [GENERAL] hiring PostgresSQL admin
On Fri, Sep 9, 2011 at 10:47 AM, Claire Chang wrote: > Merchantcircle.com (acquired by Reply!) is hiring. Please send your resume > to me. > Thanks, > Claire Make sure to post your request to the postgresql-jobs list if you haven't already -- that's the preferred place for postgresql job postings. Also a few more details about the position would be helpful -- expected duties, physical location, etc. 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] PostgreSQL Upgrade Procedure
On 09/09/2011 16:35, Carlos Mennens wrote: > I've had PG 9.0 installed and working fine however it's Friday and I'm > running updates on the server & see that 9.1 is available. I know when > I upgrade, I will now have two instances of PostgreSQL installed under > /etc/postgresql: > > Code: > > slave:~# cd /etc/postgresql > slave:/etc/postgresql# ls -l > total 8 > drwxr-xr-x 3 postgres postgres 4096 Aug 31 13:02 9.0 > drwxr-xr-x 3 postgres postgres 4096 Sep 9 10:08 9.1 > > EndCode: > > My question is what is the official procedure for removing the old > version and then running 9.1 only on my system. I don't want to leave > 9.0 config files or directories so can someone please point me into > the clean and correct way of properly upgrading from 9.0 to 9.1? I did > do a pg_dump on my two databases & the default 'postgres' database and > backed them up so I can import them into the new 9.1 instance. Removing the older installation depends on whatever package manager your system uses, if that's how you installed it. e.g. Debian: apt-get remove postgresql-9.0 Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] integer instead of 'double precision'?
thanks Tom and Guillaume, *That sequencing of casting makes sense - I appreciate the clear explanation. * * * On Fri, Sep 9, 2011 at 11:12 AM, Tom Lane wrote: > Henry Drexler writes: > > [ "1/3" yields zero ] > > Yeah, it's an integer division. > > > I thought perhaps I could cast it as double precision as noted on > > http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html > > though doing the following: > > float8(1/3) > > That's casting the result of the division to float, which is way too > late. You need to cast one or both inputs to non-integer, for instance > >1.0/3 >1/(3::float8) > > etc etc. > >regards, tom lane >
Re: [GENERAL] integer instead of 'double precision'?
Perfect, thank you. I will try to find that in the documentation as I was obviously not looking at the correct page I had linked to earlier. On Fri, Sep 9, 2011 at 11:05 AM, Day, David wrote: > Henry, > > ** ** > > Does this suit your need? > > ** ** > > select 1/3::float as answer; > > answer > > --- > > 0.333 > > (1 row) > > ** ** > > *From:* pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] *On Behalf Of *Henry Drexler > *Sent:* Friday, September 09, 2011 10:42 AM > *To:* pgsql-general@postgresql.org > *Subject:* [GENERAL] integer instead of 'double precision'? > > ** ** > > take any table and run > > ** ** > > Query > > - > > select > > 1/3 > > from > > storage > > limit 1 > > ** ** > > ** ** > > Result > > - > > ?column? > > integer > > 0 > > ** ** > > ** ** > > Expected Result > > - > > ?column? > > double precision > > 0.3... > > ** ** > > ** ** > > ** ** > > Question > > - > > Since there is no column type to begin with as this is a made-up column, > shouldn't postgres know it is double precision due to the remainder? > > ** ** > > I thought perhaps I could cast it as double precision as noted on > http://www.postgresql.org/docs/8.3/interactive/sql-expressions.html > > ** ** > > though doing the following: > > ** ** > > select > > float8(1/3) > > from > > storage > > limit 1 > > ** ** > > results in: > > ** ** > > float8 > > double precision > > 0 > > ** ** > > ** ** > > any ideas on how to get this type of a manufactured column (not sure the > right term for it) to show the double precision result? > > ** ** >
Re: [GENERAL] integer instead of 'double precision'?
On Sep 9, 2011, at 8:42 AM, Henry Drexler wrote: > any ideas on how to get this type of a manufactured column (not sure the > right term for it) to show the double precision result? Use floating point types in the calculation to begin with. 1.0/3.0 1::float8 / 3::float8 float8(1) / float8(3) 1.0/3 1/3.0 1::float8 / 3 ... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] PostgreSQL Upgrade Procedure
On 09/09/2011 08:35 AM, Carlos Mennens wrote: I've had PG 9.0 installed and working fine however it's Friday and I'm running updates on the server& see that 9.1 is available First!!! Although certain packages like Martin Pitt's PPA for Ubuntu will show PostgreSQL 9.1 as available this is the current release-candidate of PostgreSQL 9.1. PostgreSQL 9.1 final has not been released. Do not install it on a critical production system. What OS? The different package managers vary in how they deal with this. I just went through some frustration on CentOS as the directions I was reading actually applied to how the packages install on CentOS version 6, which can do parallel installs and not to CentOS 5x, which can not. On my Ubuntu systems which have the PPA installed (and granted, I've only spent a few minutes so far), it is showing 9.1 as an upgrade and wants to automatically remove 9.0. So to (sort of) answer your question. There is no single official method of upgrading - it depends on how you want to upgrade (dump/restore, in-place with pg_upgrade, etc.) and what OS and package you are using. Cheers, Steve -- 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] Problem with 8.3.14 Windows binaries
As an update, we found a 2008 box that had the same problem as below, so it doesn't seem to be a problem with Windows 2003. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Pete Wall Sent: Wednesday, August 24, 2011 2:54 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Problem with 8.3.14 Windows binaries Any ideas? Has anybody else had luck or problems with the new versions on Windows Server 2003? Thanks, -Pete From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Pete Wall Sent: Thursday, August 18, 2011 4:58 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Problem with 8.3.14 Windows binaries Hello, I am upgrading the version of PostgreSQL used in our product from 8.3.9 to 8.3.15 to close some of the vulnerabilities. After upgrading, I found that it wouldn't work on our Windows 2003 boxes, but it was fine on the 2008 ones. I downloaded every binary release from ..9 to ..15 (from here: http://www.postgresql.org/ftp/binary/) and found that the problem started with 8.3.14. What happens is when I try to launch any of the binaries, I get this message on the CLI: C:\tmp\postgresql-8.3.14-1-binaries-no-installer\pgsql\bin>pg_ctl.exe The system cannot execute the specified program. When I double-click it in Explorer, I get a dialog box with this message: "This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem." I then opened it up in Dependency Walker and got this message: Error: The Side-by-Side configuration information for "c:\tmp\postgresql-8.3.14-1-binaries-no-installer\pgsql\bin\PG_CTL.EXE" contains errors. This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem (14001). Error: The Side-by-Side configuration information for "c:\tmp\postgresql-8.3.14-1-binaries-no-installer\pgsql\bin\LIBPQ.DLL" contains errors. This application has failed to start because the application configuration is incorrect. Reinstalling the application may fix this problem (14001). It was also not detecting the MSVCR80.DLL, while 8.3.13 found it automatically. Here's a screenshot of Dependency Walker comparing the new with the old: http://i.imgur.com/FxNkG.jpg Can someone help me figure out what's missing? I found "Improve build support for Windows version" here http://archives.postgresql.org/pgsql-announce/2011-02/msg0.php. Maybe that had something to do with it. Thanks, -Pete
Re: [GENERAL] hiring PostgresSQL admin
On Fri, Sep 9, 2011 at 9:47 AM, Claire Chang wrote: > Merchantcircle.com (acquired by Reply!) is hiring. Please send your resume > to me. I'm interested, but there's just not enough info here to know what to expect. I'm currently a full time linux sysadmin / pg dba making $90k living in Denver. Been using pg since 6.5.2 roamed the land, and am quite good at it. But it's likely either the location is wrong or the pay is too low etc. Let me know more, I'll shoot you a resume. -- 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] hiring PostgresSQL admin
Sorry that was just supposed to go to Claire. apologies -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG producing odd results on epoch timestamp to string conversion
Hi folks,
I've got a web app which runs on three databases: PostgreSQL, Oracle
and Microsoft SQL Server.
This app has columns which contain Unix epoch timestamps generated
from Python's time.time() function. Now I need to write DB functions
or expressions which convert these timestamps into human-readable
format, ideally in the local timezone. These are the expressions I've
come up with so far, using 1315503340 as a test value:
PostgreSQL (testing with 8.4.3 on OS X):
SELECT TO_CHAR( TIMESTAMP WITH TIME ZONE 'epoch' + 1315503340 *
INTERVAL '1 second', 'MM-DD- HH:MM:SS TZ');
09-08-2011 12:09:40 CDT
SQL Server 2008:
select cast(DATEADD(second, 1315503340, '1970-01-01 00:00:00') as
datetimeoffset)
2011-09-08 17:35:40.000 +00:00
Oracle 11:
select to_char( cast( to_timestamp_tz('01-jan-1970 00:00:00 -00:00',
'dd-mon- HH24:MI:SS TZH:TZM') as timestamp with local time zone) +
numtodsinterval ( TIMESTAMP , 'second'), 'mm-dd- HH24:MI:SS TZR')
from dual
09-08-2011 12:35:40 -05:00
For comparison, here's Python (2.6.5 on OS X):
>>> print time.asctime(time.localtime(1315503340))
Thu Sep 8 12:35:40 2011
As you can see, Python, SQL Server, and Oracle all agree that the
timestamp 1315503340 means 12:35:40 CDT on that date. Yet PostgreSQL
shows a value that is exactly 26 minutes behind the others (12:09:40).
Can anyone help me understand why this discrepancy, and/or how to deal
with it? Is it something like PG actually accounting for civil time
oddities, slightly variations in the earth's orbit, or something like
that? I know a lot of algorithms assume exactly 86,400 seconds in a
day, which isn't always true. You'd think if it was a simple timezone
issue it would be off by increments of 1 hour. For the purposes of my
application at least, PostgreSQL is producing a "wrong" value here (it
needs to match up with Python) and I'd like to figure out how to get
the "right" value. I guess just adding 26 minutes to the PostgreSQL
value is one way but that seems kind of dangerous without
understanding what's really going on here.
If any light is shed on this I would be greatly appreciative.
Regards,
Preston Landers
--
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] PG producing odd results on epoch timestamp to string conversion
Preston Landers writes: > SELECT TO_CHAR( TIMESTAMP WITH TIME ZONE 'epoch' + 1315503340 * > INTERVAL '1 second', 'MM-DD- HH:MM:SS TZ'); > 09-08-2011 12:09:40 CDT > As you can see, Python, SQL Server, and Oracle all agree that the > timestamp 1315503340 means 12:35:40 CDT on that date. So does Postgres. regression=# set timezone = 'CST6CDT'; SET regression=# select TIMESTAMP WITH TIME ZONE 'epoch' + 1315503340 * INTERVAL '1 second'; ?column? 2011-09-08 12:35:40-05 (1 row) > Yet PostgreSQL > shows a value that is exactly 26 minutes behind the others (12:09:40). You've fat-fingered the to_char usage --- MM is month, not minutes (I think you want MI for that). regards, tom lane -- 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] PG producing odd results on epoch timestamp to string conversion
On Fri, Sep 9, 2011 at 5:13 PM, Tom Lane wrote: >> Yet PostgreSQL >> shows a value that is exactly 26 minutes behind the others (12:09:40). > > You've fat-fingered the to_char usage --- MM is month, not minutes > (I think you want MI for that). > Yep, that's exactly it. Thanks so much for your help, and everything else you've done for this project over the years! best regards, Preston -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
