Re: [SQL] Loading lots of data in a SQL command
frank church wrote: I am load lots of data via SQL into a database and wrapping it into transactions speeds it up. However this fails a number of times. The queries results are logged so it is easy for me to find problem records. However a single failure causes the whole transaction to fail. > Is there a setting or feature that allows which allows the same performance as transactions, without causing the whole process to fail, like a delayed updates or write mechanism of some sort. Not as it stands. I tend to use a small perl wrapper myself that loads in batches of e.g. 1 rows and if there is an error deal with it separately. I seem to recall it being discussed as a built-in feature recently though, so there might be someone working on it for a future version. It is something I would like to set in that particular data looad. You might find the "pgloader" project meets your needs exactly: http://pgfoundry.org/projects/pgloader/ -- Richard Huxton Archonet Ltd ---(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] Regular Expression Matching problem...
I have a table containing regular expresion patterns for matching phone
numbers with the mobile operators.
For instance, my phone number is '+385911234567', and the regexp for
this mobile operator is: "^\+38591\d{7}$".
Now, when I do a regexp match in a single select, it behaves as
expected:
octopussy2=# select '+385911234567' ~ '^\\+38591\\d{7}$';
?column?
--
t
(1 row)
Now, as I've mentioned, I have a table with operators and their patterns
for phone numbers:
octopussy2=# select * from operators;
operator_id | operator_name | operator_phonenumber_pattern
-+---+--
1 | FreeSMSC | ^\\+38590\\d{6,7}$
2 | VipNet| ^\\+38591\\d{7}$
3 | T-Mobile | ^\\+3859[9|8]\\d{6,7}$
4 | Tele2 | ^\\+38595\\d{7}$
(4 rows)
Now, if I construct my query like this:
octopussy2=# select '+385911234567', operator_phonenumber_pattern,
'+385911234567' ~ operator_phonenumber_pattern from operators;
?column?| operator_phonenumber_pattern | ?column?
---+--+--
+385911234567 | ^\\+38590\\d{6,7}$ | f
+385911234567 | ^\\+38591\\d{7}$ | f
+385911234567 | ^\\+3859[9|8]\\d{6,7}$ | f
+385911234567 | ^\\+38595\\d{7}$ | f
(4 rows)
Why do I get all the "f"'s? I tought that the operator_id 2 should
return "t", esp. when I wrote the first query it seems that the regex
match was ok.
Or I can't do regexp matching from the table columns?
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Regular Expression Matching problem...
am 04.01.2006, um 15:08:45 +0100 mailte Mario Splivalo folgendes:
> I have a table containing regular expresion patterns for matching phone
> numbers with the mobile operators.
>
> For instance, my phone number is '+385911234567', and the regexp for
> this mobile operator is: "^\+38591\d{7}$".
>
> Now, when I do a regexp match in a single select, it behaves as
> expected:
>
> octopussy2=# select '+385911234567' ~ '^\\+38591\\d{7}$';
> ?column?
> --
> t
> (1 row)
>
> Now, as I've mentioned, I have a table with operators and their patterns
> for phone numbers:
>
> octopussy2=# select * from operators;
> operator_id | operator_name | operator_phonenumber_pattern
> -+---+--
>1 | FreeSMSC | ^\\+38590\\d{6,7}$
>2 | VipNet| ^\\+38591\\d{7}$
>3 | T-Mobile | ^\\+3859[9|8]\\d{6,7}$
>4 | Tele2 | ^\\+38595\\d{7}$
> (4 rows)
>
>
> Now, if I construct my query like this:
>
> octopussy2=# select '+385911234567', operator_phonenumber_pattern,
> '+385911234567' ~ operator_phonenumber_pattern from operators;
>
>?column?| operator_phonenumber_pattern | ?column?
> ---+--+--
> +385911234567 | ^\\+38590\\d{6,7}$ | f
> +385911234567 | ^\\+38591\\d{7}$ | f
> +385911234567 | ^\\+3859[9|8]\\d{6,7}$ | f
> +385911234567 | ^\\+38595\\d{7}$ | f
> (4 rows)
>
>
> Why do I get all the "f"'s? I tought that the operator_id 2 should
> return "t", esp. when I wrote the first query it seems that the regex
> match was ok.
Try this:
test=# select '+385911234567', operator_phonenumber_pattern, '+385911234567' ~
replace(operator_phonenumber_pattern,'','\\') from operators;
?column?| operator_phonenumber_pattern | ?column?
---+--+--
+385911234567 | ^\\+38590\\d{6,7}$ | f
+385911234567 | ^\\+38591\\d{7}$ | t
+385911234567 | ^\\+3859[9|8]\\d{6,7}$ | f
+385911234567 | ^\\+38595\\d{7}$ | f
(4 rows)
HTH, Andreas
--
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
===Schollglas Unternehmensgruppe===
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Regular Expression Matching problem...
On Wed, Jan 04, 2006 at 03:50:31PM +0100, A. Kretschmer wrote:
> Try this:
>
> test=# select '+385911234567', operator_phonenumber_pattern, '+385911234567'
> ~ replace(operator_phonenumber_pattern,'','\\') from operators;
>?column?| operator_phonenumber_pattern | ?column?
> ---+--+--
> +385911234567 | ^\\+38590\\d{6,7}$ | f
> +385911234567 | ^\\+38591\\d{7}$ | t
> +385911234567 | ^\\+3859[9|8]\\d{6,7}$ | f
> +385911234567 | ^\\+38595\\d{7}$ | f
> (4 rows)
What Andreas is saying is that the patterns in the table have too
many backslashes. The original query was
test=> select '+385911234567' ~ '^\\+38591\\d{7}$';
?column?
--
t
(1 row)
but if you select just the pattern you get
test=> select '^\\+38591\\d{7}$';
?column?
^\+38591\d{7}$
(1 row)
which isn't what's in the table; somehow the patterns in the table
are over-escaped. You might want to read up on how backslashes are
handled in quoted strings, especially when those strings are used
as regular expressions. If you're using 8.0 or later then also
read about dollar-quoted strings.
http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-STRINGS
http://www.postgresql.org/docs/8.1/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP
(Use the documentation for whatever version of PostgreSQL you're
running; the above links have some comments that apply only to 8.1
and future versions).
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] JOIN question with multiple records
I have 2 tables. One containing information about servers, and the other containing information about IP addresses. E.G. Server table: namemac mac2 - SERVER1 00:0d:56:ba:ad:92 SERVER2 00:0d:56:ba:ad:93 00:0d:56:ba:ad:96 SERVER3 00:0d:56:ba:ad:94 SERVER4 00:0d:56:ba:ad:95 00:0d:56:ba:ad:97 Address table: ip mac 10.0.0.100:0d:56:ba:ad:92 10.0.0.200:0d:56:ba:ad:92 10.0.0.300:0d:56:ba:ad:94 10.0.0.400:0d:56:ba:ad:95 I need a query that will return all the IP addresses that match a server's mac address along with the rest of the information about the server in this format: name ip mac mac2 --- SERVER1 10.0.0.100:0d:56:ba:ad:92 10.0.0.2 SERVER2 00:0d:56:ba:ad:93 00:0d:56:ba:ad:96 SERVER3 10.0.0.300:0d:56:ba:ad:94 SERVER4 10.0.0.400:0d:56:ba:ad:95 However, the best that I've done is to return a record of the server for every IP that matches in the address table: name ip mac mac2 --- SERVER1 10.0.0.100:0d:56:ba:ad:92 SERVER1 10.0.0.200:0d:56:ba:ad:92 SERVER2 00:0d:56:ba:ad:93 00:0d:56:ba:ad:96 SERVER3 10.0.0.300:0d:56:ba:ad:94 SERVER4 10.0.0.400:0d:56:ba:ad:95 An abbreviate version of query looks like this. Ultimately there will be a WHERE condition on the query: SELECT servers.name,addresses.ipaddr,servers.application_mgr FROM servers LEFT JOIN addresses ON addresses.mac = servers.mac OR addresses.mac = servers.mac2 Does anyone have any suggestions on this? Regards, Casey ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] FOREIGN KEYs ... I think ...
I'm not sure if I'm looking at (for) the right thing or not, but if I am, then I'm not finding any useful examples :( I have two tables, simplified as: CREATE TABLE incident_summary ( id serial, subject text, status boolean ); CREATE TABLE incident_comments ( id serial, incident_id int4, body text, comment_date timestamp, status boolean ); Now, what I want to do is add a FOREIGN KEY (again, I think) that when incident_summary.status is changed (either closed, or reopened), the associated records in incident_comments are changed to the same state ... It *looks* like it should be simple enough, I want incident_comments.status to change to incident_summary.status whenever incident_summary.status changes ... since I'm finding nothing searching on FOREIGN KEYS, I'm guessing that I'm looking at the wrong thing ... So, what should I be searching on / reading for this one? Pointers preferred, especially one with some good examples :) Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] FOREIGN KEYs ... I think ...
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Now, what I want to do is add a FOREIGN KEY (again, I think) that when > incident_summary.status is changed (either closed, or reopened), the > associated records in incident_comments are changed to the same state ... Why not just get rid of the status column in incident_comments, and treat incident_summary.status as the sole copy of the state? When you need to get to it from incident_comments, you do a join. The foreign key you really ought to have here is from incident_comments.incident_id to incident_summary.id (assuming that I've understood your schema correctly). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] FOREIGN KEYs ... I think ...
On Wed, 4 Jan 2006, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Now, what I want to do is add a FOREIGN KEY (again, I think) that when incident_summary.status is changed (either closed, or reopened), the associated records in incident_comments are changed to the same state ... Why not just get rid of the status column in incident_comments, and treat incident_summary.status as the sole copy of the state? When you need to get to it from incident_comments, you do a join. I may end up getting to that point ... The foreign key you really ought to have here is from incident_comments.incident_id to incident_summary.id (assuming that I've understood your schema correctly). 'k, where I'm getting lost here is how do I get status changed in _comments on UPDATE of incident_summary.id? There doesn't seem to be anything for ON UPDATE to 'run SQL query' or some such ... or I'm reading old docs :( This is the part that I'm having a bugger of a time wrapping my head around ... 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] FOREIGN KEYs ... I think ...
Marc Is the "assumption" that anytime there are comments the status changes? If I'm reading between the lines correctly, there could be a large number of comments before the status changes. So no need to change status until explicitly needed. If there is a specific "comment" that means a status change, you could code a trigger to check for the comment and then update the "status". I don't really like this solution but it could work. The front end/user should explicitly change the "status". Marc G. Fournier wrote: On Wed, 4 Jan 2006, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Now, what I want to do is add a FOREIGN KEY (again, I think) that when incident_summary.status is changed (either closed, or reopened), the associated records in incident_comments are changed to the same state ... Why not just get rid of the status column in incident_comments, and treat incident_summary.status as the sole copy of the state? When you need to get to it from incident_comments, you do a join. I may end up getting to that point ... The foreign key you really ought to have here is from incident_comments.incident_id to incident_summary.id (assuming that I've understood your schema correctly). 'k, where I'm getting lost here is how do I get status changed in _comments on UPDATE of incident_summary.id? There doesn't seem to be anything for ON UPDATE to 'run SQL query' or some such ... or I'm reading old docs :( This is the part that I'm having a bugger of a time wrapping my head around ... 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] FOREIGN KEYs ... I think ...
> Now, what I want to do is add a FOREIGN KEY (again, I think) that when > incident_summary.status is changed (either closed, or reopened), the > associated records in incident_comments are changed to the same state ... As the other responders mentioned, from the schema you described, it doesn't look like you really need two separate tables. However, if you do, foreign keys aren't what you're looking for to solve your problem. That's not what foreign keys do. The only thing a foreign key provides is a guarantee that if any records in B (the referencing table) still reference a record in table A (the referenced table) then you cannot delete that referenced record. As far as the actual data in table B being modified to match table A, that's something completely unrelated to foreign key integrity. That is something I would recommend maintaining at the application level. Or, if you're a trigger guy, do it in a trigger. John ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
