Re: [GENERAL] pgrestore command

[Michael Paquier]

On Fri, Feb 28, 2014 at 4:41 PM, Arun P.L aru...@hotmail.com wrote:
 Hi

 Is there any chances for any performance issues with pg_restore when
 changing the servers, like openvz, xen, etc.?
You could expect a loss of 20% at I/O level by using a virtualized
environment compared to a physical server with similar hardware (rule
of thumb, number more or less general by experience). Then for
different hardwares, yes you will get performance differences...

 Will the server difference make the time of restore longer in this case?
Depends on the hardware, as well as on the environment used... It is
hard to tell without any details...
Regards,
-- 
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] Josh's Comments on Hstore / Jsonb

[Merlin Moncure]

On Thu, Feb 27, 2014 at 6:16 PM, Brett Mc Bride
brett.mcbr...@deakin.edu.au wrote:
 I'm in the process of prototyping a DB-backed REST web service as a
 side-project. It's still early
 days, but I've been handed a PHP/mysql setup.
 After a bit of searching around, I thought that the exact combination above
 (node.json/express +
 node-postgres), possibly coupled with JSON datatypes, could be a really
 effective solution. ()

 Now for the advocacy part - before I saw this thread I wasn't sure whether
 there were any real-world
 implementations of this combination. Are there any good skeletons or
 prototypes out there to get a
 RESTful web service up and running with express and postgres?

Express *is* the skeleton.  If you are handing auth and session mgmt
in node that's were the bulk of your code will be at the service
level.  If not, things are even easier:  my web service calls tend to
boil down to almost one liners: postgres returns pre-packaged json and
node just flips it to the client.   I've gotten pretty adept at json
serialization: when the application page refreshes I'll stuff as many
things into one json object as possible.

Posting data back to the database is more complex so you have to
decide if you want to let the database handle the json decomposition
(this requires 9.3) or reduce it to SQL in node.  I prefer the
database because things are simplified when you don't have to use
connection pooling.

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] pgrestore command

[John R Pierce]

On 2/28/2014 5:06 AM, Michael Paquier wrote:

You could expect a loss of 20% at I/O level by using a virtualized
environment compared to a physical server with similar hardware (rule
of thumb, number more or less general by experience).


and that could be 200% or more if there is IO and/or CPU contention from 
other virtual machines on the same physical host.




--
john r pierce  37N 122W
somewhere on the middle of the left coast



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

[GENERAL] high throughput 9.3, master/slave question

[Matthew Chambers]

Hi,

I have a postgres server that is fairly high traffic. (about 5MB/second 
in writes to SSD backed Netapp NFS storage).  The outbound network 
traffic for this machine averages 61MB/second, but gets over 
250MB/second during peal times.  (includes data to slave + user requests)


Initially, I had my application servers using the slave for short, read 
only queries, but this turned out to be highly unstable. The slave would 
start refusing connections, and the logs would fill with:


ERROR:  canceling statement due to conflict with recovery

I've tried these 2 settings:

max_standby_archive_delay = -1
max_standby_streaming_delay = -1

But then I starting getting these:
DETAIL:  User transaction caused buffer deadlock with recovery.

Read requests come in at anywhere between 200 and 1000/second.

I was wondering if there is some combination of configuration settings 
that would safely let me use the slave for read only queries?


-Matt




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

[GENERAL] Replacing Ordinal Suffixes

[George Weaver]

Hi list,

I'm stumped.

I am trying to use Regexp_Replace to replace ordinal suffixes in addresses (eg 
have '126th' want '126') for comparison purposes.  So far no luck.

I have found that 

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), '(?!/D)(st|nd|rd|th)', 
'', 'g');
  regexp_replace
--
 300 nor 126 reet

but 

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), '(?=/D)(st|nd|rd|th)', 
'', 'g');
 regexp_replace

 300 north 126th street

I'm a novice with regular expressions and google hasn't helped much.

Any suggestions?

Thanks,
George

Re: [GENERAL] Replacing Ordinal Suffixes

[Paul Jungwirth]

Try this:

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(\d)(st|nd|rd|th)', '\1', 'g');

