[SQL] substr
i noticed that substr behaves a bit different in pgsql than perl ie select foo from table where substr(foo,1,1) = 'X'; initially i thought it should be substr(foo,0,1) just wondering on the reasoning for this offset ? Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose
Re: [SQL] substr
Jeff MacDonald <[EMAIL PROTECTED]> writes: > i noticed that substr behaves a bit different in pgsql than perl > ie select foo from table where substr(foo,1,1) = 'X'; > just wondering on the reasoning for this offset ? Larry Wall and the SQL92 authors didn't talk to each other... We are implementing SQL around here, not Perl, so we have to follow the SQL spec's definition of substr(). regards, tom lane
Re: [SQL] substr
> Jeff MacDonald <[EMAIL PROTECTED]> writes: > > i noticed that substr behaves a bit different in pgsql than perl > > ie select foo from table where substr(foo,1,1) = 'X'; > > > just wondering on the reasoning for this offset ? > > Larry Wall and the SQL92 authors didn't talk to each other... bastards :) now we gotta decide who to kill, larry or the sql folk. i'd say the sql folk, 0 is just more inututive for computer counting. but that's just me.. :) anyway thanks. > > We are implementing SQL around here, not Perl, so we have to follow > the SQL spec's definition of substr(). > > regards, tom lane > Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose
[SQL] COUNT
Hi, How do I get a row count, like "Select [COUNT] from Table" ?? Regards, Craig May Enth Dimension http://www.enthdimension.com.au
Re: [SQL] COUNT
Select count(*) from Table At 04:58 AM 10/20/00 +, Craig May wrote: >Hi, > >How do I get a row count, like "Select [COUNT] from Table" ?? > >Regards, >Craig May > >Enth Dimension >http://www.enthdimension.com.au > >
Re: [SQL] COUNT
Hello, You will need to do "SELECT count(attribute) FROM table;" or SELECT count(table.attribute);" At 04:58 AM 10/20/00 +, Craig May wrote: >Hi, > >How do I get a row count, like "Select [COUNT] from Table" ?? > >Regards, >Craig May > >Enth Dimension >http://www.enthdimension.com.au
Re: [SQL] COUNT
Hi, there, You want how many rows in your table??? select count(*) from yourtablename; Craig May wrote: > Hi, > > How do I get a row count, like "Select [COUNT] from Table" ?? > > Regards, > Craig May > > Enth Dimension > http://www.enthdimension.com.au -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] COUNT
> "Brian" == Brian C Doyle <[EMAIL PROTECTED]> writes: Brian> Hello, You will need to do "SELECT count(attribute) FROM Brian> table;" or SELECT count(table.attribute);" You need to watch this: acspg=# create table tst ( acspg(# a integer acspg(# ); CREATE acspg=# insert into tst values (0); INSERT 333481 1 acspg=# insert into tst values (null); INSERT 333482 1 acspg=# insert into tst values (2); INSERT 333483 1 acspg=# select count(*) from tst; count --- 3 (1 row) acspg=# select count(a) from tst; count --- 2 (1 row) acspg=# select count(1) from tst; count --- 3 (1 row) acspg=# If you use the attribut name, null values won't be counted. -Dan
Re: [SQL] COUNT
On Fri, 20 Oct 2000, Craig May wrote: > How do I get a row count, like "Select [COUNT] from Table" ?? SELECT COUNT(*) FROM Brett W. McCoy http://www.chapelperilous.net --- Man's reach must exceed his grasp, for why else the heavens?
[SQL] "too big" transactions
How does PostgreSQL handles a "too big" transaction? By that I mean a transaction which, after a certain point, there will be no way to roll back. On PgSQL, maybe that only happens when the disk fills. Is there a configurable "size" limit for a single transaction? In addition, what happens if the disk fills up? Postgres is able to roll back, right? I'm assuming you can prevent the disk from actually filling up (and crashing the whole server) by turning on quotas for the postgres super user, so that only pgsql would complain. Please correct me if I'm wrong.
[SQL] what is the best way to set-up keywords in tables and Queries ?
what is the best way to set-up keywords in tables and Queries? please post examples! I am worried about a field for each keyword less
[SQL] Re: [HACKERS] Conditional query plans.
"Michael Richards" <[EMAIL PROTECTED]> writes: > The second and probably less optimal plan would be to create a hash > of these 25 answers and do a sequential scan on users updating rows > where id is found in that hash. Given the presence of the "materialize" nodes, I don't think this query plan is quite as nonoptimal as you think, especially for ~25 rows out of the subplan. It's a linear search over a 25-entry table for each outer row, but so what? With hundreds or thousands of rows out of the subquery, it'd be nice to have a smarter table lookup method, agreed, but here it hardly matters. Something that's been on the todo list for a long time is to try to convert WHERE foo IN (SELECT ...) queries into some kind of join, instead of a subselect. With that approach we'd be able to use merge or hash strategies to match up inner and outer rows, which'd work a lot better when there are large numbers of rows involved. It might actually happen for 7.2... regards, tom lane
