Re: [GENERAL] empty string causes planner to avoid index. Makes me sad.

2009-11-27 Thread Stephan Szabo
On Fri, 27 Nov 2009, Jeff Amiel wrote: > --- On Fri, 11/27/09, Tom Lane wrote: > > > You didn't show us any evidence of that, either.? Both > > of your test > > cases are using the index. > > Ok...third try. The cost when passing in an empty string is > SIGNIFICANTLY higher than when not. Would

Re: [GENERAL] What order of steps of the postgres when you change information in the table?

2009-10-31 Thread Stephan Szabo
On Sat, 31 Oct 2009, Denis Feklushkin wrote: > > Problem: > It is necessary to synchronize the "users" table with an > external storage of passwords (krb5) > > I made a trigger: > > CREATE TRIGGER "10_krb5" > AFTER INSERT OR UPDATE OR DELETE > ON users > FOR EACH ROW > EXECUTE PROCEDURE u

Re: [GENERAL] interface for "non-SQL people"

2009-10-09 Thread Stephan Szabo
On Fri, 9 Oct 2009, Joshua D. Drake wrote: > On Thu, 2009-10-08 at 19:16 -0600, Scott Marlowe wrote: > > On Thu, Oct 8, 2009 at 2:22 PM, Merlin Moncure wrote: > > > > > > The #1 tool you have at your disposal is the human brain. I > > > personally think GUI database tools are counter productive

Re: [GENERAL] bytea question

2009-09-28 Thread Stephan Szabo
On Mon, 28 Sep 2009, Maximilian Tyrtania wrote: > testdb=# create table byteatest(blob bytea); > CREATE TABLE > testdb=# insert into byteatest (blob) values (E'\\007'); > INSERT 0 1 > testdb=# insert into byteatest (blob) values (E'\\008'); > ERROR: invalid input syntax for type bytea > LINE 1: i

Re: [GENERAL] Foreign Key Deferrable Misunderstanding or Bug?

2009-08-06 Thread Stephan Szabo
On Thu, 6 Aug 2009, Paul Rogers wrote: > Why does the attached script fail with a foreign key constraint violation? Referential actions are not deferred when a constraint is marked deferrable (as that appears to be what the spec wants), so ON DELETE RESTRICT will still fail on the statement, whil

Re: [GENERAL] column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!

2009-06-09 Thread Stephan Szabo
On Tue, 9 Jun 2009, G. Allegri wrote: > Hello list. > I'm a newbie with plpgsql, so I'm sorry for doing stupid questions... > I have a situation whit one table where items are related to two other > tables through a common id (unique in the first table) and the table > name. Whenever the user exec

Re: [GENERAL] Problem defining deferred check constraints

2009-01-25 Thread Stephan Szabo
On Sun, 25 Jan 2009, Thomas Kellerer wrote: > Hi, > > I'm playing around with deferred constraints and according to the manual, it > should be possible to declare a check constraint as deferred. > > At least that's how I read the definition of /column_constraint/ at > http://www.postgresql.org/do

Re: [GENERAL] Question about NOT NULL and default values.

2008-10-16 Thread Stephan Szabo
On Thu, 16 Oct 2008, Scott Marlowe wrote: > On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo > <[EMAIL PROTECTED]> wrote: > > On Fri, 17 Oct 2008, Tim Uckun wrote: > > > >> Is there a way to change this behavior so that an attempt to set the > >> colum

Re: [GENERAL] Question about NOT NULL and default values.

2008-10-16 Thread Stephan Szabo
On Fri, 17 Oct 2008, Tim Uckun wrote: > Is there a way to change this behavior so that an attempt to set the > column to NULL will result in the default value being put in the > field? I don't think so specifically with default, but you could use a before trigger instead that would put in a value

Re: [GENERAL] Fwd: Set-valued function in wrong context

2008-10-09 Thread Stephan Szabo
On Thu, 9 Oct 2008, Raymond O'Donnell wrote: > gfc_bookings=# select * from make_time_series('11:00', '14:00', 30); > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "make_time_series" line 10 at for over > select rows > > Now, I know what the e

Re: [GENERAL] subquery in FROM must have an alias

2008-09-28 Thread Stephan Szabo
On Sun, 28 Sep 2008, Ashutosh Chauhan wrote: > Hi all, > > This has been asked before and answered as well. > http://archives.postgresql.org/pgsql-sql/2007-12/msg2.php but I > still cant figure out why postgres throws this error message even when > I have provided the aliases. My query: > > se

Re: [GENERAL] PL/PGSQL - character varying as function argument

2008-09-26 Thread Stephan Szabo
On Fri, 26 Sep 2008, Chris Baechle wrote: > When I try to run it with: > select user_checkCredentials("asdf"); Actually, I think the function probably isn't at fault here, string literals should be surrounded with ' not ". -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To

Re: [GENERAL] Help with a foreign key with non-unique reference?