Note that matching a number is \d not /D: backslash, not forward
slash, and lowercase d not uppercase. \d means a digit, \D means
anything except a digit.

Also, I don't think Postgres supports positive lookbehind expressions
(which are actually (?=foo), not (?!foo)), but you can get the same
effect by capturing the number with (\d) and then outputting it again
with the \1.

Paul



On Fri, Feb 28, 2014 at 2:04 PM, George Weaver gwea...@shaw.ca wrote:
 Hi list,

 I'm stumped.

 I am trying to use Regexp_Replace to replace ordinal suffixes in addresses
 (eg have '126th' want '126') for comparison purposes.  So far no luck.

 I have found that

 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
 '(?!/D)(st|nd|rd|th)', '', 'g');
   regexp_replace
 --
  300 nor 126 reet

 but

 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
 '(?=/D)(st|nd|rd|th)', '', 'g');
  regexp_replace
 
  300 north 126th street

 I'm a novice with regular expressions and google hasn't helped much.

 Any suggestions?

 Thanks,
 George



-- 
_
Pulchritudo splendor veritatis.


-- 
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] Replacing Ordinal Suffixes

[Steve Atkins]

On Feb 28, 2014, at 2:04 PM, George Weaver gwea...@shaw.ca wrote:

 Hi list,
  
 I'm stumped.
  
 I am trying to use Regexp_Replace to replace ordinal suffixes in addresses 
 (eg have '126th' want '126') for comparison purposes.  So far no luck.
  
 I have found that
  
 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), '(?!/D)(st|nd|rd|th)', 
 '', 'g');
   regexp_replace
 --
  300 nor 126 reet
  
 but
  
 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), '(?=/D)(st|nd|rd|th)', 
 '', 'g');
  regexp_replace
 
  300 north 126th street
  
 I'm a novice with regular expressions and google hasn't helped much.
  
 Any suggestions?

Maybe this?

select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 
'gi');

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] Replacing Ordinal Suffixes

[Bret Stern]

This is a kick *ss forum. I must say.


On Fri, 2014-02-28 at 14:17 -0800, Steve Atkins wrote:
 On Feb 28, 2014, at 2:04 PM, George Weaver gwea...@shaw.ca wrote:
 
  Hi list,
   
  I'm stumped.
   
  I am trying to use Regexp_Replace to replace ordinal suffixes in addresses 
  (eg have '126th' want '126') for comparison purposes.  So far no luck.
   
  I have found that
   
  SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
  '(?!/D)(st|nd|rd|th)', '', 'g');
regexp_replace
  --
   300 nor 126 reet
   
  but
   
  SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
  '(?=/D)(st|nd|rd|th)', '', 'g');
   regexp_replace
  
   300 north 126th street
   
  I'm a novice with regular expressions and google hasn't helped much.
   
  Any suggestions?
 
 Maybe this?
 
 select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 
 'gi');
 
 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] Replacing Ordinal Suffixes

[George Weaver]

From: Paul Jungwirth


Try this:



SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),

'(\d)(st|nd|rd|th)', '\1', 'g');

Hi Paul,

No luck...

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), E'(\d)(st|nd|rd|th)', 
E'\1', 'g');

regexp_replace

300 north 126th street
(1 row)


Note that matching a number is \d not /D: backslash, not forward
slash, and lowercase d not uppercase. \d means a digit, \D means
anything except a digit.



Also, I don't think Postgres supports positive lookbehind expressions
(which are actually (?=foo), not (?!foo)), but you can get the same
effect by capturing the number with (\d) and then outputting it again
with the \1.



Paul




On Fri, Feb 28, 2014 at 2:04 PM, George Weaver gwea...@shaw.ca wrote:

Hi list,

I'm stumped.

I am trying to use Regexp_Replace to replace ordinal suffixes in addresses
(eg have '126th' want '126') for comparison purposes.  So far no luck.

I have found that

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(?!/D)(st|nd|rd|th)', '', 'g');
  regexp_replace
--
 300 nor 126 reet

but

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
'(?=/D)(st|nd|rd|th)', '', 'g');
 regexp_replace

 300 north 126th street

I'm a novice with regular expressions and google hasn't helped much.

Any suggestions?

Thanks,
George




