[SQL] Matching a column against values in code

son to the sql statement, like this? sqlString = sqlString + " or this_column='" + arrayOfValues[i] +"' "; If someone knows a command or function I can look up in the docs, just say the name and I'll look there. Thanks a lot everyone. -- Tim

[SQL] Web Hosts (off-topic)

The Hermit Hacker can ignore this (thanks). I'm looking for a web host that provides Postgres, and runs on *nix or *BSD. Does anyone have any positive, un-solicited testimonials (or visa-versa any horror stories)? If you have a story to tell me, but you don't feel that it is appropriate to post

[SQL] PL/pgSQL "compilation error"

error near ; on line 50". Line 50 is the last line. There's probably something glaring wrong in here that I'm not seeing, but any help would be appreciated. I don't know if the \ at the end of the line is a problem, but those were added late in the game and didn't change

Re: [SQL] PL/pgSQL "compilation error"

oup_artifact_id; END IF; END IF; END IF; END IF; Yes - nesting those IFs did the trick. Are there more examples of PL/pgSQL anywhere beyond those on your website? Thanks, Bruce! Tim -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer

[SQL] Rule/currval() issue

ifact_id,count,open_count) VALUES (new.group_artifact_id,0,0); ...it doesn't fail with an error, but the sequence increments twice. Tim -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems ---(end of broadcast)-

Re: [SQL] Rule/currval() issue

On Wed, Mar 14, 2001 at 01:09:18PM -0500, Tom Lane wrote: > Tim Perdue <[EMAIL PROTECTED]> writes: > > This is related to the plpgsql project I was working on this morning. I'm > > trying to create a rule, so that when a row is inserted into a certain table, > >

Re: [SQL] PIVOT of data

Probably worth noting that this could be normalized into at least 3 tables from what I can tell. Tim On Wed, Mar 14, 2001 at 11:03:01PM +, Oliver Elphick wrote: > Srikanth Rao wrote: > >Hi, > >I have a table like this: > > location| num

[SQL] Invalid (null) int8, can't convert to float8

Hi, I have been lurking for a while and am very appreciative of the effort put in by the answers on this list, and for psql itself. I am using Postgres version 7.0.2 I have a table defined thus: CREATE TABLE "chapter" ( "id" int4 NOT NULL, "book" int4 NOT NULL, "sequ

Re: [SQL] Invalid (null) int8, can't convert to float8

Thanks a lot Tom, Tom Lane wrote: > > Tim Pizey <[EMAIL PROTECTED]> writes: > We have had some discussions about teaching the parser to be smarter > about choosing the type of numeric constants depending on context, > but for now you need to force the issue: > > s

[SQL] Vacuum Error

fear any error message that says "Frag" in it ;-) Tim -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Memory and performance

daily, extremely large query that requires a lot of grouping and sorting. Any suggestions as to how we can make more use of our RAM, paricularly during vacuum, which is when we get killed? We've actually considered mounting the database on a RAM drive in a halfway serious way. Tim -

Re: [SQL] Memory and performance

On Thu, Apr 05, 2001 at 10:46:07AM -0400, Tom Lane wrote: > Tim Perdue <[EMAIL PROTECTED]> writes: > > Our database is about 1GB in total size, the machine has 4GB, but the entire > > system is only using 1.2 GB, even during vacuum or a daily, extremely large > > q

[SQL] Re: select substr???

way to do that? Changing the data stored in the table is not an option as the suffixes are needed elsewhere.. Please help !! Thanks, Tim. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl

[SQL] Full outer join

7;ve tried several variations and keep getting the same error. Anyone have any ideas? Details below. Thanks, Tim SELECT * FROM (stats_http_downloads sh FULL OUTER JOIN stats_ftp_downloads sf USING (day,filerelease_id)) ; ERROR: FULL JOIN is only supported with mergejoinable join conditions

Re: [SQL] Full outer join

On Fri, Apr 13, 2001 at 03:02:32AM -0400, Tom Lane wrote: > Tim Perdue <[EMAIL PROTECTED]> writes: > > However, I keep getting this error, and I don't know what it means: > > ERROR: FULL JOIN is only supported with mergejoinable join conditions > > Works for me

