[SQL] Regular expression query
I have a large group of records which were entered with trailing garbage, in the form of superfluous \n. The main problem this has caused, other than the obvious one, is that the record in question is being used as a primary key, so some duplicates have slipped through. I assumed a simple statement like: SELECT url FROM sites WHERE url ~ url || '\\s+' ...would allow me to find all the duplicate-but-not-quite records. While this concatenation works with the LIKE directive (ie LIKE url || '%'), postgresql barfs on it in a regexp with the error: ERROR: Unable to identify an operator '||' for types 'bool' and 'unknown' You will have to retype this query using an explicit cast Encapsulating the concatenation in brackets leads the query parser to stop bleating. I presume this is simply a limitation in the parser's ability to make inferences about regexps vs. LIKEs. The other aspect of this is that it seems that postgresql's regexp engine doesn't understand some expected regexps; I've tried both escaped and unescaped versions of, eg \w, \s, \n and so on a pg seems to ignore them. Am I exceeding the capabilities of the regexp parser? -- Rodger Donaldson[EMAIL PROTECTED] I just had this vision of a young boy cowering in terror, whispering: "I see dumb people" -- Steve VanDevender
Re: [SQL] Wild Cards
On Thu, Jan 25, 2001 at 02:29:55PM -0500, Brett W. McCoy wrote: > On Thu, 25 Jan 2001, wrote: > > > I am not able to get Wildcards in PostgreSQL, I know its * (asterisk), but > > its not working. can someone show me a example or something? > > Wildcards where? You can use * to mean all the fields in a table in a > SELECT statement, but if you are using LIKE in a WHERE clause, the > wildcards are % to mean any group of characters and _ to mean any single > character. Although, of course, you can use POSIXlish regexps with the ~* and ~ operators. -- Rodger Donaldson[EMAIL PROTECTED] "My mother made me a lesbian" "Oh goody! If I buy her the wool, will she make me one too??"
Re: [SQL] CSV import
> --- Oliver Vecernik <[EMAIL PROTECTED]> wrote: > > Is there a direct way to import such files into > > PostgreSQL? > > > > As I believe others have replied: no, not yet. > > Otherwise, parsing CSV > files gets just too complicated, and you are better > off using an existing solution (like a Perl module) to > preprocess your data. The DBD::CSV module allows one to use a subset of SQL syntax on CSV files, as an example. Docs are at http://search.cpan.org/author/JZUCKER/DBD-CSV-0.2002/lib/DBD/CSV.pm -- Rodger Donaldson [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Cross-table constraints
The problem: I have two tables, add_queue and sites, on a postgresql 7.2.x database. add_queue is where items go to be reviewed by a human before being moved into sites. One of the things I'd like to do is to cut down on the amount of work done by humans filtering out dupes and sundry other problems. I'd prefer to keep the logic entirely within the DB, rather than on the client side, to make it consistent across the various interfaces into add_queue. Now, adding a UNIQUE constraint on the pk for add_queue weeds out dupes there. However, attempting to add a cross-table UNIQUE check with: alter table add_queue add constraint add_queue_no_dupe_sites unique (sites.url); ERROR: parser: parse error at or near "." ,,,fails. Foreign key constraints will only require a match (of one sort or another) in the foreign table, according to the postgresql 7.2 documentation, and do not have an option to require no match. Am I missing something obvious (syntax for UNIQUE, for example), or trying to do something that just doesn't work that way? -- Rodger Donaldson[EMAIL PROTECTED] "How do I set my laser printer for stun?" -- William Tansil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Cross-table constraints
> On Tue, 28 Jan 2003, Rodger Donaldson wrote: > > > Now, adding a UNIQUE constraint on the pk for add_queue weeds out > > dupes there. However, attempting to add a cross-table UNIQUE check > > with: > > > > alter table add_queue add constraint add_queue_no_dupe_sites unique > > (sites.url); > > ERROR: parser: parse error at or near "." > > > > ,,,fails. Foreign key constraints will only require a match (of one > > sort or another) in the foreign table, according to the postgresql 7.2 > > documentation, and do not have an option to require no match. > > > > Am I missing something obvious (syntax for UNIQUE, for example), or > > trying to do something that just doesn't work that way? > > I'm not sure what a unique constraint on sites.url on table add_queue > is supposed to mean precisely. Sorry; to clarify, it's table.column notation. > You can probably come pretty close by making a set of triggers on > both tables however. That's what I feared 8). -- Rodger Donaldson [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] to_date has beaten me...
Postgresql 7.2.3
insert
intolog_entries
values (to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/:HH24:MI:SS'),0,302,
[... various other data elided ...]);
Always inserts the correct date, but sets the time to midnight.
Thinking this might be solved by shuffling with the source data, I've
tried such combinations as:
to_date('06/Feb/2003:11:29:11', 'DD/Mon/:HH24:MI:SS')
to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/:HH:MI:SS')
to_date('06/02/2003:11:29:11', 'DD/MM/:HH24:MI:SS')
...and so forth, but despite the column in question being a timestamp
with timszone, everything except the date gets truncated.
--
Rodger Donaldson[EMAIL PROTECTED]
"My ATEX terminal isn't working"
"Is there power to the keyboard?"
"No, and it has smoke and flames coming out of it"
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] to_date has beaten me...
On Wed, Feb 05, 2003 at 11:22:57PM -0500, Tom Lane wrote:
> Rodger Donaldson <[EMAIL PROTECTED]> writes:
> > values (to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/:HH24:MI:SS'),0,302,
>
> > Always inserts the correct date, but sets the time to midnight.
>
> Um, well, yeah ... it's to_DATE, meaning it produces a result of type
> date. I think you wanted to_timestamp().
Why yes. Yes I did. Too much time spent with Oracle, evidently.
Next I'll be wondering why DECODE doesn't work...
--
Rodger Donaldson[EMAIL PROTECTED]
"How do I set my laser printer for stun?"
-- William Tansil
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] set decimal point in SQL
On Mon, Jun 16, 2003 at 05:02:34PM -0500, Bu, Ning wrote: > I have a field set up as money and I try to calculate some number > and put in this field, but the number will be 6 demical point and I > want to cut it to only 2-4 decimal point, how can I do it? Thanks round(); eg select round(foo, 2) frombar ...will give you values in column foo rounded to 2 decimal places. -- Rodger Donaldson[EMAIL PROTECTED] I don't mind straight people, as long as they act gay in public ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Applying SUM twice in the same query.
I have a table structured: server_name|server_version |number ---++-- Apache |1.3.11 | 1 Netscape-Enterprise|2.01| 1 Apache |1.1.3 | 2 Initially I wanted to extract the total count by server_name; this is pretty straightforward: select server_name, sum(number) from web_servers group by server_name; ...gives the result I expect. server_name|sum ---+--- Apache | 64 Draupnir | 1 I also want the total number of of servers. This can be obtained with: select sum(number) from web_servers; Again, works fine. What I really want to do with total is to derive a proportion of number of server:number of servers, something like: server_name|number|total ---+--+- AiNET Apache | 1| 84 Apache |64| 84 Draupnir | 1| 84 The problem is, I can't forumlate a query to produce it. The closest I got was: SELECT ws1.server_name, SUM(ws1.number) AS number, SUM(ws2.number) AS total FROM web_servers AS ws1, web_servers AS ws2 GROUP BY ws1.server_name; ...which is producing: server_name|number|total ---+--+- AiNET Apache |19| 84 Apache | 1216| 840 Draupnir |19| 84 I assume I'm creating a product of the query, but I'm not sure how to fix it. -- Rodger Donaldson[EMAIL PROTECTED] Driving that fast may also be an autoLART because the acceleration required to reach a high fraction of c in just a few kilometres may be difficult to withstand. You will also need a better fuel than gasoline. -- Steve VanDevender
Re: [SQL] Database authentication and configuration
On Tue, Jul 18, 2000 at 12:01:46PM +1000, Carolyn Lu Wong wrote: > Are there anywhere to configure so that whenever I call 'psql dbname', > it'll always prompt for user authentication instead of getting into the > database directly? Take a look at the configuration of your pg_hba.conf file. You will most likely find that you're set up to trust local users. You can force authentication by changing this to password, crypt, or kerberos based authentication. -- Rodger Donaldson[EMAIL PROTECTED] "Forgive us if we bite your head off; we were led to assume you weren't using it in the first place" --Jim Allenspach, in comp.lang.perl.misc