--
_
Pulchritudo splendor veritatis.



--
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] Replacing Ordinal Suffixes

[George Weaver]

From: Steve Atkins

On Feb 28, 2014, at 2:04 PM, George Weaver gwea...@shaw.ca wrote:

 Hi list,

 I'm stumped.

 I am trying to use Regexp_Replace to replace ordinal suffixes in addresses 
 (eg have '126th' want '126') for comparison purposes.  So far no luck.

 I have found that

 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
 '(?!/D)(st|nd|rd|th)', '', 'g');
   regexp_replace
 --
  300 nor 126 reet

 but

 SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
 '(?=/D)(st|nd|rd|th)', '', 'g');
  regexp_replace
 
  300 north 126th street

 I'm a novice with regular expressions and google hasn't helped much.

 Any suggestions?

Maybe this?

select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', 
'\1', 'gi');

Hi Steve,

Thanks, but no luck:

select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)', 
E'\1', 'gi');
 regexp_replace

 300 North 126th Street

George

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] Replacing Ordinal Suffixes

[Steve Atkins]

On Feb 28, 2014, at 2:43 PM, George Weaver gwea...@shaw.ca wrote:

 From: Steve Atkins
 
 Maybe this?
 
 select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', 
 '\1', 'gi');
 
 Hi Steve,
 
 Thanks, but no luck:
 
 select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)', 
 E'\1', 'gi');
  regexp_replace
 
  300 North 126th Street
 
 George

Those E’s you added completely change the meaning. If you want to
use E-style literals (and you probably don’t) you’ll need to double the
backslashes in all the strings.

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] Replacing Ordinal Suffixes

[George Weaver]

- Original Message - 
From: Steve Atkins



On Feb 28, 2014, at 2:43 PM, George Weaver gwea...@shaw.ca wrote:

Maybe this?

select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)',
'\1', 'gi');

Hi Steve,

Thanks, but no luck:

select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)',
E'\1', 'gi');
 regexp_replace

 300 North 126th Street

George



Those E’s you added completely change the meaning. If you want to
use E-style literals (and you probably don’t) you’ll need to double the
backslashes in all the strings.


Hi Steve,

Without the E's:

development=# select regexp_replace('300 North 126th Street', 
'(\d+)(?:st|nd|rd|th)', '\1', 'gi');

WARNING:  nonstandard use of escape in a string literal
LINE 1: select regexp_replace('300 North 126th Street', '(\d+)(?:st|...
   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING:  nonstandard use of escape in a string literal
LINE 1: ...'300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi'...
^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
regexp_replace

300 North 126th Street
(1 row)

Frustrating...


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



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

[GENERAL] Moving data from M$ JetDB file to Postgres on Linux

[Rich Shepard]

  I just downloaded two scientific data files from a federal agency's Web
site. Both are in M$ JetDB format. I run only linux and keep all my
scientific dat in postgres.

  My Web search did not turn up anything useful; the closest was a thread
from this mail list in 2000 on how to send a postgres query through odbc to
an Access database.

  Is there a filter I can use to get the data from these files?

TIA,

Rich



--
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] Replacing Ordinal Suffixes

[George Weaver]

- Original Message - 
From: Steve Atkins

To: pgsql-general
Sent: Friday, February 28, 2014 4:17 PM
Subject: Re: [GENERAL] Replacing Ordinal Suffixes



On Feb 28, 2014, at 2:04 PM, George Weaver gwea...@shaw.ca wrote:


Hi list,

I'm stumped.

I am trying to use Regexp_Replace to replace ordinal suffixes in addresses 
(eg have '126th' want '126') for comparison purposes.  So far no luck.


I have found that

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
'(?!/D)(st|nd|rd|th)', '', 'g');

  regexp_replace
--
 300 nor 126 reet

but

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'), 
'(?=/D)(st|nd|rd|th)', '', 'g');

 regexp_replace

 300 north 126th street

I'm a novice with regular expressions and google hasn't helped much.

Any suggestions?



Maybe this?


select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)', 
'\1', 'gi');


Hi Steve,

Thanks, but no luck:

select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)', 
E'\1', 'gi');

regexp_replace

300 North 126th Street

