Re: [GENERAL] Migrating Access to Postgres

[Fabrizio Mazzoni]

Hello .. i did the migration and my advice is that you forget about the automatic 
translation tools... 

I took the access database and recreated all af the tables and relationships between 
them manually in postgres. I also added all the necessary indexes at design time  ..
Basiclly you have to open each table in access in desgn mode and recreate it in 
postgres (maybe first write it in a text file) with sql commands ...eg:

create table foo (a serial primary key, b varchar) ..

After that you have to migrate the queries and recreate them as views in PG...

Then i dumped all the access tables to csv files and reimported them in pg with the 
copy command. Another solution to export the data from access to PG would be to link 
all the pg tables in access and execute an insert query from access This is a 
faster solution but sometimes access can run out of memory or you can get  differences 
and errors in the datatypes which are very annoying...

I kept access only as a frontend and beleive me this solution gave us a huge boost in 
production in our company ...

Best Rgeards,

Fabrizio Mazzoni

On Wed, 1 Oct 2003 09:23:44 -0600 (CST)
"Bernardo Robelo" <[EMAIL PROTECTED]> wrote:

> Hi,
> I am interested in migrating Microsoft Access database to Postgres
> database. But I do not have idea of like initiating.  Maybe some tool
> exists for this problem.
> Thanks you.
> Bernardo
> 
> 
> 
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] PGAdmin III 1.0.0 Released

[Dann Corbit]

Not everyone will be upgraded to version 7.3 or higher.

Does not work with older versions of PostgreSQL.

Just a heads up for people still using something older than 7.3.

You might have mentioned that in your release announcement.

> -Original Message-
> From: Matthew T. O'Connor [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 29, 2003 7:41 PM
> To: [EMAIL PROTECTED]
> Subject: [GENERAL] PGAdmin III 1.0.0 Released
> 
> 
> Saw this on newsforge.com just a few minutes ago.
> 
> Go to www.pgadmin.org to get your copy. I just installed the 
> windows version on a Win2k Terminal Server Box that several 
> developers use, installed easy seems to work well, and looks nice.
> 
> Contratualatios to all the PGAdmin developers on a job well 
> done, finally a nice cross-platform gui postgresql admin tool!!!
> 
> Matthew
> 
> 
> ---(end of 
> broadcast)---
> TIP 8: explain analyze is your friend
> 

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Divide by zero...

[Jan Wieck]

Robert Creager wrote:

When grilled further on (Tue, 30 Sep 2003 09:26:19 -0400),
Mike Leahy <[EMAIL PROTECTED]> confessed:
I have a query that calculates various using variables from a survey 
database.  As with any survey, there are many instantces of null values.  I'm 
wondering if there is any way to escape the error caused by dividing by zero 
or null values.  The specific message i get is:

ERROR:  floating point exception! The last floating point operation either 
exceeded legal ranges or was a divide by zero

Is there a simple trick that won't make my queries excessively complex?
I believe CASE and COALESCE will solve your problem.  Something like this:

SELECT CASE COALESCE( denom, 0.0 )
  WHEN 0.0 THEN 0.0
  ELSE COALESCE( num, 0.0 ) / denom
   END
FROM some_table;
Definitely not. The result of a division by zero is undefined, and that 
has a good reason. You cannot substitute it with zero or any other 
explicit value without rendering your whole computation absurd. Look at 
this simple example:

Let 2a = b  | * 2
4a = 2b | + 10a
   14a = 2b + 10a   | - 7b
  14a - 7b = 10a - 5b   | ()
7 (2a - b) = 5 (2a - b) | / (2a - b)
 7 = 5
Everything is fine, just that the division by (2a - b) is not allowed 
because 2a = b and thus (2a - b) = 0. This demonstrates well that 
division by zero only leads to nonsense, and nothing else. So please 
change the 0.0 case to return NULL instead.

Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] Can anyone recommend a good PostGres admin tool?

[Rick Seeger]

