R: R: R: [GENERAL] How to check if 2 series of data are equal

-Messaggio originale- Da: Adrian Klaver [mailto:akla...@comcast.net] Inviato: giovedì 12 febbraio 2009 23.22 A: Paolo Saudin Cc: pgsql-general@postgresql.org Oggetto: Re: R: R: [GENERAL] How to check if 2 series of data are equal On Thursday 12 February 2009 11:37:37 am Paolo Saudin wro

Re: [GENERAL] TSearch queries with multiple languages

On Thu, 12 Feb 2009, Tom Lane wrote: Gordon Callan writes: Next we create an index on the ts_vector column: CREATE INDEX node_ts_body on node USING gin(ts_body); From the documentation, it seems this index will know what config each row has. No, actually the index doesn't know and doesn'

Re: R: R: [GENERAL] How to check if 2 series of data are equal

On Thu, 2009-02-12 at 14:21 -0800, Adrian Klaver wrote: > On Thursday 12 February 2009 11:37:37 am Paolo Saudin wrote: > > > SELECT fulldate,sensor > FROM (SELECT fulldate,sensor,count(sensor) > FROM (SELECT 1 AS station, fulldate, meanvalue AS sensor FROM > table1 > UNION >

Re: [GENERAL] audit table

Sim Zacks wrote: > I want a trigger on every table that inserts the old row into an audit > table (for updates and deletes). If the audit table was per table, then > I could easily have a field of type that table and insert old into it. > Is there any way that I could accomplish this functionalit

Re: [GENERAL] TSearch queries with multiple languages

Gordon Callan writes: > Next we create an index on the ts_vector column: > CREATE INDEX node_ts_body on node USING gin(ts_body); > From the documentation, it seems this index will know what config each row > has. No, actually the index doesn't know and doesn't care. The tsvector representat

[GENERAL] TSearch queries with multiple languages

Greetings, I'm implementing full text search at our company, using Tsearch2 and have read Chapter 12 (FTS) numerous times and am still unclear about something. All our data is stored in Postgresql in Unicode. The data to be searched can be in a number of different languages. I plan to creat

Re: [GENERAL] Remote Connection

Bob Pawley wrote: Hi I've been operating on localhost successfully for some time. I am now attempting to access a Postgresql 8.3 database installed on one of my other computers conneted through a router. Is this possible? My first attempts have been with Postgresql Admin. I changed host

Re: [GENERAL] Remote Connection

On Thu, Feb 12, 2009 at 5:04 PM, Bob Pawley wrote: > Hi > > I've been operating on localhost successfully for some time. > > I am now attempting to access a Postgresql 8.3 database installed on one of > my other computers conneted through a router. > > Is this possible? > > My first attempts hav

Re: [GENERAL] Remote Connection

On Thursday 12 February 2009, "Bob Pawley" wrote: > I also ran - listen virtual; on the receiving server. > > It hasn't connected successfully. I get the message 'Server not > listening' > > What else can I do?? Setup PostgreSQL on the server to listen on its external interface. Adjust the firew

Re: [GENERAL] Remote Connection

On Thursday 12 February 2009 3:04:54 pm Bob Pawley wrote: > Hi > > I've been operating on localhost successfully for some time. > > I am now attempting to access a Postgresql 8.3 database installed on one of > my other computers conneted through a router. > > Is this possible? > > My first attempts

Re: [GENERAL] Remote Connection

Bob Pawley wrote: Hi   I've been operating on localhost successfully for some time.   I am now attempting to access a Postgresql 8.3 database installed on one of my other computers conneted through a router.   Is this possible?   My first attempts have been with Post

[GENERAL] Remote Connection

Hi I've been operating on localhost successfully for some time. I am now attempting to access a Postgresql 8.3 database installed on one of my other computers conneted through a router. Is this possible? My first attempts have been with Postgresql Admin. I changed host to the IP address of th

Re: [GENERAL] Good Delimiter for copy command

On Thu, 12 Feb 2009, SHARMILA JOTHIRAJAH wrote: Im not using this for loading postgres data to postgres. Im trying this method to load my Oracle data to postgresql tables just trying to migrate my data from oracle to postgresql The point other posts here were trying to make to you is that

Re: R: R: [GENERAL] How to check if 2 series of data are equal

On Thursday 12 February 2009 11:37:37 am Paolo Saudin wrote: > > > > Can be the same data ( and it is ) because of errors in the remote > > stations configurations. > > The Stations and parameters IDs were mixed up resulting in same data > > in different tables ... > > > > > >I am afraid I more co

Re: [GENERAL] Good Delimiter for copy command

On Thu, 2009-02-12 at 12:51 -0800, SHARMILA JOTHIRAJAH wrote: > > My data fields contains commas, tabs,'|' etc. So I cant use t hem as > delimiters..so I need a unique may be non-character to use as a > delimiter... > -Sharmila Is this a theoretical problem or an actual one? I haven't had any

Re: [GENERAL] Good Delimiter for copy command

Andrew Gould writes: > To the list: Does pg_dump escape characters that are the same as the > delimiter? Yes. The OP has not actually explained why he needs to pick a nondefault delimiter, unless maybe it is that he wants to feed the dump to some program that is too dumb to deal with escaping.

Re: [GENERAL] Good Delimiter for copy command

--- On Thu, 2/12/09, Andrew Gould wrote: > From: Andrew Gould > Subject: Re: [GENERAL] Good Delimiter for copy command > To: sharmi...@yahoo.com > Cc: pgsql-general@postgresql.org, "Sam Mason" > Date: Thursday, February 12, 2009, 4:15 PM > On Thu, Feb 12, 2009 at 2:51 PM, SHARMILA JOTHIRAJAH

Re: [GENERAL] Good Delimiter for copy command

On Thu, Feb 12, 2009 at 2:51 PM, SHARMILA JOTHIRAJAH wrote: > My data fields contains commas, tabs,'|' etc. So I cant use t hem as > delimiters..so I need a unique may be non-character to use as a delimiter... > -Sharmila > How are you backing up your data? If your backup method deals appropri

Re: [GENERAL] Good Delimiter for copy command

On 12/02/2009 20:51, SHARMILA JOTHIRAJAH wrote: > My data fields contains commas, tabs,'|' etc. So I cant use t hem as > delimiters..so I need a unique may be non-character to use as a > delimiter... Indeed you do... and a quick RTFM reveals that you can set a character of your choice: http://

Re: [GENERAL] Good Delimiter for copy command

My data fields contains commas, tabs,'|'  etc. So I cant use t hem as delimiters..so I need a unique may be non-character to use as a delimiter... -Sharmila --- On Tue, 2/10/09, Sam Mason wrote: From: Sam Mason Subject: Re: [GENERAL] Good Delimiter for copy command To: pgsql-general@postgresql.

R: R: [GENERAL] How to check if 2 series of data are equal

>-Messaggio originale- >Da: pgsql-general-ow...@postgresql.org >[mailto:pgsql-general-ow...@postgresql.org] Per conto di Adrian Klaver >Inviato: giovedì 12 febbraio 2009 18.57 >A: Paolo Saudin >Cc: pgsql-general@postgresql.org >Oggetto: Re: R: [GENERAL] How to check if 2 series of data are

Re: [GENERAL] COPy command question

Thanks all This is my simple java code public class copy{   public static void main(String[] args) throws Exception     {   Connection connection1=null;   Connection connection2=null;   Statement stmt;   String driverName1="org.postgresql.Driver";   Class.forName(driverName2);  

Re: [GENERAL] Multiple postgres.exe On Processes

On Thu, Feb 12, 2009 at 1:21 AM, Serge Fonville wrote: > I could not find the reason as to why this way has been chosen by the > developers Because separate processes are much more robust than multiple threads. And on Linux, the difference in performance is minimal. Note some OSes like Windows

Re: [GENERAL] Killing OIDs

On Thu, Feb 12, 2009 at 10:02 AM, Steve Crawford wrote: > Scott Marlowe wrote: >> >>> Close. It actually does a "SET default_with_oids = true;" (or false) >>> prior >>> to the CREATE TABLE statement. In any case, it does preserve the OID >>> setting >>> of the source database. >>> >> >> So, I'd th

Re: [GENERAL] COPy command question

On Wed, Feb 11, 2009 at 11:22 AM, SHARMILA JOTHIRAJAH wrote: > Hi, > A question about the Postgresql's COPY command. > > This is the syntax of this command from the manual > > COPY tablename [ ( column [, ...] ) ] > FROM { 'filename' | STDIN } > [ [ WITH ] > . > I want to migr

Re: [GENERAL] How to check if 2 series of data are equal

On Thu, Feb 12, 2009 at 09:06:41AM +0100, Paolo Saudin wrote: > I have 14 tables filled with meteorological data, one record > per parameter per hour. The id field holds the parameter type > (1=temperature, 2=humidity ...) My problem is that for short periods > (maybe one week, one month) there are

Re: [GENERAL] Update table with random values from another table

On Thu, Feb 12, 2009 at 05:39:49PM +, Rory Campbell-Lange wrote: > On 12/02/09, Rory Campbell-Lange (r...@campbell-lange.net) wrote: > > I realise that for every row in my users table (which has a unique > > integer field) I can update it if I construct a matching id field > > against a random

Re: R: [GENERAL] How to check if 2 series of data are equal

- "Paolo Saudin" wrote: > >-Messaggio originale- > >Da: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] Per conto di Adrian > Klaver > >Inviato: giovedì 12 febbraio 2009 17.28 > >A: pgsql-general@postgresql.org > >Cc: Paolo Saudin > >Oggetto: Re: [GEN

R: [GENERAL] How to check if 2 series of data are equal

>-Messaggio originale- >Da: pgsql-general-ow...@postgresql.org >[mailto:pgsql-general-ow...@postgresql.org] Per conto di Adrian Klaver >Inviato: giovedì 12 febbraio 2009 17.28 >A: pgsql-general@postgresql.org >Cc: Paolo Saudin >Oggetto: Re: [GENERAL] How to check if 2 series of data are eq

Re: [GENERAL] Update table with random values from another table

On 12/02/09, Rory Campbell-Lange (r...@campbell-lange.net) wrote: > I realise that for every row in my users table (which has a unique > integer field) I can update it if I construct a matching id field > against a random row from the testnames table. I can make my join table pretty well by using

Re: [GENERAL] row constructors

On Thu, Feb 12, 2009 at 11:38 AM, Rory Campbell-Lange wrote: > On 12/02/09, Merlin Moncure (mmonc...@gmail.com) wrote: >> On Thu, Feb 12, 2009 at 5:03 AM, Sim Zacks wrote: >> > Never mind. I found an old post. >> > I just needed to do: >> > insert into a1 select (f2).* from a2; >> > >> > I didn't

Re: [GENERAL] Killing OIDs

Scott Marlowe wrote: Close. It actually does a "SET default_with_oids = true;" (or false) prior to the CREATE TABLE statement. In any case, it does preserve the OID setting of the source database. So, I'd think it would be easier to just edit the backup than to muck around in the 7.4 dat

Re: [GENERAL] row constructors

On 12/02/09, Merlin Moncure (mmonc...@gmail.com) wrote: > On Thu, Feb 12, 2009 at 5:03 AM, Sim Zacks wrote: > > Never mind. I found an old post. > > I just needed to do: > > insert into a1 select (f2).* from a2; > > > > I didn't find it the first time I searched because I was looking for row > > c

Re: [GENERAL] Update table with random values from another table

On 12/02/09, Tom Lane (t...@sss.pgh.pa.us) wrote: > =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= writes: > > On Thu, Feb 12, 2009 at 1:10 PM, Rory Campbell-Lange > > wrote: > >> UPDATE > >> users > >> SET t_firstname = > >> (select firstname from testnames order by random() limit 1), > >> t_surname = > >

Re: [GENERAL] How to check if 2 series of data are equal

On Thursday 12 February 2009 12:06:41 am Paolo Saudin wrote: > Hi, > > I have 14 tables filled with meteorological data, one record per parameter > per hour. The id field holds the parameter type (1=temperature, 2=humidity > ...) My problem is that for short periods (maybe one week, one month) ther

Re: [GENERAL] covering indexes?

Kevin Kempter writes: > I saw in the 8.1 release notes that a bug fix for covering index logic was > included. I don't know what you thought you read, but there are no covering indexes in Postgres. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] row constructors

On Thu, Feb 12, 2009 at 5:03 AM, Sim Zacks wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Never mind. I found an old post. > I just needed to do: > insert into a1 select (f2).* from a2; > > I didn't find it the first time I searched because I was looking for row > constructors, and t

Re: [GENERAL] Update table with random values from another table

Hi Grzegorz Many thanks for your reply. On 12/02/09, Grzegorz Ja??kiewicz (gryz...@gmail.com) wrote: > actually forget about that generate_series() in sub queries, I just > realized that it won't do. > I don't have too much time to analyse and find solution, but > essentially you need to do it li

Re: [GENERAL] Update table with random values from another table

=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= writes: > On Thu, Feb 12, 2009 at 1:10 PM, Rory Campbell-Lange > wrote: >> UPDATE >> users >> SET t_firstname = >> (select firstname from testnames order by random() limit 1), >> t_surname = >> (select lastname from testnames order by random() limit 1) >> WHER

[GENERAL] covering indexes?

Hi all; I saw in the 8.1 release notes that a bug fix for covering index logic was included. Can anyone point me to documentation on how to get PostgreSQL to take advantage of a covering index (i.e. the query will return data only from the index with no need to hit the table) ? Thanks in adva

Re: [GENERAL] encoding of PostgreSQL messages

On Wed, Feb 11, 2009 at 02:20:47PM +0900, Hiroshi Inoue wrote: > Tom Lane wrote: > > Hiroshi Inoue writes: > >> I'm thinking of the following steps in the backend code. > > > >> 1.Set LC_MESSAGES to "C" until the client_encoding is > >> determined. I have tried that but it didn't work out for

Re: [GENERAL] encoding of PostgreSQL messages

On Thu, Feb 12, 2009 at 10:28:38PM +0900, Hiroshi Inoue wrote: > >>> Reflecting on the bigger picture ... I would imagine that the vast > >>> majority of existing applications depend on client_encoding settings > >>> that come from postgresql.conf, ALTER USER SET, ALTER DATABASE SET, or > >>> jus

Re: [GENERAL] Update table with random values from another table

actually forget about that generate_series() in sub queries, I just realized that it won't do. I don't have too much time to analyse and find solution, but essentially you need to do it like in my example. See, that's where normalization would help a lot. Ie, having a separate table for name, and

Re: [GENERAL] Two-phase commmit, plpgsql and plproxy

On Thu, Feb 12, 2009 at 02:17:03AM +0300, Igor Katson wrote: >> >> PostgreSQL does not provide a transaction manager. >> >> When you are dealing with multiple databases, the transaction manager >> needs to make decisions like "this transaction failed on one node, >> therefore we need to roll all th

Re: [GENERAL] Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures

Removed -hackers from the mailing-lists. Just noticed it, and I think this is probably fine on the general list. Gurjeet Singh wrote: > On Thu, Feb 12, 2009 at 6:18 PM, Richard Huxton wrote: >> CREATE OR REPLACE FUNCTION f1(IN a integer, INOUT b integer, OUT c >> integer) RETURNS RECORD AS $$ >

Re: [GENERAL] Update table with random values from another table

On Thu, Feb 12, 2009 at 1:10 PM, Rory Campbell-Lange wrote: >UPDATE >users >SET t_firstname = >(select firstname from testnames order by random() limit > 1), >t_surname = >(select lastname from testnames order by random() limit 1

Re: [GENERAL] encoding of PostgreSQL messages

Tom Lane wrote: > Peter Eisentraut writes: >> Tom Lane wrote: >>> Reflecting on the bigger picture ... I would imagine that the vast >>> majority of existing applications depend on client_encoding settings >>> that come from postgresql.conf, ALTER USER SET, ALTER DATABASE SET, or >>> just the def

Re: [GENERAL] Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures

On Thu, Feb 12, 2009 at 6:18 PM, Richard Huxton wrote: > Gurjeet Singh wrote: > > that is, not passing anything for the OUT or INOUT parameters. This works > > fine for a simple SELECT usage, but does not play well when this function > is > > to be called from another function, (and assuming that

[GENERAL] Update table with random values from another table

I have a test system for which I need to replace actual user's data (in 'users') with anonymised data from another table ('testnames') on postgres 8.3. The tricky aspect is that each row from testnames must be randomised to avoid each row in users being updated with the same value. I've been try

Re: [GENERAL] Update table with random values from another table

On 12/02/09, Rory Campbell-Lange (r...@campbell-lange.net) wrote: > I have a test system for which I need to replace actual user's data (in > 'users') with anonymised data from another table ('testnames') on > postgres 8.3. > > The tricky aspect is that each row from testnames must be randomised

Re: [GENERAL] Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures

Gurjeet Singh wrote: > that is, not passing anything for the OUT or INOUT parameters. This works > fine for a simple SELECT usage, but does not play well when this function is > to be called from another function, (and assuming that it'd break the > application code too, which uses Oracle syntax of

[GENERAL] Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures

(forgot to include general list earlier) Hi All, I am involved in porting Spacewalk 's backend DB schema from Oracle to Postgres. We are almost done with table migration, and are now attempting procedure/function porting. A few things have been sorted out

Re: [GENERAL] Making a result of transaction visible to everyone, saving the ability for a rollback

On Wed, Feb 11, 2009 at 12:58:53PM +0300, Igor Katson wrote: > I want to make the result of a transaction, in which this function is > executed, visible to the outer clients, but to have the ability of > making a rollback in case one of the clients says something went wrong. > Is it possible to

Re: [GENERAL] row constructors

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Never mind. I found an old post. I just needed to do: insert into a1 select (f2).* from a2; I didn't find it the first time I searched because I was looking for row constructors, and the post I found used the term composite value. Sim Sim Zacks wrot

[GENERAL] row constructors

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Is there any way to insert a row constructor into the table of the same type? For example: create table a1(id int, f1 text); create table a2(id int, f2 a1); insert into a1 select f2 from a2; This doesn't work, I am looking for a way to accomplish th

Re: [GENERAL] Multiple postgres.exe On Processes

Dear All, Thanks John R Pierce for replying fruitful text. I wold like to add some text in your reply from PostgreSQL document for further clarification. i.e. Each connection runs one process, plus the 3 master processes for the postmaster, the writer, etc. with two logins, I'd expect to s

Re: [GENERAL] Multiple postgres.exe On Processes

Just adding: > processes. with 102 concurrent logins, 105 processes. the bulk of the > memory and code is shared by these processes, with the exception of things > like per client work_mem buffers which by definition can't be shared as they please be advised that the default-view of TaskManage

[GENERAL] audit table

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The documentation says you cannot use a pseudo type for a table column, so I was wondering if there is any way to handle what I would like to do. I want a trigger on every table that inserts the old row into an audit table (for updates and deletes). I

Re: [GENERAL] Multiple postgres.exe On Processes

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

[GENERAL] Enumerated types and Hibernate

Hi, I'm using Hibernate and would like to map a Java enum to a table column defined as an enumerated type. Is this possible? The following is my SQL: create type day as enum ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'); create table date ( ... day day not null .

Re: [GENERAL] Multiple postgres.exe On Processes

> > I have fear that sufficient increase in number of postgres.exe after one or > two logins on servers may down the speed of the server or even server. Is > there any solution or technique to overcome this overhead. > I did some more searching and foundIs PostgreSQL multi-threaded?

Re: [GENERAL] Multiple postgres.exe On Processes

Hi, > I have fear that sufficient increase in number of postgres.exe after one > or two logins on servers may down the speed of the server or even server. Is > there any solution or technique to overcome this overhead. > Did you test this? What OS are you using? With PostgreSQL every connection

Re: [GENERAL] Multiple postgres.exe On Processes

Abdul Rahman wrote: I have fear that sufficient increase in number of postgres.exe after one or two logins on servers may down the speed of the server or even server. Is there any solution or technique to overcome this overhead. each connection runs one process, plus the 3 master processes for

[GENERAL] How to check if 2 series of data are equal

Hi, I have 14 tables filled with meteorological data, one record per parameter per hour. The id field holds the parameter type (1=temperature, 2=humidity ...) My problem is that for short periods (maybe one week, one month) there are two stations with the same data, I mean the temperature of ta