Re: [SQL] Why doesn't this work

Tirsdag 15. januar 2013 12.50.00 skrev Barbara Woolums : > I am running a query like so > > SELECT id FROM image WHERE image='demo-harvey wallbanger.jpg' > > It returns nothing > > My table looks like this > > "demo-820.jpg";1 > "demo-lemon-mousse-1.jpg";2 > "demo-pumpkinchaibars.jpg";3 > "dem

Re: [SQL] How to convert SQL store procedure to Postgresql function

Tirsdag 28. februar 2012 12.56.46 skrev Rehan Saleem : > hi , > whats wrong with this function , i am getting syntax error which is syntax > error at or near "+=" LINE 13: set sql += ' bpoverlap, centredistance You can't concatenate that way in plpgsql. Instead of "set sql +=" try with just "||

Re: [SQL] Current transaction is aborted, commands ignored until end of transaction block

Fredag 30. desember 2011 05.25.22 skrev Jan Bakuwel : > Of course I can start testing existing values in the database before > accepting them in the user interface but that's putting the horse behind > the cart. I much rather use the constraints at the database level to > tell me a particular upda

Re: [SQL] Current transaction is aborted, commands ignored until end of transaction block

Fredag 30. desember 2011 09.43.38 skrev Jan Bakuwel : > Would be nice to have an option in PostgreSQL something along the lines > of: 'abort-transaction-on-constraint-violation = false' That option is called MySQL with MyISAM tables. Seriously, if the user encounters a constraint violation

Re: [SQL] Passing function parameters to regexp_replace

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"', 'g') where source_text

Re: [SQL] Passing function parameters to regexp_replace

On Saturday 17. September 2011 13.21.43 Leif Biberg Kristensen wrote: > UPDATE sources SET source_text = regexp_replace(source_text, > E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like > '%n="%$2%">%

[SQL] Passing function parameters to regexp_replace

I'm trying to write a sql or plpgsql function update_nametags(TEXT, TEXT) which does a replace on this form: UPDATE sources SET source_text = regexp_replace(source_text, E'n="(.*?)$1(.*?)"', E'n="\\1$2\\2"', 'g') where source_text like '%n="%$2%">%'; But I can't find out how to escape the para

Re: [SQL] Want some basic compare of data type on PostgreSQL and MySQL

On Thursday 1. September 2011 11.16.23 Scott Marlowe wrote: > The postgresql type text is a varchar with > no precision that can hold up to about a gig or so of text. Not that > i recommend putting a gig of text into a single field in a database. Printed out as plain text on paper with 4,000 char

Re: [SQL] Subselects not allowed?

On Saturday 11. June 2011 22.09.09 Leif Biberg Kristensen wrote: > I've written a blog post which I hope may be helpful to others in a similar > situation: > > <http://solumslekt.org/blog/?p=321> > > Thanks to Guillaume Lelarge, Tom Lane, and Andreas Kretschmer. Yo

Re: [SQL] Subselects not allowed?

I've written a blog post which I hope may be helpful to others in a similar situation: Thanks to Guillaume Lelarge, Tom Lane, and Andreas Kretschmer. You guys are great. regards, Leif http://code.google.com/p/yggdrasil-genealogy/ -- Sent via pgsql-sql mail

Re: [SQL] Subselects not allowed?

On Saturday 11. June 2011 17.23.40 Andreas Kretschmer wrote: > Leif Biberg Kristensen wrote: > > Can anybody tell me why this doesn't work? > > > > pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT > > MAX(source_id) FROM sources);

Re: [SQL] Subselects not allowed?

On Saturday 11. June 2011 17.14.21 Tom Lane wrote: > Guillaume Lelarge writes: > > On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote: > >> Can anybody tell me why this doesn't work? > >> pgslekt=> CREATE SEQUENCE sources_source_id_seq START WI

[SQL] Subselects not allowed?

Can anybody tell me why this doesn't work? pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX(source_id) FROM sources); ERROR: syntax error at or near "(" LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX...

Re: [SQL] replace " with nothing

On Wednesday 11 May 2011 23:25:34 Ross J. Reedstrom wrote: > On Wed, May 11, 2011 at 11:11:07PM +0200, Leif Biberg Kristensen wrote: > > although it's a little above me why you would want to select firstname in > > the first place when you proceed to replace it with not

Re: [SQL] replace " with nothing

On Wednesday 11 May 2011 22:29:40 Tony Capobianco wrote: > We are converting from Oracle to Postgres. An Oracle script contains > this line: > > select replace(firstname,'"'), memberid, emailaddress from members; > > in an effort to replace the " with nothing. How can I achieve the same > resu

Re: [SQL] data import: 12-hour time w/o AM/PM

On Tuesday 8. February 2011 19.14.00 Tarlika Elisabeth Schmitz wrote: > From the date and time I want to create a timestamp. > I know that > - the events take place during the day, say between 10:30 and 22:30 > - it's always a set of events at one location spaced about 30min apart > - the imported

Re: [SQL] find and replace the string within a column

arenthesis, followed by a close parentheis) and replacing > with an empty string ? I'm doing a similar task, removing comments "hidden" within curly braces like this: str := REGEXP_REPLACE(str, '{.*?}', '', 'g'); No escaping needed at all

