Some people, when confronted with a problem, think "I know, I'll use
regular expressions." Now they have two problems. — Jamie Zawinski
3 feb 2010 kl. 21.32 skrev Tena Sakai:
> Thank you, Dirk.
>
> Regards,
>
> Tena Sakai
> tsa...@gallo.ucsf.edu
>
>
> On 2/3/10 11:43 AM, "Dirk Jagdmann" wro
Thank you, Dirk.
Regards,
Tena Sakai
tsa...@gallo.ucsf.edu
On 2/3/10 11:43 AM, "Dirk Jagdmann" wrote:
> Be careful when working with backslashes and regular expressions for
> the proper (double) escaping!
>
> # select '70a5' ~ e'\\d+\.\\d+';
> ?column?
> --
> t
> (1 row)
>
> # sel
Be careful when working with backslashes and regular expressions for
the proper (double) escaping!
# select '70a5' ~ e'\\d+\.\\d+';
?column?
--
t
(1 row)
# select '70a5' ~ e'\\d+\\.\\d+';
?column?
--
f
(1 row)
# select '70.5' ~ e'\\d+\\.\\d+';
?column?
--
t
--
---
Hi,
Thanks for your reply.
Indeed, why not?
Tena Sakai
tsa...@gallo.ucsf.edu
On 2/3/10 3:38 AM, "msi77" wrote:
> Why not to use
>
> select subjectid, height
> from tsakai.pheno
> where height like '%.%';
>
> ?
>
>> Hi everybody,
>> I need a bit of help on postgres reqular expression.
>>
Why not to use
select subjectid, height
from tsakai.pheno
where height like '%.%';
?
> Hi everybody,
> I need a bit of help on postgres reqular expression.
> With a table of the following definition:
> Table "tsakai.pheno"
> Column | Type | Modifiers
> ---+---+
Thank you kindly, Pavel.
Regards,
Tena Sakai
On 2/2/10 12:38 PM, "Pavel Stehule" wrote:
> 2010/2/2 Tena Sakai :
>> Hi everybody,
>>
>> I need a bit of help on postgres reqular expression.
>> With a table of the following definition:
>>
>> Table "tsakai.pheno"
>> Column |
2010/2/2 Tena Sakai :
> Hi everybody,
>
> I need a bit of help on postgres reqular expression.
> With a table of the following definition:
>
> Table "tsakai.pheno"
> Column | Type | Modifiers
> ---+---+---
> subjectid | integer |
Hi everybody,
I need a bit of help on postgres reqular expression.
With a table of the following definition:
Table "tsakai.pheno"
Column | Type| Modifiers
---+---+---
subjectid | integer | not null
height| character vary
andi wrote:
> I have seen this is very drawback of our beloved postgres databases,
> postgres do not support sql 2003 standards,
>
> I hope soon we can support this standards.
Hmm. True. It is also true that we'll be there sooner if you help out.
--
Alvaro Herrera
Dear friends,
I have seen this is very drawback of our beloved postgres
databases, postgres do not support sql 2003 standards,
I hope soon we can support this standards.
Thank you
Andrew Sullivan wrote:
On Fri, May 26, 2006 at 05:11:26PM +0700, andi wrote:
select rank() over(order by testeridpk ) as rank , * from tester;
I get the result is like this,
RANK TESTERIDPK TESTER_NAME
1 10TESSS
2 90NAMAAA
3
On Fri, May 26, 2006 at 09:08:20AM -0500, Bruno Wolff III wrote:
>
> Rollbacks will not reset sequence values. Use setval to do that.
No, what I posted was the CREATE SEQUENCE after the BEGIN. ROLLBACK
gets rid of the sequence. The next time you create the same
sequence, therefore, it also star
On Fri, May 26, 2006 at 06:50:37 -0400,
Andrew Sullivan <[EMAIL PROTECTED]> wrote:
> On Fri, May 26, 2006 at 05:11:26PM +0700, andi wrote:
> > select rank() over(order by testeridpk ) as rank , * from tester;
> >
> > I get the result is like this,
> >
> >
> > RANK TESTERIDPK TESTER_NA
On Fri, May 26, 2006 at 05:11:26PM +0700, andi wrote:
> select rank() over(order by testeridpk ) as rank , * from tester;
>
> I get the result is like this,
>
>
> RANK TESTERIDPK TESTER_NAME
>
> 1 10TESSS
>
> 2 90NAMAAA
>
> 3 100
When I use this syntax
select * from TESTER;
I got
TESTERIDPK TESTER_NAME
10 TE
90 NAM
100 U
In ms sql server 2005 I use this
select rank()
over(order
by testeridpk ) as rank , *
from tester;
I get the result is like
this,
RAN
andi wrote:
Dear friends,
I have table
MD_CUSTOMER
MD_CUSTOMERIDPK integer primary key
NAME varchar
OK - two columns.
But my primary key is not in correct order like
MD_CUSTOMER
MD_CUSTOMERIDPK NAME
10
ANDI
33
TESTER
100
Well, you could add a serial column. I'll tell you how, but I haven't tested the code, so be sure to check it! And using BEGIN and COMMIT/ROLLBACK to delimit transactions would not be a bad idea at all ;-)To add a serial column, just write:
--create new serial field
ALTER TABLE md_customer ADD id
On Wed, May 24, 2006 at 05:35:10PM +0700, andi wrote:
>
> But my primary key is not in correct order like
What does this mean? Is the key being generated by a sequence (i.e.
is the column DEFAULT nextval('some_sequence'))? If so, the primary
key will be assigned in COMMIT order. Note that the
Dear friends,
I have table
MD_CUSTOMER
MD_CUSTOMERIDPK integer
primary key
NAME varchar
But my primary key is not in correct order like
MD_CUSTOMER
MD_CUSTOMERIDPK NAME
10
On Tue, Apr 11, 2006 at 02:34:22 -0700,
Timo Tuomi <[EMAIL PROTECTED]> wrote:
>
> I'd need to get the time interval X-Y-Z on each date but I cannot rely
> on the date (can't make any joins based on the date part of
> timestamps). Instead I'd need to find out X-Y and Y-Z pairs with a
> minimal "s
I'm stucked..
Say a car travels from X to Y then from Y to Z (and then from Z back
to X but that's not relevant here).
In the table below are the timestamps for each point in various
dates. The complete trip X-Y-Z-X is in the table but each leg on a
separate row.
I'd need to get the time interva
> I can't understand why you are doing this big cycle.. but certainly
> when constraints can't help you.. you can use triggers to enforce
> integrity..
In my system I have to have arbitrary contact info records about my users. I
mean not only fixed like names, email and phone but many many other
I can't understand why you are doing this big cycle.. but certainly
when constraints can't help you.. you can use triggers to enforce
integrity..
On 11/20/05, Grigory O. Ptashko <[EMAIL PROTECTED]> wrote:
> Hello, everybody!
>
> I don't whether it is possible to do the following but anyway I can't
Hello, everybody!
I don't whether it is possible to do the following but anyway I can't.
I need to write a constraint as described below.
Here are four tables:
CREATE TABLE countries
(id SERIAL,
name VARCHAR(255),
PRIMARY KEY (id)
);
CREATE TABLE countries_names
(id INT NOT NULL,
id_lang INT
Moritz Bayer <[EMAIL PROTECTED]> writes:
> I get the following error:
> ERROR: missing .. at end of SQL expression
> I haven't figured out what this message wants to tell me and why it is
> thrown at all.
I think it's telling you that you are using a 7.3 or older server.
Try 7.4 or later --- plp
On Sep 12, 2005, at 8:14 AM, Moritz Bayer wrote:
I get the following error:
ERROR: missing .. at end of SQL expression
it looks like your for loop is being interpreted as the integer
variant, e.g. for i in 1..10 loop
CREATE TYPE "public"."ty_stadtlandflussentry" AS (
DECLARE objRetu
Hello group,
I 've written the following function:
CREATE OR REPLACE FUNCTION "public"."getstadtlandflussentrybyid" (integer) RETURNS SETOF "public"."ty_stadtlandflussentry" AS'DECLARE objReturn ty_stadtlandflussentry;
DECLARE iid integer;BEGIN iid := $1; for objReturn IN
ctrl wrote:
I have news...good news for me:)
even though I wasn't able to find the answers I was looking for, I did
something that made a big difference: by removing the ORDER BY clause,
the same function takes now 5 milliseconds (instead of sometimes 10
minutes).
I have tried to vacuum, analyze, e
Thanks all, :)
It working now, i'm using code as below:
to_char(t1.created::date,'DD/MM/')
but other problem come out, error as below:
ERROR: relation "plugins" does not exist
what that's mean?? table plugins already exists..
On Thu, 22 Jul 2004 10:43:35 +0800, azah azah <[EMAIL PROTECT
Why still not working???
I have try all the suggestions, still error like below:
ERROR: cannot cast type character varying to date
I'm using latest version of postresql.
On Wed, 21 Jul 2004 05:35:06 -0700 (PDT), Theodore Petrosky
<[EMAIL PROTECTED]> wrote:
>
> what kind of column is t1.created?
what kind of column is t1.created? It appears that it
is a text column and the format looks like a date. Is
this correct or is it a date? I need more information
about your table structure.
What about:
SELECT t2.id, t2.name, to_char(cast (t1.created as
date),'DD/MM/')
but the other '::' sh
Ð ÐÑÐ, 21.07.2004, Ð 10:33, azah azah ÐÐÑÐÑ:
> Hi,
> I want convert from mysql to postresql, previously
> in mysql the code as below:
>
> SELECT t2.id, t2.name, date_format(t1.created,\'%W %M %e, %Y - %r\')
>
> In postresql no date_format function, we need to use to_char function
> but it still
Hi,
I want convert from mysql to postresql, previously
in mysql the code as below:
SELECT t2.id, t2.name, date_format(t1.created,\'%W %M %e, %Y - %r\')
In postresql no date_format function, we need to use to_char function
but it still work because still need to put ::date such as
SELECT to_char
Thank you this pointed me to the problem. I have
non castable entries. I had picked up legacy data (for
the jobnumbers [that's why they were text in the first
place]) and some of the jobnumbers where 1162_01
1162_02 so this would fail.
The error message was giving me the correct message
h
On Sun, 11 Jul 2004, Theodore Petrosky wrote:
> I give up.. what don't I understand about casting and
> ints and text..
>
> i have a table jobinfo with:
>
> acode text,
> jobnumber text default
> nextval('public.jobinfo_seq'::text),
> jobtitle text
>
> I have about 3000 rows starting with jobnumb
Theodore,
Because jobnumber is declared as text, you are getting "dictionary
order" (lexicographic) ordering on the values. In a dictionary, "abc"
comes after "a", obviously. So indeed "999" will come after
"1000". To get the effect that you want you need to treat jobnumber as
a numb
I give up.. what don't I understand about casting and
ints and text..
i have a table jobinfo with:
acode text,
jobnumber text default
nextval('public.jobinfo_seq'::text),
jobtitle text
I have about 3000 rows starting with jobnumber = 1000.
SELECT jobnumber, jobtitle FROM jobinfo WHERE
jobnumb
-Message d'origine-
De : Freshman [mailto:[EMAIL PROTECTED]
Envoyé : jeudi 30 octobre 2003 13:38
À : [EMAIL PROTECTED]
Objet : [SQL] Please help me to slove this SQL statements
There are three table in database which is suppliers, projects, and
shipments
suppliers contain supplie
There are three table in database which is suppliers, projects, and
shipments
suppliers contain suppliers id, name ...etc
projects contain project name ..suppliers ID ( J1---J7) ...etc
shipments table contain suppliers ID , PROJECTS ID
how can i query to find out the suppliers to supply all the pr
Dear All
I'am a new user in postgresql, i want to ask about :
1. The one who is intended with STORED PROCEDURE,
how its implement and what its surplus.
Version POSTGRESQL that how much possess STRORED PROCEDURE.
2. In when I make the function as follows:
CREATE FUNCTION forward(C
"Matthew Geddert" <[EMAIL PROTECTED]> writes:
> create or replace view events_orders_states
> as
> select o.*,
> o_states.order_state
> from events_orders o,
> (select
> order_id,
> decode (floor(avg (decode (reg_state,
>'canceled', 0,
>'waiting', 1,
>
Hello,
I am trying to convert an application to postgresql, and am having a
bear of a time converting the following view (from oracle). What it
does, just in case you aren't very familiar with oracle syntax, is group
the average reg_state from the events_registrations table after having
converted
On Tue, Oct 01, 2002 at 11:55:14PM -0700, mgeddert wrote:
> Robert,
>
> Thanks for the help, I kept on playing with what you gave me, and after
> removing one () pair and adding the ENDs to the CASE WHENs it works!
> Thank you so much for the help, I have been very frustrated with this
> for a nu
On Tue, Oct 01, 2002 at 10:41:17PM -0700, mgeddert wrote:
>
create or replace view events_orders_states as
select o.*,
o_states.order_state
from events_orders o,
(
SELECT order_id,
CASE (
floor (avg (
Hello,
I am trying to convert an application to postgresql, and am having a
bear of a time converting the following view (from oracle). What it
does, just in case you aren't very familiar with oracle syntax, is group
the average reg_state from the events_registrations table after having
converted
Michelle Murrain wrote:
>
> Yeah, except I do have experience with ColdFusion, and ColdFusion
> runs into some very problematic issues with Postgres, sadly. Although
> I use Postgres almost exclusively, I had to switch to MySQL for use
> with ColdFusion.
I wonder what your issues are, because in
Title: Re: [SQL] Please Help me
Michelle,
Have
you tried it with Postgres 7.1 yet, which removed such
limitations?
Chris
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Michelle MurrainSent: Thursday, 1 August 2002 10:48
PMTo: Chad
Title: Re: [SQL] Please Help me
Unfortunatly i know of no such problem. I
have large text fields being submited to my database, but i restrict the submit
page to 255 chars. I will have to test larger numbers and see what errors
i get.
Thanks
Chad
- Original Message -
From
Title: Re: [SQL] Please Help me
At 8:32 AM -0600 8/1/02, Chad Thompson wrote:
I am running
RedHat, with Apache and Cold Fusion. I chose PostgreSQL for all
of the aforementioned reasons. It works very well with Cold
Fusion. I have done some optimizing and am able to run rather
complex queries
had to bail on MySQL because it wouldnt run the
sub-queries that i needed.
Thanks
Chad
- Original Message -
From:
Waheed Rahuman
To: [EMAIL PROTECTED]
Sent: Thursday, August 01, 2002 11:10
AM
Subject: [SQL] Please Help me
Dear all
Please suggest me which
Title: Re: [SQL] Please Help me
At 2:49 PM +0800 8/1/02, Christopher Kings-Lynne wrote:
I have no experience with ColdFusion, but if you ask a
question like whether MySQL or Postgres is better on a Postgres
mailing list - we're going to say Postgres.
Yeah, except I do have experience
ural languages
*
...and much, much more...
You
haven't provided us with very much information, however...
Chris
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Waheed RahumanSent: Friday, 2 August 2002 1:11
AMTo: [EMAIL PROTECTED]Subject: [SQL
Dear all
Please suggest me which database i can choose for
my server setup like
1. Manrake Linux
2. ColdFusion
3. Apache Webserver
Now i dont know which database to
choose
Whether MySQL or PostgreSQL
Please suggest me a.
Thank you
Expecting your reply
Regards
Waheed Rahuman
Dmitry Tkach <[EMAIL PROTECTED]> writes:
> It now looks like a bug in the query planner to me - it seems that it
> just doesn't consider indices with predicates for join plans...
> I was looking at the source code, and it looks like pred_test() is
> responsible for that.
Yup. I've applied the
ist) :-(
Perhaps, somebody, familiar with this code could come up with a patch
for this problem?
This would be really great!
Dima
>
>
>Jie Liang
>
>-Original Message-
>From: Dmitry Tkach [mailto:[EMAIL PROTECTED]]
>Sent: Friday, July 12, 2002 7:34 AM
>To: Jie Liang
o: Jie Liang
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [SQL] Please, HELP! Why is the query plan so wrong???
Jie Liang wrote:
>I believe that SQL will use the index of join 'key' when you join the
tables
>if
>have any, in your query the (a,c) is the join key but d is
nd
fb.c=fbr.c and fbr.d is null) where fb.b=0
It results in the same query plan (seq scan on fbr).
Dima
>
>
>
>-Original Message-
>From: Dmitry Tkach [mailto:[EMAIL PROTECTED]]
>Sent: Thursday, July 11, 2002 3:51 PM
>To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
>Subj
PROTECTED]; [EMAIL PROTECTED]
Subject: [SQL] Please, HELP! Why is the query plan so wrong???
Hi, everybody!
Here is the problem:
test=# create table fb (a int, b int, c datetime);
CREATE
test=# create table fbr (a int, c datetime, d int);
CREATE
test=# create unique index fb_idx on fb(b);
C
Hi, everybody!
Here is the problem:
test=# create table fb (a int, b int, c datetime);
CREATE
test=# create table fbr (a int, c datetime, d int);
CREATE
test=# create unique index fb_idx on fb(b);
CREATE
test=# create index fbr_idx on fbr(a,c) where d is null;
CREATE
test=# set enable_seqsca
6/14/2002 6:31:16 AM, Stephan Szabo <[EMAIL PROTECTED]> wrote:
>
>On Thu, 13 Jun 2002, Vernon Wu wrote:
>
>> I, however, didn't use double quote mark when I created the table at all.
>
>If you used an interface to generate the table def, alot of them add the
>quote marks behind your back when the
You are right, Steve. It needs the double quote mark. After I use the double quote
mark, an error message is:
ERROR: ExecAppend: Fail to add null value in not null attribute ...
which is right since I don't have non-null value to non-null field yet.
I, however, didn't use double quote mark w
On Thu, 13 Jun 2002 13:16:29 +0800, Vernon Wu
<[EMAIL PROTECTED]> wrote:
>
>Command:
>
>Insert into profile (userid, haveChildren)values('id98', 'No');
>
>Error:
>
>ERROR: Relation 'profile' does not have attribute 'havaChildren'
^
On Thu, 13 Jun 2002, Vernon Wu wrote:
>
> Command:
>
> Insert into profile (userid, haveChildren)values('id98', 'No');
You presumably used double quotes when creating the column, so
you need to use them to refer to the column from that point on:
insert into profile(userid, "haveChildren") ...
On Thu, 13 Jun 2002, Vernon Wu wrote:
> I, however, didn't use double quote mark when I created the table at all.
If you used an interface to generate the table def, alot of them add the
quote marks behind your back when they do the creation. In general, it's
safer to just use all lowercase nam
Vernon Wu wrote:
> Command:
>
> Insert into profile (userid, haveChildren)values('id98', 'No');
>
> Error:
>
> ERROR: Relation 'profile' does not have attribute 'havaChildren'
^^^
From the error message, looks like you spelled haveChild
Command:
Insert into profile (userid, haveChildren)values('id98', 'No');
Error:
ERROR: Relation 'profile' does not have attribute 'havaChildren'
Table:
Table "profile"
Column| Type | Modifiers
--+---+--
useri
Guys;
I am begging for your help again.
I can't find a solution to my problem.
I am porting a complex system from Oracle to PostgreSQL and I need to
implement the following:
function 1 does some processing and returns a record (I can declare it
as a row in a view)
function 2 uses func1 to get
Cedar Cox writes:
> It would be somewhat (very) useful to have something like this. We were
> toying with the idea of making some sort of system to figure out if a
> table is locked or not.
This will probably introduce race conditions unless done very carefully.
In theory you need a second leve
It would be somewhat (very) useful to have something like this. We were
toying with the idea of making some sort of system to figure out if a
table is locked or not. In the end we decided to go with executing this
asynchronously and after a given timeout ask the user if they would like
to wait
Yes but the option NOWAIT say to the instruction
SELECT ... FOR UPDATE to not wait the unlock but to return the information
the lines can't be lock.
(Must retry late).
Peter Eisentraut wrote:
[EMAIL PROTECTED]">Loïc Bourgeois writes:
What is the equivalent of the oracle request: SELECT ... FOR
Loïc Bourgeois writes:
> What is the equivalent of the oracle request: SELECT ... FOR UPDATE
> NOWAIT, under PostGreSQL
I don't know Oracle, but there doesn't seem to be such a command in
PostgreSQL. If the table is already locked, the SELECT FOR UPDATE has to
wait.
--
Peter Eisentraut [
What is the equivalent of the oracle request: SELECT ... FOR UPDATE
NOWAIT, under PostGreSQL
Thanks a lot
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
72 matches
Mail list logo
