Re: [SQL] Import from Ms Excel
On Tue, Mar 16, 2004 at 03:13:38PM +0530, Kumar wrote: > Dear Friends, > > Is possible to import data from MS Excel sheet into postgres > database 7.3.4 running on Linux 7.2 Yes. I find the easiest way is to export a delimited file from Excel and use the \copy command in psql. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Import from Ms Excel
O kyrios Andrew Sullivan egrapse stis Mar 16, 2004 : > On Tue, Mar 16, 2004 at 03:13:38PM +0530, Kumar wrote: > > Dear Friends, > > > > Is possible to import data from MS Excel sheet into postgres > > database 7.3.4 running on Linux 7.2 > > Yes. I find the easiest way is to export a delimited file from Excel > and use the \copy command in psql. Another fancy lib (although not necessarilly pgsql specific), is the POI project from jakarta. You can read/write M$ XLS documents from java, and subsequently (via jdbc) manipulate pgsql tables. The good part is that you can dynamically generate an excel file with arbitary content. > > A > > -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Import from Ms Excel
On Tue, Mar 16, 2004 at 01:42:45PM +0200, Achilleus Mantzios wrote: > Another fancy lib (although not necessarilly pgsql specific), > is the POI project from jakarta. > You can read/write M$ XLS documents from java, and > subsequently (via jdbc) manipulate pgsql tables. > > The good part is that you can dynamically generate an excel file > with arbitary content. You can do the same thing (I have done it) with Perl using the Pg and WriteExcel modules. Excel has a lot of pretty annoying limitations, though, and it's fairly dangerous to get too dependent on it for this sort of thing, as you can easily run into its limitations. I suppose it depends on how big your resulting spreadsheets are going to be. In my experience, though, the first thing that happens when you deliver someone a summary spreadsheet is, they ask you for the raw data so they can double-check it. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Import from Ms Excel
O kyrios Andrew Sullivan egrapse stis Mar 16, 2004 : > On Tue, Mar 16, 2004 at 01:42:45PM +0200, Achilleus Mantzios wrote: > > Another fancy lib (although not necessarilly pgsql specific), > > is the POI project from jakarta. > > You can read/write M$ XLS documents from java, and > > subsequently (via jdbc) manipulate pgsql tables. > > > > The good part is that you can dynamically generate an excel file > > with arbitary content. > > You can do the same thing (I have done it) with Perl using the Pg and > WriteExcel modules. Excel has a lot of pretty annoying limitations, > though, and it's fairly dangerous to get too dependent on it for this > sort of thing, as you can easily run into its limitations. Especially the way "excel" tries to store date/time "cells" (in a way much like cobol in MVS mainframes), or text not being able to contain only numeric chars, etc... > I suppose > it depends on how big your resulting spreadsheets are going to be. > In my experience, though, the first thing that happens when you > deliver someone a summary spreadsheet is, they ask you for the raw > data so they can double-check it. At least they dont have to worry about their latest antivirus update! > > A > > -- -Achilleus ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] conversion of postgres database to oracle
How should I convert a postgres database to oracle?
Re: [SQL] Import from Ms Excel
Certainly you can export your spreadsheet in CSV and it will easily go into Postgresql database (after you create the database and its tables). If you want something more sophisticated, a program in e.g. Perl could use one of the various library programs to extract data in some other-than-straightforward manner (perhaps only certain columns or a certain set of regions?) and then use one of the database libraries (e.g. DBI) to insert into database. The CSV export is the simplest approach. On Tue, 16 Mar 2004, Kumar wrote: > Dear Friends, > > Is possible to import data from MS Excel sheet into postgres database 7.3.4 running > on Linux 7.2 > > Thanks > Kumar > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] flat file database to postgres
Hi Does anybody know how to export data from a flatfile (using perl) database to postgres? Thanks ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] could not create shared memory segment: Invalid argument
Hi, I have just installed the latest version of OS X panther (10.3.3) and am now getting the following error message: postgres$ /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data FATAL: could not create shared memory segment: Invalid argument DETAIL: Failed system call was shmget(key=5432001, size=10444800, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 10444800 bytes), reduce PostgreSQL's shared_buffers parameter (currently 1000) and/or its max_connections parameter (currently 100). If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for. The PostgreSQL documentation contains more information about shared memory configuration. Any ideas on how i can resolve this issue? many thanks in advance ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] could not create shared memory segment: Invalid argument
O kyrios beyaNet Consultancy egrapse stis Mar 16, 2004 : > Hi, > I have just installed the latest version of OS X panther (10.3.3) and > am now getting the following error message: For start do: sysctl -a | grep -i shm or equivalent to find out your current kernel settings. in FreeBSD the way to change shmmax is # sysctl sysctl kern.ipc.shmmax= I dont know about OSX tho.. > > postgres$ /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data > FATAL: could not create shared memory segment: Invalid argument > DETAIL: Failed system call was shmget(key=5432001, size=10444800, > 03600). > HINT: This error usually means that PostgreSQL's request for a shared > memory segment exceeded your kernel's SHMMAX parameter. You can either > reduce the request size or reconfigure the kernel with larger SHMMAX. > To reduce the request size (currently 10444800 bytes), reduce > PostgreSQL's shared_buffers parameter (currently 1000) and/or its > max_connections parameter (currently 100). > If the request size is already small, it's possible that it is > less than your kernel's SHMMIN parameter, in which case raising the > request size or reconfiguring SHMMIN is called for. > The PostgreSQL documentation contains more information about > shared memory configuration. > > Any ideas on how i can resolve this issue? > > many thanks in advance > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] could not create shared memory segment: Invalid argument
Well, did you consider getting the value of SHMMAX? Check with ulimit (as the postgres user). On Tue, 16 Mar 2004, beyaNet Consultancy wrote: > Hi, > I have just installed the latest version of OS X panther (10.3.3) and > am now getting the following error message: > > postgres$ /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data > FATAL: could not create shared memory segment: Invalid argument > DETAIL: Failed system call was shmget(key=5432001, size=10444800, > 03600). > HINT: This error usually means that PostgreSQL's request for a shared > memory segment exceeded your kernel's SHMMAX parameter. You can either > reduce the request size or reconfigure the kernel with larger SHMMAX. > To reduce the request size (currently 10444800 bytes), reduce > PostgreSQL's shared_buffers parameter (currently 1000) and/or its > max_connections parameter (currently 100). > If the request size is already small, it's possible that it is > less than your kernel's SHMMIN parameter, in which case raising the > request size or reconfiguring SHMMIN is called for. > The PostgreSQL documentation contains more information about > shared memory configuration. > > Any ideas on how i can resolve this issue? > > many thanks in advance > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] flat file database to postgres
On Tue, 16 Mar 2004 12:33:57 - (GMT) [EMAIL PROTECTED] sat down, thought long and then wrote: > > Hi > > Does anybody know how to export data from a flatfile (using perl) database > to postgres? > > Thanks If it is some kind of CSV (text with separators) you could use DBD::CSV for reading and DBD::Pg for writing. Or you read the file with Perl´s standard functions, treat the data to be PostgreSQL-insert-query-compatible (regarding apostrophes for example) and then use DBD::Pg (that´s what I usually do). If you have some kind of DBase files, you can use DBD::XBase. Regards, Frank. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Can statement_timeout emulated NOWAIT?
I've researched the recent discussions about statement_timeout and NOWAIT and I'd like to confirm my understanding of the situation before acting on it. Please let me know if the following statements are true: - LOCK TABLE ... NOWAIT has been checked in and will be available in 7.5 - SELECT ... FOR UPDATE NOWAIT is in the TODO list (among other NOWAIT commands) but it will also be available in 7.5 - Using "SET LOCAL statement_timeout=xxx" can be used before "SELECT ... FOR UPDATE" to emulate NOWAIT when the select is simple and involves a relatively small number of rows. That is, the following: BEGIN; SET LOCAL statement_timeout=3000; SELECT ... FOR UPDATE; -- (1 row) COMMIT; will be equivalent to: BEGIN; SELECT ... FOR UPDATE NOWAIT; COMMIT; - The NOWAIT feature will be able to handle select-for-updates that involve many rows better than statement_timeout because it will track timeouts on a row by row basis, which eliminates false positives. That is, if the statement_timeout is too short for the select, it might fail before finishing the select instead of failing because of locked row. - The error raised by statement_timeout and NOWAIT will be the same. Basically, I'd like to write low level code that implements NOWAIT in the application using statement_timeout. Later on, 7.5, I'd be able to reimplement the low level functions using NOWAIT without having to change the higher level code. Your thoughts and comments are appreciated. Thanks Luis P Caamano Atlanta, GA, USA ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] conversion of postgres database to oracle
On Tue, Mar 16, 2004 at 02:13:57PM +0200, cristi wrote: > How should I convert a postgres database to oracle? Send it out to ASCII and then import it to Oracle. But if you want support for going _to_ Oracle, you probably ought to get support from Oracle people, right? A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Can statement_timeout emulated NOWAIT?
Yes, I think these are all correct, except I am not positive FOR UPDATE NOWAIT will be in 7.5 unless someone codes it. --- Luis P Caamano wrote: > > I've researched the recent discussions about statement_timeout > and NOWAIT and I'd like to confirm my understanding of the > situation before acting on it. Please let me know if the > following statements are true: > > - LOCK TABLE ... NOWAIT has been checked in and will be available > in 7.5 > > - SELECT ... FOR UPDATE NOWAIT is in the TODO list (among other > NOWAIT commands) but it will also be available in 7.5 > > - Using "SET LOCAL statement_timeout=xxx" can be used before > "SELECT ... FOR UPDATE" to emulate NOWAIT when the select > is simple and involves a relatively small number of rows. > That is, the following: > > BEGIN; > SET LOCAL statement_timeout=3000; > SELECT ... FOR UPDATE; -- (1 row) > COMMIT; > > will be equivalent to: > > BEGIN; > SELECT ... FOR UPDATE NOWAIT; > COMMIT; > > - The NOWAIT feature will be able to handle select-for-updates > that involve many rows better than statement_timeout because > it will track timeouts on a row by row basis, which eliminates > false positives. That is, if the statement_timeout is too > short for the select, it might fail before finishing the select > instead of failing because of locked row. > > - The error raised by statement_timeout and NOWAIT will be the > same. > > Basically, I'd like to write low level code that implements > NOWAIT in the application using statement_timeout. Later on, > 7.5, I'd be able to reimplement the low level functions using > NOWAIT without having to change the higher level code. > > Your thoughts and comments are appreciated. > > Thanks > > Luis P Caamano > Atlanta, GA, USA > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Can statement_timeout emulated NOWAIT?
"Luis P Caamano" <[EMAIL PROTECTED]> writes: > I've researched the recent discussions about statement_timeout > and NOWAIT and I'd like to confirm my understanding of the > situation before acting on it. Please let me know if the > following statements are true: > - LOCK TABLE ... NOWAIT has been checked in and will be available > in 7.5 Yes. > - SELECT ... FOR UPDATE NOWAIT is in the TODO list (among other > NOWAIT commands) but it will also be available in 7.5 I haven't seen anyone working on that. > - Using "SET LOCAL statement_timeout=xxx" can be used before > "SELECT ... FOR UPDATE" to emulate NOWAIT when the select > is simple and involves a relatively small number of rows. Yes, if you are sure you know how long the select "ought" to take. > - The error raised by statement_timeout and NOWAIT will be the > same. Wouldn't expect so. (Right now, I think statement_timeout reports the same error code as query cancel, which is pretty bogus also.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Import from Ms Excel
--- Andrew Sullivan <[EMAIL PROTECTED]> wrote: > On Tue, Mar 16, 2004 at 03:13:38PM +0530, Kumar > wrote: > > Dear Friends, > > > > Is possible to import data from MS Excel sheet > into postgres > > database 7.3.4 running on Linux 7.2 > > Yes. I find the easiest way is to export a > delimited file from Excel > and use the \copy command in psql. A couple of quick points: * Don't use .csv, unless you are quite sure that your data does not include any commas. "Copy" isn't smart enough to know that the quotes mean "ignore the delimiters inside here". * If your data does include commas, even using tab-delimited will cause Excel to pointlessly quote those columns, and the quotes will be imported as data unless you remove them. * Make sure to remove any carriage returns that may have been included with your line endings. I think newer versions of "copy" are better about handling that, but best to be sure... > > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > I remember when computers were frustrating because > they *did* exactly what > you told them to. That actually seems sort of > quaint now. > --J.D. Baldwin > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster __ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Can statement_timeout emulated NOWAIT?
Excellent! Thanks for your prompt replies. -- Luis P Caamano Atlanta, GA, USA ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] working with unix timestamp
Hi folks. I've got a last_updated field on my stock records of type timestamp. This last_updated field I get using the perl code: my $timestamp=(stat "$localcsv/VehicleStock.$data_suffix")[10]; How can I insert the integer timestamp in $timestamp into my table? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] working with unix timestamp
On Tue, 16 Mar 2004 16:54:18 + Gary Stainburn <[EMAIL PROTECTED]> sat down, thought
long and then wrote:
> Hi folks.
>
> I've got a last_updated field on my stock records of type timestamp.
>
> This last_updated field I get using the perl code:
>
> my $timestamp=(stat "$localcsv/VehicleStock.$data_suffix")[10];
>
> How can I insert the integer timestamp in $timestamp into my table?
I usually use somethinge like the following little function for getting an ISO
timestamp. The
result is suitable for a PostgreSQL timestamp field (without special timezone).
# Subroutine for ISO-Timestamp
sub mydatetime
{
my ($time)[EMAIL PROTECTED];
my ($sec,$min,$hou,$mday,$mon,$yea,$wday,$jday,$sz)=localtime($time);
if ($sec < 10) {$sec="0".$sec;}
if ($min < 10) {$min="0".$min;}
if ($hou < 10) {$hou="0".$hou;}
if ($mday < 10) {$mday="0".$mday;}
$mon++;
if ($mon < 10) {$mon="0".$mon;}
$yea=$yea+1900;
my $t=$yea."-".$mon."-".$mday." ".$hou.":".$min.":".$sec;
return $t;
}
Regards, Frank.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] working with unix timestamp
Gary Stainburn <[EMAIL PROTECTED]> writes: > How can I insert the integer timestamp in $timestamp into my table? The "clean" way is select 'epoch'::timestamptz + * '1 second'::interval; for instance regression=# select 'epoch'::timestamptz + 1079459165 * '1 second'::interval; ?column? 2004-03-16 12:46:05-05 (1 row) The "dirty" way is to rely on abstime being binary-compatible with int4: regression=# select 1079459165::abstime::timestamptz; timestamptz 2004-03-16 12:46:05-05 (1 row) This is probably a tad faster, but abstime is deprecated and will disappear sometime before Y2038 becomes an issue. Also, this *only* works for integers, whereas the other way handles fractional seconds just fine. BTW, the reverse transformation is extract(epoch): regression=# select extract(epoch from '2004-03-16 12:46:05-05'::timestamptz); date_part 1079459165 (1 row) Note that I have been careful to work with timestamp with time zone (timestamptz) here. If you work with timestamp without time zone, your results will be off by your GMT offset. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Line length in pl/pgsql function
"David Olbersen" <[EMAIL PROTECTED]> writes: > I was minding my business, writing a nice long pl/pgsql function and all was well. I > tried creating the function (using \i ) and started getting > funny errors. > I figured out eventually that the problem seems due to line length in a construct > of: FOR result IN LOOP Hmm ... plpgsql had some string-length issues as recently as 7.2.2, but I don't know of any problems since then. Could you submit a *complete* test case, rather than making us guess the details? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