Re: [SQL] Full outer join

On Fri, Apr 13, 2001 at 11:11:26AM -0400, Tom Lane wrote: > Tim Perdue <[EMAIL PROTECTED]> writes: > > Does it still work with those revised CREATE statements? > > Yes, works fine here. > > >> What version are you using? > > > That's RC3 and RC

[SQL] Fwd: line datatype

Probably the most succinct explanation would be to copy & paste from the terminal... tjhart=> create table a_line( foo line ); CREATE tjhart=> insert into a_line ( foo ) values( '(0,0), (1,1)' ); ERROR: line not yet implemented tjhart=> select version(); version

[SQL] line datatype

Probably the most succinct explanation would be to copy & paste from the terminal... tjhart=> create table a_line( foo line ); CREATE tjhart=> insert into a_line ( foo ) values( '(0,0), (1,1)' ); ERROR: line not yet implemented tjhart=> select version(); version

[SQL] Latitude / Longitude

ather data. None of the lat/longs in the two tables match up directly, so I can't do a simple join of the two tables. I need to join on closest proximity on the lat/long fields. Any suggestions? It seems to me this will be pretty expensive on CPU resources unless there's a really

Re: [SQL] Latitude / Longitude

ach postalcode. What I'll do is run that once and build a 3rd table which can be used to join the other two together using a view. Thanks, Tim -- Founder - PHPBuilder.com / Geocrawler.com / SourceForge GPG Public Key: http://www.perdue.net/personal/pgp.php msg07387/pgp0.pgp Descript

[SQL] Foreign character struggles

1') from latlong where ccode='BR'; ERROR: Could not convert UTF-8 to ISO8859-1 Also, my "Up Arrow" and "Delete" keys no longer work since I recompiled 7.2.3 on debian. Thanks for any help, Tim Perdue ---(end of broadcast)---

Re: [SQL] Foreign character struggles

On Fri, Oct 25, 2002 at 10:37:59AM -0400, Tom Lane wrote: > Tim Perdue <[EMAIL PROTECTED]> writes: > > I compiled postgres with --enable-multibyte and --enable-recode, and it > > doesn't appear to help with my problem. > > I think this is a locale issue,

Re: [SQL] Foreign character struggles

oth libreadline and libreadline headers (libreadline-devel > rpm, usually)? Nope it wasn't, but it is now. When I get the clarification on the above, I'll rebuild everything. Tim -- Founder - SourceForge.net / PHPBuilder.com / Geocrawler.com Perdue, Inc. 515-554-9520 --

Re: [SQL] Different size in the DATA directory

dump/restore the entire db and you'd be golden for a while again. After the dump/restore process things seemed much snappier too, and vacuum ran almost instantly. I haven't verified if this problem still occurs in 7.2.x, using vacuum full. Tim ---(end of broadcast

[SQL] Old "Feature" - Copy table ignores column defaults

awful weather data to make it work right. Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] ON DELETE CASCADE

I'm trying to comb through my database and add ON DELETE CASCADE to a number of tables where I already have fkeys in place, but I'm having a hard time. ALTER TABLE project_task DROP CONSTRAINT "project_task_group_project_id_f" RESTRICT; ERROR: ALTER TABLE / DROP CONSTRAINT: project_task_group

Re: [SQL] ON DELETE CASCADE

Marie G. Tuite wrote: Here is a function that I use to list fk(triggers) on a table. Execute the function to get the trigger name and then - drop trigger "trigger_name" on table_name; Thanks for the function. Strangely enough, it appears the "extra" triggers are gone all by themselves. It seem

[SQL] pl/pgsql question

ing stored in the db. NEW.start_date := NEW.start_date+delta; -- RAISE EXCEPTION ''new start date: % '',NEW.start_date; NEW.end_date := NEW.end_date+delta; It's probably something very obvious, but I'm mystified. Tim -- -- Function to enforce dependen

Re: [SQL] pl/pgsql question

was successfully finding those rows before, when the trigger was AFTER INSERT. If I manually select those rows after the query is committed, I am able to pull up the matching rows. Tim ---(end of broadcast)--- TIP 3: if posting/reading through Usen

Re: [SQL] pl/pgsql question