We're looking for a (preferrably free) Windows (or browser) based PG
admin tool to use in our office. I've only ever used the psql command
line interface, so I don't have any experience with the GUI tools out
there. Any help appreciated.

Cheers,
Rick




---(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: [GENERAL] Can anyone recommend a good PostGres admin tool?

[Robby Russell]

Rick Seeger wrote:
We're looking for a (preferrably free) Windows (or browser) based PG
admin tool to use in our office. I've only ever used the psql command
line interface, so I don't have any experience with the GUI tools out
there. Any help appreciated.
Cheers,
Rick
Rick,
This might help you:
http://techdocs.postgresql.org/guides/GUITools



--
Robby Russell,  |  Sr. Administrator / Lead Programmer
Command Prompt, Inc.   |  http://www.commandprompt.com
[EMAIL PROTECTED] | Telephone: (503) 222.2783
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Can anyone recommend a good PostGres admin tool?

[Martin Marques]

El Mié 01 Oct 2003 19:58, Rick Seeger escribió:
> We're looking for a (preferrably free) Windows (or browser) based PG
> admin tool to use in our office. I've only ever used the psql command
> line interface, so I don't have any experience with the GUI tools out
> there. Any help appreciated.

>From Windows I would recommend phpPgAdmin, but you'll need a web server with 
PHP, and if you don't have it, it might not be the best tool.
You can also try PgAdmin III.

-- 
 20:26:01 up 40 days, 12:08,  2 users,  load average: 0.65, 1.00, 0.97
-
Martín Marqués  |[EMAIL PROTECTED]
Programador, Administrador, DBA |   Centro de Telematica
   Universidad Nacional
del Litoral
-


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Server recommendations

[Dennis Gearon]

Anyone got links to good db server boxes,  not rackmount though?

Include any for HP, Gateway, etc.

--
"You are behaving like a man",
is an insult from some women,
a compliment from a good woman.


---(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: [GENERAL] Can anyone recommend a good PostGres admin tool?

[Andrew Rawnsley]

I've found DBVisualizer to be an excellent tool. Its Java based, and 
not specific to Postgres. There are free and payware options.

http://www.minq.se/

(I don't work for them.)

On Wednesday, October 1, 2003, at 06:58 PM, Rick Seeger wrote:

We're looking for a (preferrably free) Windows (or browser) based PG
admin tool to use in our office. I've only ever used the psql command
line interface, so I don't have any experience with the GUI tools out
there. Any help appreciated.
Cheers,
Rick


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



Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] BLOBs, pg_dump & pg_restore

[Howard Lowndes]

My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2

I have no difficulty inserting and managing BLOBs into the Large Object 
system table, and I have a user table called images which maintains the 
relationship between the BLOB loid and the identity that relates to it in 
my user tables.  So far so good.

When I RTFM obout psql it refers to the \lo_import, \lo_list, \lo_export
and \lo_unlink functions.

The syntax for the \lo_import function indicates that a comment may be 
appended to the BLOB entry in the large object system table.  What is not 
mentioned is that this will only occur if psql is run as the PostgreSQL 
superuser.

Now, my concern is that if I use pg_dump with the --clean or --create, and 
the --blobs options, and then try a pg_restore from the resulting archive 
file, I believe the BLOBs will take up a different loid to the one they 
came from, and hence the relation in my user table will be broken and I 
will not be able to relocate the BLOBs using my identifier in my images 
table.

My other problem is that the various functions in PHP, namely the various 
pg_lo_* functions do not appear to have the ability to include the comment 
option that is available to \lo_import under psql.

I suppose one workaround, though not very elegant, would be to use under
PHP something like `psql \lo_export ` whilst running
through the records in the images table, and not to use the --blobs option
under pg_dump, then use `psql \lo_import ` called from
PHP to reload them after a pg_restore has been run, at the same time
updating the loids in my images table.  As I say very inelegant.

I guess this must be a shortfall in both PHP, in as much as it doesn't 
appear to handle BLOBs to cleanly, and PostgreSQL in its way that it 
handles the description column in the large opjects system table.

Am I right or wrong, or is there a better workaround?

-- 
Howard.
LANNet Computing Associates - Your Linux people 
--
Flatter government, not fatter government - Get rid of the Australian states.
--
If all economists were laid end to end, they would not reach a conclusion 
- George Bernard Shaw


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] BLOBs, pg_dump & pg_restore

[Howard Lowndes]

On Wed, 1 Oct 2003, Joshua D. Drake wrote:

> Hello,
> 
>   We usually have a table called something like file_objects that 
> contains information like the loid, content-type, filesize etc...
> that we reference.

Yes, that's what I also have got:

test=> \d images
  Table "images"
  Column   |   Type   | Modifiers 
---+--+---
 id| text | not null
 loid  | oid  | not null
 imagetype | integer  | not null
 imagesize | integer  | not null
 imagex| integer  | not null
 imagey| integer  | not null
 caption   | text | 
 timestamp | timestamp with time zone | not null


and a sample if the data is:

test=> select * from images;
   id   |  loid   | imagetype | imagesize | imagex | imagey | caption 
|   timestamp   
+-+---+---+++-+---
 100732 | 2085885 | 2 | 27215 |576 |432 | Paint Job   
| 2003-10-01 09:47:01.254781+10
 100732 | 2085887 | 2 | 36606 |500 |357 | Out of 
Africa   | 2003-10-01 11:37:23.791189+10
 100732 | 2085893 | 1 | 34958 | 54 |135 | An animated 
gif | 2003-10-01 22:26:24.63995+10
 100732 | 2085895 | 3 | 45727 |523 |100 | A png image 
| 2003-10-01 22:30:44.0359+10
(4 rows)


The BLOBs are:

test=> \lo_list
 Large objects
   ID| Description 
-+-
 2085885 | 
 2085887 | 
 2085893 | 
 2085895 | 
(4 rows)


My concern is the the relationship between id and loid in images will be 
lost by reason of a pg_dump -c -b and a subsequent pg_restore causing the 
BLOBs to locate into different loids.

Is there some way of constraining loid in images to ID in Large Objects?

> 
> 
> Howard Lowndes wrote:
> 
> >My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2
> >
> >I have no difficulty inserting and managing BLOBs into the Large Object 
> >system table, and I have a user table called images which maintains the 
> >relationship between the BLOB loid and the identity that relates to it in 
> >my user tables.  So far so good.
> >
> >When I RTFM obout psql it refers to the \lo_import, \lo_list, \lo_export
> >and \lo_unlink functions.
> >
> >The syntax for the \lo_import function indicates that a comment may be 
> >appended to the BLOB entry in the large object system table.  What is not 
> >mentioned is that this will only occur if psql is run as the PostgreSQL 
> >superuser.
> >
> >Now, my concern is that if I use pg_dump with the --clean or --create, and 
> >the --blobs options, and then try a pg_restore from the resulting archive 
> >file, I believe the BLOBs will take up a different loid to the one they 
> >came from, and hence the relation in my user table will be broken and I 
> >will not be able to relocate the BLOBs using my identifier in my images 
> >table.
> >
> >My other problem is that the various functions in PHP, namely the various 
> >pg_lo_* functions do not appear to have the ability to include the comment 
> >option that is available to \lo_import under psql.
> >
> >I suppose one workaround, though not very elegant, would be to use under
> >PHP something like `psql \lo_export ` whilst running
> >through the records in the images table, and not to use the --blobs option
> >under pg_dump, then use `psql \lo_import ` called from
> >PHP to reload them after a pg_restore has been run, at the same time
> >updating the loids in my images table.  As I say very inelegant.
> >
> >I guess this must be a shortfall in both PHP, in as much as it doesn't 
> >appear to handle BLOBs to cleanly, and PostgreSQL in its way that it 
> >handles the description column in the large opjects system table.
> >
> >Am I right or wrong, or is there a better workaround?
> >
> >  
> >
> 
> 

-- 
Howard.
LANNet Computing Associates - Your Linux people 
--
Flatter government, not fatter government - Get rid of the Australian states.
--
If all economists were laid end to end, they would not reach a conclusion 
- George Bernard Shaw


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] BLOBs, pg_dump & pg_restore

