Re: [SQL] weird situation, BUG or I'm not doing it right
Ross J. Reedstrom wrote:
> On Sat, Aug 24, 2002 at 10:56:31PM -0700, Jiaqing Wang wrote:
> > Hello,
> >
> > I found below situation weird, it seems to me a bug.
> >
> > backend=> select * from valid_addr where state_abrev=upper('pr');
> > zip_code | city_name | state_abrev
> > --+---+-
> > (0 rows)
> >
> > while "select * from valid_addr where state_abrev='PR';" produces
following
> > output
> >
> <20 lines of output>
>
> You left out the critical piece: what's the schema for the table
valid_addr?
> I'll deduce that the column "state_abrev" is defined as something like
> 'char(4)'. It needs to be either char(2) (if _all_ state abbreviations are
> guarenteed to be only 2) or as text. fixed with char fields are padded
with
> blanks. Not a bug, but an feature of the SQL standard.
>
> Ross
Then, why is 'PR' blank padded to char(?) and upper('pr') not?
It seems that when comparing char with text, the comparision is done
as text, not as bpchar.
billing=# select 'A'::char(2) = upper('a');
?column?
--
f
billing=# select 'A'::char(2) = upper('a')::bpchar;
?column?
--
t
Regards,
Michael Paesold
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] left join query does not perform well
Manuel Sugawara <[EMAIL PROTECTED]> writes: > Ouch, 3117.48 msec vs. 1.15 msec is a huge difference. I need > something else? or may be postgres optimizer can't cope with > left/right joins? I think the problem is you're constraining the join order into a very inefficient one. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/explicit-joins.html But it's difficult to be sure, when you are showing us EXPLAIN output that manifestly doesn't correspond to what you say the queries are. For instance the nearest match to "epr_vord_grupo AS grupo" in the explain output is "epr_ord_grupo g" ... I'm also wondering if any of the tables used in the queries are really views, and if so what the view definitions are. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] weird situation, BUG or I'm not doing it right
"Michael Paesold" <[EMAIL PROTECTED]> writes: > It seems that when comparing char with text, the comparision is done > as text, not as bpchar. Yup. Arguably this is a bad idea: the system ought to reject the comparison entirely, and make you cast one side or the other so that it's clear to all concerned which comparison semantics you want. However, I don't see any way to do that without also breaking a lot of cases that are convenient and don't confuse anyone ... like, say, the fact that you can apply upper() to char(n) data in the first place. Upper is declared as "upper(text) returns text". You might care to read the User's Guide's discussion of type conversion, http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/typeconv.html The particular behavior at hand emerges from the fact that text is considered the preferred datatype in the string category. regards, tom lane ---(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] Separating data sets in a table
On Sat, 24 Aug 2002, Mark Stosberg wrote: > On Thu, 22 Aug 2002, Andreas Tille wrote: > > Hello, > > > > I want to solve the following problem: > > > > CREATE TABLE Ref( Id int ) ; > > CREATE TABLE Import ( Idint, > > Other varchar(42), > > Flag int, > > Tstimestamp ) ; > > CREATE TABLE Data ( Idint, > > Other varchar(42) ) ; > larger problem. I get the sense that you have data you importing on a > regular basis from outside Postgres, and you want to check it before > it get moves into production, but I'm not exactly sure what's happening. You are completely right. I just do an import from an external database. The person I obtain the data from does an output of the table in a form to do a "COPY FROM". The problem is that it might happen that there are some data rows which infringe referential integrity and I have to ask back the data provider for additional data which describe additional data which are referenced by the Id mentioned above. So I have to sort out those data sets who have no known Id in my production data. Kind regards Andreas. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Separating data sets in a table
On Sun, 25 Aug 2002, Andreas Tille wrote: > On Sat, 24 Aug 2002, Mark Stosberg wrote: > > > On Thu, 22 Aug 2002, Andreas Tille wrote: > > > Hello, > > > > > > I want to solve the following problem: > > > > > > CREATE TABLE Ref( Id int ) ; > > > CREATE TABLE Import ( Idint, > > > Other varchar(42), > > > Flag int, > > > Tstimestamp ) ; > > > CREATE TABLE Data ( Idint, > > > Other varchar(42) ) ; > > larger problem. I get the sense that you have data you importing on a > > regular basis from outside Postgres, and you want to check it before > > it get moves into production, but I'm not exactly sure what's happening. > > You are completely right. I just do an import from an external database. > The person I obtain the data from does an output of the table in a form > to do a "COPY FROM". The problem is that it might happen that there are > some data rows which infringe referential integrity and I have to ask > back the data provider for additional data which describe additional data > which are referenced by the Id mentioned above. So I have to sort out those > data sets who have no known Id in my production data. Andreas, Thanks for the clarification. Here's an idea about how to solve your problem. As you are importing your data, instead of doing it all at once, try import it a row at a time into a table that has the RI turned on. Check each insert to see if it's successful. It if it's not successful, then insert that row into a table that /doesn't/ have RI (maybe "import_failures"), perhaps also including the error that Postgres returned. (This may be stored in $DBH::errstr). Then when you are done, you can look in the import_failures for a report of which rows need some assistance. If you need every row to succeed that's imported into the production table, you can do all this inside of a transaction, and roll it back if any of the inserts fail. [ thinks for a moment. ] Of course, that would normally rollback your inserts into import_failures too, so perhaps you can use a second database connection to make sure those always happen. I hope that helps. Perhaps thinking in terms of "row-at-a-time processing" will help you solve your problem. -mark http://mark.stosberg.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] how to refer to tables in another database( or schema)
On Mon, 19 Aug 2002, Stephan Szabo wrote: > On Mon, 19 Aug 2002, Jiaqing wrote: > > > Hello, > > I'm still new here and new to PostgreSQL, I'd like to know that after I > > have created two databases on my site, such as one is called backend, and > > another one is called admin, how do I refer(query) the table from backend > > while I'm connected to admin database, or is it possible to do that in > > PostgreSQL? any answer is appreciated. > > In addition to previous answers (dblink related), in 7.3 schemas will > be implemented and you may be able to use one database with two schemas > in which case normal sql should work. This isn't out yet, so it's a > future concern. One problem space that I think either of these solutions might address is the issue of having static "country code" and "state code" tables reproduced on many databases throughout an installation. Would anyone recommend either of these solutions, or another one, for addressing this issue? I'm not looking forward to the day when a new country appears, and I have to find all the places I have country code lists to add it. :) -mark http://mark.stosberg.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIMIT 1 FOR UPDATE or FOR UPDATE LIMIT 1?
I found this email from April. It properly points out that our
LIMIT/FOR UPDATE ordering doesn't match MySQL's, and MySQL's looks more
correct, specifically that the FOR UPDATE is after the LIMIT. Our
grammar is:
| select_clause sort_clause opt_for_update_clause opt_select_limit
How do we want to deal with this? I tried allowing both orderings with
the attached patch but got:
bison -y -d gram.y
conflicts: 4 shift/reduce, 5 reduce/reduce
---
Magnus Enbom wrote:
> Hi,
>
> I've just been hit by a small but annoying difference between postgres(7.2)
> and mysql(4.x).
> In postgresql you do:
>
> SELECT * FROM table FOR UPDATE LIMIT 1;
>
> and in mysql you do:
>
> SELECT * FROM table LIMIT 1 FOR UPDATE;
>
> Is it possible for postgres to accept the mysql syntax as well?
> It's not that many databases that implement LIMIT, so it would be nice if the
> ones that do have the same syntax(or can accept each others variants).
>
> -- Magnus
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: gram.y
===
RCS file: /cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.360
diff -c -r2.360 gram.y
*** gram.y 19 Aug 2002 15:08:47 - 2.360
--- gram.y 26 Aug 2002 00:29:24 -
***
*** 4114,4123
--- 4114,4135
nth(0,
$4), nth(1, $4));
$$ = $1;
}
+ | select_clause sort_clause opt_select_limit
+opt_for_update_clause
+ {
+ insertSelectOptions((SelectStmt *) $1, $2, $4,
+ nth(0,
+$3), nth(1, $3));
+ $$ = $1;
+ }
| select_clause for_update_clause opt_select_limit
{
insertSelectOptions((SelectStmt *) $1, NIL, $2,
nth(0,
$3), nth(1, $3));
+ $$ = $1;
+ }
+ | select_clause opt_select_limit for_update_clause
+ {
+ insertSelectOptions((SelectStmt *) $1, NIL, $3,
+ nth(0,
+$2), nth(1, $2));
$$ = $1;
}
| select_clause select_limit
---(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] Separating data sets in a table
On Sun, 25 Aug 2002, Mark Stosberg wrote: > Thanks for the clarification. Here's an idea about how to solve your > problem. As you are importing your data, instead of doing it all at > once, try import it a row at a time into a table that has the RI turned > on. Check each insert to see if it's successful. It if it's not > successful, then insert that row into a table that /doesn't/ have RI > (maybe "import_failures"), > perhaps also including the error that Postgres returned. (This may be > stored in $DBH::errstr). Then when you are done, you can look in the > import_failures for a report of which rows need some assistance. If you > need every row to succeed that's imported into the production table, you > can do all this inside of a transaction, and roll it back if any of the > inserts fail. [ thinks for a moment. ] Of course, that would normally > rollback your inserts into import_failures too, so perhaps you can use a > second database connection to make sure those always happen. > > I hope that helps. Perhaps thinking in terms of "row-at-a-time > processing" will help you solve your problem. Well for sure this might be an option but as I said I receive the data in the dump format apropriate to use "COPY FROM ". Would you really like to suggest me to split those data sets into single lines? Moreover I'm not sure about how to catch the error messages of failed COPY statements. I've thought that including all data and handling them afterwards would be agood idea and it is just my lack of SQL knowledge which prevents me from finding a clever solution to sort the stuff out. Kind regards Andreas. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] ANNOUNCE: Bricolage 1.3.3
The Bricolage developers are pleased to announce the release of Bricolage version 1.3.3! This the release candidate for Bricolage verion 1.4.0, and is considered feature-complete. Nearly 50 new features have been added since the 1.2.2 release, and over 80 bugs fixed. Barring any unforseen major bugs cropping up, 1.4.0 will be released within a week of this release. Please feel give it a try, and report any issues to the Bricolage Bugzilla database, at http://bugzilla.bricolage.cc/. Learn more about Bricolage and download it from the Bricolage home page, http://bricolage.cc/. General description: Bricolage is a full-featured, enterprise-class content management system. It offers a browser-based interface for ease-of use, a full-fledged templating system with complete programming language support for flexibility, and many other features. It operates in an Apache/mod_perl environment, and uses the PostgreSQL RDBMS for its repository. Enjoy! --The Bricolage Team ---(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
