Oliver Elphick | 1 Aug 01:46 2002
Picon

Re: Importing/Exporting

On Wed, 2002-07-31 at 22:39, Nathan Hill wrote:
> I am new to doing DBA activities.  I am trying to
> write a bash script that will copy the value of a
> specific field  within a table in a postgres database
> on one server and import the value of that field into
> a table in a mysql database on a separate server.  I
> am not real familiar with sql statements yet so I was
> hoping that someone could give me some advice.
> 
> My initial thoughts would be to copy the output of the
> select statement to a file, copy the value that I need
> from the file to a variable, then import that variable
> into the appropriate field in the mysql database. 
> Where I am getting hung up is trying to export the
> value of a specific field to a file.
> 
> Is there an easier way of doing this?

If you want one value only, you can just store it in a variable rather
than a file:

  myvar=`psql -d mydatabase -A -t -c "SELECT field1 FROM table1 WHERE field2 = 'x' LIMIT 1"`
  mysql -e "UPDATE table2 SET field2 = '$myvar' ..." ...

--

-- 
Oliver Elphick                                Oliver.Elphick <at> lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
(Continue reading)

Sanjeev Rathore | 1 Aug 02:14 2002
Picon

image

Hi ,

Does anybody know how to put image into PostgreSQL and
take it out using Java.  Sample code would be most
helpful.

Thanks,

Sanjeev Rathore

__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo <at> postgresql.org so that your
message can get through to the mailing list cleanly

Haroldo Stenger | 1 Aug 02:25 2002
Picon
Picon

Re: Case Tool

El mié, 31-07-2002 a las 17:46, João Paulo Batistella escribió:
> Hi
> 
> Is there any good case tool which I can use to model
> my database and generate SQL code to Postgre??
> 

Take a look at GeneXus: http://www.genexus.com.br

Regards

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo <at> postgresql.org)

Lee Harr | 1 Aug 03:09 2002
Picon

Re: Migrating databases...

> So I got the source for 7.0.3 and installed it in 8.2 at the /usr/local level.
> All the files are where they are supposed to be, and I'm ready to get the thing
> running so I can dump the database (copied over to $HOME in 8.2 from the 8.0
> installation).
> 
> I read that I can have both versions running in parallel, and that there is a 
> simple command line that will make the database conversion.  Unfortunately,
> I don't know how to start the 7.0.3 without invoking the 7.2.1.  I'm willing
> to start over if necessary ('gmake uninstall && gmake clean && ./configure && 
> gmake && gmake install', or so I would imagine).
> 

Note: I have never tried this, but if I needed to I think I would
       do something along these lines:

check out man postmaster and man pg_dump, then
I think I would try something like this:

/usr/local/pgsql7.0.3/bin/postmaster -i -p 5433 -D /usr/local/pgsql7.0.3/data &

/usr/local/pgsql7.0.3/bin/pg_dump -h localhost -p 5433 dbToDumpName > dumpFile

Notice that you should probably specify these path names absolutely
so that you can be sure you are getting the right ones.

I was doing it this way because I could not see from pg_dump's man page
how to get it to connect via socket to a different postmaster
(the -D switch seems to do something completely different there)

---------------------------(end of broadcast)---------------------------
(Continue reading)

suga | 1 Aug 03:14 2002
Picon

Re: Case Tool


	Hi,

	On my last job they make heavy use of Genexus. It's really a
great tool, the only drawback I can say is that is does not scale well.
also, ARtech (The uruguaian producer) is a great microsoft partner (don't
even expect a Genexus for unix/linux using a Kylix codegen for example),
and there're no warranties that the Java generator will be kept in
development with the advent of the C# code generator. The connectivity
with postgresql has to be ODBC/JDBC.

	It's also a VERY VERY VERY expensive product that requires a
pretty good known-how. Might be a choice for "enterprises".

	[]'s
	Ricardo.

On 31 Jul 2002, Haroldo Stenger wrote:

> Date: 31 Jul 2002 21:25:52 -0300
> From: Haroldo Stenger <hstenger <at> adinet.com.uy>
> To: "[ISO-8859-1] Joo Paulo Batistella" <batistellabr <at> yahoo.com.br>,
>      pgsql-general <at> postgresql.org
> Subject: Re: [GENERAL] Case Tool
>
> El mi, 31-07-2002 a las 17:46, Joo Paulo Batistella escribi:
> > Hi
> >
> > Is there any good case tool which I can use to model
> > my database and generate SQL code to Postgre??
(Continue reading)

Arguile | 1 Aug 04:11 2002

Re: image

Sanjeev Rathore wrote:
> Does anybody know how to put image into PostgreSQL and
> take it out using Java.  Sample code would be most
> helpful.

Programmers Manual:
http://www.postgresql.org/idocs/index.php?jdbc-binary-data.html

Be aware of the differences between a bytea field and large objects.
http://www.postgresql.org/idocs/index.php?largeobjects.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Haroldo Stenger | 1 Aug 05:49 2002
Picon
Picon

Re: Case Tool

El mié, 31-07-2002 a las 22:14, suga <at> netbsd.com.br escribió:
> 	On my last job they make heavy use of Genexus. It's really a
> great tool, the only drawback I can say is that is does not scale well.
> also, ARtech (The uruguaian producer) is a great microsoft partner (don't
> even expect a Genexus for unix/linux using a Kylix codegen for example),
> and there're no warranties that the Java generator will be kept in
> development with the advent of the C# code generator. The connectivity
> with postgresql has to be ODBC/JDBC.
> 

The support for Java will stay as strong as the support for C# (which by
the way, is not only MS centric, check out http://www.go-mono.com).

They started to support PostgreSQL, when persuaded of that. Maybe if
they are persuaded of supporting Kylix/Delphi, they will release a
generator for that platform too. They are always open for dialog with
customers.

> 	It's also a VERY VERY VERY expensive product that requires a
> pretty good known-how. Might be a choice for "enterprises".
> 

There is a great oportunity for PostgreSQL developers and consultants
individuals and firms, in the GeneXus field. Mainly for those based in
latin american countries from Mexico, through Chile, to Brazil,
Argentina, etc. In the US GeneXus is also present, as well as in many
european, asian, and african countries.

GeneXus developers, usually know little about PostgreSQL, which opens
the oportunity I'm mentioning for PostgreSQL consultants. GeneXus
(Continue reading)

Xavier Bugaud | 1 Aug 07:41 2002

very slow updates

Hi,

I'm running PostgreSQL 7.2.1 with a redhat 7.2 box (PIII 800 / 256MB RAM /
RAID1 IDE : promise TX2000).
I'm new to Postgres, so perhaps I missed something...