[Tom Lane]

Howard Lowndes <[EMAIL PROTECTED]> writes:
> Now, my concern is that if I use pg_dump with the --clean or --create, and 
> the --blobs options, and then try a pg_restore from the resulting archive 
> file, I believe the BLOBs will take up a different loid to the one they 
> came from, and hence the relation in my user table will be broken

No, because pg_restore has logic to adjust the references to match the
new BLOB OIDs.  If you have a test case where this fails to work, let's
see it ...

> My other problem is that the various functions in PHP, namely the various 
> pg_lo_* functions do not appear to have the ability to include the comment 
> option that is available to \lo_import under psql.

psql is out on a limb claiming that LOs can have comments --- there's no
support for that in the backend or any other client application.  It's
doing it by direct manual injection of entries into the pg_description
system catalog, which is why superuser privilege is needed.  It's a
useful hack if you only use psql, but still a hack.  Feel free to
contribute a patch for backend COMMENT ON LARGE OBJECT support, if you'd
like to see a better level of support for this.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] BLOBs, pg_dump & pg_restore

[Howard Lowndes]

On Wed, 1 Oct 2003, Tom Lane wrote:

> Howard Lowndes <[EMAIL PROTECTED]> writes:
> > Now, my concern is that if I use pg_dump with the --clean or --create, and 
> > the --blobs options, and then try a pg_restore from the resulting archive 
> > file, I believe the BLOBs will take up a different loid to the one they 
> > came from, and hence the relation in my user table will be broken
> 
> No, because pg_restore has logic to adjust the references to match the
> new BLOB OIDs.  If you have a test case where this fails to work, let's
> see it ...