Josh Berkus wrote: Tim, That loop apparently does not find any matching rows, which would have been inserted just before this row was, inside the same transaction. It was successfully finding those rows before, when the trigger was AFTER INSERT. If I manually select those rows after the query

Re: [SQL] About primary keys.

I'm new to PostgreSQL but I am familiar with DB2, Oracle and Sybase. I must say, I am impressed with PostgreSQL so far! In order to compare databases across DBMS platforms, we need to create a view that queries from the system catalog tables. This view returns all of the columns in the database

Re: [SQL] About primary keys.

I looked in the info.c on line 2891 of the psqlodbc-7.2.5 to find this SQL logic (courtesy of Tom Lane) select ta.attname, ia.attnum from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i, pg_namespace n where c.oid = i.indrelid AND n.oid = c.relnamespace AND i.indisprimary = 't' AND ia

Re: [SQL] About primary keys -- made some progress

This might not be the cleanest solution, but it runs fast and it retrieved the information I need. I broke it down into pieces and created several views to query from to simplify it for myself. The first four statements are views and the last one is the query I was originally trying to get. (note

Re: [SQL] Migration from db2 to postgres'

PostgreSQL into this application, but I intend to do that over the next few months. Tim __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Performance issue

memory, I found that postgreSQL client seem not release > after allocate. I try to find bug on my script, but everything look clean to > me. > > Anyone have experience like me.. please share info with me You haven't really provided much information on your problem. Are you vacuum

Re: [SQL] How to get list of days between two dates?

-05-02 | First Event 2006-05-02 | Second Event 2006-05-03 | First Event 2006-05-04 | First Event 2006-05-04 | Third Event 2006-05-05 | Third Event 2006-05-07 | Fourth Event (8 rows) -- Tim Middleton | Vex.Net| "Who is Ungit?" said he, still holding [EMAIL PROTECTED] | VexT

[SQL] Partitioned tables not using index for min and max 8.2.7?

=0.00..0.06 rows=1 width=8)" " -> Index Scan using idx_table_log_date_only on table (cost=0.00..68272.93 rows=1215710 width=8)" "Filter: (log_date IS NOT NULL)" Am I doing something wrong or is this expected. I tried the old method of S

Re: [SQL] Partitioned tables not using index for min and max 8.2.7?

Cool thanks then not something I'm doing wrong :). Is this going to be changed or is changed in a latter version of postgres. (Do I need to do and upgrade or write a work around :) ) Tom Lane wrote: Tim Haak writes: I am running the following query again a partitioned table in 8.2.

[SQL] Comparing a string against an XPath result set

of those. Is there any other way I could tackle this? Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Tweak sql result set... ?

s? I would like to let the original sql > code stay original. I can prepare postgres before executing the sql if > this makes it easier to acheive the goal Have a look at CREATE RULE. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Tweak sql result set... ?

y, but maybe by > setting > a rule or similar. Best from performance view would be to make > something > more permanent. Is it possible to activate/deactivate a rule? Of course, DROP RULE. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] mail alert

ing 24/7 - from before the database accepts other connections, through network failures, bugs, etc. - otherwise notifica- tions will be lost. Therefore I find it much more reliable (and easier to program) to copy the relevant data to a table "mailqueue" (or whatever) and then process that que

Re: [SQL] simple? query

7; WHERE primary_key = 'id'; | INSERT INTO table (primary_key, attribute) SELECT 'id', 'something' WHERE 'id' NOT IN (SELECT primary_key FROM table); should achieve that. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] FW: simple? query

Jan Verheyden wrote: > Thanks for the suggestion, the only problem is, if primary key is used then > each row should be unique what is not true; since I have a column > 'registered' what only can be 1 or 0... > [...] I have no idea what you are trying to say. Tim

Re: [SQL] Lag and lead window functions order by weirdness

sly there are > workarounds. Is anyone able to confirm any of this? AFAIR, others have already pointed out that without an "OR- DER BY" clause PostgreSQL can return the result set in *any* "order" it deems fit. So why don't you use one? Tim -- Sent via pgsql-sql m

Re: [SQL] loading a file into a field