Here is the point :
Using JDBC, I update one column for all the 1600 rows of a table (10
columns) : I'm running 1600 times a query like this :
for (i=0 ; i<1600 ; i++)
  rs2.executeUpdate("UPDATE my_table SET my_date=now() WHERE id="+i);

And it takes around 2 minutes !!! (Using ODBC, it also takes around 2
minutes)

I tried to VACUUM the database just before the update, but it doesn't speed
up the process...

If I duplicate my table and I run several times my aplication again, here
are the results :
1 - 11"
2 - 12"
3 - 14"
4 - 15"
5 - 16"
6 - 19"
7 - 1'15"
8 - 1'58"
9 - 1'45"
10- 1'57"
(Continue reading)

Martijn van Oosterhout | 1 Aug 08:33 2002

Re: very slow updates

On Thu, Aug 01, 2002 at 09:41:39AM +0400, Xavier Bugaud wrote:
> Hi,
> 
> I'm running PostgreSQL 7.2.1 with a redhat 7.2 box (PIII 800 / 256MB RAM /
> RAID1 IDE : promise TX2000).
> I'm new to Postgres, so perhaps I missed something...
> 
> Here is the point :
> Using JDBC, I update one column for all the 1600 rows of a table (10
> columns) : I'm running 1600 times a query like this :
> for (i=0 ; i<1600 ; i++)
>   rs2.executeUpdate("UPDATE my_table SET my_date=now() WHERE id="+i);

1. Are you using transactions?
2. Do you have an index on id?

--

-- 
Martijn van Oosterhout   <kleptog <at> svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo <at> postgresql.org

Xavier Bugaud | 1 Aug 09:31 2002

FW: very slow updates


> 1. Are you using transactions?
> 2. Do you have an index on id?

Hi, 

Thanks for answering...

1. I tried with and without transctions : same result. In the 
tests I make right now, I have disable transactions.

2. yes, a unique index.

Each time I run a "VACUUM FULL", the process is very fast 
again for 6-7 times (10-15s). After that, it takes again 
about 2-3 minutes...
When I only run a "VACUUM" (not FULL), it doesn't make any difference.

--
Xavier Bugaud

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


Gmane