Re: [SQL] Making NULL entries appear first when ORDER BY
Well, for the docs to list every possible conditional-statement for an order by clause would just about include them all, so be all the more confusing. Sub queries, IN, aggregate functions, aliases.. the list goes on and on. I'd say that knowledge (that most conditionals can be used in an order-by or group-by) should be implicit once a person has a basic understanding of the language. Thomas F.O'Connell wrote: How would one know from the reference material that it is possible to include IS NOT NULL in an ORDER BY clause? Similarly, other than the FAQ, I've never been able to tell from the SELECT documentation why ORDER BY random() works. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 16, 2005, at 3:39 AM, Andreas Joseph Krogh wrote: On Wednesday 16 February 2005 04:47, Bruno Wolff III wrote: Now, as you see, touples with NULL in the "start_time"-field appear "after" the others. I would like to make all entries where start_time IS NULL apear *before* all the others. Any idea how to achieve this? SELECT start_date, start_time, end_time, title FROM onp_crm_activity_log WHERE start_date IS NOT NULL ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC; This assumes you want the NULL start times first within a particular date. Otherwise change the order in the ORDER BY clause. Thanks! This si, IMO, the cleanest solution as it doesn't involve any COALESCE. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] definative way to place secs from epoc into timestamp
and the fact that I want the same value from the data base that I put into it. "same" in which sense? The same absolute point in time? Or the same point on a calendar? Obviously if the timezone doesn't change, then the two are equivalent; but which one is your application actually looking for? (If your app is using Unix epoch times, then it's looking only at the absolute time and not the calendar time...) Unix time stamps, short (int) or long res, are always supposed to GMT based, as far as I know - I never seen anything different, except maybe in homebrew software. So it should be both calendar and P.I.T. And you wouldn't need the TZ storage if the date-number and number-> translation itself takes the TZ arg so that it can localize the Human String for you. Ken ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] definative way to place secs from epoc into timestamp
Unix time stamps, short (int) or long res, are always supposed to GMT based, as far as I know - I never seen anything different, except maybe in homebrew software. So it should be both calendar and P.I.T. And you wouldn't need the TZ storage if the date-number and number-> translation itself takes the TZ arg so that it can localize the Human String for you. Ken In fact, I would suggest that if there is any function, or field, that takes a TZ-less argument (*especially* if it takes only the number), that its name should be made to contain 'UTC' so clearly disambiguate whats its intended use for (since zone-less values/fields SHOULD be regarded as UTC) - Otherwise, some users will place epoch numbers adjusted for the their timezone in the field (and even with daylight saving offsets applies, somewhat amusingly but wrong). So then two different users are using the exact same datatype for inconsistent types. (just a concern for interoperability, user awareness, and when an employee comes on-board and has to deal with bad legacy) ---(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
Re: [HACKERS] [SQL] Case Preservation disregarding case
Chuck McDevitt wrote:
At Teradata, we certainly interpreted the spec to allow case-preserving,
but case-insensitive, identifiers.
Users really liked it that way
My 2 thoughts:
1: It seems like this behavior of case sensitive-or-not-identifiers
could/should be a config option -- either globally for the server,
database, or at the connection/session level. Other databases *do*
support this type of granular config of misc SQL behavior -- its
essential for shared hosting environments. Without it some users just
*cant* make the switch. Quoting all an app's identifiers -- or renaming
camel-case to underscored -- show stopper.
2: Even though the spec state different (that identifiers should be
treated as case sensitive or else folded), precedence seems to have
changed that:
a) The databases that enforce this rule are fewer, I believe. IMO SQL
is now considered even higher than a 4GL language because it use is so
widespread - laymen need to use it.
b) the fact that different identifiers of mixed case could even coexist
in a table-columns or 'AS' or 'JOIN' -- really represents a more of an
err'd design -- and a case-insen option would detect this (unlike the
current behavior). It would throw an immediate ("fail fast") runtime
exception. So I think it's *safer*. (If tbl.rowId and tbl.rowid both
exist in a table or AS identifiers, something bad _will_ happen when
someone takes over a project)
If there were a new default behavior (or just config option added), my
vote would, without a doubt, be for case-insens (yet case preserving)
mode... even when using quoting identifiers. This case sen. behavior
doesn't seem to offer any advantage/safety.
ken
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Case Preservation disregarding case
Martijn van Oosterhout wrote: On Sat, Dec 02, 2006 at 12:41:37AM -0700, Ken Johanson wrote: 1: It seems like this behavior of case sensitive-or-not-identifiers could/should be a config option -- either globally for the server, database, or at the connection/session level. Other databases *do* support this type of granular config of misc SQL behavior -- its essential for shared hosting environments. Without it some users just *cant* make the switch. Quoting all an app's identifiers -- or renaming camel-case to underscored -- show stopper. What about option 3: use camelcase without underscares and don't quote. Then you get case-insensetivity and it's still readable. You're obviously talking about an app which isn't quoting identifiers, so I'm not sure what the actual problem is. Have a nice day, Yes, I do routinely use non-quoted identifiers. The problem is, that they are case-folded (to lower in PG's case), so my camel-case does not match. For the query to work I MUST quote identifiers hat have camel-case. SELECT pers."firstName", pers.lastname, ... Has your experience with PG been different? If so I presume you have have found a config that allows?: SELECT pers.firstName, pers.lastname, Ken ---(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: [HACKERS] [SQL] Case Preservation disregarding case
Dennis Bjorklund wrote: Ken Johanson skrev: Has your experience with PG been different? If so I presume you have have found a config that allows?: SELECT pers.firstName, pers.lastname, As long as you don't create the columns using quotes you can use that kind of names. For example CREATE TABLE foo (BAR int); INSERT INTO foo VALUES (42); SELECT BaR, bar, BAR, "bar" FROM foo; But using "Bar" wont work. /Dennis That definitely makes sense. If one *wants* to enforce case, they should create the identifier with quotes. Although, since I'm using pgAdmin (III) to design tables in this case, or creating the tables through JDBC (a convenient way to copy tables and data from another datasource) (using the copy-paste gesture), I believe those tools both *are* quoting identifiers that have camel case. And that their behavior can't be overridden. So though I might personally be comfortable with DDL commands to re-define my existing quoted columns (as created by JDBC and pgAdmin), other users may not be. And having to recreate and issue the DDL to use un-quoted col names will be tedious in my case since I have ~20 tables to import. So my vote would remain for having a config-option to ignore case, even on quoted identifiers.. Ken ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Case Preservation disregarding case
Dennis Bjorklund wrote: So my vote would remain for having a config-option to ignore case, even on quoted identifiers.. And my vote is to not have such an option. But I'm not the one who decide so don't worry about what I think :-) I would like to have an option to upper case the identifiers instead of lower casing them as pg do. The sql standard say that they should be upper cased. But as far as I know there are no plan at the moment to add such an option either. Some time in the future I expect it to be implemented only because it's the standard. /Dennis Ya, I thought about that - just designing with all-lower case column names. But then the column names are harder to read, unless I change over to using underscore naming. I personally cant see any benefit at all to having the DB fold case (up or down), or enforcing case sensitive identifiers. In fact I believe that if here were an option to support case insensitive (at least on a session-level) that would be largely innocuous --- beneficial even since the optimizer would now detect that someone created both a rowId, rowid, and that indicates a possible design error (the whole fail-fast notion). In one way I think that even allowing creation of a separate "rowid" and "rowId" sort of violates set theory in a 4+ GL language... a "name" in its most abstract (human) sense doesn't (shouldn't) consider the case of its characters. Only what the characters are. A rowid is also a rowId (or ROWID). Who really intentionally mixes them? (only 3-4GL *programmers* who consider all-caps to represent constants in my experience). thoughts, Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [SQL] Case Preservation disregarding case
Martijn van Oosterhout wrote: On Sat, Dec 02, 2006 at 11:08:51AM -0700, Ken Johanson wrote: And my vote is to not have such an option. But I'm not the one who decide so don't worry about what I think :-) I would like to have an option to upper case the identifiers instead of lower casing them as pg do. The sql standard say that they should be upper cased. But as far as I know there are no plan at the moment to add such an option either. Some time in the future I expect it to be implemented only because it's the standard. I think it's unlikely to happen anytime soon. The primary reason being that then you can no longer use indexes to search the catalog. Which I'm pretty sure this is no the case - other DBs do allow index search on columns/identifiers regardless of their case. Probably the typical strategy is to use a case-insensitive hashtable (fold case for the keys before generating the hash). If its the actual data that you're referring to in index searches, that would be a separate topic I think. means it has to be fixed at initdb time. And it would break a large number of client apps, for no particularly good reason. I take a different opinion on this: -*If* the option to turn on case-insenetive behavior were selectable at the DB or session level, the existing apps could continue to use the case sensitve mode and be completely unaffected. -IMO turning it on *globally* would only break apps that are built case-sensitivly *and* refer to identifiers of the same name (but mixed case) *and* are written for PG (since PG *had* been by and large non-portable until recently.. the addition of standard string quoting for example) -It would *enhance* people's ability to "bring in" apps from so many other DBs which don't treat identifiers as case sensitive. More of a compatibility boon than loss. Thats is a particularly good reason to me (since I'm the one who has to issue DDL on all my camelCase columns and recode my identifiers). Since the way identifiers are treated is user-visible, it would mean that apps would have to be coded to work with any setting. What would probably happen is that app A would only work with case-sensetive, and app B would only work with case-insensetive, and you end up with two apps that can't work on the same database. That's *bad*, we don't want to go there. That is a good point and I'd normally agree - entice people to use the lowest common denominator behavior and code their apps case-sensitive. And yet, the DBs that expect case-sens are now the minority, and we have: a) programmers who code against MySQL or MSSQL, or; b) are laymen try to run or port an app designed on MySQL to PG Maybe not right per se - but the more popular way of doing things eventually wins out. .. In one way I think that even allowing creation of a separate "rowid" and "rowId" sort of violates set theory in a 4+ GL language... a "name" in its most abstract (human) sense doesn't (shouldn't) consider the case of its characters. Only what the characters are. A rowid is also a rowId (or ROWID). Who really intentionally mixes them? (only 3-4GL *programmers* who consider all-caps to represent constants in my experience). The thing is, postgresql *is* case-insensetive, as is the whole SQL language. It not case-preserving, that's all. Right, it's case insensitive only if you're willing to accept case folding (down) everything that's not quoted. Not being case-preserving, as you say. But thats a pita to anyone coming from those "other" DBs and wants their column names to have mixed/camel case (for readability). PG right now *forces* them to change/adhere to an underscore naming, or to quote *every* mixed case identifier. You MUST tolerate having your column names stored in all-lower case, or else you must quote all of them. Best, Ken ---(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: [HACKERS] [SQL] Case Preservation disregarding case
Tom Lane wrote: Ken Johanson <[EMAIL PROTECTED]> writes: -*If* the option to turn on case-insenetive behavior were selectable at the DB or session level, the existing apps could continue to use the case sensitve mode and be completely unaffected. Ken, you clearly fail to understand the point being made above. This is not something that is "selectable at the session level". It won't work from either a functional or performance perspective. Tom, re: "clearly fail to understand": I know it's not currently selectable at the session-level. My proposal (quote): *If* the option ... were selectable was to make them selectable -- this and another item that I'd proposed in the past (The proposal for 'standard_conforming_strings' at the session level was a step *toward* spec without harming existing apps). Having to set standard_conforming_strings globally for example, is a problematic setup for shared servers (ISPs etc). A session configurable strategy is a huge feature to have in shared environments. (BTW that strategy is both "functional and performing" for Mysql -- several SQL conformance items can be config'd per connection). ...proposals that involve moving away from the SQL spec rather than closer to it; and that's what you're asking us to do. If I ever proposed changing any *default* (spec) behavior, it was inadvertent or a mistake. (prob. in making my argument that case-insens does more harm that good for new PG users). Of course adhering to spec is desirable, and if that mode of operation remains *supported by default*, good. Adding per-connection or per-db 'quirks mode' (in the case of plug-in apps already built as case-insens is innocuous).. that's a useful thing. Put another way how many places is PG *deviating* from spec just by providing conveniences -- would users revolt if those were dropped? For compatibility, whether with old PG or Mysql (which is case-preserving but case insens.), we NEED them. Not as a default behavior per se, but as a per-session option. Best, Ken ---(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: [HACKERS] [SQL] Case Preservation disregarding case
Martijn van Oosterhout wrote:
I think you're missing the point that clients will be using languages
that are case sensetive. Consider the following Perl code:
$dbh->do("CREATE TEMP TABLE foo (Bar int4)");
$dbh->do("INSERT INTO foo VALUES (1)");
my $sth = $dbh->prepare("SELECT Bar FROM foo");
$sth->execute();
my $row = $sth->fetchrow_hashref();
print $row->{"bar"}; # prints 1
This code will works on any version of postgresql ever released, yet
your change will break it. By setting some options you can work around
it, but it's still a gratuitous change.
Have a nice day,
I agree, that code would break -- if the option were enabled globally --
because the named-column retrieval internals of that and many existing
modules don't do case-insens lookups. They would have to be retrofitted
to support it.
So that is the reason there was an idea proposed per database or per
connection SQL conformance controls (like what Mysql has). They'd allow
other apps to elect "less standard" modes (not just this one), for the
sake of compatibility (beit old PG modules or and other DB). You code
sample would continue to work.
Ken
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Case Preservation disregarding case
Bruce Momjian wrote: Tom Lane wrote: The real bottom line, though, is that this community has little respect for proposals that involve moving away from the SQL spec rather than closer to it; and that's what you're asking us to do. The spec is not at all vague about the case-sensitivity of identifiers. Now certainly we are not doing exactly what the spec says, but what you ask is even less like the spec's requirements. I think there are two major issues here: o if you quote identifiers that have any upper-case characters, do it both at table creation and use o display of non-quoted identifiers is lower-case I think we are OK making people either always quote, or always not quote. What we don't currently have a good answer for is people wanting the identifiers displayed using the original case. You can use quotes all the time of you want such display, but it is a pain. I think this is the crux of the complaints. Saving the case of the original creation and displaying that does work, but then it isn't clear if the identifier needs quotes (is the upper-case real or just for display). This gets us into even more confusion. Can someone can think of an answer to all this? I believe there is no one answer that will solve all cases.. but one solution that might come close is something like this (I take no credit, others have described this before me): PG would support two modes of operation: 1) Standard compliant, and; 2) Quirks / compatibility mode (case preserving but case-insensitive) I believe (and may be wrong) that the #2 mode-of-operation would only require: a) resultset data to have it's (unquoted) identifiers to be case-folded, and; b) queries with (unquoted) identifiers (joins / aliases etc) would continue in the planner to be case folded, so would work as now (and in effect be case-insensitive). c) the table describe syntax would return the case-preserved id name (which it already does if quoted?), or d: d) in addition to a), optionally send metadata describing the case-preserved name; this might someday allow newer drivers to display (display only, not column lookup) those names in database-designer views (iSQL types apps) If #a is possible, then drivers should not break, even if the DB is config'd to use setting #2. But I don't know the low-level protocol of PG to know if that is possible. .. Hopefully I'm not missing any points here, please correct me if so... I believe what I described above (while not being standard complaint per se) is identical to how mysql and mssql work (operationally anyway) right now.. On the other had Oracle and others work as PG does now, but my point in discussing this, is that the first two DBs have enough market, that offering a compatibility mode to ease the burden of porting apps would have substantial value (I know this from experience) Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Shorthand column labels (SELECT col1 foo, ...) vs (SELECT col1 AS foo, ...)
I notice PG doesn't allow shorthand column labels -- it requires the 'AS' operand. SELECT col1 foo, ...; -> ERROR: syntax error at or near "foo" For compatibility with other databases, what objections might be argued in allowing this syntax in the future? On the 'pros' side I think it eases migration to PG, shortens code, is similar syntax to shorthand table aliases, and some users might argue it has become defacto syntax among DBs. Regards, Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Shorthand column labels (SELECT col1 foo, ...) vs (SELECT col1 AS foo, ...)
Paul Lambert wrote: Ken Johanson wrote: I notice PG doesn't allow shorthand column labels -- it requires the 'AS' operand. SELECT col1 foo, ...; -> ERROR: syntax error at or near "foo" Briefly discussed a couple of weeks ago. See http://archives.postgresql.org/pgsql-general/2008-01/msg00089.php Interesting thread(s)! What I didn't see discussed was the possibility of making a server and/or session option, where we could elect to turn-off the old behavior (PG specific behavior) and enable the standard/shorthand syntax. Users need a migration path. I personally cant ever see using those PGisms/features and would choose to enable the standard mode. I think I'd have fewer compatibility problems. Ken ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] What are the (various) best practices/opinions for table/column/constraint naming?
I'd like to see a list o t he various approaches, and a poll as to which are best and why, for naming table and columns and constraints. We've all seen several variations, but the most common (and pg used) seems to be: columns: primary key: _pk OR _id OR _rowid foreign key: _fk OR _join indexes: __idx sequences: __seq constraints: __req OR __constr (etc) The most used variations seem to be removing the '_', and/or to remove the table/column prefix from objects where it is implied (when there is seen to be no point trying to make the names unique, e.g when a label is needed): columns: primary key: pk example: SELECT person.pk [AS person_id], person.named, company.pk, company.named FROM contact AS person JOIN contact AS company ON person.companyid=company.pk Other variations suggest putting the type at the start of the object name: columns: primary key: pk_ etc foreign key: fk_ etc And other names which don't necessarily represent constraints or indexes and are only meaningful to the apps: columns: _id (integer numbers or alpha-num, abstract/machine meaningful: uuids, base-36 etc) _no (integer numbers, human meaningful) _nm OR _name (named value, e.g user_name, app_name, etc) _date OR _ts (datetime/timestamp, e.g created_date, modified_date etc) _info (informational value) And other naming conventions suggest using mixed/camel case (quoted-identifiers) instead of '_' delimiters, or no delimiters at all... Has anyone seen articles or iso/ansi endorsed best-practices for naming, or otherwise have an opinion about the variations? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL standards in Mysql
Here's one Mysql developer's response to adding (fixing) the integer/bigint/tinyint types to their CAST function: http://bugs.mysql.com/bug.php?id=34562 ---(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