e is no function to do that in the standard distribu- tion. You have to solve that in your client application. Theoretically, you could try to do it in a server-side user function, but permissions, local vs. remote file systems & Co. usually only create major headaches. Tim -- Sent via pgsql

Re: [SQL] Proper case function

_test. the-hyphen-test) > This wouldn't handle the quotes and proper case all of the words. > [...] Based on your requirements, you want to hire some cheap Eng- lish native speaker with lots of cultural knowledge. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] complex join question

) > I am trying to display a count of cruise's for each ship and each > currency even if that count is 0. > But I am having trouble building the query, as some 'cruise's might not > (yet) have a 'price' in all currencies and so no link to 'currency'. Yo

Re: [SQL] Can i force deletion of dependent rows?

ds by default. > Now I want to delete a particular row from table A. Is there any way I can > force deletion of all the dependent rows in table B? > Note that the tables have already been created in the above manner and > populated. I want to do this deletion through DML only ie wi

Re: [SQL] Can i force deletion of dependent rows?

sqlserver2000.databases.aspfaq.com/schema-how-do-i-find-all-the-foreign-keys-in-a-database.html>), but I would invest more time in rethinking your processes. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] UNION or LEFT JOIN?

on how do do the former. Was does "without success" mean? The objective seems to be straight-forward: - Select all cabins that belong to the ship that belongs to the cruise id_cruise. - Left join that with the prices of the cruise id_cruise. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to max() make null as biggest value?

Feixiong Li wrote: > I am newbie for sql, I have a problem when using max() > function, I need get null when there are null in the value > list, or return the largest value as usual, who can do > this? > i.e. max([1,2,3,4,5]) => 5 > max([1,2,3,4,5,null]) => null You

Re: [SQL] regexp_replace and search/replace values stored in table

> [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784: > [p=6947|Isach Jonsen]. > (1 row) > What am I missing? For starters, omit the call to quote_literal (). Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] import ignoring duplicates

re such an > option? No. You can either disable the constraint temporarily, im- port the data, fix any duplicates and re-enable the con- straint, or you can load the data in a temporary table and then transfer only the valid data. With only 100000 records I would opt for the latter. Tim --

Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function

Kenneth Marshall wrote: > It works using 'now' and I assume that since curtime is > of type DATE that the assignment casts the return automatically > to type DATE. Thank you for the ideas. > [...] What's wrong with Pavel's correct and to-the-point answer? Tim

Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function

s correct and to-the-point answer? > No need actually to cast... just use current_date without the quotes. > Its not a string. Where did Pavel suggest to cast or use a string? Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] safely exchanging primary keys?

7;t you use something simple like (untested): | UPDATE cabin_type | SET id_cabin_type = | CASE | WHEN id_cabin_type = id1 THEN | id2 | ELSE | id1 | END | WHERE id_cabin_type IN (id1, id2); Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] safely exchanging primary keys?

ELSE >> > | id1 >> > | END >> > | WHERE id_cabin_type IN (id1, id2); >> Nice, thanks. > Ah, but this won't work as the UNIQUE PK constraint is in force. Oh, yes, you're right, I didn't have that premise in mind. Tim --

Re: [SQL] inner join and limit

2_somedate, | ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.somedate DESC) AS rn | FROM t1 JOIN t2 ON t1.id = t2.t1id) AS SubQuery | WHERE rn <= 2; Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Extending Regular Expression bounds limit of 255.

he headaches of "A{1000,2000}" left as an exercise to the read- er :-)). Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] what does this do

column_alias, ...)", but AFAIK PostgreSQL doesn't support specifying a data type for each column. Which DBMS is this code used for? Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] what does this do

arameter. > Notice the function name section taken from the from clause: > http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FROM Another lesson learned :-). But it applies strictly to *re- cord* returning functions, doesn't it? Because I had tested generate_series() prior

Re: [SQL] error on line 1 trying to execute a script using psql

have to configure your editor not to save the BOM or chop off the first three bytes yourself (with tail, sed, Perl & Co.). Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] enforcing constraints across multiple tables

cation level anyway as well as you probably don't want to pass PostgreSQL's errors directly to the user) and your mind doesn't become twisted too much. I would schedule regular tests on the data set though to ensure that you no- tice problems early on. Tim -- Sent via pgsql-sql mailin