George

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 




--
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] Josh's Comments on Hstore / Jsonb

[Brett Mc Bride]

On 26/02/14 09:32, Merlin Moncure wrote:

On Tue, Feb 25, 2014 at 3:43 PM, john.tiger 
john.tigernas...@gmail.commailto:john.tigernas...@gmail.com wrote:


Somehow my comments didn't go thru on the soup blog but wanted to throw in
more support for this patch

Josh, you are so right on the money.  An ACID compliant db that serves JSON
is great.  Sorry I didn't make SCALE this year to hear the PG talks and stop
by and say hi.  Maybe at Open West ?  We are incorporating Postgres JSON
served from Nodejs (using Express to offer the REST side) into several new
products.   For modern web apps, JSON just makes so much sense.

Yes, the Node driver needs some work - hopefully we can contribute as we
work more with it.  I'm particularly interested in working offline, then
synching (vis-a-vis couch/pouch).  If anyone else is working on this issue
please feel free to touch base.



Aside: I also have basically completely switched to node.js/express +
postgres (via 'node-postgres').  It's absolutely fantastic.  Besides
the obvious great and getting better json support, you have other
postgres-ism that mesh very well with node: asynchronous notification
for example.

merlin




I'm in the process of prototyping a DB-backed REST web service as a 
side-project. It's still early
days, but I've been handed a PHP/mysql setup.
After a bit of searching around, I thought that the exact combination above 
(node.json/express +
node-postgres), possibly coupled with JSON datatypes, could be a really 
effective solution. ()

Now for the advocacy part - before I saw this thread I wasn't sure whether 
there were any real-world
implementations of this combination. Are there any good skeletons or prototypes 
out there to get a
RESTful web service up and running with express and postgres?


--
Brett


Important Notice: The contents of this email are intended solely for the named 
addressee and are confidential; any unauthorised use, reproduction or storage 
of the contents is expressly prohibited. If you have received this email in 
error, please delete it and any attachments immediately and advise the sender 
by return email or telephone.

Deakin University does not warrant that this email and any attachments are 
error or virus free.

[GENERAL] 'tuple concurrently updated' error when granting permissions

[Jason Daly]

Hi,
When I attempt to execute the non-query GRANT SELECT ON TABLE
schema.table TO role, I occasionally encounter an exception from
postgresql, 'tuple concurrently updated'.

I think the tuple it is referring to is the role.

Our app is multi-threaded, and it is very possible for more than one thread
to be attempting to grant permissions to the same role at the same time.
(the situation is that we are creating tables on the fly, and each time we
create a new table we have to grant permissions to a role so it can read
from it).

In my view, it is a bug that postgresql cannot handle multiple concurrent
grants to a role.

We're using the latest version of postgresql (9.3) in the context of an AWS
RDS.

Should I enter a bug report, or can someone convince me that we should
single-thread this part of our app (or work around it somehow)?

Please respond to jasond...@trimblegeospatial.com

-Jason

Re: [GENERAL] Replacing Ordinal Suffixes

[Ian Lawrence Barwick]

2014-03-01 8:16 GMT+09:00 George Weaver gwea...@shaw.ca:

 - Original Message - From: Steve Atkins


 On Feb 28, 2014, at 2:43 PM, George Weaver gwea...@shaw.ca wrote:

 Maybe this?

 select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)',
 '\1', 'gi');

 Hi Steve,

 Thanks, but no luck:

 select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)',
 E'\1', 'gi');
  regexp_replace
 
  300 North 126th Street

 George


 Those E’s you added completely change the meaning. If you want to
 use E-style literals (and you probably don’t) you’ll need to double the
 backslashes in all the strings.


 Hi Steve,

 Without the E's:

 development=# select regexp_replace('300 North 126th Street',
 '(\d+)(?:st|nd|rd|th)', '\1', 'gi');
 WARNING:  nonstandard use of escape in a string literal
 LINE 1: select regexp_replace('300 North 126th Street', '(\d+)(?:st|...
^
 HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 WARNING:  nonstandard use of escape in a string literal
 LINE 1: ...'300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi'...
 ^
 HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.

 regexp_replace
 
 300 North 126th Street
 (1 row)

 Frustrating...

Per Steve Atkin's note about double backslashes:

postgres= select regexp_replace('300 North 126th Street',
E'(\\d+)(?:st|nd|rd|th)', E'\\1', 'gi');
regexp_replace
--
 300 North 126 Street
(1 row)

Regards

Ian Barwick


-- 
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] Moving data from M$ JetDB file to Postgres on Linux

