Re: [SQL] calculated expressions and index use
A very dirty work around could be: select * from foo where created+at >= (select now() - '1 hour'::interval); Or perhaps this already works: select * from foo where created+at >= (now() - '1 hour'::interval); Pavel Hlavnicka wrote: Hi all, I use simple sql statement like this select * from foo where created_at >= 'now'::timestamp - '1 hour'::interval; My table is indexed on created_at field. The query above doesn't use it, but if I use select * from foo where created_at >= 'now' the index is used. It looks like if the engine finds some expression to be evaluated it gets rid of any index use (due the possible dependency?). Is it possible to do something to 'precompute' some value in the query condition, so planner understand is as a constant value? (... and I've got some reason NOT to use a parameter in my Perl DBI code...) Maybe my conclusions are wrong, of course. Thank you very much in advance Pavel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CSV import
> > Unix EOL is LF not CR. > > Is this the only difference between a dos and unix text file? Thanks Chad ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] checking data integrity in a recursive table
Luke Pascoe wrote:
> Hi, I have a table that references itself to create a tree-like structure,
> eg:
> CREATE TABLE tree (
> id SERIAL NOT NULL,
> name VARCHAR(255) NOT NULL,
> parent INT NULL,
> customer IN NOT NULL,
> CONSTRAINT parent_key...
> CONSTRAINT customer_fk FOREIGN KEY (customer) REFERENCES customer
> );
> ALTER TABLE tree ADD CONSTRAINT FOREIGN KEY (parent) REFERENCES tree;
>
> As you can see tree also references the customer table.
>
> What I need is a CHECK that will ensuer that any given "tree" row has the
> same customer as its parent.
> Remember that "parent" can also be NULL.
>
> Or would this be better done as a trigger?
Good question. I don't think you can do actualy SQL lookups in a CHECK.
I think you will need a trigger, either in pl/pgsql or in C using SPI to
issue the lookup queries.
You can have a CHECK clause that deals with multiple columns:
CREATE TABLE friend2 (
firstname CHAR(15),
lastname CHAR(20),
city CHAR(15),
state CHAR(2) CHECK (length(trim(state)) = 2),
age INTEGER CHECK (age >= 0),
genderCHAR(1) CHECK (gender IN ('M','F')),
last_met DATE CHECK (last_met BETWEEN '1950-01-01'
AND CURRENT_DATE),
CHECK (upper(trim(firstname)) != 'ED' OR
upper(trim(lastname)) != 'RIVERS')
);
However, that doesn't help you because you can't reference a column in
another row of the same table.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CSV import
Chad Thompson schrieb: Unix EOL is LF not CR. Is this the only difference between a dos and unix text file? Yes, but to be more precise: dos: CR + LF unix: LF mac: CR Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Converting clarion
Dear friends, i'm looking for some program that converts clarion database files to sql instructions. Someone can help-me ? thanks in advance. Nasair Jr. da Silva Lajeado - RS - Brasil xx===xx || °v° Nasair Junior da Silva || || /(_)\ Linux User: 246054 || || ^ ^ [EMAIL PROTECTED]|| ||CPD - Desenvolvimento || ||Univates - Centro Universitário|| xx===xx ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Converting clarion
Nasair Junior da Silva schrieb: Dear friends, i'm looking for some program that converts clarion database files to sql instructions. Someone can help-me ? Try Google with: clarion sql convert My first result is: http://www.clarionmag.com/cmag/v1/v1n4convertingtosql.html HTH Oliver -- VECERNIK Datenerfassungssysteme A-2560 Hernstein, Hofkogelgasse 17 Tel.: +43 2633 47530, Fax: DW 50 http://members.aon.at/vecernik ---(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
Re: [SQL] help: triggers
Wei Weng wrote:
It would be better if you could provide the source of that trigger and involved
table schemas?
CREATE SEQUENCE "iobjects_id_seq" start 1 increment 1 maxvalue
9223372036854775807 minvalue 1 cache 1;
GRANT ALL on "iobjects_id_seq" to "jantos";
CREATE TABLE "iobjects" (
"id" integer DEFAULT nextval('"iobjects_id_seq"'::text) NOT NULL,
"status" character varying(10) DEFAULT 'Activo' NOT NULL,
"local" character varying(80) NOT NULL,
"fig_1" character varying(80),
"fig_2" character varying(80),
"fig_3" character varying(80),
"introduzido" timestamp with time zone DEFAULT now() NOT NULL,
"actual" timestamp with time zone DEFAULT now() NOT NULL,
"tempo" integer DEFAULT 0 NOT NULL,
"deleted" character(1) DEFAULT '0',
Constraint "pk_iobjects" Primary Key ("id")
);
GRANT ALL on "iobjects" to "jantos";
Wei
- Original Message -
From: "Tony Simbine" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, January 29, 2003 8:29 AM
Subject: [SQL] help: triggers
hello,
I'm trying to update a columm on a table with a trigger
but it don't work on PostgreSQL 7.2 (i686-pc-linux-gnu, compiled by GCC
2.96).
when I update a row the trigger does nothing.
what can I do?
thanks in advance
tony
here is my trigger:
CREATE FUNCTION update_my_objekt() RETURNS OPAQUE AS '
BEGIN
RAISE NOTICE ''My Trigger is updating the table'';
IF OLD.status=''Activo'' THEN
NEW.tempo := 10;
NEW.actual := now();
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER update_online BEFORE UPDATE ON iobjects
FOR EACH ROW EXECUTE PROCEDURE update_my_objekt();
##
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Question about passing User defined types to functions
is there a example on how to pass user defined types into a function?? What I am looking for is something of this nature. CREATE TYPE dumby_type AS (dumby_id int4, dumby_name text); create function kick_dumby(dumby dumby_type) returns INTEGER AS ' DECLARE somenumber integer; BEGIN return 1; END; ' language 'plpgsql'; Is there some way of doing this, because the above doesn't work. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Delete 1 Record of 2 Duplicate Records
How do I delete only 1 of the duplicate records? column_name | column_id -+-- test1 | 5 test1 | 5 I've tried this: tmp_test=# delete from test where column_id = 5 limit 1; ERROR: parser: parse error at or near "limit" I'm using version 7.2.1 Thank you. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Delete 1 Record of 2 Duplicate Records
On Thu, 30 Jan 2003 [EMAIL PROTECTED] wrote: > How do I delete only 1 of the duplicate records? Do select oid,* from test where column_id = 5; then choose which oid to delete and do delete from test where oid = ...; > > > column_name | column_id > -+-- > test1 | 5 > test1 | 5 > > > I've tried this: > > tmp_test=# delete from test where column_id = 5 limit 1; > ERROR: parser: parse error at or near "limit" > > I'm using version 7.2.1 > > Thank you. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] double linked list
I've seen CELKO's reply and find it very useful. But I cannot find anything about > BEGIN ATOMIC > DECLARE rightmost_spread INTEGER; > > SET rightmost_spread > = (SELECT rgt > FROM Frammis > WHERE part = 'G'); > ... Is this PostgreSQL at all? Any hints welcome. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Function executing twice
Hello, I'm executing a function with this command: select function_name(aaa,bbb,ccc); When I do it on PSQL or PGAdmin it works perfectly. However, on my application the function is executed twice. Has anybody faced a problem like this? I'm using PostgreSQL 7.2.3 and ODBC conection. Thanks
Re: [SQL] double linked list
On Thursday 30 January 2003 07:10, Christoph Haller wrote: > I've seen CELKO's reply and find it very useful. > But I cannot find anything about > > > BEGIN ATOMIC > > DECLARE rightmost_spread INTEGER; > > > > SET rightmost_spread > > = (SELECT rgt > > FROM Frammis > > WHERE part = 'G'); > > ... > > Is this PostgreSQL at all? Any hints welcome. Mr Haller No, this is a dialect SQL-92 (SQL-99?) that Mr Celko uses for his examples since his solutions are vendor nutral. He is big on standards, so posting using the standard is his way of boosting them. BEGIN ATOMIC is BEGIN in PG. I am not sure how to declare a variable in PG in normal SQL. I don't do it that often, but when I do I do this: CREATE TEMPORARY TABLE Rightmost_Spread AS SELECT rightmost_spread FROM Frammis WHERE part = 'G'; I wonder what the alterntatives are? Alan Gutierrez - [EMAIL PROTECTED] http://khtml-win32.sourceforge.net/ - KHTML on Windows ---(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
Re: [SQL] double linked list
> > On Thursday 30 January 2003 07:10, Christoph Haller wrote: > > I've seen CELKO's reply and find it very useful. > > But I cannot find anything about > > > > > BEGIN ATOMIC > > > DECLARE rightmost_spread INTEGER; > > > > > > SET rightmost_spread > > > =3D (SELECT rgt > > > FROM Frammis > > > WHERE part = 'G'); > > > ... > > > > Is this PostgreSQL at all? Any hints welcome. > > Mr Haller > > No, this is a dialect SQL-92 (SQL-99?) that Mr Celko uses for his > examples since his solutions are vendor neutral. He is big on standards, > so posting using the standard is his way of boosting them. > > BEGIN ATOMIC is BEGIN in PG. > > I am not sure how to declare a variable in PG in normal SQL. I don't do > it that often, but when I do I do this: > > CREATE TEMPORARY TABLE Rightmost_Spread > AS SELECT rightmost_spread > FROM Frammis > WHERE part = 'G'; > > I wonder what the alternatives are? > plpgsql is the best I can think of. And thanks for the quick reply. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Filter function
Evgen Potemkin <[EMAIL PROTECTED]> writes: > it's base-7.3.1 from one of the russian mirrors. Oh, it must be a post-7.3.1 fix then [ ... checks CVS logs ... ] ah, here it is: 2002-12-27 15:06 tgl * src/backend/parser/parse_expr.c (REL7_3_STABLE): Deliver better error message when a relation name is used in an expression. Per report from Ian Barwick. This will be in 7.3.2, due out next week. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Delete 1 Record of 2 Duplicate Records
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If they are truly identical, then you must use the hidden 'oid' column to differentiate the two. No need to peek at the oid, just do this: DELETE FROM test WHERE oid = (SELECT oid FROM test WHERE column_id=5 LIMIT 1); - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200301301006 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE+OT+AvJuQZxSWSsgRAgZOAKCrwW2O/bQpxo5LBBp4vDkS8YoZ9wCg2H7N R9R4CTSXx/lRmjm5NvZkYXE= =VI0G -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Function executing twice
I think you should first check your application logic, for example print
something out just before calling the function. Then you can easily see, if
the problem is in PostgreSQL or in your application.
I once had similar problem, when I used function as argument to COALESCE.
COALESCE is translated to CASE before evaluating the arguments. For example
COALESCE(id,nextval('id_seq')) becomes CASE WHEN id IS NOT NULL THEN id WHEN
nextval('id_seq') IS NOT NULL THEN nextval('id_seq') ELSE NULL END. As you
can see, when id is null, the sequence id_seq is incremented twice. Solution
was quite simple, I just used CASE directly: CASE WHEN id IS NOT NULL THEN
id ELSE nextval('id_seq') END.
Tambet
- Original Message -
From: Thiago Conti
To: pgsql-sql
Sent: Thursday, January 30, 2003 4:54 PM
Subject: [SQL] Function executing twice
Hello,
I'm executing a function with this command:
select function_name(aaa,bbb,ccc);
When I do it on PSQL or PGAdmin it works perfectly.
However, on my application the function is executed twice.
Has anybody faced a problem like this?
I'm using PostgreSQL 7.2.3 and ODBC conection.
Thanks
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Question about passing User defined types to functions
> > CREATE TYPE dumby_type AS (dumby_id int4, dumby_name text); > > create function kick_dumby(dumby dumby_type) returns INTEGER AS ' > DECLARE > somenumber integer; > BEGIN > return 1; > END; > ' language 'plpgsql'; > > > Is there some way of doing this, because the above doesn't work. > After having a look into the documentation on CREATE TYPE I would say the statement looks very wrong. But I haven't done any user defined type so far, so I can't be of any help in this case. The CREATE FUNCTION statement is not considered to accept parameter names within the parameter list. So use create function kick_dumby(dumby_type) returns INTEGER AS ' DECLARE dumby ALIAS FOR $1; ... Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question about passing User defined types to functions
"David Durst" <[EMAIL PROTECTED]> writes: > CREATE TYPE dumby_type AS (dumby_id int4, dumby_name text); > create function kick_dumby(dumby dumby_type) returns INTEGER AS ' Should be create function kick_dumby(dumby_type) returns INTEGER AS '... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] calculated expressions and index use
Hi all, I use simple sql statement like this select * from foo where created_at >= 'now'::timestamp - '1 hour'::interval; My table is indexed on created_at field. The query above doesn't use it, but if I use select * from foo where created_at >= 'now' the index is used. It looks like if the engine finds some expression to be evaluated it gets rid of any index use (due the possible dependency?). Is it possible to do something to 'precompute' some value in the query condition, so planner understand is as a constant value? (... and I've got some reason NOT to use a parameter in my Perl DBI code...) Maybe my conclusions are wrong, of course. Thank you very much in advance Pavel -- Pavel Hlavnicka Ginger Alliance www.gingerall.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] calculated expressions and index use
Pavel Hlavnicka <[EMAIL PROTECTED]> writes: > select * from foo > where created_at >= 'now'::timestamp - '1 hour'::interval; > My table is indexed on created_at field. The query above doesn't use it, What's your PG version? In 7.2 and later that expression will be folded to a constant. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
FW: [SQL] Converting clarion
Title: FW: [SQL] Converting clarion -Original Message- From: Michael Weaver Sent: Friday, 31 January 2003 10:16 AM To: '[EMAIL PROTECTED]' Subject: RE: [SQL] Converting clarion If you have access to the Clarion database tools then it's really a pretty trivial task to export scripts to somthing more readable. If you don't have access to any of the tools that created your database, eg. you are coverting a client off a TPS based DB to a PostgreSQL DB, you've got a problem. TPS seems to be closly guarded file format. I have not been able to find any publicly avalible information or utils to help. -( other than there is not help. ) There are ODBC drivers for TPS files avalible (http://www.softvelocity.com/products/pr_database_tsodbc.htm) They cost somewhere in the order of $250USD There are a couple of applications that can extract data from the TPS files and export then as flatfiles, but these are quite hard to get hold of as they are part of the Clarion tool set. - Clarion programmers are your friends... Still, the ODBC Driver is probably your best bet as it allows 'no-mess no-fuss' dumping of your data directly into Postgres. ;) Mike Weaver. > -Original Message- > From: Nasair Junior da Silva [mailto:[EMAIL PROTECTED]] > Sent: Friday, 31 January 2003 4:37 AM > To: [EMAIL PROTECTED] > Subject: [SQL] Converting clarion > > > Dear friends, > i'm looking for some program that converts clarion database > files to sql instructions. > > Someone can help-me ? > > thanks in advance. > > Nasair Jr. da Silva > Lajeado - RS - Brasil > > > xx===xx > || °v° Nasair Junior da Silva || > || /(_)\ Linux User: 246054 || > || ^ ^ [EMAIL PROTECTED] || > ||CPD - Desenvolvimento || > ||Univates - Centro Universitário|| > xx===xx > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to > [EMAIL PROTECTED] >
Re: [SQL] How to rename and drop a column in pg7.3?
Hmmm... both are supported in 7.3.x ALTER TABLE rtfm RENAME f1 to f2 ; ALTER TABLE rtfm DROP COLUMN f3; hope it helps. regds mallah. > > I need to rename and remove columns of a table. Is any new way to accomplish the two >tasks in > 7.3? I have searched the online document and don't see any related information. > > Thanks, > > Vernon > > > > ---(end of broadcast)--- TIP 2: you >can get off > all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] design review, FreshPorts change
Hi folks, I know a number of you use FreeBSD and my FreshPorts website. I've just posted http://www.freshports.org/docs/404-for-virtual-pages.php which contains some proposed changes. Of note is the use of a rule to update a cross reference table. I'd appreciate feedback please, both technical and user. Cheers -- Dan Langille : http://www.langille.org/ ---(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