Re: [SQL] How to select text field as interger

e" field is numerically equal to 1 by casting it to an integer and then comparing it to 1, i. e.: | SELECT Name FROM Test WHERE Name::INT = 1; Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to find events within a timespan to each other?

2 ... 13:03 > 213 1 ... 13:04 > 222. > 233 1 ... 13:05 > 242 1 ... 13:06 > E.g. the checked event_typ_id may be 3 then the result > should be line 19, 21, 23 You can use window functions and check whether the

Re: [SQL] grouping subsets

code) OVER (ORDER BY mydate) AS prev_code > FROM > mytable > ORDER BY mydate; > It should be possible to use that as a subquery with an > outer query that compares mycode=prev_code to get a run > length. Hmmm. Can the outer query be done without using "WITH RECURSIVE"? Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] grouping subsets

adding up over all the rows. > [...] If the date column wasn't relevant, how would you group the first two columns? Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Aggregates (last/first) not behaving

UP BY EXTRACT(day FROM t)); Obviously, this doesn't "keep using last()", so I don't know whether it's good or bad for you. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Generating Rows from a date and a duration

s(0,duration-1) AS s(a); END; $$ LANGUAGE 'plpgsql'; -- This works, but not what I want SELECT * FROM bloat_duration(1,'2010-09-03',4); -- This does not work SELECT * FROM example AS ex INNER JOIN bloat_duration(ex.id,ex.startdate,ex.duration) AS bd ON bd.id = ex.id ROLLBACK TRANS

Re: [SQL] Generating Rows from a date and a duration

Hi Brian, Hi List, At Sat, 4 Sep 2010 09:20:53 -0400, Brian Sherwood wrote: > On Fri, Sep 3, 2010 at 5:40 AM, Tim Schumacher < > tim.daniel.schumac...@gmail.com> wrote: > > > I'm kinda stuck situation, I have a timestamp which resambles a > > startdate and

Re: [SQL] Duplicates Processing

OVER (PARTITION BY wattage, tolerance, temperature |ORDER BY part_number) AS RN | FROM parts) AS SubQuery |WHERE RN > 1); Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to remove a set of characters in text-columns ?

mpty string ''. > There might be a more elegant way. > Is there ? Besides the regexp_replace() solution mentioned by Charlie and Steve, you can also use TRANSLATE(): | tim=# SELECT TRANSLATE('a{b''c"d!f', '{}()''",;.:!', '&#x

Re: [SQL] Help with regexp-query

;\|') as category from > akb_articles limit 100 > This ends with 'ERROR: invalid regular expression: quantifier operand > invalid'. > I would apreciate some help with this one please. You need to double the backslashes (e. g. "E'\\|{2,}'"); oth

Re: [SQL] Confused about writing this stored procedure/method.

get the index of August in he financial year > calendar somewhat like this > [...] You don't need any function for that, just use "ORDER BY Month < 4, Month". Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] need magic to shuffle some numbers