[Adrian Klaver]

On 02/28/2014 03:21 PM, Rich Shepard wrote:

   I just downloaded two scientific data files from a federal agency's Web
site. Both are in M$ JetDB format. I run only linux and keep all my
scientific dat in postgres.

   My Web search did not turn up anything useful; the closest was a thread
from this mail list in 2000 on how to send a postgres query through odbc to
an Access database.

   Is there a filter I can use to get the data from these files?



The only software I could find to directly read MDB files on Linux is :

http://mdbtools.sourceforge.net/

Not sure what version of MDB you have and whether the above is current 
enough to read them. Otherwise you need to crank up an Access instance 
and go from there.




TIA,

Rich






--
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] Moving data from M$ JetDB file to Postgres on Linux

[Rich Shepard]

On Fri, 28 Feb 2014, Adrian Klaver wrote:


The only software I could find to directly read MDB files on Linux is :
http://mdbtools.sourceforge.net/
Not sure what version of MDB you have and whether the above is current enough 
to read them. Otherwise you need to crank up an Access instance and go from 
there.


Adrian,

  I have mdbtools which I used on Access files in the past. I was not aware
that JetDB was the same data format.

  And, I discovered 'jet-tool' on code.google.com. That'll do the job, I
believe.

Carpe weekend,

Rich





--
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] Replacing Ordinal Suffixes

[George Weaver]

From: Ian Lawrence Barwick



- Original Message - From: Steve Atkins




On Feb 28, 2014, at 2:43 PM, George Weaver gwea...@shaw.ca wrote:

Maybe this?

select regexp_replace('300 North 126th Street', '(\d+)(?:st|nd|rd|th)',
'\1', 'gi');

Hi Steve,

Thanks, but no luck:

select regexp_replace('300 North 126th Street', E'(\d+)(?:st|nd|rd|th)',
E'\1', 'gi');
 regexp_replace

 300 North 126th Street

George




Those E’s you added completely change the meaning. If you want to
use E-style literals (and you probably don’t) you’ll need to double the
backslashes in all the strings.



Hi Steve,

Without the E's:

development=# select regexp_replace('300 North 126th Street',
'(\d+)(?:st|nd|rd|th)', '\1', 'gi');
WARNING:  nonstandard use of escape in a string literal
LINE 1: select regexp_replace('300 North 126th Street', '(\d+)(?:st|...
   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
WARNING:  nonstandard use of escape in a string literal
LINE 1: ...'300 North 126th Street', '(\d+)(?:st|nd|rd|th)', '\1', 'gi'...
^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.

regexp_replace

300 North 126th Street
(1 row)

Frustrating...


Per Steve Atkin's note about double backslashes:

   postgres= select regexp_replace('300 North 126th Street',
E'(\\d+)(?:st|nd|rd|th)', E'\\1', 'gi');
   regexp_replace
   --
300 North 126 Street
   (1 row)

Hi Ian,

I just got that as well - awesome!

Regards

Ian Barwick 




--
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] Moving data from M$ JetDB file to Postgres on Linux

[Rich Shepard]

On Fri, 28 Feb 2014, Rich Shepard wrote:


 And, I discovered 'jet-tool' on code.google.com. That'll do the job, I
believe.


  Well, it won't work: it's a M$ .exe file and the source is apparently in
some M$-type of Pascal.

Rich



--
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] Replacing Ordinal Suffixes

[James Cloos]

 GW == George Weaver gwea...@shaw.ca writes:

GW SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),
GW E'(\d)(st|nd|rd|th)', E'\1', 'g');
GW regexp_replace
GW 
GW 300 north 126th street
GW (1 row)

The E'' syntax eats your backslashes.  For that version, try just:

SELECT REGEXP_REPLACE(LOWER('300 North 126th Street'),'(\d)(st|nd|rd|th)', 
'\1', 'g');

