Antw: [SQL] aliases break my query
Joseph Shraibman wrote: > These two queries are exactly alike. The first one uses aliases except > for the order by. The second uses aliases also for the order by. The > third uses whole names. The third has the behavior I want. > Someone please tell me what I am doing wrong. I don't want to have to > use whole names for my query. > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > ta.a) from tablea ta, tableb tb order by tablea.a; > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > ta.a) from tablea ta, tableb tb order by ta.a; >playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz) > where tableb.yy = tablea.a) order by tablea.a; I think what you actually want is an outer join: SELECT tablea.a, tablea.b, tablea.c, Count(tableb.zz) AS zzcount FROM tablea LEFT JOIN tableb ON tablea.a = tableb.yy GROUP BY tablea.a, tablea.b, tablea.c; that is not supported in postgreSQL 7.0, but can be simulated with SELECT tablea.a, tablea.b, tablea.c, Count(tableb.zz) AS zzcount FROM tablea INNER JOIN tableb ON tablea.a = tableb.yy GROUP BY tablea.a, tablea.b, tablea.c UNION SELECT tablea.a, tablea.b, tablea.c, 0 AS zzcount FROM tablea WHERE tablea.a NOT IN (SELECT yy FROM tableb); Gerhard
[SQL] A Question
Hi, my name is sherril .I have the following Question My one feild in the database is Date/time I want to find a range of records which exists between two dates in the same date field in the database Also tell me how to retreive all records from database where field which is date time is null I am working on ASP and backend as ms-ACCESS My email Address is [EMAIL PROTECTED] Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
[SQL] PG/DBI: 'NOTICE: UserAbortTransactionBlock and not in in-progress state'
Hi, I am using PG 6.5.1 and DBI. My Perl programs always print to stderr: "NOTICE: UserAbortTransactionBlock and not in in-progress state" or "NOTICE: EndTransactionBlock and not inprogress/abort state" I tried to do -> finish and -> commit after each query, but the messages still appear. Any hints? Thanks, Markus
Re: [SQL] A Question
On Fri, 26 May 2000, Sherril Mathew wrote: > My one feild in the database is Date/time I want to find a range of > records which exists between two dates in the same date field in the > database SELECT * FROM table WHERE datetime_field between '1999-03-15' and '2000-10-23'; > Also tell me how to retreive all records from database where field which is > date time is null I am working on ASP and backend as ms-ACCESS SELECT * FROM table WHERE datetime_field is null; -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [SQL] aliases break my query
> > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > ta.a) from tablea ta, tableb tb order by tablea.a; > [ produces 80 rows ] > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > ta.a) from tablea ta, tableb tb order by ta.a; > [ produces 20 rows ] > > playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz) > > where tableb.yy = tablea.a) order by tablea.a; > [ produces 4 rows ] Once again, I think that we *really* need to discuss whether implicit range table entries in SELECT are a good idea. We invariably get a question like this every week and invariably the answer is "if you give a table an alias you *must* refer to it by that alias". (I'm sure Tom has this reply automated by now.) I claim the only thing that buys is confusion for very little convenience at the other end. Stop the madness! :) -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [SQL] PG/DBI: 'NOTICE: UserAbortTransactionBlock and not in in-progress state'
Markus Wagner wrote: > > Hi, > > I am using PG 6.5.1 and DBI. My Perl programs always print to stderr: > > "NOTICE: UserAbortTransactionBlock and not in in-progress state" or > "NOTICE: EndTransactionBlock and not inprogress/abort state" > > I tried to do -> finish and -> commit after each > query, but the messages still appear. > > Any hints? I think that happens if the backend aborted (elogged) with an ERROR message and you did not start a new transaction. ERROR kills the current transaction altogether in 6.5.*, IIRC. Regards, Ed Loehr
[SQL] date() indexing error..
Using PostgreSQL 7.0 I'm Doing ... ipa2=# CREATE INDEX "app_stat_month" on applicant_stats(date(month)); ERROR: SQL-language function not supported in this context. ipa2=# \d applicant_stats Table "applicant_stats" Attribute | Type| Modifier ---+---+-- app_id| integer | month | date | user_id | integer | view_time | timestamp | Index: app_id When I try and do the above there is one record in the database. ipa2=# select * from applicant_stats; app_id | month| user_id | view_time ++-+-- 56941 | 05-26-2000 | 291 | Fri May 26 09:19:41 2000 EDT (1 row) If I erase it I get the same error, only when I try and insert data into the table.. I'm doing these kind of indexes on several tables, this is the only one I'm having this occur.. Any ideas? Thanks! -Mitch
Re: [SQL] date() indexing error..
"Mitch Vincent" <[EMAIL PROTECTED]> writes: > Using PostgreSQL 7.0 I'm Doing ... > ipa2=# CREATE INDEX "app_stat_month" on applicant_stats(date(month)); > ERROR: SQL-language function not supported in this context. > ipa2=# \d applicant_stats > Table "applicant_stats" > Attribute | Type| Modifier > ---+---+-- > app_id| integer | > month | date | > user_id | integer | > view_time | timestamp | > Index: app_id Since month is already a 'date', applying date() to it is pretty pointless. I don't really know why we have date(date) defined at all, but we do, and it's defined as an SQL-language function --- which indexes don't support at the moment. Thus the error message. There are a fair number of no-op functions in pg_proc that are defined as SQL "SELECT $1". We could probably afford to lose 'em all, and let the parser treat these things as implicit type conversions instead... regards, tom lane
[SQL] POSTGRESQL and PERL?
Hi-
I'm a newbie at postgresql and was working on
sorting by category. What my question is, how do you
sort by category when using a variable. For instance,
you can sort by name in perl by doing:
$sqh = $dbh->prepare(q{select name from company order
by name;});
$sqh->execute();
but what if you have a variable set like:
$sort_selection = "name";
How do you sort by the variable?
For instance you cannot do:
$sqh = $dbh->prepare(q{select name from company order
by ?;});
$sqh->execute($sort_selection);
OR
$sqh = $dbh->prepare(q{select name from company order
by $sort_selection;});
$sqh->execute();
If anyone could help, I would greatly appreciate it.
Thanks again!
__
Do You Yahoo!?
Kick off your party with Yahoo! Invites.
http://invites.yahoo.com/
RE: [SQL] POSTGRESQL and PERL?
Peter my guess is you can just substitute the variable name in for the
actual sortfield name..
select * from company order by $field;
That's why variables BEGIN with denotation characters in Perl rather than
ending with them (like in basic)..
Anyway, let me know if that works... and which module are you using to hit
PostgreSQL
thax,
Ted
_
Ted Wallingford
Manager of Information Technology
Independence Excavating, Inc.
Precision Environmental Co.
Independence Communications, Inc.
www.indexc.com
> -Original Message-
> From: Peter Landis [mailto:[EMAIL PROTECTED]]
> Sent: Friday, May 26, 2000 11:58 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: [SQL] POSTGRESQL and PERL?
>
>
>
> Hi-
>I'm a newbie at postgresql and was working on
> sorting by category. What my question is, how do you
> sort by category when using a variable. For instance,
> you can sort by name in perl by doing:
>
> $sqh = $dbh->prepare(q{select name from company order
> by name;});
> $sqh->execute();
>
> but what if you have a variable set like:
> $sort_selection = "name";
>
> How do you sort by the variable?
>
> For instance you cannot do:
> $sqh = $dbh->prepare(q{select name from company order
> by ?;});
> $sqh->execute($sort_selection);
>
> OR
>
> $sqh = $dbh->prepare(q{select name from company order
> by $sort_selection;});
> $sqh->execute();
>
> If anyone could help, I would greatly appreciate it.
>
> Thanks again!
>
>
>
> __
> Do You Yahoo!?
> Kick off your party with Yahoo! Invites.
> http://invites.yahoo.com/
>
Wallingford, Ted.vcf
Re: [SQL] PG/DBI: 'NOTICE: UserAbortTransactionBlock and not in in-progress state'
Markus Wagner <[EMAIL PROTECTED]> writes: > I am using PG 6.5.1 and DBI. My Perl programs always print to stderr: > "NOTICE: UserAbortTransactionBlock and not in in-progress state" or > "NOTICE: EndTransactionBlock and not inprogress/abort state" > I tried to do -> finish and -> commit after each > query, but the messages still appear. I think you're doing too much rather than too little. Those notices suggest that you're sending out an extra ABORT or COMMIT command after the transaction's already been aborted/committed. IIRC, DBI has an autocommit feature that sends out COMMITs for you. If you have that turned on, *and* you are explicitly committing, then that's probably the cause of the extra commits. Get rid of one or the other. regards, tom lane
Re: [SQL] aliases break my query
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Once again, I think that we *really* need to discuss whether implicit > range table entries in SELECT are a good idea. We invariably get a > question like this every week and invariably the answer is "if you give a > table an alias you *must* refer to it by that alias". (I'm sure Tom has > this reply automated by now.) No, this one was actually a pretty original way of shooting oneself in the foot ;-). I thought the interesting point was the confusion between whether variables in the inner select were supposed to be local to the inner select or references to the outer select. I'm not sure getting rid of implicit rangetable entries would've helped prevent that. > I claim the only thing that buys is > confusion for very little convenience at the other end. > > Stop the madness! :) I doubt that it's worth breaking a lot of existing applications for. At one time Bruce had made some patches to emit informative notice messages about implicit FROM entries, but that got turned off again for reasons that I forget... regards, tom lane
Re: [SQL] POSTGRESQL and PERL?
Peter Landis wrote:
>
> but what if you have a variable set like:
> $sort_selection = "name";
>
> How do you sort by the variable?
>
> For instance you cannot do...
> $sqh = $dbh->prepare(q{select name from company order
> by $sort_selection;});
Why not? The query string is created before prepare is called...
Regards,
Ed Loehr
Re: [SQL] POSTGRESQL and PERL?
- Original Message -
From: Ed Loehr <[EMAIL PROTECTED]>
To: Peter Landis <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, May 26, 2000 5:35 PM
Subject: Re: [SQL] POSTGRESQL and PERL?
> Peter Landis wrote:
> >
> > but what if you have a variable set like:
> > $sort_selection = "name";
> >
> > How do you sort by the variable?
> >
> > For instance you cannot do...
> > $sqh = $dbh->prepare(q{select name from company order
> > by $sort_selection;});
>
> Why not? The query string is created before prepare is called...
>
I think you need qq{select ...} to indicate double-quoting "" or the
variable won't be substituted.
- Richard Huxton
Re: [SQL] aliases break my query
Peter Eisentraut wrote: > > > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > > ta.a) from tablea ta, tableb tb order by tablea.a; > > [ produces 80 rows ] > > > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > > ta.a) from tablea ta, tableb tb order by ta.a; > > [ produces 20 rows ] > > > > playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz) > > > where tableb.yy = tablea.a) order by tablea.a; > > [ produces 4 rows ] > > Once again, I think that we *really* need to discuss whether implicit > range table entries in SELECT are a good idea. What is an "implicit range table entry"? We invariably get a > question like this every week and invariably the answer is "if you give a > table an alias you *must* refer to it by that alias". Hey, I *did* do that in the second query, and that still produced extra results. I tried putting the aliases in the inner select too but that didn't help. In fact the inner select always is 4 in that case. Unless I only alias tableb in the inner query, and let it get the definition of tablea from the outer query. (I'm sure Tom has > this reply automated by now.) I claim the only thing that buys is > confusion for very little convenience at the other end. > > Stop the madness! :) > > -- > Peter Eisentraut Sernanders väg 10:115 > [EMAIL PROTECTED] 75262 Uppsala > http://yi.org/peter-e/Sweden
Re: [SQL] aliases break my query
Tom Lane wrote: > > Joseph Shraibman <[EMAIL PROTECTED]> writes: > > These two queries are exactly alike. The first one uses aliases except > > for the order by. The second uses aliases also for the order by. The > > third uses whole names. The third has the behavior I want. > > I think you are confusing yourself by leaving out FROM clauses. > In particular, with no FROM for the inner SELECT it's not real clear > what should happen there. I can tell you what *is* happening, but > who's to say if it's right or wrong? > Well I assumed that the aliases would be inerited from the outer query. > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > ta.a) from tablea ta, tableb tb order by tablea.a; > [ produces 80 rows ] > > > playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) where tb.yy = > > ta.a) from tablea ta, tableb tb order by ta.a; > [ produces 20 rows ] > > The difference between these two is that by explicitly specifying > "tablea" in the order-by clause, you've created a three-way join, > as if you had written "from tablea ta, tableb tb, tablea tablea". > Once you write an alias in a from-clause entry, you must refer to > that from-clause entry by its alias, not by its true table name. I guess I made the mistake of assuming that SQL is logical. I don't know what I was thinking. ;) > > Meanwhile, what of the inner select? It has no FROM clause *and* > no valid table names. The only way to interpret the names in it > is as references to the outer select. So, on any given iteration > of the outer select, the inner select collapses to constants. > It looks like "SELECT count(constant1) WHERE constant2 = constant3" > and so you get either 0 or 1 depending on whether tb.yy and ta.a > from the outer scan are different or equal. OK that sorta makes sense to be. What I want is the behavior I got with the third query (below). I want the values in table a, and then a count of how many entries in tableb have the yy field of tableb that matches that entry in tablea's a field. playpen=> select ta.a,ta.b,ta.c, (select count (tb.zz) from tableb tb where tb.yy = ta.a) from tablea ta, tableb tb group by ta.a, ta.b, ta.c order by ta.a; a|b|c|?column? -+-+-+ 1|2| | 0 2|3|4| 2 3|4|5| 1 4|5|4| 1 (4 rows) ... which is what I want. Thanks. > > > playpen=> select tablea.a,tablea.b,tablea.c, (select count (tableb.zz) > > where tableb.yy = tablea.a) order by tablea.a; > [ produces 4 rows ] > > Here the outer select is not a join at all --- it mentions only tablea, > so you are going to get one output for each tablea row. The inner > select looks like "select count (zz) FROM tableb WHERE yy = ", > so you get an actual scan of tableb for each iteration of the outer > scan. > > It's not very clear from these examples what you actually wanted to have > happen, but I suggest that you will have better luck if you specify > explicit FROM lists in both the inner and outer selects, and be careful > that each variable you use clearly refers to exactly one of the > FROM-list entries. > > regards, tom lane
[SQL] New Type
Hi all,
I'm trying to create a type password; the goal is to have a table like:
CREATE TABLE test (
username varchar,
pass passwd);
insert into test values ('me','secret');
and have "secret" being automagicly crypted.
What I want is to mimic the PASSWORD function of mysql but much better,
not having to call a function.
I just can't figure how to write the xx_crypt(opaque) returns opaque
function.
Any help available???
TIA
--
Olivier PRENANT Tel:+33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou +33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)
[SQL] is limit a reserved keyword?
I have a piece of commercial software that does a query like so: CREATE TABLE chargelimits (id int NOT NULL, user_id int NOT NULL, limit float NOT NULL ) but the postgresql 7.0 gives a parse error near "limit". Is limit a reserved keyword? If so, then it is not in the list of reserved keywords. Is there a way that I can make this query run without altering it? Thanks Gabriel Russell [EMAIL PROTECTED]
Re: [SQL] is limit a reserved keyword?
Gabriel Russell <[EMAIL PROTECTED]> writes: > Is limit a reserved keyword? Yes. > If so, then it is not in the list of reserved keywords. Documentation oversight, evidently :-( regards, tom lane
