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... >

[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 techni

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

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 scrip

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 -- VECE

[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 || ||

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:

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] 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.

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

[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

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 ---

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

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

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 [EMA

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 whe

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 Fram

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 pa

[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.

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, Chr

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 > -+-- > test

[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

[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;

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" (