Re: [GENERAL] Can't copy from file into table
[EMAIL PROTECTED] wrote: > > Dear all, > > I am using > copy tableA from '/tmp/A.data' and have the following error: > > Unable to identify an operatior '=' for types 'varchar' > You will have to retype this query using an explicit cast. > > What is "an explicit cast"? Something like this: SELECT '0.80'::float; The '::float' tell Postgresql to cast '0.80' (a string) in to a real number (float). But you can't use an explicit cast in your query. I think the problem comes out of your table definition. > > Best regards, > Boris -- Guillaume Perréal - Stagiaire MIAG Cemagref (URH), Lyon, France Tél: (+33) 4.72.20.87.64
Re: [GENERAL] Statistical Analysis
Steve Heaven wrote: > > At 20:18 24/07/00 -0400, you wrote: > >Sigh, forgot to mention the solution. There's an undocumented function: > > > > * bool oidrand (oid o, int4 X)- > > * takes in an oid and a int4 X, and will return 'true' > > * about 1/X of the time. > > > >typically used like this: > > > >-- select roughly 1/10 of the tuples > >SELECT * FROM onek WHERE oidrand(onek.oid, 10); > > > > It doesnt seem to work as you explain. > For a value of 1 you expect (nearly) all the tuples and two should return > half, but that not what I'm finding. > > galore=> select count(*) from topten where room='HI'; > count > - >14 > (1 row) > > galore=> SELECT * FROM topten WHERE room='HE' and oidrand(topten.oid, 1); > type|data |norder|room |grp > +-+--+--+--- > B |0764552503 | 1|HE| > B |0751327190 | 1|HE| > B |0718144392 | |HE| > B |0500280754 | |HE| > B |0028610091 | 1|HE| > (5 rows) > galore=> SELECT * FROM topten WHERE room='HE' and oidrand(topten.oid, 2); > type|data |norder|room |grp > +-+--+--+--- > B |0764552503 | 1|HE| > B |0751327190 | 1|HE| > B |0718144392 | |HE| > (3 rows) > galore=> SELECT * FROM topten WHERE room='HE' and oidrand(topten.oid, 7); > type|data |norder|room |grp > +-+--+--+--- > B |0751327190 | 1|HE| > B |0718144392 | |HE| > (2 rows) > -- > thorNET - Internet Consultancy, Services & Training > Phone: 01454 854413 > Fax: 01454 854412 > http://www.thornet.co.uk Isn't it because oidrand evals as 'random() < 1/X' ? or maybe 'random() < 1/(X+1)' ? -- Guillaume Perréal - Stagiaire MIAG Cemagref (URH), Lyon, France Tél: (+33) 4.72.20.87.64
[GENERAL] Migrating from 7.0RC1 to 7.0.2 and initdb
Hello. A few days ago, I tried to migrate from v7.0RC1 to 7.0.2. Since major version number doesn't change, I just stopped postmaster, installed the new RPM (I work under Redhat 6.1) and started the new postmaster. I got an error such as 'System catalog version changed, you should use initdb'. Well, I watched at initdb manpage, and then tried: initdb --template This should just rebuild template1 and don't delete anything. But: [Error...] Removing /var/lib/pgsql... Argh!! One restoration later, I used initdb --template --noclean and it appeared that the file pg_control must be deleted before using initdb... And postgresql-backup didn't accept to do a backup because of the version number. Two questions? 1) Why --noclean isn't implicit? 2) Why initdb doesn't delete pg_control automatically (at least ask for deletion) when needed instead of returning an error and burning all the databases ? Regards. Guillaume Perréal - Stagiaire MIAG Cemagref (URH), Lyon, France Tél: (+33) 4.72.20.87.64
Re: [GENERAL] Combining two SELECTs
Tom Lane wrote: > > "Eric Jain" <[EMAIL PROTECTED]> writes: > > Any ideas how the following two statements could be combined into a > > single one? > > > SELECT DISTINCT host, url, id > > INTO TEMP > > FROM log > > WHERE > > host IN (SELECT host FROM robots) > > AND status IN (200, 304); > > > SELECT host, COUNT(*) AS hits > > FROM TEMP > > GROUP BY host > > ORDER BY hits DESC; > > Offhand I do not think you can do this in one "simple" SQL query, > because the SQL query semantics require that GROUP BY grouping occurs > before DISTINCT processing, whereas you want the other order. > > > For now, the temp table seems like a good workaround. > And splitting some complex queries in simpler ones (using temp tables) can increase performance, depending on the query. Regards, Guillaume Perréal - Stagiaire MIAG Cemagref (URH), Lyon, France Tél: (+33) 4.72.20.87.64
[GENERAL] Getting number of days in a month
To obtain the number of days in a month, I wrote this function:
CREATE FUNCTION dayCountOfMonth(datetime) RETURNS float AS
' DECLARE
theDate ALIAS FOR $1;
monthStart date;
monthEnddate;
BEGIN
monthStart := DATE_TRUNC(''month'', theDate);
monthEnd := monthStart + ''1 month''::timespan - ''1 day''::timespan;
RETURN DATE_PART(''doy'', monthEnd) - DATE_PART(''doy'', monthStart) +
1;
END;
' LANGUAGE 'PL/pgSQL';
It seems to work, except with the month of October (10).
dayCountOfMonth('1997-10-1') => 30
dayCountOfMonth('1998-10-1') => 30
dayCountOfMonth('1999-10-1') => 31
dayCountOfMonth('2000-10-1') => 30
dayCountOfMonth('2001-10-1') => 30
dayCountOfMonth('2002-10-1') => 30
dayCountOfMonth('2003-10-1') => 30
dayCountOfMonth('2004-10-1') => 31
Just one question: WHY??
(Note: no trouble with February)
Is there a function that give the number of days of a month?
Thanks,
Guillaume Perréal - Stagiaire MIAG
Cemagref (URH), Lyon, France
Tél: (+33) 4.72.20.87.64
[GENERAL] PostgreSQL versus Oracle
Hello, Does anybody have ever heard about PostgreSQL vs Oracle performance tests and bibliography? Thanks Guillaume Perréal Stagiaire MIAG Cemagref - URH Tél: 04.72.20.87.64
[GENERAL] Splitting one big table into smaller ones
I've got a big table of measures like that: CREATE TABLE measures ( stationCode varchar(8), when datetime, value float, quality char, PRIMARY KEY (stationCode, when) ); Each station (identified by stationCode) could have up to 10**6 measures. So I want to split it into smaller tables to increase perfomance : CREATE TABLE measures<1st stationCode> ( when datetime, value float, quality char, PRIMARY KEY (when) ); CREATE TABLE measures<2nd stationCode> ( ... and so on. Is there a way to handle that using SQL and PL/pgSQL languages ? Thanks, Guillaume Perréal Stagiaire MIAG Cemagref - URH France
