[SQL] pltcl problem
Hi all! I am using postgresql 7.0.2 and then installed tcl 8.2.3 (from dev.scriptics.com). I followed all the instructions to install tcl. Then I have configured again postgres using this flag, --with-tcl. everythings looks ok, I did a createlang pltcl mydb and it worked! then load some pltcl functions and worked also. But when I try to use that functions I got this error: ERROR: Load of file /usr/local/pgsql/lib/pltcl.so failed: libtcl8.2.so: cannot open shared object file: No such file or directory Im running RH6.2 Thank you for your help. Rocael. Get free email and a permanent address at http://www.netaddress.com/?N=1
[SQL] trying to pattern match to a value contained in a column
Hi- I can't figure out how to do this I examine a table where I think that one attribute is an abbreviation of another attribute. So-If I had a table where I had LONG_NAME and ABBR as attributes. I want something like SELECT whatever FROM my_table WHERE long_name LIKE '%[the value of ABBR in that row]%'; Of course this doesn't work... Any thoughts? Thanks- Beth
RE: [SQL] trying to pattern match to a value contained in a column
Hi Beth,
Try something like this ...
Here's a simple table schema:
CREATE TABLE abbrev (
abbr varchar(10),
long_name varchar(50),
primary key(abbr)
);
Throw in some random data:
INSERT INTO abbrev VALUES ('fs', 'fsolomon');
INSERT INTO abbrev VALUES ('bg', 'bgatewood');
INSERT INTO abbrev VALUES ('junk', 'nomatch');
Query the table:
SELECT * FROM abbrev WHERE long_name~abbr;
... which yields these results:
abbr | long_name
--+---
fs | fsolomon
bg | bgatewood
Note that ~ does a case-sensitive regex match. If you really want a
'like' match, you could do this instead:
SELECT * FROM abbrev where long_name~~('%' || abbr || '%');
... where '||' is the string-concatenation operator.
Hope this helps
Francis Solomon
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Beth Gatewood
> Sent: 07 December 2000 21:06
> To: [EMAIL PROTECTED]
> Subject: [SQL] trying to pattern match to a value contained
> in a column
>
>
> Hi-
>
> I can't figure out how to do this
>
> I examine a table where I think that one attribute is an
> abbreviation of
> another attribute.
>
> So-If I had a table where I had LONG_NAME and ABBR as attributes.
>
> I want something like
>
> SELECT whatever FROM my_table WHERE long_name LIKE '%[the
> value of ABBR
> in that row]%';
>
>
> Of course this doesn't work...
>
> Any thoughts?
>
> Thanks-
> Beth
>
>
>
Re: [SQL] trying to pattern match to a value contained in a column
Hi Francis-
Thank you for your rapid and excellent response.
This makes perfect sense...unfortunately it isn't working...
I hope this isn't because I am using 6.3 (yes...I know it is very very
old but this is currently where the data is!)
here is the query:
select * from av34s1 where chromat ~~ ('%' || sample || '%');
ERROR: parser: syntax error at or near "||"
I have also tried using LIKE
samething..
NOW..
select * from av34s1 where chromat~sample;
ERROR: There is no operator '~' for types 'bpchar' and 'bpchar'
You will either have to retype this query using an explicit
cast,
or you will have to define the operator using CREATE OPERATOR
Indeed...
Table= av34s1
+--+--+---+
| Field | Type|
Length|
+--+--+---+
| contig | char()
|10 |
| contig_pos | char()
|10 |
| read_pos | char()
|10 |
| chromat | char()
|30 |
| sample | char()
|30 |
| allele1 | char()
|10 |
| allele2 | char()
|10 |
| ref_pos | char()
|10 |
| ref_sample | char()
|10 |
| tag | char()
|10 |
| source | char()
|10 |
+--+--+---+
Thanks for your response...
Beth
Francis Solomon wrote:
> Hi Beth,
>
> Try something like this ...
>
> Here's a simple table schema:
>
> CREATE TABLE abbrev (
> abbr varchar(10),
> long_name varchar(50),
> primary key(abbr)
> );
>
> Throw in some random data:
>
> INSERT INTO abbrev VALUES ('fs', 'fsolomon');
> INSERT INTO abbrev VALUES ('bg', 'bgatewood');
> INSERT INTO abbrev VALUES ('junk', 'nomatch');
>
> Query the table:
>
> SELECT * FROM abbrev WHERE long_name~abbr;
>
> ... which yields these results:
>
> abbr | long_name
> --+---
> fs | fsolomon
> bg | bgatewood
>
> Note that ~ does a case-sensitive regex match. If you really want a
> 'like' match, you could do this instead:
>
> SELECT * FROM abbrev where long_name~~('%' || abbr || '%');
>
> ... where '||' is the string-concatenation operator.
>
> Hope this helps
>
> Francis Solomon
>
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]]On Behalf Of Beth Gatewood
> > Sent: 07 December 2000 21:06
> > To: [EMAIL PROTECTED]
> > Subject: [SQL] trying to pattern match to a value contained
> > in a column
> >
> >
> > Hi-
> >
> > I can't figure out how to do this
> >
> > I examine a table where I think that one attribute is an
> > abbreviation of
> > another attribute.
> >
> > So-If I had a table where I had LONG_NAME and ABBR as attributes.
> >
> > I want something like
> >
> > SELECT whatever FROM my_table WHERE long_name LIKE '%[the
> > value of ABBR
> > in that row]%';
> >
> >
> > Of course this doesn't work...
> >
> > Any thoughts?
> >
> > Thanks-
> > Beth
> >
> >
> >
[SQL] postmaster restart error
Hi all!
I'm using postgresql7.0.2
I did
ipcclean
and then
pg_ctl stop
then I tried to start again the DB server as usual
nohup postmaster -i > pgserver.log 2>&1 &
I got this error:
FATAL 2: Read("/usr/local/pgsql/data/pg_control") failed:2
Startup failed - abort.
Somebody know what could be wrong? and how to start my DB again...
Thank you,
Rocael
Get free email and a permanent address at http://www.netaddress.com/?N=1
[SQL] restart postmaster error
Hi! I did as postgres user ipcclean then pg_ctl stop but then when I tried to start again the server as usual I couldn't, I just doesn't do anything. Somebody know whats going on? Thank you, Rocael. Get free email and a permanent address at http://www.netaddress.com/?N=1
Re: [SQL] how to execute a C program via trigger ?
Hi, Is any other SQL implicit cursor attribute in PL/plsql ?? when you say (in pl/plsql): select field into v_1 from atable where whatever; special variable FOUND can be used to tell return is null or not. this functions like SQL%FOUND or SQL%NOTFOUND in Oracle, however, when I do some DML(insert,delete,update), is there any other special variable can tell me howmany success. Like SQL%ROWCOUNT in Orcale?? And if there is an error such as : cannot insert since duplicate key on an unique index, is it possible to catch it?? Thanks. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] trying to pattern match to a value contained in a column
> This makes perfect sense...unfortunately it isn't working...
>
> I hope this isn't because I am using 6.3 (yes...I know it is very very
> old but this is currently where the data is!)
>
> here is the query:
>
> select * from av34s1 where chromat ~~ ('%' || sample || '%');
>
>
> ERROR: parser: syntax error at or near "||"
>
> I have also tried using LIKE
>
> samething..
>
> NOW..
> select * from av34s1 where chromat~sample;
>
> ERROR: There is no operator '~' for types 'bpchar' and 'bpchar'
> You will either have to retype this query using an explicit
> cast,
> or you will have to define the operator using CREATE OPERATOR
>
The suggestion works fine w/recent versions; perhaps it's a '6.3
thing'
Perhaps
SELECT * FROM tbl WHERE chromat::TEXT ~~ ('%' || sample || '%'
)::TEXT;
?
Also, upgrading isn't difficult in most cases; you can pg_dumpall and
upgrade and restore your files. 7.0 has many nice features over the
6.x series.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
Re: [SQL] trying to pattern match to a value contained in a column
Beth Gatewood <[EMAIL PROTECTED]> writes:
> I hope this isn't because I am using 6.3 (yes...I know it is very very
> old but this is currently where the data is!)
> here is the query:
> select * from av34s1 where chromat ~~ ('%' || sample || '%');
> ERROR: parser: syntax error at or near "||"
I seem to recall that || (and most other operators) wasn't
considered associative by the grammar way back when.
Try a fully parenthesized expression:
select * from av34s1 where chromat ~~ (('%' || sample) || '%');
regards, tom lane
PS: And do think about updating soon, hmm?
Re: [SQL] trying to pattern match to a value contained in a column
Beth -
Both errors you describe are due to using 6.3. The first one might work if
you parenthize the repeated use of ||, as so:
select * from av34s1 where chromat ~~ (('%' || sample ) || '%');
Ross
On Thu, Dec 07, 2000 at 01:45:00PM -0800, Beth Gatewood wrote:
> Hi Francis-
>
> Thank you for your rapid and excellent response.
>
> This makes perfect sense...unfortunately it isn't working...
>
> I hope this isn't because I am using 6.3 (yes...I know it is very very
> old but this is currently where the data is!)
>
> here is the query:
>
> select * from av34s1 where chromat ~~ ('%' || sample || '%');
>
>
> ERROR: parser: syntax error at or near "||"
>
> I have also tried using LIKE
>
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
[SQL] FOREIGN KEY errors.
When trying to alter a table and add a foreign key, I am getting this error if the table has any data in it: playpen=# alter table message add FOREIGN KEY (pod,originator) REFERENCES usertable (podkey,userkey); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: constraint : table usertable does not have an attribute originator If I do the alter before I put any data in the table: playpen=# alter table message add FOREIGN KEY (pod,originator) REFERENCES usertable (podkey,userkey); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) CREATE playpen=# select version(); version - PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 (1 row) Reversing the order creates a different message: playpen=# alter table message add FOREIGN KEY (originator,pod) REFERENCES usertable (userkey,podkey); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: constraint : table usertable does not have an attribute pod Am I just misunderstanding how to use FOREIGN KEY? Then why would it work one time and not the other? http://www.postgresql.org/docs/aw_pgsql_book/node159.html doesn't have any column names after 'refrences '. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
[SQL] Unable to convert null timestamp to date. Bug?
Strange, this works: select * from users where last_visit > now() + 7; -- last_visit is nullable, of type timestamp But this doesn't select * from users where last_visit + 7 > now(); ERROR: Unable to convert null timestamp to date -- yes, there are users where last_visit IS NULL BTW, this works too: select null+1; -- obviously returns NULL System is FreeBSD 4.1, PgSQL 7.0.2 Anyone got a clue? Yours sincerely, Edmar Wiggers BRASMAP Information Systems +55 48 9960 2752
Re: [SQL] Unable to convert null timestamp to date. Bug?
"Edmar Wiggers" <[EMAIL PROTECTED]> writes: > select * from users where last_visit + 7 > now(); > ERROR: Unable to convert null timestamp to date Yeah, someone who hadn't quite grokked the concept of NULL seems to have written a lot of the date.c code :-(. This is fixed for 7.1. If it's really bothering you in 7.0.*, see src/backend/utils/adt/date.c, and change code like if (!PointerIsValid(timestamp)) elog(ERROR, "Unable to convert null timestamp to date"); to if (!PointerIsValid(timestamp)) return NULL; in several places. regards, tom lane
Re: [SQL] trying to pattern match to a value contained in a column
Beth Gatewood writes: > So-If I had a table where I had LONG_NAME and ABBR as attributes. > > I want something like > > SELECT whatever FROM my_table WHERE long_name LIKE '%[the value of ABBR > in that row]%'; SELECT whatever FROM my_table a, my_table b WHERE a.long_name like (b.abbr || '%'); -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