second smallest prio get the > second-to-last biggest value and v.v. If you just want to reverse the priorities: | UPDATE TestTable | SET prio = (SELECT MIN(prio) FROM TestTable WHERE group_id = 'testgroup') + | (SELECT MAX(prio) FROM TestTable WHERE group_id = 't

Re: [SQL] Add one column to another

f an obvious query. Try: | SELECT first_name, surname, email1 AS email FROM testtable WHERE email1 IS NOT NULL UNION ALL | SELECT first_name, surname, email2 AS email FROM testtable WHERE email2 IS NOT NULL; Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make

Re: [SQL] Window function sort order help

FROM previous_move_date |ORDER BY consumer_id, history_timestamp DESC; Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Passing function parameters to regexp_replace

%n="%', $1, '%">%') If $1 and $2 (can) include meta characters, you have to es- cape them properly. Please consider that regexp_replace() uses POSIX Regular Expressions while LIKE uses a different syntax. If possible, I would replace the LIKE expression with its "~" equivalent so chances of confusion are minimized. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Passing function parameters to regexp_replace

Leif Biberg Kristensen wrote: > On Saturday 17. September 2011 19.07.03 Tim Landscheidt wrote: >> Leif Biberg Kristensen wrote: >> > UPDATE sources SET source_text = regexp_replace(source_text, >> > E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2&

Re: [SQL] how to calculate differences of timestamps?

. The query above requires a full table scan which may kill performance in some circumstances. Of course, any design has to deal with the possibility of an event not having been logged, multiple logins, etc. The query above just forms pairs based on temporal proximity. Tim -- Sent via pgsql

Re: [SQL] postgres sql help

OM version > ORDER BY string_to_array(ver_no, '.', '')::int[] > should do what you want. Really neat! :-) For pre-9.1, you have to "ORDER BY string_to_array(TRIM('.' FROM ver_no), '.')::int[];", though. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Getting a list of a table's attributes that are sortable sorted by uniqueness

that will return a list of a table's attributes that are sortable (e. g. no XML fields) and sorted by "uniqueness", i. e. first attributes repre- senting the primary key, then other unique keys, then the rest. Before I dive into the depths of PostgreSQL's system cata- logues,

Re: [SQL] lo_import

I Guess postgres only see file on the machine it is runing > and not through the network... > I will have to upload the file into the server and then use > import ? is there any other way ? Create a large object and then write to it? What driver are you using? Tim -- Sent via pgsql-

Re: [SQL] Fwd: i want small information regarding postgres

email sysadm...@postgresql.org with > mailing list complaints, as I don't read this list (or indeed many > others) I had reported this to pgsql-sql-ow...@postgresql.org. Where do that end up? Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to yo

[SQL] Token separation

Hi, I just tried to input a hexadecimal number in PostgreSQL (8.4) and was rather surprised by the result: | tim=# SELECT 0x13; | x13 | - |0 | (1 Zeile) | tim=# SELECT 0abc; | abc | - |0 | (1 Zeile) | tim=# The documentation says: | A token can be a key word, an identifier

Re: [SQL] Token separation

ger sensibly > be added to it. I know, but - off the top of my head - in most other lan- guages "0abc" will then give a syntax error. > Possibly the documentation should be tweaked to mention the > number-followed-by-identifier case. Especially if you consider such cases: |

Re: [SQL] Getting a list of a table's attributes that are sortable sorted by uniqueness

RDER BY keyrank, | columnrankinkey, | attnum; does almost what I want except: - Attributes that can't be sorted (XML) aren't skipped, and - "UNIQUE(A, B)" and "UNIQUE(C, D)" would give "A, C, B, D" (untested) so the "rank" of a non-primary k

Re: [SQL] time interval math

es. > Knowing the total in seconds was the big roadblock. And > converting back is easier (a / 3600 + ":" + a / 60 + ":" + a % 60) > [...] PostgreSQL has also: | tim=# SELECT 3661::TEXT::INTERVAL; | interval | -- | 01:01:01 | (1 Zei

Re: [SQL] How to split up phone numbers?

erman area codes, i. e. "+49 xy" may be city A, with "+49 xyz" being city B. Obviously, you will also not be able to differentiate between normal and extension numbers if they aren't properly marked up in the input. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Can I read the data without commit

John Fabiani wrote: > Yes I understand - but I want to know is it possible? Can I read the data I > just inserted without a commit. > [...] Are you talking about a web application? Then no, you'll have to code that yourself. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@

[SQL] Re: Matching and Scoring with multiple fields

shouldn't just dump my problem here, but I am truly stumped by this one - if anybody can give me a pointer in the right direction I'd greatly appreciate it. Thanks, Tim Johnson --- http://www.theinkfactory.co.uk

RE: [SQL] Re: Matching and Scoring with multiple fields

ant fields and then pick how they feel. I still worry about the results being skewed by extreme data in certain fields but I guess there's no way around that. Thanks again. Tim Johnson, -- http://www.theinkfactory.co.uk -Original Message- From: Oliver Mueschke [mailto:[EMAIL PROTECTED]

Re: [PERFORM] [SQL] OFFSET impact on Performance???

do their work and what their work is). And of course better performance cannot be guaranteed until you try it. Would such a scheme give you any hope of improved performance, or would it be too much of a nightmare? cheers, --Tim -Original Message- From: [EMAIL PROTECTED] on behalf