Re: [GENERAL] Query output into a space delimited/location sensitive file
On Fri, 15 Feb 2008, Ubence Quevedo wrote: What would the command be to have a query result be put into a location/space sensitive file [position 1 through 5 would be one thing where position 6 through 10 would be the next field, 11 through 16 another, etc]? Is this even possible with Postgres? Check out string operators like rpad and maybe even overlay in http://www.postgresql.org/docs/current/static/functions-string.html , the date/number formatting operations in http://www.postgresql.org/docs/current/static/functions-formatting.html , and note that you can combine strings with || in between them. You can do something like this: select rpad(field1,5) || rpad(field2,5) || rpad(field3,5) from table1 To get back a string formatted the way you describe. You may have to tweak that to get the formatting on the fields exactly right. Will be kind of brute force and fragile if anything changes, but it should work if you tinker enough. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Strict-typing benefits/costs
Ken Johanson <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> These examples miss the point, because they'd give the same answer >> whether you think the values are text or integer. ... > Agreed, so should we disallow 7 > '08'? Maybe, but the usability ramifications would be enormous --- you'd also be talking about breaking sale_date > '2008-01-20' and other cases where "leave off the quotes" is not an available option. In practice the current handling of one-known-and-one-unknown-input has not seemed to cause problems for people; it almost always does what they're expecting it to do. It's the cases where the values are of *known and incompatible* types that silently casting to make them match has proven to be dangerous. >>> Numbers and datetime in sql have exactly prescribed standard char >>> representations (even if others dbs don't use them for datetimes). >> >> See the datestyle parameter before you maintain that Postgres >> should assume that. > I agree. Unless the date style is know to always be iso8601, which is > not true owed to datestyle. Unless sql spec allows for it, could this be > an argument for removing the datestyle implict (non-iso8601) feature? I doubt we'll be doing that. It would not fix the problem anyway, since now that I think about it, your argument doesn't hold for numbers either. Consider regression=# select 2.0 / 3.0; ?column? 0.6667 (1 row) regression=# select 10 ^ 14; ?column? - 100 (1 row) regression=# select 10 ^ 15; ?column? -- 1e+15 (1 row) regression=# select 1.230e15; ?column? -- 1230 (1 row) There are a large number of details here that are not prescribed by the SQL standard (much less actually standardized across systems). They make little or no difference in the numeric domain, but they sure do as soon as you start doing textual operations. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Query output into a space delimited/location sensitive file
On Feb 16, 2008 7:19 PM, Ubence Quevedo <[EMAIL PROTECTED]> wrote: > Scott, you are exactly right. I am looking to take various data in > multiple tables and create an output file delimited into specific > sections. I'll look more into the proper usage of select into as well > as the substring/field example you have given below. Then you'll be wanting lpad or rpad to make the fixed widths. It's pretty straight ahead. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Query output into a space delimited/location sensitive file
Scott, you are exactly right. I am looking to take various data in multiple tables and create an output file delimited into specific sections. I'll look more into the proper usage of select into as well as the substring/field example you have given below. Thanx! On Feb 16, 2008, at 04:12 PM, Scott Marlowe wrote: On Feb 16, 2008 5:29 PM, Bruce Momjian <[EMAIL PROTECTED]> wrote: Ubence Quevedo wrote: What would the command be to have a query result be put into a location/space sensitive file [position 1 through 5 would be one thing where position 6 through 10 would be the next field, 11 through 16 another, etc]? Is this even possible with Postgres? Not possible --- many data types are of variable length. I think the OP was just looking at processing fixed with records into pgsql. If that's the case you can make a load table with one text field and select into from it with something like substring(field,1,5) etc... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Query output into a space delimited/location sensitive file
On Feb 16, 2008 5:29 PM, Bruce Momjian <[EMAIL PROTECTED]> wrote: > Ubence Quevedo wrote: > > What would the command be to have a query result be put into a > > location/space sensitive file [position 1 through 5 would be one thing > > where position 6 through 10 would be the next field, 11 through 16 > > another, etc]? Is this even possible with Postgres? > > Not possible --- many data types are of variable length. I think the OP was just looking at processing fixed with records into pgsql. If that's the case you can make a load table with one text field and select into from it with something like substring(field,1,5) etc... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Query output into a space delimited/location sensitive file
Ubence Quevedo wrote: > What would the command be to have a query result be put into a > location/space sensitive file [position 1 through 5 would be one thing > where position 6 through 10 would be the next field, 11 through 16 > another, etc]? Is this even possible with Postgres? Not possible --- many data types are of variable length. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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: [GENERAL] Pains in upgrading to 8.3
Magnus Hagander wrote: > Dave Page wrote: > > On Fri, Feb 15, 2008 at 4:21 PM, Tony Caduto > > <[EMAIL PROTECTED]> wrote: > >> paul rivers wrote: > >> >> > >> > Going from 8.2.4 and 8.2.6 to 8.3.0 has been painless for me. > >> > However, unlike the blogger you cite, I read the directions before, > >> > not after, attempting it. > >> > >> > >> The blogger has a point about pg_dump and restore, it could be much > >> better, for example > >> the backup process could be part of the server core and instead of > >> having a fat client where most of the process is running on the client, > >> a API could be > >> used where the backup is generated on the server and then have options > >> where it could be left on the server or transferred to the clients PC. > > > > Not really an option - the reason it's recommended to use the new > > pg_dump version with the older server when upgrading is to allow the > > dump to be made in the way most compatible with the new server, > > effectively doing some of the upgrade process as part of the dump > > operation. > > For the case of upgrading, it wouldn't work. But there are certainly > other cases where it would help. Say from your central pgadmin console > administering 10 servers from 3 different major release trees :-( > > It can be done with commandline pg_dump, but it means you have to have > three different installs on your management or backup or whatever > machine. Those cases would certainly be easier if you could just call a > backup API on the server that would feed you the data... (yes, there are > ways to do it with ssh tunneling and whatever, but that's yet another > external service that has to be set up and configured) Using the new pg_dump for dumping older versions during an ugprade is just inconvenient and something we should not need to do. At the worst we should have a way for us to upgrade the older version of pg_dump with whatever functionality we need and just tell people to be running the most recent minor release before upgrading. What cases on the past have needed the new pg_dump? -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
On Sat, 16 Feb 2008, Ken Johanson wrote: > Tom Lane wrote: > > > Hm, good point, so really we ought to have a separate casting path for > > numeric types to char(n). However, this section still doesn't offer > > any support for the OP's desire to auto-size the result; it says > > that you get an error if the result doesn't fit in the declared > > length: > > > >> iv) Otherwise, an exception condition is raised: data > >> exception- > >> string data, right truncation. > > > > I don't believe the size is being declared in the OP's (subject line) > example: SELECT CAST(123 AS char) The other part of Tom's quotes still apply: If length is omitted then a length of 1 is implicit. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Are indexes blown?
On 17/02/2008, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > But this is kind of sitting there, hogging the command prompt. Is > there any way I can let it go on in the background? Ouch ... no, that's entirely my fault, wasn't quite awake I guess, and hadn't thought it through completely ... that's not going to give us the desired result... Try this: for z in `seq 1 3600`; do top -b -d 1 -n 1| awk -f top.awk; done | tee topoutput Not sure whether it's going to give us the desire granularity of time... Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 1: 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: [GENERAL] Are indexes blown?
On 17/02/2008, Shashank Tripathi <[EMAIL PROTECTED]> wrote: > On 17/02/2008, Andrej Ricnik-Bay <[EMAIL PROTECTED]> wrote: > > On 17/02/2008, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > > > ~> top -b -d 1 | awk -f top.awk | tee topdata > > > > > > awk: top.awk:24: for(i=8;i > > awk: top.awk:24:^ syntax error > > > awk: top.awk:28: > > > awk: top.awk:28: ^ syntax error > > > awk: top.awk:29: > > > awk: top.awk:29:^ syntax error > > > awk: top.awk:31: > > > awk: top.awk:31: ^ syntax error > > > awk: top.awk:31: } > > > awk: top.awk:31: ^ invalid char ' ' in expression > > > > > > > Any ideas? > > What OS are you on, which version of awk? Mine > > works on most current Linux variants with a gawk > 3.x > > awk -W version > > GNU Awk 3.1.5 > > Copyright (C) 1989, 1991-2005 Free Software Foundation. > > > > Another thought is that maybe when you copy & pasted you > > got some special characters into the script that awk doesn't > > like > > > > > > Thanks. I am on CentOS 4 (Linux) and the awk bit is > > GNU Awk 3.1.3 > Copyright (C) 1989, 1991-2003 Free Software Foundatio > > Do I need to update awk? > My question exactly. Anyway I downloaded the text file that was attached in this thread, and then it works. Must have been some copy/paste problem as suggested. I ran it with this command: top -b -d 1 -n 3600 | awk -f top.awk | tee topdata But this is kind of sitting there, hogging the command prompt. Is there any way I can let it go on in the background? Thanks for the awk tip. Looks like a thing I need to learn! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Are indexes blown?
On 17/02/2008, Andrej Ricnik-Bay <[EMAIL PROTECTED]> wrote:
> On 16/02/2008, Greg Smith <[EMAIL PROTECTED]> wrote:
>
> > top -bc | tee topdata
> >
> > That will save everything to a file called topdata while also letting you
> > watch it scroll by. Not as easy to catch the bad periods that way, the
> > output is going to be a huge data file, but you'll have a log to sort
> > through of everything. Control-C to get out of there when you're bored.
>
> Or pipe through the following awk-script to make the output
> less overwhelming ... save it as top.awk
>
> BEGIN{
> # the sort numerically sorts by memory usage percentage
> # head filters out the top 20 contenders
> command = "LC_ALL=C sort -k 10,10gr|head -20"
> }
> {
> # read all of top into an array
> line[NR]=$0
> last=NR
> }
> END{
> # print the header well-formed
> for(i=1;i<8;i++){
> print line[i]
> }
> # and do the sort & strip of the processes
> for(i=8;i print line[i]|& command
> }
> close(command, "to")
> while ((command |& getline out) > 0)
> print out
> close(command)
> }
>
> Invoke like so
> top -b -d 1 | awk -f top.awk | tee topdata
>
> Instead of the "getting bored Ctrl-C" maybe a "-n 3600" as
> extra parameter to top to get roughly one hours worth of data...
Thanks, but it gives me syntax errors:
~> top -b -d 1 | awk -f top.awk | tee topdata
awk: top.awk:24: for(i=8;i
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
On Sat, 16 Feb 2008, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 12 Feb 2008, Tom Lane wrote: > >> Also, section 6.10 defines an explicit cast to > >> a fixed-length string type as truncating or padding to the target > >> length (LTD): > > > Are you sure that's the correct section to be using? Isn't that 6.10 > > General Rules 5c which is if the source type is a fixed or variable > > length character string? Wouldn't the correct place for an int->char > > conversion be 5a or am I misreading it? > > Hm, good point, so really we ought to have a separate casting path for > numeric types to char(n). However, this section still doesn't offer > any support for the OP's desire to auto-size the result; it says > that you get an error if the result doesn't fit in the declared > length: Yeah. Although, IIRC, it was one of the options he mentioned as being better than getting the first character but not what he really wanted. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL]
Why would I want to purchase a replica from Prestige Replicas? There may be many reasons: a) You want a genuine Rolex / Breitling watch, but the price is too ridiculous b) You want to impress your friends or business clients c) You want to keep your original safe, while using the replica for daily wear and tear The main reason why you should select to purchase from Prestige Replicas is because it is almost impossible to tell the difference between our replicas and the real thing. Our finely crafted replicas are created with the utmost care, and using only state of the art workmanship and finishing. The result is a timepiece that is guaranteed to be meticulous in its finish, and impeccable in style and quality. http://www.nudisooe.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] nntp interface not working?
HI all, The news/NNTP feed to these mailing lists does not seem to be working. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Strict-typing benefits/costs
Tom Lane wrote:
Ken Johanson <[EMAIL PROTECTED]> writes:
select 5<'6' -> true
select 5>'6' -> false
select 15<'60' -> true
select 15>'60' -> false
These examples miss the point, because they'd give the same answer
whether you think the values are text or integer. Consider instead
these cases:
regression=# select 7 > '60'; -- int > int
?column?
--
f
(1 row)
regression=# select '7' > '60'; -- text > text
?column?
--
t
(1 row)
regression=# select 7 > '08'; -- int > int
?column?
--
f
(1 row)
regression=# select '7' > '08'; -- text > text
?column?
--
t
(1 row)
All of a sudden it seems much more important to be clear about
what data type is involved, no?
Agreed, so should we disallow 7 > '08'? Because that is (tell me if you
disagree), much more hazardous than allowing, say TRIM(7) or
POSITION('7' IN 7). Or for non-failfast comparison of two columns of
dissimilar types (say bigint, integer, real, char).
select 'ba'>'ab' -> true
select 'ab'>'ba' -> false
select '0.5'=.5 -> true (is char comparator or numeric to laymen?)
select '7a'<'070' -> true (is char comparator or numeric to laymen?)
select '7a'<70 -> failfast, good.
Numbers and datetime in sql have exactly prescribed standard char
representations (even if others dbs don't use them for datetimes).
See the datestyle parameter before you maintain that Postgres
should assume that.
I agree. Unless the date style is know to always be iso8601, which is
not true owed to datestyle. Unless sql spec allows for it, could this be
an argument for removing the datestyle implict (non-iso8601) feature?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Strict-typing benefits/costs
Ken Johanson <[EMAIL PROTECTED]> writes: > select 5<'6' -> true > select 5>'6' -> false > select 15<'60' -> true > select 15>'60' -> false These examples miss the point, because they'd give the same answer whether you think the values are text or integer. Consider instead these cases: regression=# select 7 > '60'; -- int > int ?column? -- f (1 row) regression=# select '7' > '60'; -- text > text ?column? -- t (1 row) regression=# select 7 > '08'; -- int > int ?column? -- f (1 row) regression=# select '7' > '08'; -- text > text ?column? -- t (1 row) All of a sudden it seems much more important to be clear about what data type is involved, no? > Numbers and datetime in sql have exactly prescribed standard char > representations (even if others dbs don't use them for datetimes). See the datestyle parameter before you maintain that Postgres should assume that. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Analogue to SQL Server UniqueIdentifier?
Hi My porting experiment has encountered the SQL Server UniqueIdentifier problem. I can see one or two suggestions about this have been made over the years but I'd like to try and stay close to the original. So: I'm wondering if I can use a combination of a domain 'hack' for syntatic compatibillity and an externally implemented function to handle generation. More specifically, given a table defined thus: CREATE TABLE jazz( UUID UniqueIdentifier DEFAULT newIdentifier(), rootname VARCHAR(255), data_source VARCHAR(1024), date_created DATETIME DEFAULT GETDATE()) 1. Can I handle the UniqueIdentifier datatype via a domain that aliases UniqueIdentifier to char(X) (for example) ? This seems to work fine for the DATETIME datatype. 2. Implement newIdentifier() in some extension DLL that simply calls CoCreateGUID() ? or does uuid-ossp do this for me? Thx. Jerry.
Re: [GENERAL] Timestamp indexes (why ">" or "between" does not use index?)
"Phoenix Kiula" <[EMAIL PROTECTED]> writes: > orguser=# explain analyze select alias from clientswhere modify_date > > '2008-01-01' ; >QUERY PLAN > -- > Seq Scan on clients (cost=0.00..237043.09 rows=136617 width=10) > (actual time=0.391..4007.188 rows=148225 loops=1) > Filter: (modify_date > '2008-01-01 00:00:00'::timestamp without time zone) > Total runtime: 4539.242 ms > (3 rows) Given the large number of rows being retrieved, I'm not sure that the seqscan choice is wrong. You could force the issue by setting enable_seqscan = off; see what EXPLAIN ANALYZE gives you then. If it does come out significantly faster, this may mean that you need to dial down random_page_cost to make the planner's cost estimates for indexscans be closer to reality on your machine. Be wary however of changing that parameter on the basis of only one test case. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Strict-typing benefits/costs
Michael Glaesemann wrote:
On Feb 15, 2008, at 18:11 , Ken Johanson wrote:
Tom, is it accurate to assume that newer PG versions will further
tighten type-strictness (say, '2008-01-01' presently being comparable
to a datetime)? Also, do you know of any other vendors that are
heading in this direction (removing by default the autocasts)?
'2008-01-01' does not indicate some kind of string: it's just an untyped
literal. Postgres will determine its type in context.
Exactly, it is performing a context based auto conversion, what some
will call a cast.
select 5<'6' -> true
select 5>'6' -> false
select 15<'60' -> true
select 15>'60' -> false
So one can argue that is is convenient, and safe, to perform the same
implicit/auto conversion for many functions which no longer do that. And
that even if looses-typing / auto cast it allows/encourages bad design,
that does not mean that the all designs cases will be bad. Some users
prefer convenience over type safety, and some of those same users *will*
produce error free code.
On the other hand, should we go the extra mile and failfast when
comparing 5 and '6'? No, because there is clearly only one appropriate
conversion path (cast string to numeric) for the context. Or, some might
argue we should not allow that comparison.
select position('5' in 5)
select position('.' in 5.1)
select position('2008' in current_timestamp)
Numbers and datetime in sql have exactly prescribed standard char
representations (even if others dbs don't use them for datetimes). So
one can argue implicit conversion to char IS safe for these types and
any char-consuming functions.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Ken Johanson <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Hm, good point, so really we ought to have a separate casting path for >> numeric types to char(n). However, this section still doesn't offer >> any support for the OP's desire to auto-size the result; it says >> that you get an error if the result doesn't fit in the declared >> length: >> >>> iv) Otherwise, an exception condition is raised: data exception- >>> string data, right truncation. > I don't believe the size is being declared in the OP's (subject line) > example: SELECT CAST(123 AS char) No, because section 6.1 still defines what "char" means, and it says that means "char(1)". regards, tom lane ---(end of broadcast)--- TIP 1: 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: [GENERAL] Timestamp indexes (why ">" or "between" does not use index?)
This table is vacuumed and analyzed every hour, so yes, it's been analyzed recently. These are the EXPLAIN ANALYZE outputs for both the equality condition and the greater than condition: orguser=# explain analyze select alias from clientswhere modify_date = '2008-01-01' ; QUERY PLAN - Index Scan using new_idx_modify_date on clients (cost=0.00..30.23 rows=8 width=10) (actual time=0.136..0.136 rows=0 loops=1) Index Cond: (modify_date = '2008-01-01 00:00:00'::timestamp without time zone) Total runtime: 0.220 ms (3 rows) Time: 2.832 ms orguser=# explain analyze select alias from clientswhere modify_date > '2008-01-01' ; QUERY PLAN -- Seq Scan on clients (cost=0.00..237043.09 rows=136617 width=10) (actual time=0.391..4007.188 rows=148225 loops=1) Filter: (modify_date > '2008-01-01 00:00:00'::timestamp without time zone) Total runtime: 4539.242 ms (3 rows) Time: 4539.850 ms Welcome any thoughts. Thanks! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Strict-typing benefits/costs
Michael Glaesemann <[EMAIL PROTECTED]> writes: > test=# select '2008-02-15' > CURRENT_DATE; > Here, we're comparing against a date type, so Postgres treats > '2008-02-15' as a date. It might be worth pointing out that this is not magic, but an application of the general rule mentioned at step 2a here: http://www.postgresql.org/docs/8.3/static/typeconv-oper.html In any case where Postgres is trying to interpret a binary operator, and one input has a known type while the other is an unknown-type literal constant, the preferred interpretation will be that the constant has the same type as the known-type input. In a case like 'today is ' || '2008-02-16', *both* inputs are initially unknown-type literals. There is a fallback heuristic that prefers to resolve such cases as type text, which is why you get text concatenation rather than a "couldn't resolve operator" error. In no case does Postgres look at the content of an unknown literal to determine its type. '2008-02-16' is not treated differently from 'foobar'. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Tom Lane wrote: Hm, good point, so really we ought to have a separate casting path for numeric types to char(n). However, this section still doesn't offer any support for the OP's desire to auto-size the result; it says that you get an error if the result doesn't fit in the declared length: iv) Otherwise, an exception condition is raised: data exception- string data, right truncation. I don't believe the size is being declared in the OP's (subject line) example: SELECT CAST(123 AS char) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT CAST(123 AS char) -> 1
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Tue, 12 Feb 2008, Tom Lane wrote: >> Also, section 6.10 defines an explicit cast to >> a fixed-length string type as truncating or padding to the target >> length (LTD): > Are you sure that's the correct section to be using? Isn't that 6.10 > General Rules 5c which is if the source type is a fixed or variable > length character string? Wouldn't the correct place for an int->char > conversion be 5a or am I misreading it? Hm, good point, so really we ought to have a separate casting path for numeric types to char(n). However, this section still doesn't offer any support for the OP's desire to auto-size the result; it says that you get an error if the result doesn't fit in the declared length: > iv) Otherwise, an exception condition is raised: data exception- > string data, right truncation. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [GENERAL] Timestamp indexes (why ">" or "between" does not use index?)
Michael Glaesemann <[EMAIL PROTECTED]> writes: > The planner will choose a seq scan if it thinks that it will be > faster than using an index: if based on its statistics it thinks a > large portion of rows will match the criteria, a seq scan may well be > faster than an index scan. > Have you analyzed recently? If you've never analyzed at all, the default assumptions will discourage the planner from using an indexscan for a one-sided inequality condition (like "x > constant"). On the other hand, it usually will use an indexscan for a range inequality (like "x > constant1 and x < constant2", or a BETWEEN construct). If you do have ANALYZE stats then it all depends on what fraction of the column's range is selected by the inequality or range condition. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Timestamp indexes (why ">" or "between" does not use index?)
On Feb 16, 2008, at 9:42 , Phoenix Kiula wrote: The EXPLAIN tells me it needs to do a seq scan. Why is this? How can I make a date/time field index which uses both equality criteria and the greater than/lesser than/between criteria? The planner will choose a seq scan if it thinks that it will be faster than using an index: if based on its statistics it thinks a large portion of rows will match the criteria, a seq scan may well be faster than an index scan. Have you analyzed recently? Showing us the EXPLAIN ANALYZE for your query would be helpful. Have you tried running the EXPLAIN ANALYZE with seq scans disabled? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Strict-typing benefits/costs
On Feb 15, 2008, at 18:11 , Ken Johanson wrote: Tom, is it accurate to assume that newer PG versions will further tighten type-strictness (say, '2008-01-01' presently being comparable to a datetime)? Also, do you know of any other vendors that are heading in this direction (removing by default the autocasts)? '2008-01-01' does not indicate some kind of string: it's just an untyped literal. Postgres will determine its type in context. For example: test=# select 'today is ' || '2008-02-16'; ?column? - today is 2008-02-16 (1 row) || is a string concatenation operator, so '2008-02-16' is treated as a string: it's not cast from date to text: from Postgres' eyes, it never was a date. test=# select '2008-02-15' > CURRENT_DATE; ?column? -- f (1 row) Here, we're comparing against a date type, so Postgres treats '2008-02-15' as a date. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Timestamp indexes (why ">" or "between" does not use index?)
IN the second SQL, I meant this: > WHERE modify_date > '2008-01-01' On 16/02/2008, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > I have a table with an index on a field called "modify_date". > > This works well if I have SQL which ends in > > WHERE modify_date = '2008-01-01' > > But if I try this condition: > > WHERE modify_date = '2008-01-01' > > THis index is not used. The EXPLAIN tells me it needs to do a seq > scan. Why is this? How can I make a date/time field index which uses > both equality criteria and the greater than/lesser than/between > criteria? > > Thanks. > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Timestamp indexes (why ">" or "between" does not use index?)
I have a table with an index on a field called "modify_date". This works well if I have SQL which ends in WHERE modify_date = '2008-01-01' But if I try this condition: WHERE modify_date = '2008-01-01' THis index is not used. The EXPLAIN tells me it needs to do a seq scan. Why is this? How can I make a date/time field index which uses both equality criteria and the greater than/lesser than/between criteria? Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Metadata/ODBC query
I wrote: > What userid are you connecting as under ODBC? Has it got privileges to > any of the schemas? The information_schema views generally hide objects > that you have no privileges for ... In fact, looking closer, it looks like the schemata view only shows you schemas that you are the *owner* of (as is required by spec). So a superuser would see everything but ordinary users might well not see anything. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Metadata/ODBC query
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > If I use pgAdmin and run "SELECT catalog_name FROM > Information_Schema.Schemata" I get data back as expected. > If I connect via ODBC and issue the same query I don't see any data. What userid are you connecting as under ODBC? Has it got privileges to any of the schemas? The information_schema views generally hide objects that you have no privileges for ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Metadata/ODBC query
Hi I'm experimenting with PostgreSQL 8.3.0 on Windows connecting via ODBC. One curiosity so far is this: If I use pgAdmin and run "SELECT catalog_name FROM Information_Schema.Schemata" I get data back as expected. If I connect via ODBC and issue the same query I don't see any data. SQLFetch() simply returns SQL_NO_DATA_FOUND. Both ODBC test apps work fine when accessing the same server/database running queries such as "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE'" I'm using the latest psqlODBC Windows installer from the web site. The driver is set to show system tables ... Any clues? Thanks. Jerry.