2008-09-16 Thread Stephan Szabo
On Tue, 16 Sep 2008, Brent Wood wrote: > I need a foreign key (or equivalent) where the referenced table cannot > have a unique constraint. Well, do you need a full foreign key or just the insert-time check on the referencing table? Does the referenced table get updates or deletes that you want t

Re: [GENERAL] Problem with trigger function

2008-09-03 Thread Stephan Szabo
On Wed, 3 Sep 2008, Mira Dimitrijevic wrote: > Hi, > I wrote the trigger function below and when trying to execute it, I > get the following error: > > 15:00:42 [CREATE - 0 row(s), 0.000 secs] [Error Code: 0, SQL > State: 42601] ERROR: syntax error at or near "INSERT" > > I am using DBVisuali

Re: [GENERAL] deleting the master but not the detail

2008-07-17 Thread Stephan Szabo
On Thu, 17 Jul 2008, Ismael wrote: > So is there no other way to do it but to verify the integrity using triggers > and drop the referential constraints? Well, you could do something using a before delete trigger on the referencing table that returns NULL to avoid the delete as well, but mak

Re: [GENERAL] Script errors on run

2008-06-04 Thread Stephan Szabo
On Wed, 4 Jun 2008, Ralph Smith wrote: >-- == >good_date := to_date(year||'-'||month||'-'||day , '-MM-DD') ; > >RAISE INFO 'good_date = %', good_date ; > >UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ; You want something like: UsecsD

Re: [GENERAL] Script errors on run

2008-06-04 Thread Stephan Szabo
On Wed, 4 Jun 2008, Ralph Smith wrote: >date_string := to_date(year||'-'||month||'-'||day , '-MM-DD') ; >RAISE INFO 'date_string = %', date_string ; >good_date := to_timestamp(date_string, '-MM-DD') ; >RAISE INFO 'good_date = %', good_date ; This seems like alot of extra

Re: [GENERAL] Script errors on run

2008-06-04 Thread Stephan Szabo
On Wed, 4 Jun 2008, Ralph Smith wrote: >-- == >good_date := to_date(year||'-'||month||'-'||day , '-MM-DD') ; >RAISE NOTICE 'good_date = %',good_date ; >Usecs := EXTRACT(EPOCH FROM TIMESTAMP good_date) ; > END ; > > QUERY: SELECT EXTR

Re: [GENERAL] Exception handling

2008-06-04 Thread Stephan Szabo
On Wed, 4 Jun 2008, sam wrote: > Can someone explain me about the exception handling in postgresql. Iam > not understanding the scope of a exception block. The exact confusion > that iam facing is as follows: > I have a procedure as follows > BEGIN > EXECUTE an update statement > > EXECUTE an inse

Re: [GENERAL] join ... using ... and - is this expected behaviour?

2008-06-03 Thread Stephan Szabo
On Tue, 3 Jun 2008, Rob Johnston wrote: > Just wondering if this is expected behaviour. When executing a query in > the form of: > > select column from table join table using (column) and column = clause > > pgsql (8.2) returns the following: syntax error at or near "and" > > Obviously, you can ge

Re: [GENERAL] HELP with a query with blank fields

2008-06-01 Thread Stephan Szabo
On Tue, 27 May 2008, J. Manuel Velasco wrote: > Hello, > > This is the current query I have: > > SELECT dominis.nom, dominis.extensio, dominis.creat, dominis.expira, > titulars.first_name, titulars.last_name, contactes_admin_tec.first_name, > contactes_admin_tec.last_name, dns1.nom, dns2.nom, dom

Re: [GENERAL] ranked subqueries vs distinct question

