Re: [SQL] Foreign Unique Constraint
Am Mittwoch, 28. März 2007 00:17 schrieb Phillip Smith: > Perhaps this...? It would work, but depending how many rows are in the > table, it could become incredibly slow. > > ALTER TABLE table1 ADD CHECK (extension NOT IN (SELECT extension FROM > table2)); Subqueries are not allowed in check constraints. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Regular Expressions [progress]
Richards and List,
Now I find out the 'similar to' statement where I can do such search, but I
must still parse all substrings.
Here is my stage:
Select * from myAddressTable where address similar to ('%(ELMO|ST.|258)%')
But I still need to Separete all strings.
Could someone help me in this crusade ?
Regards
Ezequias
Em Tue, 27 Mar 2007 15:16:10 -0700 (PDT)
Richard Broersma Jr <[EMAIL PROTECTED]> escreveu:
Could you give me a hand ?
I have a ZipCode table and my address table
I just would like to find out all matches that my zipcode table has where my
address table appears like this:
Elmo Street, 30
I would like my SQL find out all matches we can find 'Elmo', 'Street'.
select zipcode
from zipzodetable
where address ~ 'Elmo'
and address ~ 'Street';
If the query is too slow I expect that installing the tsearch2 contrib module
and using the
tsearch2 type queries would give you want you wanted but in a fraction of the
time.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
--
Ezequias Rodrigues da Rocha
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Foreign Unique Constraint
Subqueries are not allowed in check constraints. I hadn't tried this yet but i'm sure i would have figured this out quickly then =) Thanks, Jon ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Regular Expressions [progress]
> Select * from myAddressTable where address similar to ('%(ELMO|ST.|258)%')
>
> But I still need to Separete all strings.
What is it that you are trying to achieve? What string would you like to
seperate?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] union with count?
Hi dudes, i have the following question i have 2 tables with the same format, and i want to know if is there a possibility of using some sort of count(*) for achieving this result: select a from table1 where (condition) union select a from table2 where (condition), count(a) group by a The idea is having how many times (condition) is true for both tables. Im not shure im explaining myself clearly, my english is not the best (as you can see ;) Thanks! Gerardo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] union with count?
am Wed, dem 28.03.2007, um 11:04:29 -0300 mailte Gerardo Herzig folgendes: > Hi dudes, i have the following question > i have 2 tables with the same format, and i want to know if is there a > possibility of using some sort of count(*) for achieving this result: > select a from table1 where (condition) union select a from table2 where > (condition), count(a) group by a > > The idea is having how many times (condition) is true for both tables. > > Im not shure im explaining myself clearly, my english is not the best > (as you can see ;) You can use a subselect, a simple example: test=# select *, count(1) from (select 1 union select 2 union select 3) foo group by 1; ?column? | count --+--- 1 | 1 2 | 1 3 | 1 (3 rows) 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] union with count?
--- Gerardo Herzig <[EMAIL PROTECTED]> wrote: > Hi dudes, i have the following question > i have 2 tables with the same format, and i want to know if is there a > possibility of using some sort of count(*) for achieving this result: > select a from table1 where (condition) union select a from table2 where > (condition), count(a) group by a I think you are showing count(a) in the wrong spot in your example. But if I understand you correctly the following should do what you want: SELECT count(a) FROM ( SELECT a FROM table1 WHERE (your_condition = true) UNION -- you can use UNION ALL if you -- want rows that are duplicates from each table. SELECT a FROM table2 WHERE (your_condition = true) ) GROUP BY a; ---(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] union with count?
Thanks! But now i have another problem related with count():
select page_id, word, word_position, count(page_id) from (select * from
search_word('word1', 'table1') union search_word('word2', 'table2')) foo
group by page_id;
and gives me "foo.word must appear in GROUP clause or be used in an
aggregate function"
And i want to group by page_id only, because that is what i need to
count. Tips here?
Thanks again man.
Gerardo
am Wed, dem 28.03.2007, um 11:04:29 -0300 mailte Gerardo Herzig folgendes:
Hi dudes, i have the following question
i have 2 tables with the same format, and i want to know if is there a
possibility of using some sort of count(*) for achieving this result:
select a from table1 where (condition) union select a from table2 where
(condition), count(a) group by a
The idea is having how many times (condition) is true for both tables.
Im not shure im explaining myself clearly, my english is not the best
(as you can see ;)
You can use a subselect, a simple example:
test=# select *, count(1) from (select 1 union select 2 union select 3) foo
group by 1;
?column? | count
--+---
1 | 1
2 | 1
3 | 1
(3 rows)
Andreas
---(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
[SQL] Rules with sequence columns
I have the following situation that I would appreciate your input on: I have a table with a column that I use to uniquely identify its rows. The table also has a rule on insert that stores the row identifier into another table for reference at some other point. The table is defined as CREATE SEQUENCE foo_seq; CREATE TABLE foo ( fooK INTEGER DEFAULT NEXTVAL ( 'foo_seq' ), fooN VARCHAR(32) NOT NULL UNIQUE, link INTEGER NOT NULL DEFAULT 0 ); The rule does an insert into another table and I have implemented the rule in two ways. CREATE RULE insertCD AS ON INSERT TO foo DO INSERT INTO cdFoo ( contextK, componentK ) SELECT currval ( 'foo_seq' ), componentK FROM Component WHERE componentN = 'Division'; or CREATE RULE insertCD AS ON INSERT TO foo DO INSERT INTO cdFoo ( contextK, componentK ) SELECT new.fooK, componentK FROM Component WHERE componentN = 'Division'; The situation is that every time the rule fires, the foo sequence is incremented for each row in the foo table. and the reference value is not the same in the table. I have tried to take the default nextval ( 'foo_seq' ) from the row initialization and move it to the insert insert into foo ( fook, fooN ) values ( nextval ( 'foo_seq' ), 'Name' ); with the same result. The only way I have been able to make it work is ugly. int fooK = select nextval ( 'foo_seq' ); insert into foo ( fooK, fooN ) values ( fooK, 'Name' ); Does anyone have any suggestion? ---(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] Rules with sequence columns
Ray Madigan <[EMAIL PROTECTED]> writes: > Does anyone have any suggestion? Use a trigger to propagate the data to the other table. You can't make this work reliably with a rule, because rules are macros and hence inherently subject to double-evaluation problems when dealing with volatile functions. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] union with count?
Gerardo Herzig <[EMAIL PROTECTED]> schrieb:
> Thanks! But now i have another problem related with count():
>
> select page_id, word, word_position, count(page_id) from (select * from
> search_word('word1', 'table1') union search_word('word2', 'table2')) foo
> group by page_id;
>
> and gives me "foo.word must appear in GROUP clause or be used in an
> aggregate function"
>
> And i want to group by page_id only, because that is what i need to count.
> Tips here?
You can't aggregate without grouping the not-aggregated columns.
Expand your 'group by' with word and word_position.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] How too select different views using a IF/THEN/ELSE procedure ?
Below is a select statement that select incidents by month. I need a function or a method to select differents views that will show the Month columns is a different order. Say Apr - Mar for a fiscal year. I need to do something like an if/then/else statement that selects the correct view to use by a variable check. Any ideas what I can use or do ? select case public.incident.gender_code_id WHEN 31 THEN 'Male' WHEN 32 THEN 'Female' ELSE 'Non-Person' END, count (case extract ( month from public.incident.incident_date )WHEN 01 then 1 Else NULL END) as Jan, count (case extract ( month from public.incident.incident_date )WHEN 02 then 1 Else NULL END) as Feb, count (case extract ( month from public.incident.incident_date )WHEN 03 then 1 Else NULL END) as Mar, count (case extract ( month from public.incident.incident_date )WHEN 04 then 1 Else NULL END) as Apr, count (case extract ( month from public.incident.incident_date )WHEN 05 then 1 Else NULL END) as May, count (case extract ( month from public.incident.incident_date )WHEN 06 then 1 Else NULL END) as Jun, count (case extract ( month from public.incident.incident_date )WHEN 07 then 1 Else NULL END) as Jul, count (case extract ( month from public.incident.incident_date )WHEN 08 then 1 Else NULL END) as Aug, count (case extract ( month from public.incident.incident_date )WHEN 09 then 1 Else NULL END) as Sep, count (case extract ( month from public.incident.incident_date )WHEN 10 then 1 Else NULL END) as Oct, count (case extract ( month from public.incident.incident_date )WHEN 11 then 1 Else NULL END) as Nov, count (case extract ( month from public.incident.incident_date )WHEN 12 then 1 Else NULL END) as Dec, count (extract ( month from public.incident.incident_date )) as Total from public.incident GROUP BY public.incident.gender_code_id
[SQL] Track query status
Hi all,
I have ran a update query on a dataset which has about 48 million records
and the query is already running for the third dayim so tempted to the
kill this query nowis there a way to know if the query is running??
here is the query i've ran
UPDATE ma SET av=to_tsvector('default',coalesce(name_first,'') ||' '||
coalesce(name_last,''));
Thanks,
Sumeet
Re: [SQL] Track query status
On Wed, Mar 28, 2007 at 04:59:24PM -0400, Sumeet wrote: > Hi all, > > I have ran a update query on a dataset which has about 48 million records > and the query is already running for the third dayim so tempted to the > kill this query nowis there a way to know if the query is running?? > here is the query i've ran 48 million records is a lot. You oughta see activity with iostat or something. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(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] Foreign Unique Constraint
Is that an 8.2 thing? I'm sure I've done it before, probably in 8.1 Maybe it was a trigger I did it in - I can't remember what I had for breakfast, let alone a slow sub-query I did months ago :P Cheers, ~p -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Peter Eisentraut Sent: Wednesday, 28 March 2007 19:08 To: pgsql-sql@postgresql.org Cc: Phillip Smith; 'Jon Horsman' Subject: Re: [SQL] Foreign Unique Constraint Am Mittwoch, 28. März 2007 00:17 schrieb Phillip Smith: > Perhaps this...? It would work, but depending how many rows are in the > table, it could become incredibly slow. > > ALTER TABLE table1 ADD CHECK (extension NOT IN (SELECT extension FROM > table2)); Subqueries are not allowed in check constraints. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(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] SELECT INSTEAD
Hi all, I'm trying to create a view of the query below, but I'm being barked at about "rules on SELECT must have action INSTEAD SELECT". I don't have any rules in my database, and I don't know how this query is trying to create one. The query does work if I just run it interactively. Any help would be much appreciated. SELECT DISTINCT ON (m1.id) UPPER(m1.id) AS id, UPPER(m1.first_name) AS first_name, UPPER(m1.last_name) AS last_name, UPPER(m1.company) AS company, UPPER(m1.address1) AS address1, UPPER(m1.address2) AS address2, UPPER(m1.suburb) AS suburb, UPPER(m1.state) AS state INTO TEMP TABLE mail_duplicates FROMmaillist as m1 INNER JOIN maillist as m2 ON ( m1.id <> m2.id AND UPPER(m1.first_name) = UPPER(m2.first_name) AND UPPER(m1.last_name) = UPPER(m2.last_name) AND UPPER(m1.suburb) = UPPER(m2.suburb) ) WHERE TRIM(m1.first_name) <> '' AND TRIM(m1.last_name) <> '' ; SELECT d.id AS "ID", INITCAP(d.first_name) AS first_name, INITCAP(d.last_name) AS last_name, INITCAP(d.company) AS company, INITCAP(d.address1) AS address1, INITCAP(d.address2) AS address2, d.suburb AS suburb, d.state AS state FROMmail_duplicates AS d ORDER BY d.last_name, d.first_name, d.suburb, d.id ; ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
Re: [SQL] SELECT INSTEAD
On Thu, Mar 29, 2007 at 03:35:52PM +1000, Phillip Smith wrote: > I'm trying to create a view of the query below, but I'm being barked at > about "rules on SELECT must have action INSTEAD SELECT". I don't have any > rules in my database, and I don't know how this query is trying to create > one. http://www.postgresql.org/docs/8.2/interactive/rules-views.html "Views in PostgreSQL are implemented using the rule system." > The query does work if I just run it interactively. Any help would be > much appreciated. You showed two queries, not one. The error occurs because you're trying to create a view that creates a temporary table; you'll need to write the two queries as a single query that doesn't use a temporary table. Also, are you sure you need DISTINCT ON (m1.id)? What do you intend for that to do? Is id unique (PRIMARY KEY or UNIQUE)? And unless ORDER BY is necessary to determine the result set (as with DISTINCT ON) then consider leaving it out of the view definition -- if the outermost query (the query that selects from the view) needs a certain order than that's the proper place for ORDER BY. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT INSTEAD
Thanks Michael - The 2 queries (sorry, should have said transaction) are a simple way to identify duplicates in our mailing list. If there are triplicates, the first query will return 6 rows into the temp table, 1 for each of the 2 duplicates), but I only need to show the 3 triplicates once each, not twice each. The DISTINCT ON does that, and that's basically the whole reason I need to put it in to a temp table and re-select from that, otherwise I have to sort by the id column (ORDER BY must match DISTINCT ON expression). Sorting by the ID column doesn't "group" the duplicate / triplicate rows together for review - that's why I need the ORDER BY. I guess I could remove the ORDER BY, which eliminates my need to use the temp table and 2 queries, then do the ordering when I select the view...? Cheers, ~p -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Thursday, 29 March 2007 16:49 To: Phillip Smith Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] SELECT INSTEAD On Thu, Mar 29, 2007 at 03:35:52PM +1000, Phillip Smith wrote: > I'm trying to create a view of the query below, but I'm being barked at > about "rules on SELECT must have action INSTEAD SELECT". I don't have any > rules in my database, and I don't know how this query is trying to create > one. http://www.postgresql.org/docs/8.2/interactive/rules-views.html "Views in PostgreSQL are implemented using the rule system." > The query does work if I just run it interactively. Any help would be > much appreciated. You showed two queries, not one. The error occurs because you're trying to create a view that creates a temporary table; you'll need to write the two queries as a single query that doesn't use a temporary table. Also, are you sure you need DISTINCT ON (m1.id)? What do you intend for that to do? Is id unique (PRIMARY KEY or UNIQUE)? And unless ORDER BY is necessary to determine the result set (as with DISTINCT ON) then consider leaving it out of the view definition -- if the outermost query (the query that selects from the view) needs a certain order than that's the proper place for ORDER BY. -- Michael Fuhr ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT INSTEAD
Phillip Smith wrote: I'm trying to create a view of the query below, but I'm being barked at about "rules on SELECT must have action INSTEAD SELECT". I don't have any rules in my database, and I don't know how this query is trying to create one. The query does work if I just run it interactively. Any help would be much appreciated. You have two queries here - which one is giving the problem? SELECT DISTINCT ON (m1.id) ... ; SELECT d.id AS "ID", ... ; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