No, I don't have any example, it is an enquiry.  What I am reading into 
the above however is that the loid column in my table should have a 
CONSTRAINT REFERENCES clause to whereever in the system large objects 
table.  Correct?

> 
> > My other problem is that the various functions in PHP, namely the various 
> > pg_lo_* functions do not appear to have the ability to include the comment 
> > option that is available to \lo_import under psql.
> 
> psql is out on a limb claiming that LOs can have comments --- there's no
> support for that in the backend or any other client application.  It's
> doing it by direct manual injection of entries into the pg_description
> system catalog, which is why superuser privilege is needed.  It's a
> useful hack if you only use psql, but still a hack.  Feel free to
> contribute a patch for backend COMMENT ON LARGE OBJECT support, if you'd
> like to see a better level of support for this.

Sorry, way beyond my competency level.

-- 
Howard.
LANNet Computing Associates - Your Linux people 
--
Flatter government, not fatter government - Get rid of the Australian states.
--
If all economists were laid end to end, they would not reach a conclusion 
- George Bernard Shaw


---(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: [GENERAL] BLOBs, pg_dump & pg_restore

[Tom Lane]

Howard Lowndes <[EMAIL PROTECTED]> writes:
> On Wed, 1 Oct 2003, Tom Lane wrote:
>> No, because pg_restore has logic to adjust the references to match the
>> new BLOB OIDs.  If you have a test case where this fails to work, let's
>> see it ...

> No, I don't have any example, it is an enquiry.  What I am reading into 
> the above however is that the loid column in my table should have a 
> CONSTRAINT REFERENCES clause to whereever in the system large objects 
> table.  Correct?

No.  No doubt if Postgres had had foreign keys when the large-object stuff
was invented, it would have required such a constraint for LO
references, but it didn't and it doesn't.  The pg_restore code simply
goes through all "oid" columns (and all "lo" columns if you've installed
the contrib/lo datatype) and looks for matches to LO OIDs that existed
in the dumped database.  When it finds a match, it replaces that value
with the new BLOB's OID.  Simple, effective, crufty ...

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