Re: [SQL] C/C++ interface
>> Original Message << On 2/16/01, 8:24:09 AM, Volker Paul <[EMAIL PROTECTED]> wrote regarding Re: [SQL] C/C++ interface: > Tom Lane wrote: > > > > Volker Paul <[EMAIL PROTECTED]> writes: > > > Is someone maintaining the C++ interface and its documentation? > > > > Not really. Feel free to step up and lend a hand ... > I would like to complete the documentation at these points, > and maybe add some small example C++ programs. > In which form should I write the documentation and where > should I send it? > (I have no possibility at the moment to test SGML documents, > i.e. convert them to HTML.) > Regards, > Volker Paul I believe there are guidelines in the developer's section of the postgresql.org website - towards the end of the developer's docs. - Richard Huxton
Re: [SQL] Comparing dates
On 3/6/01, 4:38:41 PM, <[EMAIL PROTECTED]> wrote regarding Re: [SQL] Comparing dates: > Markus Fischer wrote: > > I've a SELECT statement on many joined Tabled and one of them has > > a date column called 'date_date'. When I fetch a date e.g. > > '02-03-2001', I get, say, 60 results back. When I now perform the > > same query with another date, lets take '03-03-2001', I get back > > about 70 results. > > > > When I now modify my query to get both results in one I write > > > > SELECT > > > > FROM > > .. > > AND > > date_date >= '2001-03-02' > > AND > > date_date <= '2001-03-03' > > AND > > > > > > I think I should get back the rows for both days, 60 + 70 makes > > 130 to me. But what I get back is even smaller then 60. I > > allready tried TO_DATE conversion, an OR construct but always > > the same result. > ANDing restrictions makes them narrower. > If you want to broaden your selection, try ORing the conditions > HTH > Patrick True enough - but in this case he should still get two days' worth. There's something odd here - try AND date_date >= '2001-03-02'::date AND date_date <= '2001-03-03'::date Or even AND (date_date = '2001-03-02'::date OR date_date = '2001-03-03'::date) And see if that helps. The only thing I can think of is that either date_date or the comparisons are being used as a timestamp and there is an invisible time part in there skipping some of the entries. Actually, try: AND date_date >= '2001-03-02' AND date_date < '2001-03-04' (note the < on the second part) If that does it, there are times in there somewhere - Richard Huxton ---(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] Problems with RULE
On 3/6/01, 7:11:48 AM, Jens Hartwig <[EMAIL PROTECTED]> wrote regarding [SQL] Problems with RULE: > Hello all, > I tried to implement the following rule: if someone wants to delete a record > from a table t_xyz (id integer, deleted boolean) the record should get a > delete-flag (deleted = true). When this "pre-deleted" record is deleted for > the next time it should be physically deleted from the database. > I implemented the following rule: > CREATE RULE r_del_xyz > AS ON DELETE TO t_xyz WHERE (old.deleted = false) > DO INSTEAD > UPDATE t_xyz > SET deleted = true > WHERE id = old.id; > Now I tested the new rule: > INSERT INTO t_xyz VALUES (1, false); > INSERT INTO t_xyz VALUES (2, false); > DELETE FROM t_xyz WHERE id = 1; > SELECT * FROM t_xyz ; >id | deleted > +- > 2 | f > What has happened? The rule seems to be ignored and the record was deleted! No help I'm afraid, but I encountered something similar the other day on 7.1b3 CREATE RULE ... AS ON UPDATE TO ... WHERE ... DO INSTEAD UPDATE ... Didn't run, but removing the WHERE did. I had thought someone had raised this recently, but looking through the list I can't see it now, so maybe it's a real bug. Is there any mention of this is the CHANGES file in beta4? I'll try and check this end whether it applies to all rule-types if you add a WHERE. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems with RULE
On 3/6/01, 7:11:48 AM, Jens Hartwig <[EMAIL PROTECTED]> wrote regarding [SQL] Problems with RULE: > Hello all, > I tried to implement the following rule: if someone wants to delete a record > from a table t_xyz (id integer, deleted boolean) the record should get a > delete-flag (deleted = true). When this "pre-deleted" record is deleted for > the next time it should be physically deleted from the database. Jens - more info Definitely a bug if my testing is correct (see below) - I'll file a report on it and include your example too (hope that's OK) - Richard Huxton -- OK define a table foo with data and a view voo showing -- even-numbered entries -- richardh=> create table foo (a int, b text); CREATE richardh=> insert into foo values (1,'aaa'); INSERT 1287580 1 richardh=> insert into foo values (2,'bbb'); INSERT 1287581 1 richardh=> insert into foo values (3,'ccc'); INSERT 1287582 1 richardh=> insert into foo values (4,'ddd'); INSERT 1287583 1 richardh=> create view voo as select * from foo where (a % 2)=0; CREATE richardh=> select * from voo; a | b ---+- 2 | bbb 4 | ddd (2 rows) -- Now define an insert rule with a where on voo -- richardh=> CREATE RULE voo_ins_rule AS ON INSERT TO voo WHERE (NEW.a % 2)=0 DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); CREATE richardh=> insert into voo values (99,'zzz'); ERROR: Cannot insert into a view without an appropriate rule richardh=> insert into voo values (98,'yyy'); ERROR: Cannot insert into a view without an appropriate rule richardh=> select * from foo; a | b ---+- 1 | aaa 2 | bbb 3 | ccc 4 | ddd (4 rows) richardh=> select * from voo; a | b ---+- 2 | bbb 4 | ddd (2 rows) -- OK: rule wasn't accepted, so lets add another rule to voo without a where -- richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); CREATE richardh=> insert into voo values (99,'zzz'); INSERT 1287602 1 richardh=> insert into voo values (98,'yyy'); INSERT 1287604 1 richardh=> select * from foo; a | b +- 1 | aaa 2 | bbb 3 | ccc 4 | ddd 99 | zzz 98 | yyy 98 | yyy (7 rows) richardh=> select * from voo; a | b +- 2 | bbb 4 | ddd 98 | yyy 98 | yyy (4 rows) -- So: looks like either rule2 executes twice or both fire. -- Is it because we have a second rule? -- richardh=> drop rule voo_ins_rule2; DROP richardh=> CREATE RULE voo_ins_rule3 AS ON INSERT TO voo WHERE (NEW.a % 2)=1 DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); CREATE richardh=> insert into voo values (99,'zzz'); ERROR: Cannot insert into a view without an appropriate rule richardh=> insert into voo values (98,'yyy'); ERROR: Cannot insert into a view without an appropriate rule richardh=> select * from foo; a | b +- 1 | aaa 2 | bbb 3 | ccc 4 | ddd 99 | zzz 98 | yyy 98 | yyy (7 rows) richardh=> select * from voo; a | b +- 2 | bbb 4 | ddd 98 | yyy 98 | yyy (4 rows) -- No: it must be the lack of where on rule2 -- Let's put rule2 back in and see what executes now -- richardh=> CREATE RULE voo_ins_rule2 AS ON INSERT TO voo DO INSTEAD INSERT INTO foo VALUES (NEW.a, NEW.b); CREATE richardh=> insert into voo values (99,'zzz'); INSERT 1287608 1 richardh=> insert into voo values (98,'yyy'); INSERT 1287610 1 richardh=> select * from foo; a | b +- 1 | aaa 2 | bbb 3 | ccc 4 | ddd 99 | zzz 98 | yyy 98 | yyy 99 | zzz 99 | zzz 98 | yyy 98 | yyy (11 rows) richardh=> select * from voo; a | b +- 2 | bbb 4 | ddd 98 | yyy 98 | yyy 98 | yyy 98 | yyy (6 rows) -- OK: so it looks like rules with "WHERE" don't execute until -- there is a rule that fires unconditionally, when -- the "WHERE" is recognised and applies accordingly. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Quick question MySQL --> PgSQL
On 3/6/01, 5:26:18 PM, Josh Berkus <[EMAIL PROTECTED]> wrote regarding [SQL] Quick question MySQL --> PgSQL: > Folks, > Just a quick question ... I need to do a regular transfer (daily + on > demand) of data from a MySQL database to a PostgreSQL database and back > again. Can anybody steer me towards a good script for this, or do I > have to write my own in PHP? Don't think you'll find much off the shelf - this sort of thing tends to be specific to each person and each project. It's just a matter of SELECTing from MySQL and INSERTing into PostgreSQL in a loop. Be careful with things like times and dates and if you are doing it in PHP make sure you've thought about error recovery. I'd recommend copying into an import table then when it's confirmed that's worked do an internal copy in PG. Remember if it's run from a browser the user can just break the connection. Consider running it from a cron-job with lynx or wget (or write the thing in perl). > Sorry to bother everyone with something that isn't strictly a SQL > question, but I'm not sure where else to ask. > -Josh Berkus > P.S. If somebody wants consulting $$$ for the above, it may be > available. Sadly I charge in £££ and it doesn't sound like a large enough job to make it worth mucking around with conversions. - Richard Huxton ---(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] Permissons on database
On 3/6/01, 5:00:47 PM, Boulat Khakimov <[EMAIL PROTECTED]> wrote regarding [SQL] Permissons on database: > Hi, > How do I grant permissions on everything in the selected databes? > GRANT doesnt take as on object database name nor does it accept wild > chars By hand at the moment I'm afraid. There is no GRANT ALL ON ALL... Having said that, if you have plpgsql enabled on that database you could write a simple loop to do so for you. If you'd find one useful let me know and I'll knock one up. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] work on rows
Author: Rachel Coin <[EMAIL PROTECTED]> I have a problem with a sql query.I have two tables : the first contains categories and the second subcategories. What kind of select may I use to get something like Yahoo! categories ( each "main" category gets *at most* three sub-categories.. Do you mean something like the following? (PS - please don't post HTML to mailing lists) richardh=> select * from cats; c --- A B (2 rows) richardh=> select * from subcats; c | s ---+ A | a1 A | a2 A | a3 A | a4 (4 rows) richardh=> select cats.c,subcats.s from cats,subcats where cats.c=subcats.c; c | s ---+ A | a1 A | a2 A | a3 A | a4 (4 rows) richardh=> select cats.c,subcats.s from cats,subcats where cats.c=subcats.c and subcats.s in (select s from subcats where subcats.c=cats.c limit 2); c | s ---+ A | a1 A | a2 (2rows) - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Newbie: execute function error!
On 3/6/01, 5:55:05 AM, John GM <[EMAIL PROTECTED]> wrote regarding [SQL] Newbie:
execute function error!:
> I created a sample function:
> CREATE FUNCTION concat_text (text, text) RETURNS text AS '
> BEGIN
> RETURN $1 || $2;
> END; '
> LANGUAGE 'plpgsql';
> The creation was fine. But when I execute the function with : select
> concat('a', 'b');
> I get the error:
> NOTICE: plpgsql: ERROR during compile of concat_text near line 1
> "RROR: parse error at or near "
> What did I do wrong?
Nothing wrong with your function, but the error seems to be missing an E
at the front (unless it's a typo) - you didn't cut and paste the
definition from a Windows machine to *nix did you - leaves CR characters
lying around.
- Richard Huxton
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Writing SQL functions in Postgres
Boulat Khakimov <[EMAIL PROTECTED]> said: > I want to write an SQL function in postgres that returns > row as a result. > > The problem is the select statement inside the funtion has > a two table join. So I dont know what to put after SETOF > > CREATE FUNCTION dummy() > RETURNS SETOF ? > AS 'select a.name,b.cc > from tblusers a, > tbldocs b > where a.name=b.name' > LANGUAGE 'SQL'; > > > SETOF tblusers -- doesnt work > ERROR: function declared to return type tblusers does not retrieve > (tblusers.*) > > neither does SETOF tbldocs > > SETOF tblusers,tbldocs wont work either. There's good news and bad news. The good news is that if you define a view "tblboth" that selects from your two tables you can then do "returns setof tblboth". The bad news is that your function won't return a set of records - you'll get a list of OIDs (at least I think they're OIDs). Check the mailing archives for more on this. You can do your example with a simple view, but if you want a parameterised view you'll have to wait until 7.2 (I think it's on the todo list) - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Date Validation?
> Richard, > >> Out of curiosity Josh, why aren't you validating in PHP? - only takes >> a >> couple of lines there. > > We are. It's just that all other validation takes place inside the > PL/pgSQL functions in order to keep all the business logic in the > database. Having one form of validation in the interface (PHP) and the > rest in the function can cause the user to go through multiple > validation errors, which is especially frustrating if the second > validation error is fatal. Example: [snip] Hmm - fair enough - PITA really. Poked around and had a think and can't come up with anything better than Stephan Szabo's suggestion of hacking PG's built-in function. You'll want to_timestamp() which is called from to_date and it's in src/backend/utils/adt/formatting.c - AFAICT you'll just need to comment out the elog(...) calls and return a null value (or whatever). Looks like there are a lot of dependencies - my C is too rusty to figure that out just by skimming. I'm guessing the validation is fiddly though - PHP's date handling code is no shorter. Failing that, what about writing is_valid_date() in pl-tcl/perl? Don't know anything about tcl, but should be easy enough in perl (cut & paste from a suitable module) Or (and this is horrible) check the date in PHP and if it's not valid replace it with NULL. You can check for the null in the trigger fn, but unfortunately you no longer have the original value for your error message (no, I don't like it either). For the interested on the list: The central problem seems to be that the error logging function elog() never returns from an ERROR message and kills the whole transaction. This makes sense since any code can call elog(ERROR,...) and not have to worry about recovering from the error. If PostgreSQL had been written using Java, there'd probably be try...catch everywhere and it wouldn't be an issue (of course there might well be performance problems as well as someone having to invent java 10 years before Sun did ;-) Presumably, once we have nested transactions all this will be magically solved by wrapping the possibly dodgy statements with an inner transaction. Aside: I may have found the world's first "Y2K BC" bug - if we make enough noise over this it could turn the IT industry round again. richardh=> select '01-01-01 BC'::date, '0001-01-01 BC'::date; ?column?| ?column? ---+--- 2001-01-01 BC | 0001-01-01 BC (1 row) - Richard Huxton ---(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] enforcing with unique indexes..
> > ie i want 1 to 1 mapping between group_id and userid so that , there > shud not be a single group_id having more that one kind of userid. > > can it be done with some sort of UNIQUE INDEX? If you want multiple groups but the user should always be the same 1 1 ok 1 1 ok 2 1 ok 3 2 ok 3 3 bad Perhaps you should normalise a bit further with a separate group_user table - you could restrict that to being unique on group then. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Are sub-select error suppressed?
> On Tue, 26 Nov 2002, Richard Huxton wrote: > >> On Tuesday 26 Nov 2002 9:43 am, patrick wrote: >> > Greetings, >> > >> > I'm not sure what the correct behavior is here but the observed >> > behavior seems "wrong" (or at least undesirable). >> Don't look right to me, and I still see it here in 7.2 and the 7.3 beta >> I've >> got (note - not most recent). I don't think it's in the subselect itself >> - >> what's happening is when you do > > I think it's standard behavior. The column reference is an outer > reference I believe, IIRC all the names from the outer query are in scope > in the subselect (although if there's an equivalent name in the subselect > from tables you'd have to qualify it). Ah - of course. Otherwise you couldn't do a subselect where foo=outer_foo. It tries to bind within the subselect, fails, then binds to the outer clause. Obvious now Stephan's pointed it out. Also reminds me why I like table aliases for any complicated queries. - Richard Huxton ---(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] order by and aggregate
> Richard Huxton wrote: > >> On Monday 06 Jan 2003 12:44 pm, Tomasz Myrta wrote: >> >> >Hi >> >I created my own aggregate function working as max(sum(value)) >> >It adds positive and negative values and finds maximum of this sum. >> >To work properly this function needs data to be sorted. >> >> >> I'm not sure that an aggregate function should require data to be sorted >> before processing. Could you show details of your function - there may >> be a >> way to rewrite it to handle unsorted data. > > Standard Postgresql aggregate functions don't need sorted data, but my > function needs. Look at the data: > > 3 3 > -21 > 6 7 *** max_sum=7 > -34 > 2 6 > > For example, if you inverse your data, you have: > > 2 2 > -3-1 > 6 5 > -23 > 3 6 *** max_sum=6 > > As you see, data order is very important in this aggregate. Ah - so it's maximum of a running-total rather than a sum. AFAIK you are out of luck with aggregate functions. The order data is supplied to them is *not* defined - the "order by" operates just before results are output. Not much you can do about this, the whole basis of relational DBs are sets and sets don't have any idea of ordering. However, there are three options: You could define a cursor (or a table-function in 7.3) which would handle the order-by and then calculate the running-total on the fly. You then just need a standard max(running_total) call to pick out the value. Actually, if you use the cursor you might need to implement the max() in the application. Alternatively, you could add a running_total column and use a trigger to ensure the value is kept up to date. Finally, you could do the work in the application. Difficult to say which is the best for you. If you have 7.3, don't need these figures often and do a lot of updates/inserts I'd recommend option 1. If you're using 7.2, don't do a lot of inserts and want the figures frequently I'd choose option 2. HTH - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] insert rule doesn't see id field
> On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote: >> On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote: >> >> > > I thought that the idea behind noup was to protect single columns >> from >> > > update. However, when I apply the noup trigger as above, I can't >> > > update /any/ column. Is this the intended behaviour? >> > >> I'm going to see if I can create this function. > > Well, I think I've thunk something up. Of course I'm happy to submit > my modification for distribution or ridicule, as the case may be. > Where should I submit this? > > I made a function noupcols() which takes one or more column names as > arguments. The function then creates a new tuple by getting the old > values for those columns, and then doing an SPI_modifytuple on the new > tuple using the old values for those columns. > > I'm kind of flying by the seat of my pants here, so if anyone would > care to critically review my code, by all means... Sounds similar to the plpgsql example at: http://www.archonet.com/pgdocs/lock-field.html which silently discards changes. It's trivial (apart from quoting issues) to write a trigger generator to customise the above in plpgsql (see the Cookbook on techdocs.postgresql.org for examples) - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Table Design Questions
> Hello, > > I am trying to come up with an efficient table design > that describes a fantasy character that meets the > following criteria: Apologies for only dealing with the last part of your query - busy at the moment. I'll try and follow up in more detail later. > CREATE TABLE ATTRIBUTES ( > CHAR_ID INT PRIMARY KEY NOT NULL, > ATTRIB_TYPE_ID INT NOT NULL, > ATTRIB_VALUE INT, > CONSTRAINT ATTRIB_TYPE_ID_FK FOREIGN KEY > (ATTRIB_TYPE_ID) REFERENCES ATTRIB_TYPES > (ATTRIB_TYPE_ID) > ); > > CREATE TABLE ATTRIB_TYPES ( > ATTRIB_TYPE_ID INT PRIMARY KEY NOT NULL, > ATTRIB_TYPE VARCHAR(20) NOT NULL, > ATTRIB_NAME VARCHAR(20) UNIQUE NOT NULL, > ); > Please forgive my naiveté, but, what are typical > solutions for dealing with enumerated types or sets? > > For example, a set of (Human | Elf | Dwarf | Gnome) > where the ATTRIBUTES.VALUE could be any combination of > the above. In this case just multiple entries in the ATTRIBUTES table with the same CHAR_ID and ATTRIB_TYPE_ID. char01, race, human char01, race, elf (of course, I've used the text representation of the actual numbers above) > I realize I could, in this case, think of > ATTRIBURES.VALUE as a binary value where a character > is both a Human & Elf (half elf) is (1100), but that > just doesn't sit right with me. Is there a better way > to do this? Perhaps by making ATTRIBUTES.VALUE an > array? If so, would I be correct in assuming this > would add a performance hit to searches? If you're doing it properly, you probably want to be able to have something like (human 75%,elf 25%) which would mean adding a "percentage" or "multiplier" column to your ATTRIBUTE table. Or you could classify the different races/species with their own attribute type. So - you'd either have ATTRIBUTES (with new column) == char01, race, human, 0.75 char01, race, elf, 0.75 or ATTRIBUTES (existing columns) == char01, ishuman, 75 char01, iself, 25 You might want the extra column anyway, then you could have: char01, shortsword, attack, 40 char01, shortsword, parry, 30 > (And please don't tell me to just add half elf to the > set :). Of course, you could just add half-elf to the set ;-) - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Unique indexes not unique?
> Jimmy Mäkelä wrote:
>
>> I found that Postgres isn't behaving like I thought when using a
>> unique index in
>> combination with NULL-values...
>> Is this a bug or specified in the SQL-standard? If its a bug, is it
>> fixed in a
>> recent version? We are using 7.2.3
>> intranet=# insert into "foo" (a, b) values ('apa', null);
>> INSERT 26229706 1
>> intranet=# insert into "foo" (a, b) values ('apa', null);
>> INSERT 26229707 1
>
> I'm not sure unique index works properly for null values. I can't
> explain, why. Maybe it comes from SQL standard - null i a special value
> and can't be compared using default operators to other non null values:
> 1>null =null
> 1 1=null =null
Null is not a value or even a "special" value, it is supposed to represent
the absence of a value. It means either "not applicable" or "not known".
It doesn't make sense to say whether one null is the same as another, a
null is an absence, a hole. As a result, you can't really talk about
comparing two nulls, only testing whether a value is null.
If you are using a null in a situation where it should be unique, you
probably want a value instead. Can't say more without an actual example.
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Crosstab-style query in pure SQL
TIA all I have a table containing milestones achieved for projects, marked as being in particular quarters. CREATE TABLE milestones ( proj_id int4, sortorder SERIAL, qtr int4, description varchar(200) ); Now I need the milestone descriptions output across the page like: proj_id | q1 | q2 | q3 | q4 and sorted according to "sortorder". Judicious use of CASE can get me the quarterly columns but I need to eliminate holes. Any clever ideas without resorting to procedural solutions (either table function or application code)? TIA - Richard Huxton ---(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] Crosstab-style query in pure SQL
> Richard, > >> I have a table containing milestones achieved for projects, marked as >> being in particular quarters. >> >> CREATE TABLE milestones ( >> proj_id int4, >> sortorder SERIAL, >> qtr int4, >> description varchar(200) >> ); >> >> Now I need the milestone descriptions output across the page like: >> proj_id | q1 | q2 | q3 | q4 >> and sorted according to "sortorder". > > Ah! A classic SQL problem. > > Take a look at Joe Celko's "SQL for Smarties": he does a good job of > defining and discussing the three different solutions to the "Crosstab > Query" problem. > > A second method you can use is the subselect method: > > SELECT proj_id, sortorder, qart1.q1, quart2.q2 > FROM milestones > LEFT OUTER JOIN ( SELECT proj_id, description as q1 >FROM milestones WHERE qtr = 1) quart1 > ON quart1.proj_id = milestones.proj_id > LEFT OUTER JOIN ( SELECT proj_id, description q2 ... > > However, the above is generally chosen over the CASE statement method > when the crosstab involves multiple tables; in your case, it is not a > performance or similicity gain. Thanks Josh, but that still leaves me with nulls if I join on sortorder too, and duplicates if not (as you imply, since it's equivalent to the CASE option). The problem is that since I defined "sortorder" as a serial, equivalent rows of a specific project don't match across the key. Without calculating a "row_index" based on (proj_id,sortorder) it doesn't look like there's anything to be done without procedural help. Didn't think there was anything simple - my fault for not having common keys to match up output rows - oversimplified the input stage and I'm paying for it on output. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] RFC: A brief guide to nulls
There have been a few posts recently where people have had problems with
nulls. Anyone got comments on the below before I submit it to techdocs?
TIA
- Richard Huxton
A Brief Guide to NULLs
==
What is a null?
===
A null is *not* an empty string.
A null is *not* a value.
A null is *not* a "special" value.
A null is the absence of a value.
What do nulls mean?
===
Well, they *should* mean one of two things:
1. There is no applicable value
2. There is a value but it is unknown
Example 1: Imagine you have a customer table with name and sex fields.
If you get a new customer "ACME Widgets Ltd", the sex field is meaningless
since your customer is a company (case 1).
If you get a new customer "Jackie Smith" they might be male or female, but
you might not know (case 2).
Example 2: You have an address table with (street,city,county,postalcode)
fields.
You might insert an address ("10 Downing Street","London",Null,"WC1 1AA")
since you don't have a valid county.
You might also insert an address ("1 Any Street","Maidstone","Kent",Null)
where there *must be* a valid postalcode, but you don't know what it is.
It might be useful to be able to distinguish between these two cases - not
applicable and unknown, but there is only one option "Null" available to
us, so we can't.
How do nulls work?
==
There is one very important rule when dealing with nulls. The result of
any operation or comparison, when applied to a null is null. The only
exception is testing if a value is null.
Example: with the customer table above you could run the following queries:
SELECT * FROM customer WHERE sex='M';
SELECT * FROM customer WHERE sex<>'M';
Now you might think this returns all customers, but it will miss those
where sex is null. You've asked for all rows where the value of sex is 'M'
and all those with values not equal to 'M' but not rows with *no value at
all*
It might help to think of a database as a set of statements you *know* to
be true. A null indicates that you *cannot say anything at all* about that
field. You can't say what it is, you can't say what it isn't, you can only
say there is some information missing.
So, to see all the customers with unknown or inapplicable sex you would need:
SELECT * FROM customer WHERE sex IS NULL;
There are actually three possible results for a test in SQL - True (the
test passed), False (the test failed) and Null (you tested against a
null). A result of null usually gets treated as False, so testing against
nulls always fails.
If you try to perform an operation on nulls, again the result is always
null. So the results of all of the following are null:
SELECT 'abc' || null;
SELECT 1 + null;
SELECT sqrt(null::numeric);
The first case can be especially confusing. Concatenating a null string to
a string value will return null, not the original value.
Uniqueness and nulls
If you define a unique index on a column it prevents you inserting two
values that are the same. It does not prevent you inserting as many nulls
as you like. How could it, you don't have a value so it can't be the same
as any other.
Example: We create a table "ta" with a unique constraint on column "b"
CREATE TABLE ta (
a int4,
b varchar(3),
PRIMARY KEY (a)
);
CREATE UNIQUE INDEX ta_b_idx ON ta (b);
INSERT INTO ta VALUES (1,'aaa'); -- succeeds
INSERT INTO ta VALUES (2,'bbb'); -- succeeds
INSERT INTO ta VALUES (3,null); -- succeeds
INSERT INTO ta VALUES (4,'bbb'); -- fails
INSERT INTO ta VALUES (5,null); -- succeeds!
Given the definition of what a null is, you only have two choices: allow
multiple nulls or allow no nulls. If you want no nulls, define the column
as NOT NULL when creating the table.
Keys and nulls
==
No column that is part of a primary key can be null. When you define a
PRIMARY KEY, none of the columns mentioned can take a null value.
Postgresql makes sure of this by defining the columns as NOT NULL for you.
Example: With table "ta" we just created, \d ta will show column a as
being not null. Otherwise, we could insert rows with a set to null and
have no way to tell them apart.
Subqueries and nulls
Since tests always fail when testing against nulls you can have unexpected
results with sub-queries.
Example: Assume we have a companies table and a diary table. Diary entries
are usually related to a particular company but not always.
SELECT co_name FROM companies WHERE co_id NOT IN (SELECT dy_company FROM
diary);
If any row in diary contains a null dy_company then you will get *no
results*.
We can expand the query like so:
WHERE co_id NOT IN (SELECT dy_company FROM diary)
WHERE co_id NOT IN (1, 2, null, 3...)
WHERE NOT (co_id=1 OR co_id=2 OR co_id=null OR co_id=3...)
WHERE NOT (... OR null OR ...)
WHERE NOT (null)
WHERE null
You either need to explicitly check for null values, or define the column
in question as NOT NU
Re: [SQL] RFC: A brief guide to nulls
> > --- [EMAIL PROTECTED] wrote: >> There have been a few posts recently where people >> have had problems with >> nulls. Anyone got comments on the below before I >> submit it to techdocs? >> >> TIA >> >> - Richard Huxton >> >> A Brief Guide to NULLs >> == >> >> What is a null? >> === >> A null is *not* an empty string. >> A null is *not* a value. >> A null is *not* a "special" value. >> A null is the absence of a value. >> >> >> What do nulls mean? >> === >> Well, they *should* mean one of two things: >> 1. There is no applicable value >> 2. There is a value but it is unknown > > Good job!, it sure helps people who don't much > background on formal database theory. > > What about adding this to the section "What does > nulls mean" > --> 3) No value has yet been assigned to that > particular attribute (field). > > > I think it would also be nice if you can add a > section on functions that deals with NULL such as > nullif() and coalesce(). These functions help users > interpret NULL values. > > > best regards, > > ludwig > > > > __ > Do you Yahoo!? > New DSL Internet Access from SBC & Yahoo! > http://sbc.yahoo.com > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] RFC: A brief guide to nulls
Apologies for previous post with no content - hit send by mistake. Thanks to everyone for the feedback, 2nd draft out later today/tomorrow. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] RCF: 2nd draft: A brief guide to Nulls
Thanks all for the previous feedback. If no-one comes up with any errors
in this draft I'll call it finished.
- Richard Huxton
A Brief Guide to NULLs
==
revision: 0.9
date: 2002-01-17
author: Richard Huxton <[EMAIL PROTECTED]>
Overview
This is a short guide to the use of nulls in SQL databases. It is written
with Postgresql in mind but should be applicable to any SQL-based DBMS.
Thanks to the members of the psql-sql mailing list for their assistance in
preparing this guide.
You can get further information in:
Any good relational database book (try something written by Date or Pascal)
Bruce's book (link LHS at http://techdocs.postgresql.org)
My Postgresql Notes (link at http://techdocs.postgresql.org)
PART I - INTRODUCTION
What is a null?
===
A null is *not* an empty string.
A null is *not* a value like others.
A null is the absence of a value[1].
What do nulls mean?
===
Well, they *should* mean one of two things:
1. There is no applicable value
2. There is a value but it is unknown
Example 1: Imagine you have a customer table with name and sex fields.
If you get a new customer "ACME Widgets Ltd", the sex field is meaningless
since your customer is a company (case 1).
If you get a new customer "Jackie Smith" they might be male or female, but
you might not know (case 2).
Actually, since you are trying to store a company in the ACME example that
might indicate that you need to rethink your design.
Example 2: You have an address table with (street,city,county,postalcode)
fields.
You might insert an address ("10 Downing Street","London",Null,"WC1 1AA")
since you don't have a valid county.
You might also insert an address ("1 Any Street","Maidstone","Kent",Null)
where there *must be* a valid postalcode, but you don't know what it is.
It might be useful to be able to distinguish between these two cases - not
applicable and unknown, but there is only one option "Null" available to
us, so we can't.
How do nulls work?
==
There is one very important rule when dealing with nulls. A null is
unknown and thus not equal to, less than or greater than any value it is
compared to.
Example: with the customer table above you could run the following queries:
SELECT * FROM customer WHERE sex='M';
SELECT * FROM customer WHERE sex<>'M';
Now you might think this returns all customers, but it will miss those
where sex is null. You've asked for all rows where the value of sex is 'M'
and all those with values not equal to 'M' but not rows with *no value at
all*
It might help to think of a database as a set of statements you *know* to
be true. A null indicates that you *cannot say anything at all* about that
field. You can't say what it is, you can't say what it isn't, you can only
say there is some information missing.
So, to see all the customers with unknown or inapplicable sex you would need:
SELECT * FROM customer WHERE sex IS NULL;
Note that the following will not work, you need to use "IS NULL"
SELECT * FROM customer WHERE sex=NULL;
There are actually three possible results for a test in SQL - True (the
test passed), False (the test failed) and Null (unknown or can't say). The
table below indicates the result of using AND/OR operations on a,b for
values of True,False and Null.
a | b | a AND b | a OR b
--+---+-+
TRUE | TRUE | TRUE| TRUE
TRUE | FALSE | FALSE | TRUE
TRUE | NULL | NULL| TRUE
FALSE | FALSE | FALSE | FALSE
FALSE | NULL | FALSE | NULL
NULL | NULL | NULL| NULL
In the example of a=True,b=Null, (a AND b) is Null (which gets treated as
false for the purposes of WHERE clauses). However (a OR b) is True since
if a is True, we don't care what b is.
If you try to perform an operation on nulls, again the result is always
null. So the results of all of the following are null:
SELECT 'abc' || null;
SELECT 1 + null;
SELECT sqrt(null::numeric);
The first case can be especially confusing. Concatenating a null string to
a string value will return null, not the original value. This can catch
you out if you are joining first_name to last_name and one of them
contains nulls.
How are nulls implemented?
==
You can think of each null-able field/column having a separate "is_null"
flag attached to it. So, if you have a column "a" of type integer, in
addition to space required to store the number, there is another bit which
says whether the item is null and the value should be ignored. Of course,
there are optimisations that get made, but that is the general idea.
PART II - IMPLICATIONS
Uniqueness and nulls
If you define a unique index on a column it prevents you inserting two
values that are the same. It does not prevent you inserting as many nulls
as you like. How could it? You don't have a value so it can't be the same
as any other.
Example: We create a table "ta" with a unique constraint on column "b"
Re: [SQL] which will be faster? w/ or w/o indices
> I understand that if you are importing data into the database > that this will go faster if there are no indices against the > target table. > > If I want to run this statement: > INSERT INTO table_a SELECT * from table_b WHERE value_1 BETWEEN > X AND Y; > is it best to do that with or without indices? Can't really say without testing - it'll depend on the size of the various tables, X and Y disk speeds etc. The only suggestion I can make is to try an EXPLAIN (Reference manual, SQL commands) with and without ENABLE_SEQSCAN off (Admin guide ch 3.2). - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Poor performance on a right join
> When doing a join query I am getting a responce time of 3 seconds. The > callhist table has 66000 rows and the phone table has 1 rows. I > have an indexes on callhist.call_date, callhist.operator_id, > phone.phone, & phone.cust_code. Here's the SQL > SELECT a.CALL_DATE, a.OPERATOR_ID, a.CUST_CODE FROM PHONE as b right > join CALLHIST as a on (a.CUST_CODE=b.CUST_CODE) where (b.PHONE = > '555-555-') order by a.call_date desc; > The postgres db is running on a 2 P3 700 processor server with 1GB of > ram running Red Hat Linux 7.3. I am running PostgreSQL 7.2.2 > > Why is the query taking so long? What can I do to help the > performance? We'll need the output of EXPLAIN ANALYSE SELECT ... Perhaps table definitions too. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Poor performance on a right join
> Here's the EXPLAIN output: > > EXPLAIN ANALYZE SELECT a.CALL_DATE, a.OPERATOR_ID, a.CUST_CODE FROM > PHONE as b right join CALLHIST as a on (a.CUST_CODE=b.CUST_CODE) where > (b.PHONE > = '847-478-2100') order by a.call_date desc; > NOTICE: QUERY PLAN: > > Sort (cost=14320.04..14320.04 rows=60466 width=68) (actual > time=5104.37..5104.3 > 8 rows=23 loops=1) > -> Merge Join (cost=0.00..8157.88 rows=60466 width=68) (actual > time=1396.24. > .5104.08 rows=23 loops=1) > -> Index Scan using phone_custcode_idx on phone b > (cost=0.00..345.34 r > ows=11395 width=28) (actual time=0.28..473.16 rows=11402 loops=1) > -> Index Scan using callhist_cust_idx on callhist a > (cost=0.00..4667.4 > 8 rows=60466 width=40) (actual time=0.37..3717.76 rows=254386 loops=1) > Total runtime: 5104.58 msec I might be wrong, but it looks like the join is happening before filtering on the phone number (rows=254386). What happens if you don't use and explicit join: ...WHERE a.CUST_CODE=b.CUST_CODE AND b.PHONE = '847-478-2100'... - Richard Huxton ---(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] upcasting multiplication in a query
> i have a schema that stores timestamps in seconds/microseconds format. > each > column in the table is int4. what i want to do is to compute the int8 > value > of total microseconds, a la: > select (seconds*100)+micros from my_table; > but it looks to me like postgresql puts the rresult into another int4 > (since > i am getting negative numbers, i assume overflow). how can i get it to use > int8? Try (seconds::int8 * 1000)+micros - you could use cast(...) if you want to be more standard. - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] update from select
Hello I have a performance problem with an SQL statement. Is there a better way to do this update: UPDATE table1 SET column2 = temp_table.column2, column3 = temp_table.column3, column4 = CAST(temp_table.column4 AS date) FROM ( SELECT DISTINCT table2.column1, table2.column2, table2.column3, table2.column4 FROM table2 WHERE column4 IS NOT NULL AND column4 <> '' AND (length(column4) = 10 OR length(column4) = 23) ) AS temp_table WHERE table1.column1 = temp_table.column1; The select by it's own takes around 1 second. The Update is around 120'000 rows. I got an index on column1. The whole query needs around 16 minutes. The same procedure on MSSQL needs around 30 seconds. I hope to get it too in Postgres. Please help me. Regards Reto
Re: [SQL] update from select
Yes, both have varchar(50).
Query:
UPDATE owner SET picturemedium = dvds.picturemedium, title = dvds.title,
titleOrder = dvds.title, releasedate = CAST(dvds.releasedate AS date) FROM
(
SELECT DISTINCT
detail_dvd.asin,
detail_dvd.picturemedium,
detail_dvd.title,
detail_dvd.releasedate
FROM detail_dvd
WHERE releasedate IS NOT NULL AND releasedate <> '' AND
(length(releasedate) = 10 OR length(releasedate) = 23)
)
AS dvds WHERE owner.asin = dvds.asin;
***
EXPLAIN ANALYZE:
Hash Join (cost=10827.45..25950.05 rows=4906 width=1191) (actual
time=586.251..2852.691 rows=111306 loops=1)
" Hash Cond: ((""owner"".asin)::text = (dvds.asin)::text)"
" -> Seq Scan on ""owner"" (cost=0.00..14148.98 rows=230198 width=101)
(actual time=0.050..968.028 rows=230198 loops=1)"
-> Hash (cost=10825.02..10825.02 rows=194 width=1208) (actual
time=584.463..584.463 rows=19489 loops=1)
-> Subquery Scan dvds (cost=10820.66..10825.02 rows=194
width=1208) (actual time=435.005..545.213 rows=19489 loops=1)
-> Unique (cost=10820.66..10823.08 rows=194 width=110)
(actual time=435.002..520.725 rows=19489 loops=1)
-> Sort (cost=10820.66..10821.14 rows=194 width=110)
(actual time=434.998..491.487 rows=19489 loops=1)
Sort Key: asin, picturemedium, title, releasedate
-> Seq Scan on detail_dvd (cost=0.00..10813.29
rows=194 width=110) (actual time=0.042..166.493 rows=19489 loops=1)
Filter: ((releasedate IS NOT NULL) AND
((releasedate)::text <> ''::text) AND ((length((releasedate)::text) = 10) OR
(length((releasedate)::text) = 23)))
Total runtime: 633548.404 ms
***
He is not using the index on asin? When I reduce the SELECT to 100, he is
using the index! perhaps to many rows are affected?
The DISTINCT in the SELECT is not really necessary. It's just for security
reasons. And I did check it. It's unique! But as I said before. The SELECT
takes around 1 second! I have 13 indices on the UPDATE table. So I did
delete the one I dont need for this query... Now I have 3 left! And it
takes around 2 Minutes! But thats also a problem, because I need the
Indexes again! Is it possible to set the way, Postgres is building the
indices? Or is the only way deleting the indeces before UPDATE and then
creating them again?
I also tried this query (PostgreSQL's extension):
UPDATE owner SET picturemedium = detail_dvd.picturemedium, title =
detail_dvd.title, titleOrder = detail_dvd.title, releasedate =
CAST(detail_dvd.releasedate AS date)
FROM detail_dvd
WHERE owner.asin = detail_dvd.asin
AND detail_dvd.releasedate IS NOT NULL
AND detail_dvd.releasedate <> ''
AND (length(detail_dvd.releasedate) = 10 OR length(detail_dvd.releasedate) =
23);
But its also to slow:
***
EXPLAIN ANALYZE:
Nested Loop (cost=0.00..28175.75 rows=2006 width=195) (actual
time=0.138..127695.132 rows=111306 loops=1)
-> Seq Scan on detail_dvd (cost=0.00..10813.29 rows=194 width=110)
(actual time=0.035..615.511 rows=19489 loops=1)
Filter: ((releasedate IS NOT NULL) AND ((releasedate)::text <>
''::text) AND ((length((releasedate)::text) = 10) OR
(length((releasedate)::text) = 23)))
" -> Index Scan using ""iidx-owner-asin"" on ""owner"" (cost=0.00..89.04
rows=26 width=99) (actual time=2.848..6.485 rows=6 loops=19489)"
"Index Cond: ((""owner"".asin)::text = (detail_dvd.asin)::text)"
Total runtime: 1039998.325 ms
***
Thaks for helping!! Bye the way, we are changing our system from MSSQL2000
to Postgres :-)!
Regards
Reto
-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Im Auftrag von A. Kretschmer
Gesendet: Montag, 29. Oktober 2007 11:45
An: pgsql-sql@postgresql.org
Betreff: Re: [SQL] update from select
am Mon, dem 29.10.2007, um 10:18:38 +0100 mailte [EMAIL PROTECTED]
folgendes:
>
> WHERE table1.column1 = temp_table.column1;
table1.column1 and temp_table.column1 have the same type?
>
>
>
> The select by it?s own takes around 1 second. The Update is around 120?000
> rows. I got an index on column1. The whole query needs around 16 minutes.
Show us the EXPLAIN ANALYSE - result.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] Querying multiple database
Hello everyone, Please can you point me to the contrib/dblink package links. What am actually looking for is query multiple database located on different machines. Is there any documentation for same which tells the metadata steps? Existing archive link if exists please can you point me same. Regards - Dev
Re: [SQL] Querying multiple database
Am looking for pointers related to cross-database queries? Thanks in advance! On Tue, Feb 5, 2013 at 10:42 PM, Dev Kumkar wrote: > Hello everyone, > > Please can you point me to the contrib/dblink package links. > > What am actually looking for is query multiple database located on > different machines. Is there any documentation for same which tells the > metadata steps? > Existing archive link if exists please can you point me same. > > Regards - Dev > -- :o) dev
Re: [SQL] Querying multiple database
Hello Again,
Using dblink and now am facing a fatal error while tryinf to execute
dblink_connect as follows:
SELECT *
FROM dblink_connect('host=127.0.0.1 port=5432 dbname=postgres
password=test')
ERROR: could not establish connection
DETAIL: FATAL: password authentication failed for user "NETWORK SERVICE"
What this error is related to? Do I need to modify pg_hba.conf file by any
chance?
Regards...
[SQL] Facing authentication error on postgres 9.2 -> dblink functions
Hello Everyone,
I am using postgres 9.2 and when executing function dblink facing a fatal
error while trying to execute dblink_connect as follows:
* SELECT * FROM dblink_connect('host=127.0.0.1 port=5432 dbname=postgres
password=test')*
*ERROR*: could not establish connection DETAIL: FATAL: password
authentication failed for user "NETWORK SERVICE"
What this error is related to? Do I need to modify pg_hba.conf file by any
chance?
Thanks..
[SQL] Unquoted column names fold to lower case
Hello, All unquoted identifiers are assumed by PostgreSQL to be lower case by default. This means - SELECT MY_COLUMN FROM my_table - SELECT my_column FROM my_table - SELECT my_column as MY_COLUMN FROM my_table All refer to my_column and has column name in lowercase as my_column. If the aliases are in upper case and quoted then those are returned in upper case. - SELECT my_column as "MY_COLUMN" FROM my_table Is there any workaround to set the alias columns to be returned as upper case with adding quotes. Is there any connection level parameter to flip this behavior? Thanks in advance! Regards...
[SQL] Re: Unquoted column names fold to lower case
Resending On Wed, Jul 3, 2013 at 2:42 PM, Dev Kumkar wrote: > Hello, > > All unquoted identifiers are assumed by PostgreSQL to be lower case by > default. This means > - SELECT MY_COLUMN FROM my_table > - SELECT my_column FROM my_table > - SELECT my_column as MY_COLUMN FROM my_table > All refer to my_column and has column name in lowercase as my_column. > > If the aliases are in upper case and quoted then those are returned in > upper case. > - SELECT my_column as "MY_COLUMN" FROM my_table > > Is there any workaround to set the alias columns to be returned as upper > case without adding quotes. Is there any connection level parameter to flip > this behavior? > > Thanks in advance! > > Regards... >
Re: [SQL] Unquoted column names fold to lower case
Thanks! Sure this is not a bug. Just want to know if there is any possible way to achieve this. Reason being - need to change all the data layer of project where the column mapping has been done with Upper Case. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 8:54 PM, Bruce Momjian wrote: > Agreed. The original poster specifically wanted "MYTABLE" and mytable > to be the same, not "mytable" and mytable. Postgres is certainly > non-standard in this area. I think the ability visiually distinguish > lower-case letters better than upper-case letters has led to our > behavior. Not really, actually am looking for column aliases here and not the table. Here is the example again when the aliases are unquoted: - SELECT my_column as MY_COLUMN FROM my_table The above SELECT will fold the alias name as my_column and not MY_COLUMN. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 9:05 PM, Bruce Momjian wrote: > Yes, both the identifier names and alias names are folded to lower case. > I never thought of them as different, but you are right, they are, and > we are non-standard in both areas. Sorry. Any plans to fix this in next release or having a patch to fix this? The default PostgreSQL behavior is folding column names to lower case but when an alias is used it should fold as per alias name. Note that adding quotes for aliases will be blessed by PostgreSQL and then those will be folded to upper case BUT this adds up to lot of changes in the SQL layer. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 9:35 PM, Thomas Kellerer wrote: > I wonder why you need that. I never had the requirement for that. > > Which driver/interface do you use that requires an alias to be all > uppercase? Looks like you didn't get me, am not saying aliases to be all upper case by default (BTW upper case behavior is seen in Oracle by default which is exactly reverse of postgres). But what I am asking here is if an alias name is provided be it upper case, lower case, or a mix then shouldn't it be preserved as as it is given. All this talk is when alias names are unquoted, when quoted then its standard behavior as seen in other databases. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Wed, Jul 3, 2013 at 9:50 PM, Tom Lane wrote: > > Any plans to fix this in next release or having a patch to fix this? > > No. > > This has been discussed (many times) before. There isn't any feasible > way to change this behavior without breaking an incredible amount of > code, much of which isn't even under our control. The marginal increase > in standards compliance is not worth the pain --- especially when the > aspect of the standard in question isn't even one that most of us like. > (All-upper-case is hard to read.) > > If this is a deal-breaker for you, then I'm sorry, but you need to find > another database. Postgres settled on this behavior fifteen years ago, > and we're not changing it now. > Again my question was not to change the default behavior and make then All-upper-case as there is no need to change it. Rather it was for alias names and if those are with upper case or mix case then wouldn't it will be good to preserve same. For me these changes to have aliases quoted are manageable and I was just checking for any thoughts here. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Thu, Jul 4, 2013 at 12:38 AM, Alvaro Herrera wrote: > Aliases are treated just like any other identifier. The downcasing > happens in the lexer (src/backend/parser/scan.l), which is totally > unaware of the context in which this is happening; so there's no way to > tweak the downcasing behavior for only aliases and not other > identifiers. > Oh OK, that makes sense and yes can see through complexity. So I believe when aliases are quoted then the scan process might be different and hence quotes aliases TEXT CASE gets preserved. Regards...
Re: [SQL] Unquoted column names fold to lower case
On Thu, Jul 4, 2013 at 1:36 AM, Bruce Momjian wrote: > And let's not forget that column aliases can be used as indentifiers in > queries: > > test=> SELECT 1 AS x > test-> ORDER BY x; > x > --- > 1 > (1 row) > > test=> SELECT 1 AS "X" > ORDER BY x; > ERROR: column "x" does not exist > LINE 2: ORDER BY x; > > Changing this would mean that the same identifier would have different > case-folding rules depending on where it appeared in the query. Sorry but I am not sure about your point here. Currently if the alias is quoted then same needs to be used in queries as identifies: SELECT 1 AS "X" ORDER BY "X"; Regards...
Re: [SQL] Unquoted column names fold to lower case
On Thu, Jul 4, 2013 at 1:17 AM, Tom Lane wrote: > Quite aside from implementation difficulty, restricting the change to > just column aliases doesn't make it more palatable. You'd entirely lose > the argument that the change increases spec compliance, because the spec > is perfectly clear that a column alias is an identifier just like any > other. And you'd still be paying a large part of the application > breakage costs, because the identifiers coming back in query descriptors > are one of the main ways applications would notice such a change. True, applications will notice and map only the identifiers coming back in query descriptor and in cases when the application reading these resultsets is case-sensitive for descriptors then breakage cost will be there. Hence was all this discussion to reduce the application breakage cost in particular cases. Regards...
Re: [SQL] Change of data type
Use Alter table notebook from control center right click on the table and open alter table notebook you can drop a column or add a column or change the datatype Kumar On 8/7/06, Judith <[EMAIL PROTECTED]> wrote: Hello everybody, excuse me how can I change de data type of a field,I currently have: material character(30) but I now want the field in text type like this: material text somebody knows if ALTER TABLE has some option to do this?, or Howcan I do that?---(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 -- KumarDB2 DBA & SAP Basis professional