-JimC
--
James Cloos cl...@jhcloos.com OpenPGP: 1024D/ED7DAEA6


-- 
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] Moving data from M$ JetDB file to Postgres on Linux

[Adrian Klaver]

On 02/28/2014 03:58 PM, Rich Shepard wrote:

On Fri, 28 Feb 2014, Adrian Klaver wrote:


The only software I could find to directly read MDB files on Linux is :
http://mdbtools.sourceforge.net/
Not sure what version of MDB you have and whether the above is current
enough to read them. Otherwise you need to crank up an Access instance
and go from there.


Adrian,

   I have mdbtools which I used on Access files in the past. I was not
aware
that JetDB was the same data format.


Well JET is the database engine for Access and mdb is one of the file 
format extensions for Access database files. JET has been used in other 
MS products and Access has used different file formats(extensions) over 
the years. So you may be in for a chore depending on what files you 
actually have. Might be worth it to do some exploring and see if the 
data is available in a more useable form.




Carpe weekend,

Rich








--
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] Moving data from M$ JetDB file to Postgres on Linux

[Thomas Kellerer]

Rich Shepard wrote on 01.03.2014 00:21:

   I just downloaded two scientific data files from a federal agency's Web
site. Both are in M$ JetDB format. I run only linux and keep all my
scientific dat in postgres.

   My Web search did not turn up anything useful; the closest was a thread
from this mail list in 2000 on how to send a postgres query through odbc to
an Access database.

   Is there a filter I can use to get the data from these files?



It's not clear to me what exactly you are trying to do.

Are you trying to query the Access database from within Postgres?

Or are you just trying to copy the data into the Postgres database?

If the latter you could try one of the JDBC based query tools to export
or copy the data using the UCanAccess JDBC driver for MS Access:

http://sourceforge.net/projects/ucanaccess/

UCanAccess works quite well with the tool I am maintaining as far as I can tell.
The WbCopy command or the DbExplorer should be able to copy the data over to 
Postgres.

http://www.sql-workbench.net/
http://www.sql-workbench.net/manual/command-copy.html
http://www.sql-workbench.net/manual/data-pumper.html

Another alternative would be to export the Access database to flat files and 
import those into Postgres.

Thomas



--
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] Moving data from M$ JetDB file to Postgres on Linux

[Rich Shepard]

On Sat, 1 Mar 2014, Thomas Kellerer wrote:


It's not clear to me what exactly you are trying to do.
Or are you just trying to copy the data into the Postgres database?



If the latter you could try one of the JDBC based query tools to export
or copy the data using the UCanAccess JDBC driver for MS Access:


  Well, if I had any Microsoft software on a system that might work well.
However, I've used nothing but F/OSS on linux since 1997.

  Or, are you saying that from within psql on my linux system I can access
the data in the .mdb?

  What I want to do is extract the data from the JetDB as SQL or otherwise
get it out of the proprietary format so I can store and manipulate it in
postgres and analyze it with R.

  I'll grab the latest mdbtools and see if that works. I did not realize
before that the JetDB has the same file format as Access.

Rich



--
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] Moving data from M$ JetDB file to Postgres on Linux

[Rich Shepard]

On Fri, 28 Feb 2014, Adrian Klaver wrote:


Well JET is the database engine for Access and mdb is one of the file
format extensions for Access database files. JET has been used in other MS
products and Access has used different file formats(extensions) over the
years. So you may be in for a chore depending on what files you actually
have. Might be worth it to do some exploring and see if the data is
available in a more useable form.


Adrian,

  I'll try my mdbtools and see if there's a more current version.

  The data came from the USGS and they have it in only one format,
unfortunately.

Thanks,

Rich



--
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] Moving data from M$ JetDB file to Postgres on Linux

[Thomas Kellerer]

Rich Shepard wrote on 01.03.2014 01:33:

On Sat, 1 Mar 2014, Thomas Kellerer wrote:


It's not clear to me what exactly you are trying to do.
Or are you just trying to copy the data into the Postgres database?



If the latter you could try one of the JDBC based query tools to export
or copy the data using the UCanAccess JDBC driver for MS Access:


   Well, if I had any Microsoft software on a system that might work well.