2008-05-14 Thread Stephan Szabo
On Wed, 14 May 2008, Karsten Hilbert wrote: > Modifying to: > > select * from ( > > select distinct on (name) * from ( > > select *, 1 as rank from dem.urb where > name ilike 'Lei%' and > zip = '0

Re: [GENERAL] String Comparison and NULL

2008-04-29 Thread Stephan Szabo
On Mon, 28 Apr 2008 [EMAIL PROTECTED] wrote: > I'm fairly new to PG and databases in general so this may very well be > a problem in my thought process. > > If I have a simple table with an ID (integer) and Animal (text) like > this... > > 1 Dog > 2 Cat > 3 NULL > 4 Horse > 5 Pig > 6 Cat > 7 Cat >

Re: [GENERAL] Updating with a subselect

2008-04-23 Thread Stephan Szabo
On Wed, 23 Apr 2008, Leandro Casadei wrote: > On Wed, Apr 23, 2008 at 10:59 AM, Stephan Szabo < > [EMAIL PROTECTED]> wrote: > > > On Tue, 22 Apr 2008, Leandro Casadei wrote: > > > > > Hi, I need to update a field from a table based in a

Re: [GENERAL] Updating with a subselect

2008-04-23 Thread Stephan Szabo
On Tue, 22 Apr 2008, Leandro Casadei wrote: > Hi, I need to update a field from a table based in a count. > > This is the query: > > > updateshops > setitemsqty = > ( > select count(*) > from items i1 > join shops s1 on i1.shopid = s1.shopid > where s1.sh

Re: [GENERAL] SQL injection, php and queueing multiple statement

2008-04-13 Thread Stephan Szabo
On Sun, 13 Apr 2008, Ivan Sergio Borgonovo wrote: > On Sun, 13 Apr 2008 16:02:35 +0800 > Craig Ringer <[EMAIL PROTECTED]> wrote: > > > > I think this logic is already somewhere in the driver or the pg > > > engine. Whatever you write at the application level a) risk to be > > > a duplication of pa

Re: [GENERAL] mac ports question

2008-04-05 Thread Stephan Szabo
On Sat, 5 Apr 2008, Tom Allison wrote: > If it doesn't remove the 8.2 then I guess I can migrate it. > But that requires that I still need to get 8.2 running. > > > Right now it complains that it can't find a listening socket at /tmp/... > (localhost mode). And I can't find the configuration file

Re: [GENERAL] mac ports question

2008-04-05 Thread Stephan Szabo
On Sat, 5 Apr 2008, Tom Allison wrote: > I ran into a problem today where somewhere my port of postgresql82 just > stopped working. I'm largely an idiot on Mac because I use is as a > workstation/development box and do most of the real system related work > on my debian boxes. > > But I don't kno

Re: [GENERAL] how to insert values into complex type field

2008-04-04 Thread Stephan Szabo
On Fri, 4 Apr 2008 [EMAIL PROTECTED] wrote: > hi all, i want to know how to insert values into the field which is a complex > type. In fact it is a complex type which also include a complex type. The > following is its definition: > create TYPE lifetime as( strattime date, endtime date); > crea

Re: [GENERAL] unexpected results with NOT IN query

2008-03-20 Thread Stephan Szabo
On Thu, 20 Mar 2008, Mason Hale wrote: > Hello -- > > I'm getting some unexpected results with a NOT IN query -- this is on 8.2.5. > > This is the query in question: > > prod_2=> select id from feed_download_task where id in (02466,141701504) > and id not in (select last_feed_download_task_id

Re: [GENERAL] Confused about CASE

2008-02-29 Thread Stephan Szabo
On Sat, 1 Mar 2008, Thomas Kellerer wrote: > I was writing a statement retrieve dependency information out of the > system catalog, when I noticed something that I didn't expect. > > I wanted to use the following statement to "translate" the relkind > column to a more descriptive value: > > selec

Re: [GENERAL] bug in 8.3? foreign key refers to different type

2008-02-26 Thread Stephan Szabo
On Tue, 26 Feb 2008, craigp wrote: > These create table commands succeed, even tho the foreign key refers to > a 'different' type (int2 product_id column refers to an int8 column): The requirements in recent SQL specs appears to be that the column types are comparable, not the same. SQL2003 11.8

Re: [HACKERS] [GENERAL] deadlock with truncate and foreing keys

2008-02-18 Thread Stephan Szabo
On Mon, 18 Feb 2008, Tom Lane wrote: > Alexey Nalbat <[EMAIL PROTECTED]> writes: > > create table t1 ( id integer primary key, name text ); > > create table t2 ( id integer references t1 ); > > insert into t1 values ( 1 ); > > insert into t2 values ( 1 ); > > > Then two concurrent transactions sta

Re: [GENERAL] SELECT CAST(123 AS char) -> 1

2008-02-16 Thread Stephan Szabo
On Sat, 16 Feb 2008, Ken Johanson wrote: > Tom Lane wrote: > > > Hm, good point, so really we ought to have a separate casting path for > > numeric types to char(n). However, this section still doesn't offer > > any support for the OP's desire to auto-size the result; it says > > that you get an

Re: [GENERAL] SELECT CAST(123 AS char) -> 1

2008-02-16 Thread Stephan Szabo
On Sat, 16 Feb 2008, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 12 Feb 2008, Tom Lane wrote: > >> Also, section 6.10 defines an explicit cast to > >> a fixed-length string type as truncating or padding to the target > >> l

Re: [GENERAL] SELECT CAST(123 AS char) -> 1

2008-02-14 Thread Stephan Szabo
[Way behind on reading stuff - so I hope this wasn't covered later] On Tue, 12 Feb 2008, Tom Lane wrote: > Ken Johanson <[EMAIL PROTECTED]> writes: > > For sake of interoperability (and using an API that requires String-type > > hashtable keys), I'm trying to find a single CAST (int -> var/char)

Re: [GENERAL] 8.2/8.3 incompatibility

2008-02-07 Thread Stephan Szabo
On Thu, 7 Feb 2008, Harald Fuchs wrote: > This works fine in 8.2.4, but 8.3.0 rejects the ALTER TABLE with the > following (somewhat misleading) error message: > > ERROR: insert or update on table "t2" violates foreign key constraint > "t2_t1id_fk" > DETAIL: Key (t1id)=(t1id1) is not presen

Re: [GENERAL] turning off notices

2008-01-19 Thread Stephan Szabo
On Sat, 19 Jan 2008, Sue Fitt wrote: > Hi All, > > I'm having trouble with turning off notices. Within psql I use \set > VERBOSITY terse, which is fine. However, using psql -c I am having > trouble. It seems I should be able to use psql -qc 'mycommand' but I am > still getting notices output, e.g.

Re: [GENERAL] why it doesn't work? referential integrity

2007-08-11 Thread Stephan Szabo
On Sat, 11 Aug 2007, Pavel Stehule wrote: > Hello > > I found strange postgresql's behave. Can somebody explain it? There's a bug since it should work for any number, but we've likely missed something. I'm not sure why 2 references work, as I'd expect it to stop working after 1 with the likely ca

Re: [GENERAL] why is the LIMIT clause slowing down this SELECT?

2007-08-01 Thread Stephan Szabo
On Wed, 1 Aug 2007, Scott Marlowe wrote: > On 8/1/07, Mason Hale <[EMAIL PROTECTED]> wrote: > > On a 8.1.9 version database that has been recently vacuumed and > > analyzed, I'm seeing some dramatic performance degradation if a limit > > clause is included in the query. This seems counter-intuiti

Re: [GENERAL] query to match '\N'

2007-07-28 Thread Stephan Szabo
On Fri, 27 Jul 2007, pc wrote: > Hi, > > I have a table test with columns col1 col2.col2 contains an entry > '\N' .I want to select all entries which have '\N' in col2.How do i > do that? > > select * from test where col2 like '\N' ; > select * from test where col2 like '\\N' ; select * from

Re: [GENERAL] Delete/update with limit

2007-07-24 Thread Stephan Szabo
On Tue, 24 Jul 2007, Csaba Nagy wrote: > > How about using the following? > > > > delete from > > where ctid in (select ctid from limit ); > > > > I actually checked this out before starting this thread, and the plan > looked like: > > > explain delete from my_table where ctid in (select cti

Re: [GENERAL] Delete/update with limit

2007-07-24 Thread Stephan Szabo
On Tue, 24 Jul 2007, Gregory Stark wrote: > "Csaba Nagy" <[EMAIL PROTECTED]> writes: > > >> Unfortunately the stuff that makes a ctid= nice doesn't seem to be > >> used when you're doing an in. It's possible that a function that does > >> something like > >> for rec in select ctid from my_table

Re: [GENERAL] String trim function - possible bug?

2007-06-06 Thread Stephan Szabo
On Wed, 6 Jun 2007, Woody Woodring wrote: > I am seeing weirdness using the trim function on a string: > > This works as expected: > > SELECT 'dhct:bn', trim(leading 'dhct:' from 'dhct:bn'); > ?column? | ltrim > --+--- > dhct:bn | bn > (1 row) > > However it fails for these cases:

Re: [GENERAL] Lock contention, docs vs. reality

2007-04-22 Thread Stephan Szabo
On Sun, 22 Apr 2007, Roland Turner wrote: > I'm working with 7.4, but the 8.2 docs[1] have the same apparent error: > > ROW SHARE > > Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes. > > The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a > lock of th

Re: [GENERAL] How to disable duplicate columns

2007-04-09 Thread Stephan Szabo
On Sun, 8 Apr 2007, Andrus wrote: > I have tables with large number of columns some of which are duplicate. > I need to use > > SELECT t1.*, t2.* FROM t1 join t2 using (t) > > since I don't know all column names of t1 and t2 tables at design time. > > In this case PostgreSQL returns table with du

Re: RES: [GENERAL] Order by behaviour

2007-03-29 Thread Stephan Szabo
On Thu, 29 Mar 2007, Carlos H. Reimer wrote: > Humm, ok, it is clear now. > > And is there a way to change something in this behaviour, like not ignore > spaces and some type of symbols? Well, right now it's generally determined by your OS's definition of the locale you've chosen. You might be ab

Re: [GENERAL] Order by behaviour

2007-03-28 Thread Stephan Szabo
On Wed, 28 Mar 2007, Carlos H. Reimer wrote: > Hi, > > We have a PostgreSQL 8.0.6 cluster configured with lc_collate=pt_BR.UTF-8 > and when we run the following SELECT: > SELECT substr(nomerazao,1,4), > ascii(substr(nomerazao,1,1)), > ascii(substr(nomerazao,2,1)) > from spunico.unico order by nome

Re: [GENERAL] deleting a foreign key that has no references

2007-03-19 Thread Stephan Szabo
On Mon, 19 Mar 2007, Glen W. Mabey wrote: > Hello, > > I'm using 8.1.8, and I have a situation where a record in one table is > only meaningful when it is referenced via foreign key by one or more > records in any one of several tables. > > So, really what I want is when one of the referring recor

Re: [GENERAL] [Bulk] Re: quoted identifier behaviour

2007-03-15 Thread Stephan Szabo
On Wed, 14 Mar 2007, Randall Smith wrote: > Stephan Szabo wrote: > > On Wed, 14 Mar 2007, Randall Smith wrote: > > > >> Scott Marlowe wrote: > >>> This whole discussion is reminding me of one of my personal mantras, and > >>> that is that relyin

Re: [GENERAL] [Bulk] Re: quoted identifier behaviour

2007-03-14 Thread Stephan Szabo
On Wed, 14 Mar 2007, Randall Smith wrote: > Scott Marlowe wrote: > > This whole discussion is reminding me of one of my personal mantras, and > > that is that relying on "artifacts" of behaviour is generally a bad > > idea. > > > > For instance, many databases accept != for not equal, but the sql

Re: [GENERAL] complex referential integrity constraints

2007-02-23 Thread Stephan Szabo
On Fri, 23 Feb 2007, Joris Dobbelsteen wrote: > >-Original Message- > >From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] > >Sent: vrijdag 23 februari 2007 9:50 > >To: Joris Dobbelsteen > >Cc: pgsql-general@postgresql.org > >Subject: Re: [GENERAL] complex referential integrity constra

Re: [GENERAL] complex referential integrity constraints

2007-02-22 Thread Stephan Szabo
On Thu, 22 Feb 2007, Joris Dobbelsteen wrote: > >-Original Message- > >From: [EMAIL PROTECTED] > >[mailto:[EMAIL PROTECTED] On Behalf Of > >Martijn van Oosterhout > >Sent: donderdag 22 februari 2007 18:17 > >To: Joris Dobbelsteen > >Cc: Robert Haas; pgsql-general@postgresql.org > >Subject:

Re: [GENERAL] invalid input syntax for integer: "NULL"

2007-02-20 Thread Stephan Szabo
On Tue, 20 Feb 2007, Yonatan Ben-Nes wrote: > Hi everyone, > > I'm trying to write a PL/pgSQL function which execute an insert, I encounter > a problem when I try to insert NULL value into an integer field. > The following code is for reproducing: > > CREATE TABLE test( > bh INT8 > ); > > CREATE O

Re: [GENERAL] invalid regular expression: invalid backreference number

2007-02-18 Thread Stephan Szabo
On Sun, 18 Feb 2007, Jeff Ross wrote: > Tom Lane wrote: > > > > Since ceil() produces float8 which does not implicitly cast to int, > > this call has probably never done what you thought --- AFAICS it will > > cast all the arguments to text and invoke substring(text,text,text) > > which treats its

Re: [GENERAL] can't CREATE TRIGGER

2007-01-21 Thread Stephan Szabo
On Mon, 22 Jan 2007, gustavo halperin wrote: > I can't create triggers, when I call for example: > ficha=> CREATE TRIGGER TRG_persons_id AFTER INSERT ON ficha_ofperson > ficha-> EXECUTE PROCEDURE add_person_id ( 'family_id', 'person_id' ); > > , the creation never finish and finally, after many m

Re: [GENERAL] Multi-column constraint behaviour

2007-01-16 Thread Stephan Szabo
On Tue, 16 Jan 2007, Bertram Scharpf wrote: > Hi, > > > please have a look at these introducing statements: > > sandbox=# create table q(i integer, t text, primary key (i,t)); > sandbox=# create table f(i integer, t text, foreign key (i,t) references q); > > Now, this is surprising me: > > s

Re: [GENERAL] Functional Index

2006-11-22 Thread Stephan Szabo
On Wed, 22 Nov 2006, Alexander Presber wrote: > Hello everybody, > > I am trying to speed up a query on an integer column by defining an > index as follows > > > CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text) > using varchar_ops); > > on column "main_subject". > > I had hope

Re: [GENERAL] I'm lost :-( with FOR...IN

2006-11-07 Thread Stephan Szabo
On Tue, 7 Nov 2006, Alain Roger wrote: > Hi, > > I' still with my stored procedure : > > -- Function: SP_U_001(typeofarticle varchar) > > -- DROP FUNCTION SP_U_001(typeofarticle varchar); > > CREATE OR REPLACE FUNCTION SP_U_001(IN typeofarticles VARCHAR) > RETURNS SETOF active_articles AS > $BO

Re: [GENERAL] Simple stored procedure examples?

2006-11-04 Thread Stephan Szabo
On Sat, 4 Nov 2006, novnov wrote: > > Thanks to both of you for responding. I should have included the code for my > own attempt, at #1 which is just as you suggest: > > update item set itemname = 'fox'; > > I've tried single, and double quoting the table and field names; call caps > to the UPDATE

Re: [GENERAL] Simple stored procedure examples?

2006-11-04 Thread Stephan Szabo
On Fri, 3 Nov 2006, novnov wrote: > I'm completely new to pgsql, using 8.1 and pgAdmin III. I'm not finding a lot > of bare bones simple example stored procs that I can learn from. It would be > very helpful if someone could show me some simple code. > > In the pgAdmin interface I've been picking

Re: [GENERAL] Deleting Problem

2006-10-30 Thread Stephan Szabo
On Tue, 31 Oct 2006, Jamie Deppeler wrote: > Here is my problem > > I have a level structure which is 5 levels deep with 6 tables, for this > example i will call it table1,table2,table3,table4,table5,table6 > > (1)table1 > (2)table2 > (3)table3 > (4)table4 > (5)table5,table6 > (6)table7,table8 >

Re: [GENERAL] Wrong record type - caused by SELECT order ???

2006-10-27 Thread Stephan Szabo
On Fri, 27 Oct 2006, John Cobo wrote: > I am trying to create some functions which return many rows using > plpgsql. This example could be done with SQL, but later I will need > plpglsql. I was constantly getting the wrong record type error with a > couple different functions. Finally I found t

Re: [GENERAL] c (lowercase) privilege

2006-10-20 Thread Stephan Szabo
On Fri, 20 Oct 2006, Javier Carlos wrote: >Does anybody know what's the meaning of the c (lowercase) privilege in > PostgreSQL 8.2 Beta? That should be for CONNECT privilege. ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Normal vs Surrogate Primary Keys...

2006-10-01 Thread Stephan Szabo
On Sun, 1 Oct 2006, rlee0001 wrote: > I know, for example, that by default PostgreSQL assigns every record a > small unique identifier called an OID. It seems reasonable then, that > when the DBA creates a cascading foreign key to a record, that the DBMS > could, instead of storing the record's en

Re: [GENERAL] text to point conversion not working. ( cannot cast

2006-09-28 Thread Stephan Szabo
On Wed, 27 Sep 2006, Dan Libby wrote: > Background: > > I have a hierarchical table where I have coordinate data for only the leaf > nodes. I therefore want to find the center of all the leaf nodes under a > given parent node, and set the parent node coordinate to that center point. > > I can cal

Re: [GENERAL] Is this logical?

2006-09-09 Thread Stephan Szabo
On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote: > But "not null" is in contradiction with "default null" so the create > statement should not proceed successfuly IMHO. The fact that the default value isn't going to pass a constraint means that it's invalid to not provide a value or use default, y

Re: [GENERAL] Is this logical?

2006-09-09 Thread Stephan Szabo
On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote: > Shouldn't this create statement trigger an error? > > create table bar (col1 int not null default null); > > Shouldn't I be forbidden to insert null values into a non null column? I think it should forbid it when the default actually comes into p

Re: [GENERAL] select * from users where user_id NOT in (select

2006-08-17 Thread Stephan Szabo
On Thu, 17 Aug 2006, Alexander Farber wrote: > I have this strange problem that the following statement works: NULLs are not your friends. :( > phpbb=> select user_id, username from phpbb_users > phpbb-> where user_id in (select ban_userid from phpbb_banlist); > user_id | username > -

Re: [GENERAL] Weird join result

2006-08-16 Thread Stephan Szabo
On Wed, 16 Aug 2006, Peter Nixonn wrote: > I am getting a result for an JOIN that I think is wrong. Maybe its my > understanding that is wrong here however, so please be gentle :-) > > The "phones" table contains a list of phone numbers and an associated > customer ID. The radacct table contains a

Re: [GENERAL] REFERENCE problem with parent_table

2006-08-15 Thread Stephan Szabo
On Tue, 15 Aug 2006, gustavo halperin wrote: > Hello > > I need many tables of type "id" and "name", see below: > / CREATE TABLE id_names ( > idsmallintCONSTRAINT the_id PRIMARY KEY NOT NULL, > nametextCONSTRAINT the_name UNIQUE > ) WIT

Re: [GENERAL] CREATE DATABASE

2006-08-03 Thread Stephan Szabo
On Fri, 4 Aug 2006, Nikolay Samokhvalov wrote: > On 8/4/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: > [...] > > BTW, difference vanishes due to expression power of SQL - > > it supports session comands in the same context as DDL commands and > > data manipulation stmts (SQL:200n, 4.33.2.5

Re: [GENERAL] CASE statement and SETOF values

2006-07-25 Thread Stephan Szabo
On Tue, 25 Jul 2006, Christian Schoenebeck wrote: > Am Dienstag, 25. Juli 2006 00:01 schrieb Stephan Szabo: > > The above basically looks like: > > CASE WHEN THEN ELSE > > END. > > > > In SQL92 at least, the form of which looks like (SELECT > > ...) is

Re: [GENERAL] CASE statement and SETOF values

2006-07-24 Thread Stephan Szabo
On Mon, 24 Jul 2006, Christian Schoenebeck wrote: > Consider the following server side function: > > CREATE FUNCTION my_function(int4) RETURNS SETOF int8 AS > $BODY$ > SELECT > CASE WHEN (some_condition) > THEN ( > SELECT ... -- arbitrary select (returning row(s) of int8 values) >

Re: [GENERAL] SQL Standards Compliance With Case

2006-07-12 Thread Stephan Szabo
On Wed, 12 Jul 2006, Rich Shepard wrote: >I'm trying to assist the XRMS developers port their application to > postgres (8.1.x on), and it's almost there. One (perhaps the only) stumbling > block is case for table and column (relation and attribute) names. > Apparently MySQL allows for mixed c

Re: [GENERAL] Bug? Changing where distinct occurs produces error?

2006-07-07 Thread Stephan Szabo
On Fri, 7 Jul 2006, Michael Loftis wrote: > OK I'm either insane or found a bug in 8.1.3 > > If you execute say: > > SELECT DISTINCT(ua.user_id),pa.poll_id FROM user_answers ua, poll_answers > pa WHERE pa.poll_answer_id = ua.poll_answer_id AND ua.active='Y'; > > Everything is fine, however if you

Re: [GENERAL] ERROR: more than one row returned by a subquery used

2006-05-05 Thread Stephan Szabo
On Wed, 3 May 2006, Arjan Vroege wrote: > Hello, > > I have the following Query with Subqueries. > This query gives the error : ERROR: more than one row returned by a subquery > used as an expression. Is there a solution to solve this problem: Scalar subqueries (like the ones in your select list)

Re: [GENERAL] Adding ON UPDATE CASCADE to an existing foreign key

2006-05-04 Thread Stephan Szabo
On Thu, 4 May 2006, Rich Doughty wrote: > I have a foreign key constraint that I'd like to alter. I'd rather not > drop and re-create it due to the size of the table involved. All I need > to do is add an ON UPDATE CASCADE. > > Is it ok to set confupdtype to 'c' in pg_constraint (and will this be

Re: [GENERAL] A few questions about ltree

2006-04-21 Thread Stephan Szabo
On Fri, 21 Apr 2006, Alban Hertroys wrote: > Stephan Szabo wrote: > >>SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES > >>ltree_test(path)); > >>NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > >>"ltree_test_pkey&q

Re: [GENERAL] A few questions about ltree

2006-04-21 Thread Stephan Szabo
On Fri, 21 Apr 2006, Alban Hertroys wrote: > Teodor Sigaev wrote: > >> Maybe something along the lines of the following is possible?: > > > > Exact, it's for what ltree was developed. > > Cool, looks like it is what I need then. > > > contrib_regression=# select 'a.b.c' <@ 'a.b'::ltree; > > ?colu

Re: [GENERAL] How to replace rows in table so that foreign key rows

2006-04-21 Thread Stephan Szabo
On Fri, 21 Apr 2006, Andrus wrote: > > ... and there doesn't appear (for > > non-match partial constraints) seem to be a special case for the > > referenced row coming back into existance as far as I can tell either. > > > Or, if you're willing to patch, I think a first order approximation of > >

Re: [GENERAL] How to replace rows in table so that foreign key rows

2006-04-21 Thread Stephan Szabo
On Thu, 20 Apr 2006, Stephan Szabo wrote: > On Thu, 20 Apr 2006, Andrus wrote: > > > I want to replace ( delete and insert) records in master table . > > I delete and insert record with same primary key. > > I want that foreign key records are not deleted. >

Re: [GENERAL] How to replace rows in table so that foreign key rows

2006-04-20 Thread Stephan Szabo
On Thu, 20 Apr 2006, Andrus wrote: > I want to replace ( delete and insert) records in master table . > I delete and insert record with same primary key. > I want that foreign key records are not deleted. > > I tried > > begin; > create temp table t1 ( pk integer primary key ); > insert into t1 va

Re: [GENERAL] Self-referencing and inherited table

2006-04-04 Thread Stephan Szabo
On Tue, 4 Apr 2006, Anastasios Hatzis wrote: > Hello! > > I want to realize some kind of parent-child relation with-in a table, > but have problems with foreign key / references. Probably this issue > occurs because I use inheritance (as implied by some pages, I found). Probably. If the matching

Re: [GENERAL] How to use views&rules to dynamically choose which

2006-03-31 Thread Stephan Szabo
On Sat, 1 Apr 2006, Ashley Moran wrote: > I'm still relatively new to Postgres (at least when it comes to > clever stuff - especially rules) so I hope I've missed something here. > > Basically I'm still trying to combine multiple databases with > identical schemas into one schema, adding a column

Re: [GENERAL] How to use result column names in having cause

2006-03-31 Thread Stephan Szabo
On Fri, 31 Mar 2006, Andrus wrote: > >> In real application I have long expression instead of 123 and do'nt want > >> repeat this expression in HAVING clause. > > > > You have to repeat the expression. "AS" changes the output name, it > > can't be used either in the where clause or any other limi

Re: [GENERAL] Foreign key / performance question

2006-03-29 Thread Stephan Szabo
On Wed, 29 Mar 2006, Nico Callewaert wrote: > Is it wise to define foreign keys for referential entegrity ? > Example : I have a customer table with 40 fields. Out of that 40 > fields, 10 fields contain information linked to other tables. So, is > defining foreign keys for these 10 fiel

Re: [GENERAL] Order of Update - Second Try

2006-03-21 Thread Stephan Szabo
> Ok, find attached a script called test.sql that will create three tables > called parent, child, and totals. It will create a simple AFTER UPDATE > trigger on child and a BEFORE trigger on parent simply to show that the > values of batch and chkno are set to NULL right in the beginning. Just loa

Re: [GENERAL] passing parameters to a trigger function

2006-03-21 Thread Stephan Szabo
On Tue, 21 Mar 2006, Larry White wrote: > I can't figure out how to pass parameters to a trigger function. > > I checked the documentation and saw that trigger functions don't take > params in the usual fashion, > but couldn't find an example of a pl-sql trigger function that used > the original r

Re: [GENERAL] can't create user collumn

2006-03-17 Thread Stephan Szabo
On Fri, 17 Mar 2006, loki wrote: > Hi, > i'm just starting with postgres DB, but this looks very strange to me: > > If i try to create table with collumn user, it fails with error: > create exec error:ERROR: syntax error at or near "user" at character 368 USER is a reserved word in SQL and as s

Re: [GENERAL] select where in and order

2006-03-09 Thread Stephan Szabo
On Thu, 9 Mar 2006, Tony Smith wrote: > I have two tables action and group: > > action > > id, > name > > group: > > action_id > rank > > I what to select from action table by order by the > rank in the group table. > > If I use > > select * from a

Re: [GENERAL] Wish: remove ancient constructs from Postgres

2006-02-27 Thread Stephan Szabo
On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote: >it's completely different thing. look at the spec and you'll >understand the difference. in two words, with 'DISTINCT ON' we lose >some values (from some columns), when UNION not (it just removes >duplicates, comparing _entire_ rows). No it's not,

Re: [GENERAL] Wish: remove ancient constructs from Postgres

2006-02-27 Thread Stephan Szabo
On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote: > On 2/27/06, Bruno Wolff III <[EMAIL PROTECTED]> wrote: > The alternatives to distinct on are painful. They are generally both harder > to read and run slower. > >'DISTINCT ON' is evil constuction, because (w/o any 'ORDER BY') it >produses unpredi

Re: [GENERAL] Same data, different results in Postgres vs. FrontBase

2006-02-19 Thread Stephan Szabo
On Sat, 18 Feb 2006, Brendan Duddridge wrote: > Hi, > > I have a query that returns 569 rows in FrontBase, but only 30 rows > in Postgres. The data is the same as I just finished copying my > entire database over from FrontBase to Postgres. > > I've reduced my problem to the following statement an

Re: [GENERAL] Domains

2006-02-18 Thread Stephan Szabo
On Sat, 18 Feb 2006, Peter wrote: > Hello, > > I am migrating to postgresql from another database. I want to take > advantage of using domains. Let's suppose I create domain > 'email'(varchar 128). Then I change my mind and want to increase all > columnst that have type 'emaill' to varchar(255). H

Re: [GENERAL] Why does an ON SELECT rule have to be named "_RETURN"?

2006-02-13 Thread Stephan Szabo
On Mon, 13 Feb 2006, Ken Winter wrote: > You're right: This thing I call a "view-table" would behave *exactly* like a > view that has insert, update, and delete rules. > > The *only* difference I'm trying to achieve is to get it stored in > pg_catalog.pg_class with relkind = 'r' ("ordinary table"

Re: [GENERAL] Why does an ON SELECT rule have to be named "_RETURN"?

2006-02-12 Thread Stephan Szabo
On Sun, 12 Feb 2006, Ken Winter wrote: > > -Original Message- > > From: Stephan Szabo [mailto:[EMAIL PROTECTED] > > Sent: Sunday, February 12, 2006 8:47 PM > > To: Ken Winter > > Cc: 'Tom Lane'; 'PostgreSQL pg-general List' > > Subj

Re: [GENERAL] Why does an ON SELECT rule have to be named "_RETURN"?

2006-02-12 Thread Stephan Szabo
On Sun, 12 Feb 2006, Ken Winter wrote: > Hi Tom ~ > > You're right: I appealed to the PostgreSQL folks rather than the client > tool builders. I did so because my guess is that the latter have a harder > row to hoe: They have to figure out whether a view really IS updatable - > most presumably

Re: [GENERAL] referential integrity without trigger

2006-02-09 Thread Stephan Szabo
On Thu, 9 Feb 2006, Alexander Presber wrote: > Hello everybody, > > Assuming I want to empty and refill table A (with roughly the same > content, preferrably in one transaction) and don't want to completely > empty a dependent table B but still keep referential integrity after > the commit. > > W

Re: [GENERAL] Can't get the field = ANY(array) clause to work...

2006-02-02 Thread Stephan Szabo
On Thu, 2 Feb 2006 [EMAIL PROTECTED] wrote: > The problem was fixed by initializing the array before giving it a > value. Not surprising Postges isnt as popular as it should be. I was by > luck that I found this out - the manual says nothing about init arrays. Well, I think that's in part becaus

  1   2   3   4   5   6   >