[SQL] postgreSQL 8beta
Hi, does postgresql have a datatype 'other' which in hsqldb is an Object? I am trying to convert the table below into postgreSQL 8: create table TIMERS ( TIMERID varchar(50) not null, TARGETID varchar(50) not null, INITIALDATE timestamp not null, INTERVAL bigint, INSTANCEPK other, INFO other, constraint timers_pk primary key (TIMERID) regards Andrew
Re: [SQL] [GENERAL] how to use COPY within plperl
Why can you not use simple insert statements (sql insert).
Copy is meant to transfer large amount of data from text files to
databases and vice versa.
- Goutam
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Marek Lewczuk
> Sent: Monday, November 08, 2004 3:59 AM
> To: Lista dyskusyjna pgsql-general; Lista dyskusyjna pgsql-sql
> Subject: [GENERAL] how to use COPY within plperl
>
>
> Hello,
> I need to use COPY (instead of INSERT) within plperl
> function. I know that COPY will work if data will be taken
> from file - however I need to use STDIN. I tried this:
> spi_exec_query("COPY sometable (field1, field2) FROM
> stdin;"."\n"."sometext"."\t"."sometext"."\n"."\.")
>
> But it didn't work. Thanks in advance.
>
>
>
> ---(end of
> broadcast)---
> TIP 9: the planner will ignore your desire to choose an index
> scan if your
> joining column's datatypes do not match
>
Confidentiality Notice
The information contained in this e-mail is confidential and intended for use
only by the person(s) or organization listed in the address. If you have
received this communication in error, please contact the sender at O'Neil &
Associates, Inc., immediately. Any copying, dissemination, or distribution of
this communication, other than by the intended recipient, is strictly
prohibited.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] A transaction in transaction? Possible?
Andrei Bintintan wrote: //Is it possible to have another transatction in a transaction??? In the following example the last ROLLBACK is totally ignored(transaction1). ///connect to database/ $database = dbConnect($dbhost, $dbuser, $dbpass, $dbname); dbExec($database, "BEGIN"); //transaction1 ///*/ //dbExec($database, "BEGIN");//transaction2 $sql = "UPDATE orders SET technikernotiz='51' WHERE id=16143"; dbExec($database, $sql); dbExec($database, "COMMIT");//transaction2 //**/ / $sql = "UPDATE orders SET reklamationsdetail='51' WHERE id=16143"; dbExec($database, $sql); dbExec($database, "ROLLBACK");//transaction1 dbClose($database); This appears to be the same as Oracle's "autonomous transactions", fwiw. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] select using regexp does not use indexscan
This is what I get with postgres-7.3 (from Redhat Enterprise !!) Here below a select with a regexp ansroc=# explain select * from s12hwdb where host~'^tna2582t'; QUERY PLAN - Seq Scan on s12hwdb (cost=0.00..30660.35 rows=1 width=128) Filter: (host ~ '^tna2582t'::text) (2 rows) Here below the same select without regexp ansroc=# explain select * from s12hwdb where host='tna2582t'; QUERY PLAN - Index Scan using s12hwdb_host_rit_idx on s12hwdb (cost=0.00..18123.85 rows=4828 width=128) Index Cond: (host = 'tna2582t'::bpchar) (2 rows) ansroc=# As you can see, the index is not use when a regexp is used in the select. I did the same test with postgres-7.3.6 & postgres-7.4.6 (compiled from sources) but the results where the same. (index is NEVER used with regexp on a RHE) I even tried with a 'set enable_seqscan to off', but the result is the same. BUT, with Debian (woody & sarge) everything is ok. (has always been with debian-:) I did try with a postgres debian pachage, and also with a postgres compiled from source, and even with different version (7.3.4, 7.4.6). Index is always used ! ansroc=# explain SELECT * FROM s12hwdb where host~'^tna2582t'; QUERY PLAN -- Index Scan using s12hwdb_host_rit_idx on s12hwdb (cost=0.00..4.41 rows=1 width=128) Index Cond: ((host >= 'tna2582t'::bpchar) AND (host < 'tna2582u'::bpchar)) Filter: (host ~ '^tna2582t'::text) (3 rows) ansroc=# explain SELECT * FROM s12hwdb where host='tna2582t'; QUERY PLAN Index Scan using s12hwdb_host_rit_idx on s12hwdb (cost=0.00..76.02 rows=17 width=128) Index Cond: (host = 'tna2582t'::bpchar) (2 rows) ansroc=# And it works also perfectly with Gentoo. So,is this a typical "Redhat Enterprise" problem ? Or do I overlook something ?? Has someone experienced the same problem ?? Thanks. carex. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] select using regexp does not use indexscan
On Tue, 9 Nov 2004, carex wrote: > And it works also perfectly with Gentoo. > > So,is this a typical "Redhat Enterprise" problem ? > Or do I overlook something ?? IIRC, in 7.3.x, index scans are only considered in "C" locale for regexp/LIKE. In 7.4.x, non-"C" locale databases can use a special index of a different opclass (_pattern_ops I believe). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] postgreSQL 8beta
> does postgresql have a datatype 'other' which in hsqldb is an Object? I > am trying to convert the table below into postgreSQL 8: > > create table TIMERS ( > TIMERID varchar(50) not null, > TARGETID varchar(50) not null, > INITIALDATE timestamp not null, > INTERVAL bigint, > INSTANCEPK other, > INFO other, > constraint timers_pk primary key (TIMERID) For INFO use either TEXT or BYTEA depending on what you want to store. Regarding INSTANCEPK you should think again whether OTHER is an appropriate datatype in the first place. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select using regexp does not use indexscan
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Tue, 9 Nov 2004, carex wrote: >> And it works also perfectly with Gentoo. >> So,is this a typical "Redhat Enterprise" problem ? >> Or do I overlook something ?? > IIRC, in 7.3.x, index scans are only considered in "C" locale for > regexp/LIKE. In 7.4.x, non-"C" locale databases can use a special > index of a different opclass (_pattern_ops I believe). Not sure if this answer was explicit enough, so: evidently the database was initdb'd in "C" locale on Gentoo, but in some other locale on Red Hat. The only "typical Red Hat problem" is that they are more enthusiastic about setting up non-C default locales than some other distros. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] upper/lower for german characters
Hi Markus,
I tried different types of encoding, LATIN1 to LATIN 9, still the same
behavior. I read a few forums, but there I couldn't find a concrete answer
for this.
So... still searching.
Best regards,
Andy.
- Original Message -
From: "Markus Schaber" <[EMAIL PROTECTED]>
To: "Andrei Bintintan" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, November 09, 2004 7:59 PM
Subject: Re: [SQL] upper/lower for german characters
Hi, Andrei,
On Tue, 9 Nov 2004 16:58:27 +0200
"Andrei Bintintan" <[EMAIL PROTECTED]> wrote:
> Hi to all, I have the problem that:
> select lower('MöBÜEL') or select upper('MöBÜEL') are not working well.
>
> I read on some forums that there is some locale setting that needs to
> be done here, but could not fix this.
>
> I am using the ASCII encoding.
By definition, ASCII does not contain any umlauts.
So I would advise that, first, you switch to an umlaut capable encoding
(e. G. Latin1, Latin9 or UTF-8).
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] UPDATE/INSERT on multiple co-dependent tables
Is it possible for an UPDATE/INSERT query string to function in such a way that it requires two like fields in different tables to be equal to/'in sync with' one another: Example: I have two tables: registration & schedules they both record a class_id, start_date, end_date... I want to make sure that if the schedule_id field is updated in the registration table; that class_id, start_date & end_date fields automatically change to match the schedules.id record in the schedules table I've devised a function to handle this but pgsql recognizes the query to be 'infinitely recursive: CREATE RULE registration_update AS ON UPDATE TO registration DO UPDATE registration SET class_id = schedules.class_id WHERE new.schedule_id = (SELECT schedules.id FROM schedules WHERE id = new.schedule_id); What I'm doing is kind of redundant but necessary for backwards-compatibility -- www.sleekcollar.com Ferindo Middleton, Jr. Chief Architect Sleekcollar Internet Application & Artistic Visualizations [EMAIL PROTECTED]
