Re: [SQL] [GENERAL] SQL query problem (many table in FROM statement and many LEFT JOIN's)
"Marek Lewczuk" <[EMAIL PROTECTED]> writes: > ... It also working fine. The question is, why my first query isn't > working: > SELECT > _CON.con_id, > _MOD.mod_ty, > _VER.version, > _YEA.year, > _CON.dri_id, > _CON.man_cod, > _ENG.eng_pow > FROM > db_data.mda_mod _MOD, > db_data.mda_mod_con _CON, > db_data.mda_mak_eng _ENG, > db_data.set_mda_fue _FUE > LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id > LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id > WHERE > _MOD.mod_id = '283' AND > _CON.mod_id = _MOD.mod_id AND > _CON.psd <= NOW() AND > _CON.ped > NOW() AND > _ENG.eng_id = _CON.eng_id AND > _ENG.eng_fue = _FUE.fue_id The reason that works in MySQL and fails in Postgres is that MySQL isn't compliant with the SQL standard. The standard says that the above FROM clause means that _FUE is left-joined to _VER, then that result is left-joined to _YEA, then the _MOD, _CON, and _ENG tables are joined to that result (in no particular order). You get the error because the LEFT JOIN ON clauses refer to _CON which is not part of what they are joining. I believe that MySQL interprets the above statement as "join the tables in the order listed in the FROM clause", that is they join _MOD/_CON/_ENG/_FUE, then left-join _VER to that result, etc. This is a hangover from days when they didn't actually have a query planner. Unfortunately, it's not SQL, it's only something that looks like SQL. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] sql performance and cache
"Chris Faulkner" <[EMAIL PROTECTED]> writes: > I am seeing this message in my logs. > "bt_fixroot: not valid old root page" That's not good. I'd suggest reindexing that index. 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] [postgres] Foreign Key
Hallo,
> > In der Tabelle status befinden sich Datensätze. Die Tabelle logins
> ist
> > leer. Nun versuche ich folgenden Befehl:
> >
> > ALTER TABLE "public"."login " ADD CONSTRAINT "test" FOREIGN KEY
> > ("status__id") REFERENCES "public"."status"("id")
> > ON DELETE RESTRICT
> > ON UPDATE RESTRICT
> > NOT DEFERRABLE;
> >
> > Jetzt bekomme ich immer die Fehlermeldung:
> > ERROR: Query was cancelled.
>
> hmmm, die Fehlermeldung ist seltsam und tritt normalerweise bei
> Abbruch zum
> Beispiel bei ^C im Frontent auf.
Ich tippe mal, das der PostgreSQL Manager da selber ein Timeout macht.
Habe es auch mal mit phpPgAdmin versucht. Der läuft sich Tod.
> Bei ALTER TABLE kann Postgres noch nicht alle Features; je älter die
> Version, desto weniger ;-)
Ich verwende die Version 7.3.4. Das sollte doch wohl reichen, oder?
> Evtl. sind auch die Anführungszeichen ein Problem.
Habe es auch ohne versucht, das gleiche Problem.
Ich werde es jetzt mal mit psql, direkt auf dem Server testen.
Vielleicht geht es ja dann.
Grüße,
Stefan
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Escaping the $1 parameter in stored procedures
I'm running Postgres 7.3.2 in Redhat 9.0. I'm trying to execute a function below defined as a stored procedure ALTER TABLE tms_schedule DROP CONSTRAINT "$1"; However, postgres thinks the "$1" is a parameter value. How do I tell postgres to treat it as a literal $1? TIA, Robert ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] removing precision from timestamp (microseconds) ..
>From the docs, if you do: traffic=# select CURRENT_TIMESTAMP(0); timestamptz 2003-10-13 11:04:09-03 (1 row) the 0 reduces the precision of the time to get rid of the microseconds ... is there a way of having this done by default on, if anything, a per connection basis? For instance, I want to be get rid of the microseconds from: traffic=# select now(); now --- 2003-10-13 11:02:20.837124-03 (1 row) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] HELP ME
Where can i find a tutorial on PL/PGSQL?.Help me by listing some sites to guide me in this context. Thanks. __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] about postgre SQL download
how can we download the postgre SQL database Thank you Jagdish RautWeb DeveloperIllusion Technologies--Illusion Technologies: A complete Design and Development company.IndiaPh: 0091-253-2319670Cell: 0091-9823196176Web: www.illusiongraphix.com & www.illusiontechnologies.comEmail: [EMAIL PROTECTED], [EMAIL PROTECTED]IRC: MSN: [EMAIL PROTECTED]--
Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
Hello!
Can anyone help me to use connectby() with my structure?
I cannot change the name of tables. It is a import!
--
Thomas Wegner
"Thomas Wegner" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
> Hello,
>
> i have a table like this:
>
> CREATE TABLE "public"."WINUSER" (
> "ID_WINUSER" INTEGER NOT NULL,
> "STATUS" INTEGER NOT NULL,
> "CUSTOMERID" VARCHAR(8) NOT NULL,
> "CUSTOMERPW" VARCHAR(100) NOT NULL,
> "EMAIL" VARCHAR(100) NOT NULL,
> "REF_ID_WINUSER" INTEGER,
> PRIMARY KEY("ID_WINUSER"),
> ) WITH OIDS;
>
> and will get the tree from this to fields:
>
> "ID_WINUSER" INTEGER NOT NULL,
> "REF_ID_WINUSER" INTEGER,
>
> i write this sql:
>
> SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
> '"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
>
> and get this error:
>
> ERROR: Query-specified return tuple not valid for Connectby: wrong number
> of columns
>
> How is the correct use of connectby() for me?
> --
> Thomas Wegner
>
> "Joe Conway" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
> news:[EMAIL PROTECTED]
> > Merrall, Graeme wrote:
> > > Am I right in thinking that recursive procedures and procs returning
row
> > > sets would allow us to better emulate this behaviour? As anyone looked
> > > at it yet?
> > >
> >
> > See connectby() in contrib/tablefunc. Someone was working on SQL99
> > recursive queries but it didn't get done for 7.4 -- perhaps it will be
> > in 7.5. In the meantime, connectby() is in 7.3 and might work for you.
> >
> > HTH,
> >
> > Joe
> >
> >
> > ---(end of broadcast)---
> > TIP 8: explain analyze is your friend
> >
>
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
Hello,
i have a table like this:
CREATE TABLE "public"."WINUSER" (
"ID_WINUSER" INTEGER NOT NULL,
"STATUS" INTEGER NOT NULL,
"CUSTOMERID" VARCHAR(8) NOT NULL,
"CUSTOMERPW" VARCHAR(100) NOT NULL,
"EMAIL" VARCHAR(100) NOT NULL,
"REF_ID_WINUSER" INTEGER,
PRIMARY KEY("ID_WINUSER"),
) WITH OIDS;
and will get the tree from this to fields:
"ID_WINUSER" INTEGER NOT NULL,
"REF_ID_WINUSER" INTEGER,
i write this sql:
SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
'"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
and get this error:
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
How is the correct use of connectby() for me?
--
Thomas Wegner
"Joe Conway" <[EMAIL PROTECTED]> schrieb im Newsbeitrag
news:[EMAIL PROTECTED]
> Merrall, Graeme wrote:
> > Am I right in thinking that recursive procedures and procs returning row
> > sets would allow us to better emulate this behaviour? As anyone looked
> > at it yet?
> >
>
> See connectby() in contrib/tablefunc. Someone was working on SQL99
> recursive queries but it didn't get done for 7.4 -- perhaps it will be
> in 7.5. In the meantime, connectby() is in 7.3 and might work for you.
>
> HTH,
>
> Joe
>
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] smart(er) column aliases
(B (B (BHi all, (B (BTo take a simple example here is what I (Bwanted to do: (B (B select 1 as one, 2 as two, (Bone + two as three; (B (Bbut it doesn't work. I've checked it out (Bthough, and I found that I can do this: (B (B select one, two, one + two (Bfrom (select 1 as one, 2 as two) as a; (B (Bwhich is acceptable, I guess. (B (BI'm using 7.1 (I think) and will update to (B7.3 soon but I guess that the first query still doesn't work. (B (BI'd just like to suggest to the gurus (Bdeveloping this DB that it would be realy cool if the first query was possible. (BFor me it's just about 2 things: simplicity and efficiency. The queries I'm (Bworking on at the moment are a little too complex for my liking and the "1" in (Bthis example is typically a call to a very involved function whose result is (Bre-used in a number of calculations. Obviously, I only want to so it once, and (Band I want to keep the main query as simple as possible as things are already (Bout of hand... (B (Bcheers all. (B (BIain
[SQL] security definer function
Hi I have two functions: A) function defined with "SECURITY DEFINER" B) function defined with "SECURITY INVOKER" Function A calls function B. How is the function b called - with rights of definer of function A, or rather with rights of caller of function A ? Regards, Tomasz Myrta ---(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] PG equivalent to Sybase varbinary
Thanks for the response. I'll look into re-running the ddl using the bytea datatype the next time I perform the setup I'm using. (I used a Sybase SQL Anywhere db in the meantime to get me back on track with the task at hand). Thanks again, Bill "Richard Huxton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On Monday 06 October 2003 15:40, Bill Pfeiffer wrote: > > Anybody know what the Postgresql equivalent to a Sybase varbinary data type > > is? I have a package that provides ddl to store a 40 byte/char? varbinary > > column in a table and it is failing against postrgresql. > > Sounds like "bytea" to me - or have you rejected that? > > -- > Richard Huxton > Archonet Ltd > > ---(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 > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] select
roberto wrote:
Dear friends,
I have this table
table work{
day date,
hour integer,
}
select * from work;
date | text
---
1-1-20031
1-1-20031
2-1-20035
3-1-200310
5-1-200315
how can i obtain this?
date |text
---
1-1-20032
2-1-20035
3-1-200310
4-1-2003null
5-1-200315
6-1-2003null
First , you need a sequence of days. Just create
a function like this:
CREATE OR REPLACE FUNCTION dateseq ( DATE , DATE )
RETURNS SETOF DATE
LANGUAGE 'plpgsql'
AS '
DECLARE
bdate ALIAS FOR $1 ;
edate ALIAS FOR $2 ;
cdate DATE ;
BEGIN
cdate := bdate;
WHILE cdate <= edate LOOP
RETURN NEXT cdate ;
cdate := CAST ( cdate + interval ''1 day'' AS date );
END LOOP;
RETURN;
END;
';
The function is like a table/view , where the fist function argument
is the start date , the second argument is the end date.
Now try :
SELECT ds.day, sum(w.hour)
FROM dateseq( '2003-1-1', '2003-1-6' ) AS ds (day)
LEFT JOIN work w ON ds.day=w.day
GROUP BY ds.day;
Regards, Janko
--
Janko Richter
---(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
[SQL] get diagnostics not supported by ecpg?
Hi all,
It seems that get diagnostics is not supported by ecpg in postgresql
7.3, I always get the following error when trying to use it:
ERROR: parse error at or near "diagnostics"
For example, the following code does not compile:
#include
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char msg[8191];
int msg_len=0;
EXEC SQL END DECLARE SECTION;
EXEC SQL connect to 'test';
EXEC SQL get diagnostics :msg_len=ROW_COUNT;
fprintf(stderr, "SQL: %d\n", msg_len);
EXEC SQL disconnect current;
return 0;
}
same if I use
EXEC SQL get diagnostics exception 1
:msg=MESSAGE_TEXT,
:msg_len=MESSAGE_LENGTH;
What am I doing wrong? Or it's just not supported by ecpg in 7.3?
Thanks in advance,
Slava
__
www.newmail.ru -- всегда что-то новое.
---(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
[SQL] select
Dear friends,
I have this table
table work{
day date,
hour integer,
}
select * from work;
date | text
---
1-1-20031
1-1-20031
2-1-20035
3-1-200310
5-1-200315
how can i obtain this?
date |text
---
1-1-20032
2-1-20035
3-1-200310
4-1-2003null
5-1-200315
6-1-2003null
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] about postgre SQL download
On Thursday 09 October 2003 11:09, Illusiontechnologies wrote: > how can we download the postgre SQL database Start from http://www.postgresql.org/ Click the "download" link near the top, and choose a country near to you. There are source downloads available for a variety of *nix compatible systems, and RPMs for RedHat Linux. Packages are available for Debian from the usual route. If you want to run on Windows, you should search for the cygwin project, or there are a number of commercial packages available. Installation instructions are available in the source distribution and in the documentation on the website. You might also find the techdocs site useful if you are porting from another database: http://techdocs.postgresql.org/ Sorry I can't be more specific, but you didn't say what platform you wanted to run on. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
Thomas Wegner wrote:
SELECT "ID_WINUSER" FROM connectby('"WINUSER"', '"ID_WINUSER"',
'"REF_ID_WINUSER"', 4, 0, '~') AS t("ID_WINUSER" integer)
and get this error:
ERROR: Query-specified return tuple not valid for Connectby: wrong number
of columns
Please see the documentation (README.tablefunc). You need to properly
specify the column definitions in the FROM clause, i.e. (untested):
SELECT "ID_WINUSER" FROM
connectby('"WINUSER"', '"ID_WINUSER"','"REF_ID_WINUSER"', 4, 0, '~')
AS t("ID_WINUSER" integer,
"REF_ID_WINUSER" integer,
level integer,
branch text);
HTH,
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] get diagnostics not supported by ecpg?
Slava Gorski writes: > What am I doing wrong? Or it's just not supported by ecpg in 7.3? Indeed. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] How to determine the current user
I'm running Pg v7.3.4 I have a function where I need to determine the current user. But since the function was created with option "SECURITY DEFINER" (it has to be this way) current_user returns the name of creator, rather than current user. Is there a way in such function find out the real current user? Thanks, Mike. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to determine the current user
> I'm running Pg v7.3.4 > I have a function where I need to determine the current user. > But since the function was created with option "SECURITY DEFINER" > (it has to be this way) current_user returns the name of creator, > rather than current user. > > Is there a way in such function find out the real current user? CURRENT_USER should be right. SESSION_USER is the username that connected to the DB. Look at table 6-27, session information functions: http://www.postgresql.org/docs/7.3/static/functions-misc.html -sc -- Sean Chittenden ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [postgres] Foreign Key
Sorry für die letzte Mail. Habe bei der falschen Mail auf Antwort
geklickt.
Nochmals Sorry,
Stefan
> -Original Message-
> From: Stefan Sturm [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 13, 2003 12:48 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [SQL] [postgres] Foreign Key
>
> Hallo,
>
> > > In der Tabelle status befinden sich Datensätze. Die Tabelle logins
> > ist
> > > leer. Nun versuche ich folgenden Befehl:
> > >
> > > ALTER TABLE "public"."login " ADD CONSTRAINT "test" FOREIGN KEY
> > > ("status__id") REFERENCES "public"."status"("id")
> > > ON DELETE RESTRICT
> > > ON UPDATE RESTRICT
> > > NOT DEFERRABLE;
> > >
> > > Jetzt bekomme ich immer die Fehlermeldung:
> > > ERROR: Query was cancelled.
> >
> > hmmm, die Fehlermeldung ist seltsam und tritt normalerweise bei
> > Abbruch zum
> > Beispiel bei ^C im Frontent auf.
>
> Ich tippe mal, das der PostgreSQL Manager da selber ein Timeout macht.
> Habe es auch mal mit phpPgAdmin versucht. Der läuft sich Tod.
>
>
> > Bei ALTER TABLE kann Postgres noch nicht alle Features; je älter die
> > Version, desto weniger ;-)
>
> Ich verwende die Version 7.3.4. Das sollte doch wohl reichen, oder?
>
> > Evtl. sind auch die Anführungszeichen ein Problem.
>
> Habe es auch ohne versucht, das gleiche Problem.
>
> Ich werde es jetzt mal mit psql, direkt auf dem Server testen.
> Vielleicht geht es ja dann.
>
> Grüße,
> Stefan
>
>
> Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
> Sie eine E-Mail an:
> [EMAIL PROTECTED]
>
>
>
> Die Nutzung von Yahoo! Groups ist Bestandteil von
> http://de.docs.yahoo.com/info/utos.html
>
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
---(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] [postgres] Antwort von Microsoft auf Mail hier!
Guten morgen miteinander > aber auf > jeden Fall gehen die Mails an [EMAIL PROTECTED] > seit Freitag auch an die (englischsprachige) Liste pgsql-sql bei > postgresql.org, was eindeutig fragwuerdig ist. Das habe ich gesehen, ich kann mir aber keinen Reim drauf machen, wie das moeglich ist. > Vielleicht waere es eine Idee, diese Liste bei postgresql.org > hosten zu lassen? Das laesst sich relativ unbuerokratisch > einrichten, auch andere nicht-englischsprachige Listen gibt es dort. Vielleicht waere es eine Idee, die Liste auf postgres.de zu hosten ;-) Ralf wuerde die Liste einrichten und auf seinen Servern hosten. Kai, wie denkst Du darueber? Herzliche Gruesse Conni Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [postgres] Foreign Key
Ich melde mich doch. Ich sitze hier halt uns arbeite. Ich arbeite aber
derzeit (außer der Formel1 Auswertung) nur an meinen Sachen. Und ich
mache das schon ganz schön Fortschritte.
Michelle hat gerade hier geklingelt. Sie kommt heute um ca. 19 Uhr zu
uns.
> -Original Message-
> From: Stefan Sturm [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 13, 2003 12:48 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [SQL] [postgres] Foreign Key
>
> Hallo,
>
> > > In der Tabelle status befinden sich Datensätze. Die Tabelle logins
> > ist
> > > leer. Nun versuche ich folgenden Befehl:
> > >
> > > ALTER TABLE "public"."login " ADD CONSTRAINT "test" FOREIGN KEY
> > > ("status__id") REFERENCES "public"."status"("id")
> > > ON DELETE RESTRICT
> > > ON UPDATE RESTRICT
> > > NOT DEFERRABLE;
> > >
> > > Jetzt bekomme ich immer die Fehlermeldung:
> > > ERROR: Query was cancelled.
> >
> > hmmm, die Fehlermeldung ist seltsam und tritt normalerweise bei
> > Abbruch zum
> > Beispiel bei ^C im Frontent auf.
>
> Ich tippe mal, das der PostgreSQL Manager da selber ein Timeout macht.
> Habe es auch mal mit phpPgAdmin versucht. Der läuft sich Tod.
>
>
> > Bei ALTER TABLE kann Postgres noch nicht alle Features; je älter die
> > Version, desto weniger ;-)
>
> Ich verwende die Version 7.3.4. Das sollte doch wohl reichen, oder?
>
> > Evtl. sind auch die Anführungszeichen ein Problem.
>
> Habe es auch ohne versucht, das gleiche Problem.
>
> Ich werde es jetzt mal mit psql, direkt auf dem Server testen.
> Vielleicht geht es ja dann.
>
> Grüße,
> Stefan
>
>
> Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
> Sie eine E-Mail an:
> [EMAIL PROTECTED]
>
>
>
> Die Nutzung von Yahoo! Groups ist Bestandteil von
> http://de.docs.yahoo.com/info/utos.html
>
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] [postgres] Antwort von Microsoft auf Mail hier!
Ja, sone Mail habe ich auch mal bekommen.. da ist wohl was zwischen MS und Yahoo im Busch ... Gruß, Danny Am Mon, 13 Oct 2003 14:18:41 +0200 hat Alvar Freude <[EMAIL PROTECTED]> geschrieben: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hallo allerseits, > > wer hat denn Zugriff auf die Subscriber-Liste dieser Liste? > > Eben bekam ich auf meine Mail oben eine Antwort von *Microsoft*. Der > Inhalt > unten. > > > Also, entweder ist da jemand offiziell eingetragen, oder hat einen Deal > mit > Yahoogroups, dass dies unsichtbar geschieht. Wäre ja eine Möglichkeit, um > potentielle Konkurrenz zu beobachten: alles was "postgres" im Namen hat > geht an "[EMAIL PROTECTED]" oder so ... > > > Oder jemand hat scherzeshalber MS hier eingetragen. > > > Sehr ominös. > > > > Ciao > Alvar > > > - -- Forwarded Message -- > Date: Montag, Oktober 13, 2003 04:11:51 -0700 > From: [EMAIL PROTECTED] > To: [EMAIL PROTECTED] > Subject: RE: RE: [SQL] [postgres] Foreign Key - Remove > > MSDN Auto-Response: Please do not reply back to this e-mail as this is > not > a monitored mailbox. > > We really do want your submission. To enable us to provide you with a > more > timely response, please submit your questions or feedback via the MSDN > Contact Us web form at > http://register.microsoft.com/contactus30/contactus.asp?domain=msdn > Please > select an option from the list that best aligns with the subject of your > mail. > > **Please note: If you have a Microsoft Product or technical (how to) > support question, you > will get a faster resolution and be better served by using the options > available and detailed in the Microsoft Support Site at: > http://support.microsoft.com Links to the knowledge base, support phone > numbers, online support and > submitting feedback about our products options are available from within > the left hand side navigation. > _ Q&A: > Q1: Why have you switched from e-mail to a Web form? A1: The MSDN Contact > Us Web form enables us to provide you with a quicker > response by eliminating the huge volumes of spam that our e-mail > addresses > attract. We recognize that legitimate customers like yourself have used > this e-mail address in the past to contact Microsoft. This auto reply is > sent to inform you of these changes and your options. Please bookmark the > MSDN Contact Us link above and remove the MSDN mail-to e-mail address you > used from your address book. > > Q2: When did Microsoft.com Contact Us implement this new process? A2: > April 4th 2003. > > Q3: Why did I get this auto-response if I am responding to an existing > MSDN > Contact Us inquiry that has this return e-mail address? A3: Take a look > at your sent messages folder in your e-mail application to > review the subject line of the e-mail you sent this alias. Does it have > an > MSDN Contact Us inquiry number in the format of CSTx x xID, where x is an > eight-digit number? - - Yes: Unfortunately we can only process inquiry > less than three months > old. Please create a new inquiry using the MSDN Contact Us link above. - - > > > No: If you removed or edited the subject line of the e-mail, please > resend your e-mail to this address with the original subject line. It > should have the original Microsoft.com Contact Us inquiry number. > > Q4: How do I know that someone will actually read my feedback? A4: Our > MSDN Contact Us customer support representatives read every piece > of feedback that we receive via the MSDN Contact Us Web form. If you ask > us > a question and provide us with your e-mail address, we will send you a > response. If you send us feedback, we will route it to the right people > at > Microsoft. > > Q5: Why did your Web site have a link to this e-mail address? A5: We > replaced e-mail links on our Web site with links to the MSDN Contact > Us Web form. If you found one that we missed, please visit the MSDN > Contact > Us Web form (link above) and let us know. We really appreciate your > assistance. > > - -- End Forwarded Message -- > > > > > > - -- ** Alvar C.H. Freude -- http://alvar.a-blast.org/ > ** ** ODEM.org-Tour: http://tour.odem.org/ > ** > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.2.3 (FreeBSD) > > iD8DBQE/ipghOndlH63J86wRApSKAKCL5VPsmxmXI4sy9VCPtq4Xjstk+ACgyP7u > DkE5quj0AJ1HMJu6xtSRLss= > =rNQ5 > -END PGP SIGNATURE- > > > Yahoo! Groups Sponsor > > Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie > eine E-Mail an: > [EMAIL PROTECTED] > > > > Die Nutzung von Yahoo! Groups ist Bestandteil der Allgemeinen > Geschäftsbedingungen von Yahoo!. -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---
[SQL] Inquiry From Form [pgsql]
Hello, I\'m looking for info about Join conditions in Postgre\'. I\'m head developer of ikonboard and we\'re trying to optimise it, with joins but how does this work in postgre can i download a manual anywhere? Thanks in advanced, Bram Wijnands Head IB Developer [EMAIL PROTECTED] ---(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] [postgres] Foreign Key
Hallo zusammen,
ich würde gerne folgenden Foreign Key in meine DB einbauen. Leider
bekomme ich immer eine Fehlermeldung.
Hier die Situation:
Ich habe 2 Tabellen:
status:
ID Numeric
Name Text
logins:
ID Numeric
Name Text
Status__id Numeric
In der Tabelle status befinden sich Datensätze. Die Tabelle logins ist
leer. Nun versuche ich folgenden Befehl:
ALTER TABLE "public"."login " ADD CONSTRAINT "test" FOREIGN KEY
("status__id") REFERENCES "public"."status"("id")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE;
Jetzt bekomme ich immer die Fehlermeldung:
ERROR: Query was cancelled.
Und das war auch schon alles.
Kann mir da einer Helfen?
Danke und Grüße,
Stefan Sturm
PS: Ich mache das ganze mit dem Programm EMS PostgreSQL Manager Version
1.8.0.1
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] [postgres] Foreign Key
Hallo, > Hallo, > > > Ich tippe mal, das der PostgreSQL Manager da selber ein Timeout > macht. > > Habe es auch mal mit phpPgAdmin versucht. Der läuft sich Tod. > > Ist die Tabelle groß? Da kann es natürlich sein, dass da ein ganzer > Haufen > an Updates gemacht werden muss, und dass gerade dies in diesem Falle > überhaupt nicht optimiert ist. > > Zumindest muss ja ein Test auf die References-Tabelle gemacht werden. > Sind > da entsprechende Indexe drauf? Evtl. hilft da auch das Clustern. Die Tabelle ist sehr klein. Max. 20 Datensätze. Ist halt noch in der Entwicklung. Direkt auf der Datenbank mit psql hat es auf Anhieb geklappt. Hat keine Sekunde gedauert. Jetzt frage ich mich, warum? Grüße, Stefan Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] [postgres] Copy Timestamp NULL
Hi Leute, Ich habe vor einiger Zeit eine spezielle Frage gestellt, die nicht ganz beantwortet wurde. Also es geht darum, dass ich in eine Postgres Tabelle per Copy Daten laden möchte. Bei einer Spalte handelt es sich um Timestamp NULL ... In der Source-Datei befinden sich in dieser Spalte ISO Timestamp Werte und NULL - Werte . (Nicht jede Zeile besitzt einen Timestamp-wert) Die Source-Datei sieht prinzipiell so aus : 1|Hallo|17.0|1999-01-23 14:30:08.456234|usr01 2|Test|18.5||usr02 Die Spalte 4 ist somit vom Typ Timestamp. Allerdings kann ich diese Source-Datei nicht in die Postgres Tabelle per Copy laden, da es an den NULL - Stellen zum Fehler kommt : Bad timestamp external representation '' Wenn also eine "Lücke" gefunden wird, interpretiert Copy diese "Lücke" nicht als Null-Wert und beschwert sich über das fehlerhafte Timestamp Format. Es geht ebenfalls nicht, wenn ich an der entsprechenden Stelle NULL schreibe. Was muss ich also angeben, damit ich solch eine Datei laden kann ? Viele Grüße, Danny -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Die Nutzung von Yahoo! Groups ist Bestandteil von http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [PERFORM] sql performance and cache
I have two very similar queries which I need to execute. They both have exactly the same from / where conditions. When I execute the first, it takes about 16 seconds. The second is executed almost immediately after, it takes 13 seconds. In short, I'd like to know why the query result isn't being cached and any ideas on how to improve the execution. OK - so I could execute the query once, and get the maximum size of the array and the result set in one. I know what I am doing is less than optimal but I had expected the query results to be cached. So the second execution would be very quick. So why aren't they ? I have increased my cache size - shared_buffers is 2000 and I have doubled the default max_fsm... settings (although I am not sure what they do). sort_mem is 8192. PostgreSQL does not have, and has never had a query cache - so nothing you do is going to make that second query faster. Perhaps you are confusing it with the MySQL query cache? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [SQL] sql performance and cache
Nested Loop (cost=0.00..147.11 rows=1 width=148) (actual time=84.00..12323.00 rows=67 loops=1) The planner estimate doesn't seem to match reality in that particular step. Are you sure you've run: ANALYZE oscar_node; ANALYZE oscar_point; And you could even run VACUUM FULL on them just to make sure. Does that make any difference? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [PERFORM] sql performance and cache
On Sat, 11 Oct 2003, Christopher Kings-Lynne wrote: > > > I have two very similar queries which I need to execute. They both have > > exactly the same from / where conditions. When I execute the first, it takes > > about 16 seconds. The second is executed almost immediately after, it takes > > 13 seconds. In short, I'd like to know why the query result isn't being > > cached and any ideas on how to improve the execution. > > > > > OK - so I could execute the query once, and get the maximum size of the > > array and the result set in one. I know what I am doing is less than optimal > > but I had expected the query results to be cached. So the second execution > > would be very quick. So why aren't they ? I have increased my cache size - > > shared_buffers is 2000 and I have doubled the default max_fsm... settings > > (although I am not sure what they do). sort_mem is 8192. > > PostgreSQL does not have, and has never had a query cache - so nothing > you do is going to make that second query faster. > > Perhaps you are confusing it with the MySQL query cache? > > Chris > Is there plan on developing one (query cache)? Thanks Wei ---(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] [PERFORM] sql performance and cache
On Tue, 14 Oct 2003, Wei Weng wrote: > On Sat, 11 Oct 2003, Christopher Kings-Lynne wrote: > > > > > > I have two very similar queries which I need to execute. They both have > > > exactly the same from / where conditions. When I execute the first, it takes > > > about 16 seconds. The second is executed almost immediately after, it takes > > > 13 seconds. In short, I'd like to know why the query result isn't being > > > cached and any ideas on how to improve the execution. > > > > > > > > > OK - so I could execute the query once, and get the maximum size of the > > > array and the result set in one. I know what I am doing is less than optimal > > > but I had expected the query results to be cached. So the second execution > > > would be very quick. So why aren't they ? I have increased my cache size - > > > shared_buffers is 2000 and I have doubled the default max_fsm... settings > > > (although I am not sure what they do). sort_mem is 8192. > > > > PostgreSQL does not have, and has never had a query cache - so nothing > > you do is going to make that second query faster. > > > > Perhaps you are confusing it with the MySQL query cache? > > > > Chris > > > Is there plan on developing one (query cache)? Not really, Postgresql's design makes it a bit of a non-winner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [PERFORM] sql performance and cache
> > Perhaps you are confusing it with the MySQL query cache? > Is there plan on developing one (query cache)? For the most part, prepared queries and cursors give you a greater advantage due to their versatility -- both of which we do have. In the cases where an actual cache is useful, the client application could do it just as easily or temp tables can be used. I suspect it would be implemented more as a caching proxy than as an actual part of PostgreSQL, should someone really want this feature. signature.asc Description: This is a digitally signed message part
[SQL] Alias-Error
Hi, I am running a query with alias (a self join) against version 7.3.4 on Suse Linux 7.3 and on FreeBSD (v5?). It runs fine on Linux, but produces an error on FreeBSD: "unknown alias C2". And btw: the query runs on every other DB I have tried... The Query is something like this: SELECT DISTINCT C2.cmc_mchap, C2.cmc_sort FROM sis_cmca, sis_cmca C2 WHERE cm_status != 'U' AND sis_cmca.cmc_name='INTERN2000' AND C2.cmc_name='INTERN2000' AND sis_cmca.cmc_mchap=C2.cmc_mchap What causes this error? Workarounds? Jost ---(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] Create View
Dear all, I Have This table Table Billing: id trx_date trx_time depart payment_method billing_amount amount_paid balance creator 1 10/09/2003 21:55:02 Resto Visa 13.800,00 10.000,00 3.800,00 middink Table Payment id r trx_date trx_timedescriptions payment_method amount creator 1 10/08/2003 18:17:40 Payment Cash 2.000,00 middink I would like to create "View " from above table with result look like: trx_date trx_time descriptions payment_method debet credit balance creator 10/09/2003 21:55:02 Resto Billing 13.800,00 Paid: 10.000,00 Visa 3.800,00 3.800,00 middink 10/08/2003 18:17:40Payment Cash 2.000,00 1.800,00 middink How can I create View like above?
[SQL] How can I produce the following desired result?
How can I produce the following desired result? goodid totalnum operationdate storehistoryid 132 35.000 09-28-2003 66 135 11.500 09-28-2003 61 132 35.000 09-27-2003 60 135 11.000 09-28-2003 59 135 12.000 09-28-2003 58 134 100.000 09-28-2003 57 134 112.000 09-27-2003 56 131 0.000 09-26-2003 54 131 33.000 09-26-2003 51 131 -2.000 09-26-2003 50 3 550.000 09-26-2003 49 3 52.000 09-26-2003 48 132 35.000 09-27-2003 42 3 124.000 09-25-2003 41 131 59.000 09-25-2003 40 132 57.000 09-26-2003 39 131 2.000 09-24-2003 38 3 2.000 09-20-2003 23 result: goodid totalnum operationdate storehistoryid 132 35.000 09-28-2003 66 135 11.500 09-28-2003 61 134 100.000 09-28-2003 57131 0.000 09-26-2003 54 3 550.000 09-26-2003 49 I need to select rows which storehistoryid is max as the same goodid . Thanks in advance aicean Mailto:[EMAIL PROTECTED]
Re: [SQL] How can I produce the following desired result?
On Tue, 2003-10-14 at 22:09, aicean wrote: > How can I produce the following desired result? I'm not sure I understand the problem, but you might want to try a subselect in the FROM. SELECT FROM table JOIN (SELECT goodid FROM table WHERE ) AS tab USING (goodid) WHERE signature.asc Description: This is a digitally signed message part
Re: [SQL] How can I produce the following desired result?
Thanks very much. It seems can solve my problem.For select the max "storehistoryid",I have to compare each row's storehistoryid. Any other advice? Thanks again. aicean Mailto:[EMAIL PROTECTED] - Original Message - From: "Rod Taylor" <[EMAIL PROTECTED]> To: "aicean" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, October 15, 2003 10:27 AM Subject: Re: [SQL] How can I produce the following desired result? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Sorting problem
hi all, i am using postgres 7.3.2 .i am converitng a mssql database to postgres. now i am facing a strange problem. sorting based on a varchar field is not working as expected. the non alphanumeric characters are not sorting based on the ascii value of them. i have the following table structure.. create table accounts ( AcNo varchar (10), Name varchar(100), balance numeric(19,4) ) when i used the query select * from accounts order by acno. the result is not correct suppose that the acno field contains values '###1' ,'###2' ,'##10' , '#100' the sort order in postgres is '###1' '##10' '#100' '###2' But i want the result as follows '###1' '###2' '##10' '#100' that means the ascii value of # should be considered for sorting.. what is the problem. is it the behaviour of postgres. do i need to change any configuration. i am using all default configurations or is it a bug...? the problem actually is of < & > operators for varchar. in a simple comparison select '###2' < '##10' returns false but i need true. is there any solution exist. even if i replaced # with any non alphanumeric character the result is same.. pls help jinu jose Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: [SQL] Sorting problem
On Tue, 14 Oct 2003, George A.J wrote: > hi all, > i am using postgres 7.3.2 .i am converitng a mssql database to postgres. > now i am facing a strange problem. sorting based on a varchar field is not working > as expected. the non alphanumeric characters are not sorting based on the ascii > value of them. What OS and locale are you using? If it's not "C", you're probably getting bit by the fact that many natural language collations (such as en_US for example) don't consider most of the symbols except as tie breakers when sorting. Unfortunately, to change the locale you need to run initdb again with the appropriate locale, something like: LANG="C" initdb -D /path/to/dataspace ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