However, I've used nothing but F/OSS on linux since 1997.

   Or, are you saying that from within psql on my linux system I can access
the data in the .mdb?


No, not within psql, but from within a Java/JDBC based query tool (such as my 
SQL Workbench)





--
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] Moving data from M$ JetDB file to Postgres on Linux

[Ian Lawrence Barwick]

2014-03-01 9:34 GMT+09:00 Rich Shepard rshep...@appl-ecosys.com:
 On Fri, 28 Feb 2014, Adrian Klaver wrote:

 Well JET is the database engine for Access and mdb is one of the file
 format extensions for Access database files. JET has been used in other MS
 products and Access has used different file formats(extensions) over the
 years. So you may be in for a chore depending on what files you actually
 have. Might be worth it to do some exploring and see if the data is
 available in a more useable form.


 Adrian,

   I'll try my mdbtools and see if there's a more current version.

   The data came from the USGS and they have it in only one format,
 unfortunately.

Not sure if this will be of use, but there are a couple of links here:

  
https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_Access

I'll post it anyway in case someone comes across this thread in the future.

Regards

Ian Barwick


-- 
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] Moving data from M$ JetDB file to Postgres on Linux

[Rich Shepard]

On Fri, 28 Feb 2014, Adrian Klaver wrote:


Well JET is the database engine for Access and mdb is one of the file
format extensions for Access database files. JET has been used in other MS
products and Access has used different file formats(extensions) over the
years. So you may be in for a chore depending on what files you actually
have. Might be worth it to do some exploring and see if the data is
available in a more useable form.


Adrian,

  I'll make life easier for myself and find a different data set. I need
example data to analyze and it doesn't matter what data set I use.

Thanks,

Rich



--
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] Moving data from M$ JetDB file to Postgres on Linux

[Rich Shepard]

On Sat, 1 Mar 2014, Thomas Kellerer wrote:

No, not within psql, but from within a Java/JDBC based query tool (such as my 
SQL Workbench)


  Oh. OK.

Thanks,

Rich



--
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] Moving data from M$ JetDB file to Postgres on Linux

[Rich Shepard]

On Sat, 1 Mar 2014, Ian Lawrence Barwick wrote:


Not sure if this will be of use, but there are a couple of links here:

 
https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_Access

I'll post it anyway in case someone comes across this thread in the future.


Ian,

  Thanks. I'll look at it.

Rich



--
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] Moving data from M$ JetDB file to Postgres on Linux

[Adrian Klaver]

On 02/28/2014 04:10 PM, Thomas Kellerer wrote:

Rich Shepard wrote on 01.03.2014 00:21:

   I just downloaded two scientific data files from a federal agency's
Web
site. Both are in M$ JetDB format. I run only linux and keep all my
scientific dat in postgres.

   My Web search did not turn up anything useful; the closest was a
thread
from this mail list in 2000 on how to send a postgres query through
odbc to
an Access database.

   Is there a filter I can use to get the data from these files?



It's not clear to me what exactly you are trying to do.

Are you trying to query the Access database from within Postgres?

Or are you just trying to copy the data into the Postgres database?

If the latter you could try one of the JDBC based query tools to export
or copy the data using the UCanAccess JDBC driver for MS Access:

http://sourceforge.net/projects/ucanaccess/


That is one I missed. Thanks for the heads up.




--
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] Moving data from M$ JetDB file to Postgres on Linux

[Adrian Klaver]

On 02/28/2014 04:34 PM, Rich Shepard wrote:

On Fri, 28 Feb 2014, Adrian Klaver wrote:


Well JET is the database engine for Access and mdb is one of the file
format extensions for Access database files. JET has been used in
other MS
products and Access has used different file formats(extensions) over the
years. So you may be in for a chore depending on what files you actually
have. Might be worth it to do some exploring and see if the data is
available in a more useable form.


Adrian,

   I'll try my mdbtools and see if there's a more current version.

   The data came from the USGS and they have it in only one format,
unfortunately.


What dataset?

My Dad is a geologist and I remember downloading datasets from the USGS 
for him, in a variety of formats.




Thanks,

Rich






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