Re: [SQL] ARRAYs and INDEXes ...
On Tue, Aug 16, 2005 at 01:54:13AM -0300, Marc G. Fournier wrote: > > CREATE TABLE customers ( > customer_id SERIAL, > monthly_balance DECIMAL(7,2)[12] > ); > > Is it possible to create an INDEX on customers.monthly_balance such that I > could do something like: > > SELECT * FROM customers WHERE monthly_balance[6] = 0.00; You could use expression indexes, one per month: CREATE INDEX customers_mb_1_idx ON customers ((monthly_balance[1])); CREATE INDEX customers_mb_2_idx ON customers ((monthly_balance[2])); etc. > SELECT * FROM customers WHERE 0.00 = any (monthly_balance); Not sure about that one. -- Michael Fuhr ---(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
[SQL] ARRAYs and INDEXes ...
Can't seem to find anything concerning this in the docs, and I don't think it is something that can be done, but figure I'll double check before I write it off completely ... If I create a table: CREATE TABLE customers ( customer_id SERIAL, monthly_balance DECIMAL(7,2)[12] ); Is it possible to create an INDEX on customers.monthly_balance such that I could do something like: SELECT * FROM customers WHERE monthly_balance[6] = 0.00; As an example ... or SELECT * FROM customers WHERE 0.00 = any (monthly_balance); Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Parentheses in FROM clause and evaluation order.
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Mon, 15 Aug 2005, Dario Bahena Tapia wrote: >> The final result seems to be the same, I just was curious about the >> standard behavior. Does the SQl says something about this execution >> order? > I believe SQL defines the order to pay attention to parens, so A join (B > join C) style clauses result in a "table" being derived from B join C and > another from A joined with that table. SQL only constrains the results, though. It does not forbid the implementation from doing the work in whatever way seems best to it, so long as the results are the same (and "same" does not consider row ordering). For example, SQL92 3.3.4.4 says A conforming implementation is not required to perform the exact sequence of actions defined in the General Rules, but shall achieve the same effect on SQL-data and schemas as that sequence. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Parentheses in FROM clause and evaluation order.
On Mon, 15 Aug 2005, Dario Bahena Tapia wrote: > The final result seems to be the same, I just was curious about the > standard behavior. Does the SQl says something about this execution > order? I believe SQL defines the order to pay attention to parens, so A join (B join C) style clauses result in a "table" being derived from B join C and another from A joined with that table. ---(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: [SQL] Problem with a Pettern Matching Check
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Mon, Aug 15, 2005 at 08:21:23PM -0400, Tom Lane wrote: >> Given that we consider trailing spaces in char(n) to be semantically >> insignificant, would it make sense to strip them before doing the >> regex pattern match? > How standards-compliant would that be? Does the standard specify > what should happen when using SIMILAR TO with a char(n) value? Hmm ... suddenly I'm getting a strong sense of deja vu ... think we've been around this merry-go-round before. SQL99 says ii) The MC LIKE PC is true if there exists a partitioning of MCV into substrings such that: 1) A substring of MCV is a sequence of 0 (zero) or more contiguous s of MCV and each of MCV is part of exactly one substring. 2) If the i-th substring specifier of PCV is an arbitrary character specifier, the i-th substring of MCV is any single . 3) If the i-th substring specifier of PCV is an arbitrary string specifier, then the i-th substring of MCV is any sequence of 0 (zero) or more s. 4) If the i-th substring specifier of PCV is neither an arbitrary character specifier nor an arbitrary string specifier, then the i-th substring of MCV is equal to that substring specifier according to the collating sequence of the , without the appending of characters to MCV, and has the same length as that substring specifier. 5) The number of substrings of MCV is equal to the number of substring specifiers of PCV. Rule ii.4 says that you use the collating sequence associated with the data values, which is where the SQL spec keeps its space sensitivity information --- but the restrictions about not adding space characters and having the same length seem to be intended to prevent use of pad-space-insensitivity to create a match. I think we read this text before, came to the same conclusion, and put in the special operator to make it behave that way. So ... never mind. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem with a Pettern Matching Check
On Mon, Aug 15, 2005 at 08:21:23PM -0400, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
> > The CHAR(3) specification causes the value to be space-padded, so
> > '1' becomes '1 ' (the digit "one" followed by two spaces).
>
> Actually, we seem to be going out of our way to make this case fail.
> Given that we consider trailing spaces in char(n) to be semantically
> insignificant, would it make sense to strip them before doing the
> regex pattern match?
How standards-compliant would that be? Does the standard specify
what should happen when using SIMILAR TO with a char(n) value?
I notice that equality and SQL regular expressions treat trailing
spaces differently -- is that intentional or accidental?
CREATE TABLE foo (test char(3));
INSERT INTO foo VALUES ('1');
SELECT test, test = '1', test SIMILAR TO '1' FROM foo;
test | ?column? | ?column?
--+--+--
1| t| f
(1 row)
SELECT test, test = '1 ', test SIMILAR TO '1 ' FROM foo;
test | ?column? | ?column?
--+--+--
1| t| t
(1 row)
> That would happen automatically if we allowed
> the char(n) value to promote to text --- and the only reason it's
> not doing so is that there's an extra ~ operator definition that
> specifically prevents that (bpcharregexeq).
"bpcharregexeq" -- didn't Philip Glass write the score to that?
--
Michael Fuhr
---(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: [SQL] SQL output
Quoting Simon Law <[EMAIL PROTECTED]>:
>
> CREATE TABLE tablename (field INTERVAL);
> INSERT INTO tablename VALUES('3 weeks');
> SELECT field FROM tablename;
> | 21 days |
>
> The output shows up in days or months but not weeks how do i make
Internally, INTERVAL is stored as a 12byte tuple (years, months, days,
hours, minutes, seconds, microseconds). It discards any knowledge of
"weeks" (and "centuries" likewise) when it encodes the interval. So
there's no way to force it to say "weeks" back to you. There is no
datestyle that will do it for you, either.
You can MANUALLY extract the number of days in the interval, and divide
by 7 (round up or down, your choice).
SELECT EXTRACT(DAYS FROM INTERVAL '3 WEEKS')
Note, however, that if you define an interval with units greater than
days (i.e. months or years) you'll get nothing, which is reasonable:
months and years do not have fixed numbers of weeks in them.
---(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: [SQL] Problem with a Pettern Matching Check
Michael Fuhr <[EMAIL PROTECTED]> writes: > The CHAR(3) specification causes the value to be space-padded, so > '1' becomes '1 ' (the digit "one" followed by two spaces). Actually, we seem to be going out of our way to make this case fail. Given that we consider trailing spaces in char(n) to be semantically insignificant, would it make sense to strip them before doing the regex pattern match? That would happen automatically if we allowed the char(n) value to promote to text --- and the only reason it's not doing so is that there's an extra ~ operator definition that specifically prevents that (bpcharregexeq). I have a feeling that we added that operator definition at some point for backwards compatibility, but it seems a bit odd now. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] SQL output
CREATE TABLE tablename (field INTERVAL);
INSERT INTO tablename VALUES('3 weeks');
SELECT field FROM tablename;
_
| field |
|--|
| 21 days |
||
The output shows up in days or months but not weeks how do i make
it output in weeks?
Any help with this will be greatly appreciated
P.S.
Best postgresql book?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Problem with a Pettern Matching Check
>> Any reason you are using char(3) instead of varchar(3)? >The numbers will have 2 or 3 digits so I tried to save some space :) Well, smallint is only 2 bytes, so it would be more compact than either char(3) or varchar(3). Dmitri The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem with a Pettern Matching Check
On Mon, 15 Aug 2005 18:37:52 -0400 "Dmitri Bichko" <[EMAIL PROTECTED]> wrote: > I'm guessing it's because char gets padded with spaces to the > specified length. argh. Thank you. > Any reason you are using char(3) instead of varchar(3)? The numbers will have 2 or 3 digits so I tried to save some space :) > And why are you storing numbers as a string, anyway? If you defined > the column as a numeric type, postgres will tell you if you try to > insert something non-numeric. Correct. I will not let the values to be used together with sum/avg/+/... > > Dmitri > -- Regards Sebastian Siewior ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem with a Pettern Matching Check
On Tue, Aug 16, 2005 at 12:19:50AM +0200, Sebastian Siewior wrote: > > create table t ( > col CHAR (3) CONSTRAINT numonly_col CHECK ( col ~ '^\\d+$' ) > ); > > This check avoids non-numbers like '1a1' and allows '123'. For some > reason, I'm unable to find out why, it also avoids things like '1' and > '12'. Could someone please give me hint? :) The CHAR(3) specification causes the value to be space-padded, so '1' becomes '1 ' (the digit "one" followed by two spaces). See "Character Types" in the documentation: http://www.postgresql.org/docs/8.0/static/datatype-character.html Do you have a reason for using a character type instead of a numeric type like integer? -- Michael Fuhr ---(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: SUSPECT: RE: Re: [SQL] Problem with a Pettern Matching Check
Can someone PLEASE do something about this guy? It's rather annoying getting this after every single post, and I am certainly not going to justify my intentions to his fancy spam service. Dmitri -Original Message- From: AntiSpam UOL [mailto:[EMAIL PROTECTED] Sent: Monday, August 15, 2005 6:34 PM To: Dmitri Bichko Subject: SUSPECT: RE: Re: [SQL] Problem with a Pettern Matching Check ANTISPAM UOL » TIRA-TEIMA Olá, Você enviou uma mensagem para [EMAIL PROTECTED] Para que sua mensagem seja encaminhada, por favor, clique aqui Esta confirmação é necessária porque [EMAIL PROTECTED] usa o Antispam UOL, um programa que elimina mensagens enviadas por robôs, como pornografia, propaganda e correntes. As próximas mensagens enviadas para [EMAIL PROTECTED] não precisarão ser confirmadas*. *Caso você receba outro pedido de confirmação, por favor, peça para [EMAIL PROTECTED] incluí-lo em sua lista de autorizados. Atenção! Se você não conseguir clicar no atalho acima, acesse este endereço: Hi, You´ve just sent a message to [EMAIL PROTECTED] In order to confirm the sent message, please click here This confirmation is necessary because [EMAIL PROTECTED] uses Antispam UOL, a service that avoids unwanted messages like advertising, pornography, viruses, and spams. Other messages sent to [EMAIL PROTECTED] won't need to be confirmed*. *If you receive another confirmation request, please ask [EMAIL PROTECTED] to include you in his/her authorized e-mail list. Warning! If the link doesn´t work, please copy the address below and paste it on your browser: Use o AntiSpam UOL e proteja sua caixa postal The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem with a Pettern Matching Check
Sebastian Siewior schrob: > Hello hopefully correct List, perfectly. > I was trying to do something that is not working as it supposed to. > First I created a table: > > create table t ( > col CHAR (3) CONSTRAINT numonly_col CHECK ( col ~ '^\\d+$' ) > ); > > This check avoids non-numbers like '1a1' and allows '123'. For some > reason, I'm unable to find out why, it also avoids things like '1' and > '12'. Could someone please give me hint? :) Char is padded with spaces, and that is also why your regexp is not matching in these situations. You could either adjust your regexp to match the trailing spaces or use varchar(3) instead: --8<---cut here---start->8--- scratch=# select '1'::char(3) ~ '^\\d+$'; ?column? -- f (1 row) scratch=# select '1'::char(3) ~ '^\\d+\\s*$'; ?column? -- t (1 row) scratch=# select '1'::varchar(3) ~ '^\\d+$'; ?column? -- t (1 row) --8<---cut here---end--->8--- regards Andreas -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Problem with a Pettern Matching Check
I'm guessing it's because char gets padded with spaces to the specified length. Any reason you are using char(3) instead of varchar(3)? And why are you storing numbers as a string, anyway? If you defined the column as a numeric type, postgres will tell you if you try to insert something non-numeric. Dmitri -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sebastian Siewior Sent: Monday, August 15, 2005 6:20 PM To: pgsql-sql@postgresql.org Subject: [SQL] Problem with a Pettern Matching Check Hello hopefully correct List, I was trying to do something that is not working as it supposed to. First I created a table: create table t ( col CHAR (3) CONSTRAINT numonly_col CHECK ( col ~ '^\\d+$' ) ); This check avoids non-numbers like '1a1' and allows '123'. For some reason, I'm unable to find out why, it also avoids things like '1' and '12'. Could someone please give me hint? :) I was trying this one on PostgreSQL 8.0.3 -- Regards Sebastian Siewior ---(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 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Problem with a Pettern Matching Check
Hello hopefully correct List, I was trying to do something that is not working as it supposed to. First I created a table: create table t ( col CHAR (3) CONSTRAINT numonly_col CHECK ( col ~ '^\\d+$' ) ); This check avoids non-numbers like '1a1' and allows '123'. For some reason, I'm unable to find out why, it also avoids things like '1' and '12'. Could someone please give me hint? :) I was trying this one on PostgreSQL 8.0.3 -- Regards Sebastian Siewior ---(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: [SQL] sql function: using set as argument
Is there a reason not to build it in as a sub-query?E.g., if you have a function get_count( int ): SELECT count(b_column)FROM some_tableWHERE some_field_1 in ( SELECT a_column FROM a_table WHERE some_condition)AND some_field_2 = $2; --Thomas F. O'ConnellCo-Founder, Information ArchitectSitening, LLCStrategic Open Source: Open Your i™http://www.sitening.com/110 30th Avenue North, Suite 6Nashville, TN 37203-6320615-469-5150615-469-5151 (fax) On Aug 10, 2005, at 9:30 AM, Akshay Mathur wrote: Hi, I have a function that returns a set. Looks like: Select a_column from a_table where some_condition; I want to use output of this function as an argument of another function Second looks like: Get_count(result_set_of_function_1, int) Select count(b_column) from some_table where some_field_1 in ($1) and some_field_2 = $2; Please suggest how can I do that? Regards, akshay ---Akshay MathurSMTS, Product VerificationAirTight Networks, Inc. (www.airtightnetworks.net)O: +91 20 2588 1555 ext 205F: +91 20 2588 1445
Re: [SQL] catch an 'update where false' ?
santiago, here are the code snippets for you that come right out of a trigger that is being used in production today. This trigger is an after update trigger, so take that into consideration if needed. I think it will do for you what you need to do. CREATE OR REPLACE FUNCTION update_rpt_history() RETURNS "trigger" AS $BODY$ DECLARE rpt_hour_hist report_history%ROWTYPE; (other unrelated variables) work_hour_curr timestamp; work_hour_usage timestamp; work_report varchar; BEGIN (other unrelated logic) perform * from report_history where key_fld1 = work_hour_curr and key_fld2 = work_report; if not found then -- if there is no curr hr rcd for report, create it insert into report_history VALUES ( work_hour_curr, work_report, rpt_hour_hist.fld-1, rpt_hour_hist.fld-2, rpt_hour_hist.fld-3, rpt_hour_hist.fld-4, rpt_hour_hist.fld-5, rpt_hour_hist.fld-6); else -- if curr hr rcd for report, update it update report_history set ifID = rpt_hour_hist.ifID, fld-1 = rpt_hour_hist.fld-1, fld-2 = rpt_hour_hist.fld-2, fld-3 = rpt_hour_hist.fld-3, fld-4 = rpt_hour_hist.fld-4, fld-5 = rpt_hour_hist.fld-5, fld-6 = rpt_hour_hist.fld-6 where key_fld1 = work_hour_curr and key_fld2 = work_report; -- neighbor end if; (other unrelated logic) RETURN NULL; END $BODY$ LANGUAGE 'plpgsql' STABLE; I am running PostgreSQL version 8.0 on Windows 2003 and Slackware Linux, if that makes a difference. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of santiago Sent: Sunday, August 14, 2005 9:13 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] catch an 'update where false' ? Hi thanks all for your answers ! to Lane Van Ingen: my purpose is to write a trigger on update so that 'users' might then us update without having to perform the if found. I don't know if I was clear about that idea, but the checks on where update will or not succeed, is what I'm trying to hide from the upper-level users for this specific table. If what your sugesting works inside such a trigger, please show my some code of how to do that, beacause it's precisely what I fail to do. to Alvaro Herrera : according to my book, triggers are only implemented whit the FOR EACH ROW clause in postgres, and not available in the FOR EACH STATEMENT case. has this changed in some new version ? which shall I use then ? thanks to both ! Alvaro Herrera wrote: > On Fri, Aug 12, 2005 at 05:13:24PM +0200, santiago wrote: > > Triggers FOR EACH ROW are called once for each updated row. I think you > could try with a "FOR EACH STATEMENT" trigger. > Lane Van Ingen wrote: > Don't know where you are doing this, but I can think of a couple ways: > (1) Do a 'select count(*) ' on what you are trying to > update first to see if it returns a count greater than 0 > (2) If in pl/pgsql functions, you can use the 'IF FOUND' or 'IF NOT FOUND' > construct to see if update was successful > > NOTE: I saw that in version 8.1 you will be able to test a PostgreSQL- > supplied variable, but it is not available until 8.1 release. > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Parentheses in FROM clause and evaluation order.
On Wed, 10 Aug 2005 [EMAIL PROTECTED] wrote: > I thought that the parenthesis in the table expression > (FROM clause), could be used to indicate the desired > evaluation order. But, I tried with a couple of samples > and the explain command returned me the same result; no matter > what parentheses association I used. I am using only INNER JOINs. > > In fact, I thought that the whole table expression was gonna be > evaluated before the WHERE filter. Does the stantard says something > about this evaluation order when the parentheses are present? > Does PostgreSQL implements this behavior? AFAIK we only try to provide final results that are equivalent to following the steps in order, so it'll reorder joins or push clauses around as long as it thinks the semantics of the query won't change. For example, actually doing unconstrainted joins before where clauses is a very bad plan if you've got a FROM table1, table2, table3 style query. If you're seeing a place where the reorder affects the query results as opposed to the query plan, that's probably a bug, can you give more information? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] catch an 'update where false' ?
santiago wrote: Hi thanks all for your answers ! to Lane Van Ingen: my purpose is to write a trigger on update so that 'users' might then us update without having to perform the if found. Of course the other option is to make sure there is always a row available to update. Impossible to say whether this is sensible without knowing the precise details of your problem. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] insert into / select from / serial problem
On Wed, 10 Aug 2005 05:03:47 +0200, tgh002 <[EMAIL PROTECTED]> wrote: I am using a insert statement like: INSERT INTO newtable SELECT field1, field2 FROM anothertable newtable structure is: serial, varchar, varchar What syntax do I use to insert the serial field? Ive tried something like: Try : INSERT INTO newtable (col1, col2) SELECT field1, field2 FROM anothertable col1, col2 being the names of your columns that you want to put field1 and field2 into... The serial will take care of itself. ---(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: [SQL] Select problems
Lucas Grijander wrote: Hi: I've just migrated from Linux/PostgreSQL 7.2 to Windows/PostgreSQL 8.0.3. I have a large view. When I make: "Select . WHERE mydate = 'anydate'" the view lasts 19 seconds to complete. But, when I make: "Select . WHERE mydate >= 'anydate'" the view lasts 7 minutes. And what are teh view definitions and the output of EXPLAIN ANALYSE? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