Re: [SQL] Postgresql database

anufacturers", and a table of "models", the latter with a foreign key referencing the id of the former. The number of wheels is obviously an attribute of "models". regards, -- Leif Biberg Kristensen http://solumslekt.org/blog/ -- Sent via pgsql-sql mailing list (pgsql-

Re: [SQL] Need a help in regexp

lways is an integer, and it's always the only or first integer in the string, you can use: SELECT (REGEXP_MATCHES(bar, E'(\\d+)'))[1] FROM foo; You can even cast it to an integer on the fly: SELECT (REGEXP_MATCHES(bar, E'(\\d+)'))[1]::INTEGER FROM foo; Or as a more g

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

Followup II: I've written a blog post on the subject at <http://solumslekt.org/blog/?p=151>. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mai

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

:= REGEXP_REPLACE(str, links.short_link, links.long_link, 'g'); END LOOP; RETURN str; END $$ LANGUAGE plpgsql IMMUTABLE; By the way, those who haven't read this gem should probably do so: <http://database-programmer.blogspot.com/2008/05/minimize-code-maximize- data.html> reg

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

On Tuesday 27. April 2010 13.43.48 Leif Biberg Kristensen wrote: > CREATE TABLE short_links ( > link_type CHAR(2) PRIMARY KEY, > short_link TEXT, > long_link TEXT, > description TEXT > ); > > It appears like I have to double the number of backslashes

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

On Tuesday 27. April 2010 15.04.23 Tim Landscheidt wrote: > Leif Biberg Kristensen wrote: > > > [...] > > So far, so good. But when I try to do the actual expansion, I'm stumped. > > > pgslekt=> select regexp_replace((select source_text from sources

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

ks where link_type = 'sk'), (select quote_literal(long_link) from short_links where link_type = 'sk'), 'g'); regexp_replace -------- [sk=25658|67|side 66a]. Vabakken under Klyve vestre i Solum 07.07.1784

Re: [SQL] Table Design for Hierarchical Data

On Monday 12. April 2010 17.37.58 Yeb Havinga wrote: > Leif Biberg Kristensen wrote: > > On Monday 12. April 2010 16.57.38 Rob Sargent wrote: > > > >> Believe me: "ego-ma-pa" will correctly define genealogical relationships > >> (at least among humans

Re: [SQL] Table Design for Hierarchical Data

here is the root node of a family tree? Old Adam & Eve? On the other hand, a pedigree may be considered a true binary tree with a root node, the proband. regards, -- Leif Biberg Kristensen http://solumslekt.org/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make cha

[SQL] Plpgsql: Iterating through a string of parameters

gards, -- Leif Biberg Kristensen http://solumslekt.org/ -- 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] Plpgsql: Iterating through a string of parameters

On Thursday 25. March 2010 16.16.53 Leif Biberg Kristensen wrote: > I'm struggling with how to make plpgsql iterate through a list of numbers > input as a text string, eg. "1438 2656 973 4208". I figure that I can use the > regexp_split_to_array() function to make an arra

Re: [SQL] DDL problems: Referential issue?

On Wednesday 4. November 2009 21.03.26 Scott Marlowe wrote: > On Wed, Nov 4, 2009 at 11:53 AM, Leif Biberg Kristensen > > This looks strange to me, but it works: > > > > pgslekt=> CREATE TABLE participant_notes ( > > pgslekt(> person_fk INTEGER NOT NULL, >

Re: [SQL] DDL problems: Referential issue?

On Wednesday 4. November 2009 19.37.41 Scott Marlowe wrote: > On Wed, Nov 4, 2009 at 11:36 AM, Leif Biberg Kristensen > wrote: >> I'd missed that particular syntax. >> >> This table is now without a primary key, but is that a problem? I don't >> expect it

Re: [SQL] DDL problems: Referential issue?

without a primary key, but is that a problem? I don't expect it to grow beyond maybe a few thousand rows. -- Leif Biberg Kristensen http://solumslekt.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] DDL problems: Referential issue?

However, I think that this table should reference participants, not the primary tables persons and events. -- Leif Biberg Kristensen http://solumslekt.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql