Re: [SQL] Existential quantifier
On Sat, 10 Oct 2009, [utf-8] Dag-Erling Sm??rgrav wrote: Consider the attached schema (filmstars.sql), which is a poorly designed database of films and actors. The following query gives me a list of films in which either Charlie or Martin Sheen starred: select fs.film.title, fs.film.year from fs.film left join fs.star on fs.film.id = fs.star.film where fs.star.last = 'Sheen' group by fs.film.title, fs.film.year; Is there a way to do this without the group by clause? Not at all tested as I don't have access to my db right now, but I think something like one of these would work: select fs.film.title, fs.film.year from fs.film where exists(select 1 from fs.star where fs.film.id = fs.star.film and fs.star.last = 'Sheen'); select fs.film.title, fs.film.year from fs.film where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen'); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Existential quantifier
On Sat, 10 Oct 2009, [utf-8] Dag-Erling Sm??rgrav wrote: Stephan Szabo ssz...@megazone.bigpanda.com writes: Not at all tested as I don't have access to my db right now, but I think something like one of these would work: select fs.film.title, fs.film.year from fs.film where exists(select 1 from fs.star where fs.film.id = fs.star.film and fs.star.last = 'Sheen'); Ah, that was exactly what I was looking for. select fs.film.title, fs.film.year from fs.film where fs.film.id in (select fs.star.film where fs.star.last = 'Sheen'); ITYM select fs.film.title, fs.film.year from fs.film where fs.film.id in ( select fs.star.film from fs.star where fs.star.last = 'Sheen' ); (missing FROM) Yeah, that'd be necessary. You might want to try them on a realistic data set to see how the various options are planned. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] JOIN and aggregate problem
On Fri, 20 Feb 2009, Tarlika Elisabeth Schmitz wrote: I have 2 tables T1 and T2 T1 has the columns: D, S, C. The combination of D,S,C is unique. T2 has the columns: D, S, C, and boolean X. The combination of D,S,C is not unique. I need to produce the following result for every occurrence of T1: D,S,C, COUNT COUNT is the number of matching D,S,C combinations in T2 where X = true. There might be no matching pair in T2 or there might be match but X is false. How can I express this? Maybe something like one of these barely tested queries? select d, s, c, sum(case when t2.x then 1 else 0 end) from t1 left outer join t2 using(d,s,c) group by d, s, c; or select d,s,c, (select count(*) from t2 where t2.d=t1.d and t2.s=t1.s and t2.c=t1.c and t2.x) from t1; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Must I use DISTINCT?
On Thu, 5 Feb 2009, Michael B Allen wrote: Please consider the following SQL SELECT e.eid, e.name FROM entry e, access a WHERE e.eid = 120 AND (e.ownid = 66 OR e.aid = a.aid) The intent is to match one entry with the eid of 120. However I would like to impose an additional constraint that either e.ownid must be 66 or e.aid must match the aid of an entry in the access table (there's actually a lot more to the query but I think this should be sufficient to illustrate my problem). The problem is that the e.ownid is 66 and therefore the same entry is returned for each access entry. Of course I can simply SELECT DISTINCT but that seems like an improper usage of DISTINCT here. Is there an alternative way to write this query? I only want to select from the access table for the purpose of constraining by aid. Would something like: SELECT e.eid, e.name FROM entry e WHERE e.eid = 120 AND (e.ownid = 66 OR e.aid in (select a.aid from access a)) do what you wnat? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] prepared query plan did not update
On Wed, 17 Sep 2008, Emi Lu wrote: Good morning, I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SETcol1 = false WHERE col1 AND col2 = '$1' ; I don't think you want those quotes in the second part of the where clause. I'm pretty sure that means you're comparing against the literal string with a dollar sign and one rather than the value given at execute time for $1. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] prepared query plan did not update
On Wed, 17 Sep 2008, Emi Lu wrote: Stephan Szabo wrote: On Wed, 17 Sep 2008, Emi Lu wrote: Good morning, I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SETcol1 = false WHERE col1 AND col2 = '$1' ; I don't think you want those quotes in the second part of the where clause. I'm pretty sure that means you're comparing against the literal string with a dollar sign and one rather than the value given at execute time for $1. Do you mean: PREPARE pname(varchar) AS UPDATE t1 SET col1 = false WHERE col1 AND col2 = $1 ; But still does not work? Strange, right? I think we'll need to see a complete example with table definitions and sample data because it does appear to work for me in simple tests. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Aggregates in WHERE clause?
On Wed, 10 Sep 2008, Ruben Gouveia wrote: I tried to do the following and got the following error message: select employee,count(distinct tasks) from job where greatest(max(last_job_date),max(last_position_date)) 2008-08-28 + integer '1' group by employee; ERROR: aggregates not allowed in WHERE clause You probably want to look at some variant with HAVING, assuming you intend those max()s to be based on the employee groups. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] What is wrong with this PostgreSQL UPDATE statement??
On Fri, 22 Aug 2008, Steve Johnson wrote: update certgroups set termgroupname = tg.termgroupname from certgroups c, termgroup tg where (c.days = tg.mindays) and (c.days = tg.maxdays); In recent PostgreSQL versions I believe this is properly written: update certgroups c set termgroupname = tg.termgroupname from termgroup tg where (c.days = tg.mindays) and (c.days = tg.maxdays); At least as of SQL2003, I think both of the above use extensions, so there's no guarantee to the behavior on different systems and to do it with a standard query, you'd need to use a subselect, something like: update certgroups c set termgroupname = (select termgroupname from termgroup tg where (c.days = tg.mindays) and (c.days =tg.maxdays)); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Strange query duration
On Tue, 22 Jul 2008, Fernando Hevia wrote: I just enabled log duration in a 8.3.1 database and got puzzling information. I have a daemon shell-script run every 10 seconds the following: psql -c select f_tasador(); The 'f_tasador' procedure is quite fast. As per log output I can see the procedure completes its execution within one second. Nevertheless in the LOG duration entry it shows a statement duration of over 36 secs. I think you're misleading the log. It's showing a number of milliseconds (ms) not seconds. 2008-07-22 15:52:37 ART|postgres| LOG: statement: select f_tasador(); 2008-07-22 15:52:37 ART|postgres| LOG: duration: 38.154 ms 2008-07-22 15:52:47 ART|postgres| LOG: statement: select f_tasador(); 2008-07-22 15:52:47 ART|postgres| LOG: duration: 36.781 ms -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query question
On Thu, 22 May 2008, Medi Montaseri wrote: Hi, I can use some help with the following query please. Given a couple of tables I want to do a JOIN like operation. Except that one of the columns might be null. create table T1 ( id serial, name varchar(20) ); create table T2 ( id serial, name varchar(20) ); create table T1_T2 ( id serial, t1_id integer not null , t2_id integer ); Now I'd like to show a list of records from T1_T2 but reference T1 and T2 for the names instead of IDs. But T1_T2.t2_id might be null select T1_T2.id, T1.name , T2.name from T1, T2, T1_T2 where T1_T2.t1_id = T1.id and T1_T2.t2_id = T2.id What would you want it to do if T1_T2.t2_id has a value that isn't in T2? And should it do it for both T2 and T1? If using a NULL name is okay for both, you can look at outer joins, something like: select T1_T2.id, T1.name, T2.name from T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) left outer join T2 on (T1_T2.t2_id = T2.id) T1_T2 left outer join T1 on (T1_T2.t1_id = T1.id) will for example give you a row even if there's not a row in T1 with T1.id being the same as T1_T2.t1_id. In that case, you'll get the fields from T1_T2 and NULLs for the fields from T1. The same between that table and T2 occurs with the second outer join. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Joining with result of a plpgsql function
On Wed, 7 May 2008, Matthew T. O'Connor wrote: I have a pl/pgsql function, defined as: CREATE FUNCTION tms.get_tms_summary(id integer) RETURNS tms.tms_summary get_tms_summary returns a composite type, tms_summary, which is comprised of several numerics. What I would like to do is something like: select f.id, f.name, tms.get_tms_summary(f.id) from foo f; However this returns only three columns, the third of which is the entire complex data type in one column. I can do: select * from tms.get_tms_summary(99); But I would really like to be able to combine it with other data and get a result set that looked like: f.id, f.name, tms_summary.col1, tms_summary.col2 ... Well I think select f.id, f.name, (tms.get_tms_summary(f.id)).* from foo f; would expand it out into separate columns, but I think that might also call it multiple times. You might have better luck combining that with a subquery like select id, name, (summary).col1, (summary).col2, ... from (select id, name, tms.get_tms_summary(f.id) as summary from foo) f; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trim(both) problem?
On Fri, 25 Apr 2008, Emi Lu wrote: Hi, Isn't this a bug about trim both. select trim(both 'BR/' from 'BR/ROI Engineering Inc.'); btrim - OI Engineering Inc. (1 row) R is missing? How? Trim doesn't do what you think it does. The 'BR/' in the above is not a string to remove it is a list of characters to remove. Thus, the R is removed as it matches a character given. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] apparent RI bug
On Wed, 2 Apr 2008, chester c young wrote: it appears I have a broken RI in my db. call_individual.clh_id references call_household.clh_id \d call_individual ... Foreign-key constraints: call_individual_clh_id_fkey FOREIGN KEY (clh_id) REFERENCES call_household(clh_id) ON DELETE CASCADE however: development=# select clh_id from call_individual cli where not exists( select 1 from call_household clh where clh.clh_id=cli.clh_id ); clh_id 14691 should not matter, but call_individual has a pre-delete trigger that simply raises an exception to prevent deletions: raise exception 'calls may not be deleted'; Yeah, that looks pretty broken. Can you reproduce this from a clean start repeatedly or is this a one off? Do you ever turn off triggers, perhaps by modifying the pg_class row's reltriggers (I'd guess the answer is no, but it'd be good to make sure)? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] apparent RI bug
On Thu, 3 Apr 2008, chester c young wrote: Stephan Szabo [EMAIL PROTECTED] wrote: On Wed, 2 Apr 2008, chester c young wrote: it appears I have a broken RI in my db. Yeah, that looks pretty broken. Can you reproduce this from a clean start repeatedly or is this a one off? Do you ever turn off triggers, perhaps by modifying the pg_class row's reltriggers (I'd guess the answer is no, but it'd be good to make sure)? only one error. unable to duplicate so far. this is a development db - triggers are frequently dropped and created, but I don't think ever concurrently with db activity. Is it possible you ever had a before delete trigger that just did a return NULL rather than raising an exception? IIRC, explicitly telling the system to ignore the delete will work on the referential actions. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] apparent RI bug
On Thu, 3 Apr 2008, chester c young wrote: --- Stephan Szabo [EMAIL PROTECTED] wrote: Is it possible you ever had a before delete trigger that just did a return NULL rather than raising an exception? IIRC, explicitly telling the system to ignore the delete will work on the referential actions. yes, it is possible, for example, a function without a body or without a return old. are you saying this would override the RI constraint? If it returned something that would have prevented the delete without an error, yes. if so, is this by design? It's basically an ongoing question (without concensus AFAIK) about whether a rule or trigger should be allowed to stop the referential action and what should happen if it does. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] difference between EXCEPT and NOT IN?
On Tue, 1 Apr 2008, Raphael Bauduin wrote: The 2 following statements don't give the same result. I expected the second ti give the exact same result as the first one. If any entree_id can be NULL they aren't defined to give the same result. EXCEPT is defined in terms of duplicates based on distinctness, and for example (1 is distinct from 1) is false, (1 is distinct from NULL) is true and (NULL is distinct from NULL) if false. NOT IN is defined in terms of equality, and for example, (1=1) is true, (1=NULL) is unknown and (NULL=NULL) is unknown. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trap for any exception
On Thu, 15 Nov 2007, Sabin Coanda wrote: I read 37.7.5. Trapping Errors, but the syntax requires to write explicitly the exception condition, and not a generic one. Is it possible to build a generic trap or do you know a workaround for that ? Sorry, I found the OTHERS condition that trap any error. But the question still remains to find how could I interpret it and found the error code and message ? I think you'll want to look at SQLSTATE and SQLERRM inside the exception handler. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trap for any exception
On Thu, 15 Nov 2007, Sabin Coanda wrote: Hi there, I'd like to build a PL/pgSQL function which is able to generic trap any error, and interpret it. I read 37.7.5. Trapping Errors, but the syntax requires to write explicitly the exception condition, and not a generic one. Is it possible to build a generic trap or do you know a workaround for that ? Won't OTHERS catch most of what you want? From the 8.2 docs: The special condition name OTHERS matches every error type except QUERY_CANCELED. (It is possible, but often unwise, to trap QUERY_CANCELED by name.) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Why does the sequence skip a number with generate_series?
On Tue, 2 Oct 2007, Jeff Frost wrote: I expected these numbers to be in sync, but was suprised to see that the sequence skips a values after every generate series. CREATE TABLE jefftest ( id serial, num int ); INSERT INTO jefftest (num) values (generate_series(1,10)); INSERT INTO jefftest (num) values (generate_series(11,20)); INSERT INTO jefftest (num) values (generate_series(21,30)); It seems to do what you'd expect if you do INSERT INTO jefftest(num) select a from generate_series(1,10) as foo(a); INSERT INTO jefftest(num) select a from generate_series(11,20) as foo(a); INSERT INTO jefftest(num) select a from generate_series(21,30) as foo(a); I tried a function that raises a notice and called it as select f1(1), generate_series(1,10); and got 11 notices so it looks like there's some kind of phantom involved. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Alternative to INTERSECT
On Tue, 31 Jul 2007, Andreas Joseph Krogh wrote:
Hi all. I have the following schema:
CREATE TABLE test (
id integer NOT NULL,
field character varying NOT NULL,
value character varying NOT NULL
);
ALTER TABLE ONLY test
ADD CONSTRAINT test_id_key UNIQUE (id, field, value);
CREATE INDEX test_like_idx ON test USING btree (id, field, value
varchar_pattern_ops);
Using INTERSECT I want to retrieve the rows matching (pseudo-code) firstname
LIKE ('andrea%' OR 'jose%') AND lastname LIKE 'kro%'
on= SELECT t.id from test t WHERE t.field = 'firstname' AND t.value
LIKE 'andrea%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'firstname' AND t.value
LIKE 'jose%'
INTERSECT SELECT t.id FROM test t WHERE t.field = 'lastname' AND t.value
LIKE 'kro%';
Do you want something with only a firstname of jose or a firstname of jose
and something other than andrea (and no others) to match or not? I'd read
the pseudo-code to say yes, but AFAICT the query says no.
In general, some form of self-join would probably work, but the details
depend on exactly what should be returned.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] How to cast, if type has spaces in the name
On Thu, 26 Jul 2007, Bryce Nesbitt wrote: How do I specify a cast, if the type name has spaces? foo::integer is easy, but foo::'timestamp without time zone' is more murky. foo::timestamp without time zone should work (no quotes). Another alternative if you don't like the way that looks is to use the SQL cast syntax, CAST(foo AS timestamp without time zone). ---(end of broadcast)--- TIP 1: 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] hi
On Tue, 24 Apr 2007, Penchalaiah P. wrote: Hi I have the data like this in temp table SQL Select sno, value from temp; SNO Value 1 650.00 2 850.00 3 640.00 3 985.00 5 987.00 9 9864.00 7 875.00 Tables are not ordered. You'll need something like an ordering column that represents the ordering and is unique. Then you can probably do something like (untested): select sno, value, (select sum(value) as sum from temp t where t.ordering = temp.ordering) from temp order by ordering; or select t1.sno, t1.value, sum(t2.value) from temp as t1, temp as t2 where t1.ordering = t2.ordering group by t1.ordering, t1.sno, t1.value order by t1.ordering; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Using Temporary Tables in postgres functions
On Thu, 25 Jan 2007, Mario Splivalo wrote: When I try to use TEMPORARY TABLE within postgres functions (using 'sql' as a function language), I can't because postgres can't find that temporary table. Consider this example: CREATE FUNCTION func1() RETURNS SETOF v_messages_full AS $BODY$ CREATE TEMPORARY TABLE tmpTbl AS SELECT message_id FROM cached_messages WHERE billing_status = 2; UPDATE cached_messages SET billing_status = 1 WHERE message_id IN (SELECT message_id FROM tmpTbl); SELECT * FROM v_messages_full WHERE message_id IN (SELECT message_id FROM tmpTbl); $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER; It seems like the sql function checker is unhappy with the above. Does it actually work if you turn off the check_function_bodies configuration variable, create the function and then call it? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help ... Unexpected results when using limit/offset with
On Thu, 18 Jan 2007, Barbara Cosentino wrote: Then I perform the following selects SELECT host_id, host_datum_type_id, host_datum_source_id, data FROM nc_host_datum INNER JOIN nc_host USING (host_id) WHERE audit_id=2041 ORDER BY host_id LIMIT 49 OFFSET 1372; And SELECT host_id, host_datum_type_id, host_datum_source_id, data FROM nc_host_datum INNER JOIN nc_host USING (host_id) WHERE audit_id=2041 ORDER BY host_id LIMIT 49 OFFSET 1421; A portion of the output follows. host_id | host_datum_type_id | host_datum_source_id | data -++--+-- : : 963710 | 58 | 17| harrish 963711 | 27 |3 | 1 963711 | 28 |3 | 1 (49 rows) host_id | host_datum_type_id | host_datum_source_id | data -++--+-- 963711 | 28 |3 | 1 963711 | 58 | 17 | lmitchel 963711 | 39 |3 | us.aegon.com : : (49 rows) Notice that host_id = 963711 and host_datum_type_id = 28 is repeated twice. Since the offset is not overlapping, how can this happen? I'd suggest adding host_datum_type_id to the order by so that you have a guarantee of the order that the rows for a given host_id will come, otherwise I don't think you can assume anything within one host_id which means you could get the same row at different effective offsets in different runs of the base query (especially if you hit a point where the plan changes). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem inserting composite type values
On Fri, 1 Dec 2006, Chris Dunworth wrote:
Hi all --
(huge apologies if this is a duplicate post -- I sent from an
unsubscribed email account before...)
I have a problem trying to INSERT INTO a table by selecting from a
function that returns a composite type. (I'm running version 8.1.4, FYI)
Basically, I have two tables. I want to retrieve rows from one table and
store them into the other. The schema of the two tables is not the same,
so I use a conversion function (written in plperl) that takes a row from
the start table and returns a row from the end table. However, I can't
get the insert working.
Here's a simplified example of my real system (must have plperl
installed to try it):
---
-- Read rows from here...
CREATE TABLE startTable ( intVal integer, textVal text );
-- ...and store as rows in here
CREATE TABLE endTable ( intVal integer, newVal1 integer, newVal2 integer);
-- Some test data for the startTable
INSERT INTO startTable VALUES ( 1, '10:11');
INSERT INTO startTable VALUES ( 2, '20:25');
INSERT INTO startTable VALUES ( 3, '30:38');
-- Note: Takes composite type as argument, and returns composite type.
-- This just converts a row of startTable into a row of endTable, splitting
-- the colon-delimited integers from textVal into separate integers.
CREATE OR REPLACE FUNCTION convertStartToEnd(startTable) RETURNS
endTable AS $$
my ($startTable) = @_;
my @newVals = split(/:/, $startTable-{textval});
my $result = { intval=$startTable-{intval},
newval1=@newVals[0], newval2=@newVals[1] };
return $result;
$$ LANGUAGE plperl;
---
Now, if I run the following SELECT, I get the results below it:
SELECT convertStartToEnd(st.*) FROM startTable st;
convertstarttoend
---
(1,10,11)
(2,20,25)
(3,30,38)
(3 rows)
This seems OK. But when I try to INSERT the results of this select into
the endTable, I get this error:
INSERT INTO endTable SELECT convertStartToEnd(st.*) FROM startTable st;
I think you'd need something like
INSERT INTO endTable SELECT (convertStartToEnd(st.*)).* FROM startTable
st;
to make it break up the type into its components.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Subselects in CHECK clause ...
On Wed, 29 Nov 2006, James Robinson wrote: I see that subselects are not directly supported in check clauses, but one can work around that by writing a stored function which returns boolean and performs the subselect. Are there any known gotchas with doing this? To completely get the constraint, you have to also apply constraints on the tables referenced in the function that prevent modifications on those tables from causing the constraint to be violated. For example, if you were to do an exists test on another table for a row that matches up with this row in some fashion (for a specialized referential integrity constraint) modifications on that other table could also cause the constraint to be violated, but that isn't caught by the CHECK function(...) case and you'll probably need triggers or other constraints on that table. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SQL command join question
On Wed, 29 Nov 2006, Ehab Galal wrote: I have three tables t1(a, b, c, d), t2(a, b, c, k), and t3(c, e). I need to outer join them as shown below, but only have all tuples from t1 as output. But the following syntax does not allow me to do so. SELECT t1.* FROM (t1 outer join t2 on (t1.a=t2.a and t1.b=t2.b)) t outer join t3 on (t1.c=t3.c); I think you don't want to alias the output of the t1/t2 join to t if you're planning to continue referring to t1 in the rest of the query since I think the alias is going to hide the original t1 name. I'm not sure which outer join you were trying to use, but assuming left for now, I think something like SELECT t1.* FROM t1 left outer join t2 on (t1.a=t2.a and t1.b=t2.b ) left outer join t3 on (t1.c=t3.c); might work for you. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Case Preservation disregarding case
On Tue, 31 Oct 2006, Chuck McDevitt wrote: We treated quoted identifiers as case-specific, as the spec requires. In the catalog, we stored TWO columns... The column name with case converted as appropriate (as PostgreSQL already does), used for looking up the attribute, And a second column, which was the column name with the case exactly as entered by the user. Wouldn't using that second column's value tend to often violate 5.2SR10 (at least that's the reference item in SQL92)? AFAICT, that rule basically says that the regular identifier is equivalent to the case-folded one for purposes of information and definition schema and similar purposes which seems like it would be intended to include things like column labeling for output. There's a little bit of flexibility there on both similar purposes and equivalence, though. 10) The identifier body of a regular identifier is equivalent to an identifier body in which every letter that is a lower- case letter is replaced by the equivalent upper-case letter or letters. This treatment includes determination of equiva- lence, representation in the Information and Definition Schemas, representation in the diagnostics area, and similar uses. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] delete on cascade
On Mon, 23 Oct 2006, Luca Ferrari wrote: Hi all, I guess this is an already asked question, but I didn't found an answer, so apologize me. Imagine I've got two tables: skill(id,description) // primary key = id family(id,description)// primary key = id and I want to associate skills to families: ass_sf(id_skill,id_family)// appropriate foreign keys Using the type information from the original and assuming it's the same for family, without referential actions that'd look something like: Create table skill(id varchar(20) primary key, description varchar(50)); Create table family(id varchar(20) primary key, description varchar(50)); Create table ass_sf(id_skill varchar(20) references skill, id_family varchar(20) referenced family); Tables are already created and the database is running. Now I'd like to implement a delete cascade, thus when I delete a skill also its association with the family must be deleted. In this case, you'd need to remove the constraint on ass_sf.id_skill and replace it with one like foreign key(id_skill) references skill on delete cascade which you can do with alter table in two steps. The on delete information is associated with the foreign key and deletes matching rows from the referencing table (ass_sf) when rows in the referenced tablen (skill) are deleted. You may also want to think about what the on update behavior should be. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] age() vs. timestamp substraction
On Fri, 6 Oct 2006, Jean-Paul Argudo wrote: Hi all, Where did you get that idea? age's reference point is current_date (ie, midnight) not now(). There are also some differences in the calculation compared to a plain timestamp subtraction. I'm jumping on this thread to point out a little strange thing to me. CURRENT_DATE, converted (stupidly) as a string *with* hour is current date at mid-day: test=# select to_char(current_date,'-MM-DD HH:MI:SS'); to_char - 2006-10-06 12:00:00 (1 ligne) It was a day when I had to debug a strange behaviour in a customer's (bad) code :-) That's both midnight and mid-day. It's asking for 12 hour time and no AM/PM marker. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem with FOR UPDATE
On Thu, 7 Sep 2006, Kaloyan Iliev wrote: Hi All, I have a query in which I want to SELECT FOR UPDATE same rows but only from one table. Firs I try just with SELECT FOR UPDATE but I receive an error because of the LEFT JOIN - ERROR: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join. So I decide to use SELECT FOR UPDATE OF table name but I then receive the error you can see. I think you'd want to use DD not debts_desc as you've renamed the from list entry. Can anyone help me with this query? Thanks in advance. Regards, Kaloyan Iliev rsr=# SELECT rsr-#DD.* rsr-# ( SELECT sum(-amount * saldo_sign(credit)) rsr(# FROM acc_debts ACD1 rsr(# WHERE ACD1.debtid = DD.debtid ) AS saldo, rsr-# C.custid, rsr-# S.descr_bg rsr-#FROM debts_desc DD LEFT JOIN config C ON (DD.conf_id = C.id), rsr-# acc_debts AD, rsr-# acc_clients AC, rsr-# services S rsr-#WHERE DD.debtid = AD.debtid rsr-# AND DD.closed AND NOT DD.paid AND DD.has_invoice AND DD.active AND DD.has_proform AND NOT DD.storned rsr-# AND AD.transact_no = AC.transact_no rsr-# AND AC.ino = 45 rsr-#FOR UPDATE OF debts_desc; ERROR: relation debts_desc in FOR UPDATE/SHARE clause not found in FROM clause ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is it possible to left join based on previous joins result
On Thu, 7 Sep 2006, Emi Lu wrote: Hello, Is it possible to do something like: select ... from t1 inner join t2 ... left join t2.colN When t1 inner join with t2 I got unique result for t2.colN( colN's value is table name). Can I continue to left join with the column colN in table t2? No, in part because it'd have to actually evaluate the first join in order to even plan the remainder of the query. It might be possible to do something similar, albeit somewhat slowly, inside a set returning function, but you'd have to decide how to handle more than one row being returned from the first join even if the value is unique, is that one join against the table or multiple joins. ---(end of broadcast)--- TIP 1: 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 with pagila
On Fri, 1 Sep 2006, Walter Cruz wrote: Hi all. I'm with a little doubt. I'm testing the pagila (the postgres port of mysql sakila sample). Well, I was trying to translate the query: select film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price, film.length AS length, film.rating AS rating, group_concat(concat(actor.first_name,_utf8' ',actor.last_name) separator ',') AS actors from category inner join film on(category.category_id = film.category_id) inner join film_actor on(film.film_id = film_actor.film_id) inner join actor on(film_actor.actor_id = actor.actor_id) group by film.film_id; Assuming that film_id is the primary key on film and category_id is the primary key on category, I think you'd be allowed to have the other column references in SQL03 (and 99?) but not in SQL92 (which is the version that PostgreSQL currently implements). IIRC, the later specs allow you to not mention columns in group by that are functionally dependant on other columns that are mentioned. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] joining VIEWs
On Tue, 22 Aug 2006, Brian Cox wrote: Given a view like: create view view1 as select g.id as UserGroupId, s.uid as UserId, s.time as StartTime from stats s join groups g on g.uid = s.uid and a SELECT like: select a.UserGroupId,b.UserGroupId from view1 a full outer join view1 b on b.UserGroupId = a.UserGroupId WHERE a.StartTime = '2006-1-1' AND a.StartTime '2007-1-1' AND b.StartTime = '2005-1-1' AND b.StartTime '2006-1-1'; where there are 5695 rows in 2006 and 1 row in 2005, I expected to get a result set of 5695 rows, but instead got only 1 row (the common row in the 2 years). This seems contrary to the definition of full outer join. Am I missing something? The where clause is applied after the join. If you want to filter the rows before/during the join itself you can use subselects in the from clause or put the additional conditions in the on condition. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CREATE TABLE AS inside of a function
On Mon, 24 Jul 2006, Kevin Nikiforuk wrote: So, I've changed my code as Erik suggested: CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ DECLARE lv RECORD; BEGIN FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS SELECT ldev FROM ldevrg WHERE rg=' || quote_literal($lv) || ';' I think you want something like lv.rg (no special punctuation) rather than $lv in the above. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error when trying to use a FOR loop
On Fri, 21 Jul 2006, Kevin Nikiforuk wrote: Many thanks to Stephan, Richard and George. When I was reading the documentation about FOR loops, I didn't realize that I was in the plpgsql section! CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$ BEGIN DECLARE lv RECORD; You'd want to put declare first (it goes before begin). FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP SELECT ldev FROM ldevrg WHERE ldevrg='$lv'; I think you'd want something like ldevrg=lv.rg. END LOOP; What's the final intent for this since AFAICS this is just going to do busy work that throws away the results. If you wanted to see the results of each of these selects you have to do a bit more work. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with privilages please
On Thu, 20 Jul 2006, Hilary Forbes wrote: Dear All We are running pg v 7.4.1 and importantly the database has been converted from earlier versions of pg (6.5 I seem to recall). I have an existing table suppliers and I have created a new user 'hilary' REVOKE ALL on TABLE suppliers FROM hilary; now login as hilary SELECT * from suppliers; and I get all the records!!! This probably means that public also has rights on suppliers (and thus, the user still has access through the public permissions). You can probably get around this by revoking the public rights and granting rights explicitly to the users that should have rights. ---(end of broadcast)--- TIP 1: 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] Error when trying to use a FOR loop
On Thu, 20 Jul 2006, Kevin Nikiforuk wrote: Sorry if this is in the archives, but I've done a search and couldn't find anything relevant. I'm running HP's precompiled version of 8.1.3.1 as part of their Internet Express offering, and I can't seem to run a for loop. Here's what I'm seeing: xp512-0715-0716=# FOR LV in 1..10 LOOP xp512-0715-0716-# select * from ldevrg; ERROR: syntax error at or near FOR at character 1 LINE 1: FOR LV in 1..10 LOOP I think the problem is that the FOR is a pl/pgsql construct and isn't allowed in straight sql contexts but only inside a function. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Atomar SQL Statement
On Fri, 7 Jul 2006, Michael Glaesemann wrote: On Jul 7, 2006, at 7:55 , Weber, Johann (ISS Kassel) wrote: My concern: in a multi threaded environment, can a second thread interrupt this statement and eventually insert the same email address in the table with a different id? Or is this statement atomar? You're safe. Take a look at the FAQ entries on SERIAL: http://www.postgresql.org/docs/faqs.FAQ.html#item4.11.2 I don't think he is, because I don't think the issue is the SERIAL behavior, but instead the NOT EXISTS behavior. Won't the NOT EXISTS in read committed potentially be true for both concurrent sessions if the second happens before the first commits, which then would mean that both sessions will go on to attempt the insert (with their own respective ids from the serial)? Without a unique constraint on email I think he can end up with the same email address with two different ids. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] problem with uniques and foreing keys
On Sat, 6 May 2006, kernel.alert kernel.alert wrote: I create the follow tables... CREATE TABLE empresa ( id_empresa integer NOT NULL primary key, nombre varchar(45), ); CREATE TABLE casino ( id_casino integer NOT NULL, id_empresa integer REFERENCES empresa(id_empresa), nombre varchar(45), primary key(id_casino,id_empresa) ); CREATE TABLE maq_casino ( id_empresa integer NOT NULL REFERENCES casino(id_empresa), id_casino integer NOT NULL REFERENCES casino(id_casino), You probably want a table level constraint like: foreign key (id_casino, id_empresa) references casino(id_casino, id_empresa) That's not the same as two single-column constraints which is what you have above. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] is an explicit lock necessary?
On Thu, 4 May 2006, Ash Grove wrote: Hi, Does beginning a transaction put locks on the tables queried within the transaction? In the example below, is #2 necessary? My thought was that I would need to use an explicit lock to make sure that the sequence value I'm selecting in #4 is the same one that is generated from #3. I'm worried about another instance of the application doing an insert on table1 between #3 and #4. If you have 1 session per instance and #3 and #4 are done after each other without any intervening commands, the behavior of nextval/currval should guarantee that (currval gives the value from this session's nextval, not any other). ---(end of broadcast)--- TIP 1: 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] Outer joins?
On Fri, 28 Apr 2006, Emils wrote: I am trying to do simple self-joins. The table structure is: object_values == obj_id att_id value namely, each object can have arbitrary number of attributes each of them with a value. What I want, is a simple table of objects with some of their specific attributes, the result should be in form: obj_id1 o1att1_value o1att2_value o1att3_value obj_id2 o2att1_value o2att2_value o2att3_value ... Obviously, if eg obj2 doesn't have att2 in the table, I want a NULL in that grid point. So, I thought some nested outer joins should be OK? SELECT OV.obj_id AS obj_id, OV.value AS NAME, ov1.value AS DESCRIPTION, ov2.value AS ICON FROM object_values OV LEFT JOIN object_values ov1 USING(obj_id) LEFT JOIN object_values ov2 USING(obj_id) WHERE OV.att_id=7 AND ov1.att_id=8 AND ov2.att_id=16; AFAIK, effectively first the join happens then the where filter. So, imagine the output of the joins without any where clause and then apply the where clause as a filter upon that. Even if you got NULL extended rows, you'd filter them out because the ov1.att_id and ov2.att_id tests would filter them out. In addition, you won't actually get NULL extended rows I think, because there will always be at least one row with matching obj_id (the one from ov that's being worked on). I think putting a test in an ON clause associated with the join (using something like ... left join object_values ov1 ON(ov.obj_id = ov1.obj_id and ov1.att_id=8) rather than where will consider both as part of the join and null extend even if there are obj_id matches if none of those have att_id=8. Another way of doing the same thing is using subselects in from to filter the right hand tables you wish to join. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] trigger to enforce FK with nulls?
On Wed, 12 Apr 2006, George Young wrote: [PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] I'm starting to use lots of foreign key constraints to keep my data clean. In one case, however, I need to allow null values for the key. E.g.: create table opset_steps(opset text, step text, step_num int); create table steps(run text, step text, opset text, user text, step_num int); The constraint on steps should be: steps.(opset,step) must be found in opset_steps.(opset,step) UNLESS steps.opset is null. The default foreign key case should not error if either steps.opset or steps.step is null. If you're seeing something else, can you give a complete test case? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
On Thu, 6 Apr 2006, Eugene E. wrote: Stephan Szabo wrote: On Wed, 5 Apr 2006, Eugene E. wrote: Stephan Szabo wrote: On Tue, 4 Apr 2006, Eugene E. wrote: Stephan Szabo wrote: On Fri, 31 Mar 2006, Eugene E. wrote: Peter Eisentraut wrote: Eugene E. wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. What you seem to be missing is that PostgreSQL data can be represented in textual and in binary form. What you in psql is the textual form. If you want the binary form you need to select it. Then you can pass the exact bytes back and forth. your sentence is not true. I can not select exact bytes even if i use BYTEA type No, that is still using the textual form. If you use PQexecParams and set the last argument to show you want binary data, you should get binary data. ok then i am using PQexecParams the following tiny program shows a wonderful lameness... What lameness? The fact that you're trying to use a binary (network order maybe) integer as a string? That's not the fault of PQexecParams but of the code calling it. You're right ! That's is not a fault of PQexecParams at all. That's the fault of its design. No, I'd argue in this case that it was the fault of a programmer not paying enough attention/not thinking through what the api doc says. (I pretty know why an integer has been not displayed, but why they designed this function that way ? i do not know) What would you expect it to do given a single result format argument? If you want to propose a new function (set of functions) that have different behavior, make a coherent proposal. Statements like it should do X because I want it to aren't coherent proposals. Expect to get asked why bytea is special -- why should integer be passed as a string given that you may often want to do operations on the value which expect it as an actual number not the string representation of a number. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
On Thu, 6 Apr 2006, Eugene E. wrote: Praescriptum: If my english is ugly and something is written unclear, please complaint, and i'll try to rephrase. anyway i am trying to be understood. I said WHY SHOULD I RETRIVE A SINGLE BYTEA FIELD IN A SEPARATE QUERY ??? I didn't answer this because I didn't feel that it moved the argument forward, but... If you meant that you must retrieve them in a separate query, you're incorrect, since you *could* use the binary form for the others. I can't understand if you don't realize that there is one for all these various types, or that you just don't wish to use it (for example, I believe using %d on ntohl(value from pqgetvalue) or something similar will print your integer). If you are arguing that you don't *wish* to do use that binary form for the other values, I don't see how that's relevant until you've proven the rest of the argument (*). Stephan Szabo wrote: What would you expect it to do given a single result format argument? If you want to propose a new function (set of functions) that have different behavior, make a coherent proposal. Statements like it should do X because I want it to aren't coherent proposals. AFAIK, they convert each value before put it to a result set. I propose to do the following convertion to the textual-form for bytea values: X-X where X is byte [0..255] Okay, now pass that to strcmp or a %s format. AFAIK, the textual-form of values is meant to be a c-string. ab\0cd\0 is not a c-string containing ab\0cd, it's a c-string containing ab. Expect to get asked why bytea is special _Because each type is special._ And at the same time they made bytea MORE special than any other type. I don't think that it's appreciably more special. Look: every type has many representations for its values, some are obvious some are more usefull, some are less useful. they define very useful and obvious representations for all the types but BYTEA. There are two representations of (at least most) types. There's a binary format and a textual format. (They call those representations textual-form.) I think I don't exactly agree with this description, but I'm unclear exactly what you're saying. Are you saying that textual-form is the useful representation, or are you saying that textual-form is the representation and it is useful? and the input of a value demands escaping (we all undersdand why) and for each type the following equality is TRUE: some_data == OUTPUT(INPUT(ESCAPE(some_data))) but for the BYTEA this equality is FALSE ! Why BYTEA is so special ? every value of every type is expected to be given to a client UNCHANGED. This is already false AFAICS. Leading or trailing spaces on a string containing integer get trimmed during the input for example, the string format of date comes back in a particular but other input formats are supported. I don't think the above equality is valid for textual representation. In addition, input could be binary and output textual or the other way around, in some_data is different on both sides. There's no reason that you can't be passing an integer that way. I expect a value of BYTEA to be unchaged too. I think (as above) that your perception of the problem isn't correct. why should integer be passed as a string given because it is not causing problems, as well as if it be passed in any other common form. And i ask you: why integer is actually passed as a string (decimal notation) ? It's not always. It can be, just as bytea can be passed as a string needing escaping, however it can be passed as effectively a binary blob containing an integer value (in network order I believe) just as bytea can be passed as a binary blob. why not to define your own unique more_sofisticated representation ? (as for bytea is defined.) AFAICS, there is one, the binary format for integer. And finally Why so special textual-form defined for bytea ? Why not to leave every byte unchanged, since user knows what kind of data he got. I think this is mostly answered by the above with a little bit of connecting the dots. P.S. changing a format of a whole result-set is not a solution for a field-type-dependent problem. Since we're still arguing about whether it's a field-type-dependent problem or a field-use-dependent problem, I can't really argue this point since it assumes the former and I don't believe that's been shown yet. (*) Yes, it might be nice to have something that did it for you. Having one is not, in my mind, a requirement for the API but instead something to make it easier. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
On Thu, 6 Apr 2006, Eugene E. wrote: Stephan Szabo wrote: What would you expect it to do given a single result format argument? If you want to propose a new function (set of functions) that have different behavior, make a coherent proposal. Statements like it should do X because I want it to aren't coherent proposals. AFAIK, they convert each value before put it to a result set. I propose to do the following convertion to the textual-form for bytea values: X-X where X is byte [0..255] Okay, now pass that to strcmp or a %s format. AFAIK, the textual-form of values is meant to be a c-string. ab\0cd\0 is not a c-string containing ab\0cd, it's a c-string containing ab. WHY strcmp ?! do you really think the user is a fool ? if the user declared something binary, he obviously knows what he has done. WHY c-string ? the user only wants to get PGresult structure. Since this structure provides a length of each value, you have no need in c-string. Why do think the user needs it ? textual-form is just a name of actually existent convertion rule. i am not trying to find out a philosophy here. Then, honestly, nothing anyone can say will help, because you're not willing to actually hold a conversation on the topic. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
On Wed, 5 Apr 2006, Eugene E. wrote: Stephan Szabo wrote: On Tue, 4 Apr 2006, Eugene E. wrote: Stephan Szabo wrote: On Fri, 31 Mar 2006, Eugene E. wrote: Peter Eisentraut wrote: Eugene E. wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. What you seem to be missing is that PostgreSQL data can be represented in textual and in binary form. What you in psql is the textual form. If you want the binary form you need to select it. Then you can pass the exact bytes back and forth. your sentence is not true. I can not select exact bytes even if i use BYTEA type No, that is still using the textual form. If you use PQexecParams and set the last argument to show you want binary data, you should get binary data. ok then i am using PQexecParams the following tiny program shows a wonderful lameness... What lameness? The fact that you're trying to use a binary (network order maybe) integer as a string? That's not the fault of PQexecParams but of the code calling it. It'd be nice for ease of use to be able to say, give me this column (the integer) as a string and this column (the bytea) as binary, especially as the commentary implies that the protocol supports it. But as I said before, that's a separate discussion from whether or not Peter's claim that it's possible to get binary data is false. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] have you feel anything when you read this ?
On Tue, 4 Apr 2006, Eugene E. wrote:
Stephan Szabo wrote:
On Fri, 31 Mar 2006, Eugene E. wrote:
Peter Eisentraut wrote:
Eugene E. wrote:
the problem is: you'll get this four byte sequence '\000' _instead_
of NUL-byte anyway.
What you seem to be missing is that PostgreSQL data can be represented
in textual and in binary form. What you in psql is the textual form.
If you want the binary form you need to select it. Then you can pass
the exact bytes back and forth.
your sentence is not true.
I can not select exact bytes even if i use BYTEA type
No, that is still using the textual form. If you use PQexecParams and set
the last argument to show you want binary data, you should get binary
data.
Documentation says:
===
PQexecParams
Submits a command to the server and waits for the result, with the
ability to pass parameters separately from the SQL command text.
===
How should i use this func to change so-called textual form of a
select-result to so-called binary form ?
From the 8.1 docs (although I believe this applies back to 7.4):
PQexecParams
Submits a command to the server and waits for the result, with the
ability to pass parameters separately from the SQL command text.
PGresult *PQexecParams(PGconn *conn,
const char *command,
int nParams,
const Oid *paramTypes,
const char * const *paramValues,
const int *paramLengths,
const int *paramFormats,
int resultFormat);
PQexecParams is like PQexec, but offers additional functionality:
parameter values can be specified separately from the command string
proper, and query results can be requested in either text or binary
format. PQexecParams is supported only in protocol 3.0 and later
connections; it will fail when using protocol 2.0.
If parameters are used, they are referred to in the command string as
$1, $2, etc. nParams is the number of parameters supplied; it is the
length of the arrays paramTypes[], paramValues[], paramLengths[], and
paramFormats[]. (The array pointers may be NULL when nParams is zero.)
paramTypes[] specifies, by OID, the data types to be assigned to the
parameter symbols. If paramTypes is NULL, or any particular element in the
array is zero, the server assigns a data type to the parameter symbol in
the same way it would do for an untyped literal string. paramValues[]
specifies the actual values of the parameters. A null pointer in this
array means the corresponding parameter is null; otherwise the pointer
points to a zero-terminated text string (for text format) or binary data
in the format expected by the server (for binary format). paramLengths[]
specifies the actual data lengths of binary-format parameters. It is
ignored for null parameters and text-format parameters. The array pointer
may be null when there are no binary parameters. paramFormats[] specifies
whether parameters are text (put a zero in the array) or binary (put a one
in the array). If the array pointer is null then all parameters are
presumed to be text. resultFormat is zero to obtain results in text
format, or one to obtain results in binary format. (There is not currently
a provision to obtain different result columns in different formats,
although that is possible in the underlying protocol.)
---
Note the last argument to the function, and the last couple of sentences
in the above describe how to use resultFormat. It'd be nice if we could
get an interface which allowed mixing, but that's secondary to can we get
binary data or not.
Here's a similar app to the one you sent which for me seemingly gives the
binary data:
#include stdlib.h
#include stdio.h
#include libpq-fe.h
int
main (void)
{
PGconn * conn;
PGresult * res;
char * val;
inti;
intlen;
conn = PQconnectdb(user=sszabo password=a dbname=sszabo);
PQexec(conn, CREATE TABLE t (a BYTEA));
PQexec(conn, INSERT INTO t VALUES ('ab000cd'));
res = PQexecParams(conn, SELECT a FROM t, 0, NULL, NULL,
NULL, NULL, 1);
val = PQgetvalue(res,0,0);
len = PQgetlength(res,0,0);
printf(what_we_retrive='%s' its_value_length=%i\n,val,len);
for (i=0; i len; ++i) {
printf(Position %d is %d (%c)\n, i, val[i], val[i]);
}
PQclear(res);
PQfinish(conn);
return 0;
}
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] have you feel anything when you read this ?
On Fri, 31 Mar 2006, Eugene E. wrote: Peter Eisentraut wrote: Eugene E. wrote: the problem is: you'll get this four byte sequence '\000' _instead_ of NUL-byte anyway. What you seem to be missing is that PostgreSQL data can be represented in textual and in binary form. What you in psql is the textual form. If you want the binary form you need to select it. Then you can pass the exact bytes back and forth. your sentence is not true. I can not select exact bytes even if i use BYTEA type No, that is still using the textual form. If you use PQexecParams and set the last argument to show you want binary data, you should get binary data. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem using set-returning functions
On Mon, 27 Mar 2006, Markus Schaber wrote: Hi, John, John DeSoi wrote: With SRFs, you need to specify what you want to select. In other words if you are calling generate_x(bar) you need select * from generate_x(bar) -- select generate_x(bar) will not work. So, then, why does it work with generate_series() and dump()? It's an implementation detail. Some languages handle SRFs in a way that can be handled in the select list (SQL and C I think) and others do not (plpgsql). The latter will likely change at some point, although there are some confusing issues with SRFs in the select list as well, see the difference in behavior between: select generate_series(1,10), generate_series(1,5); vs select * from generate_series(1,10) g1, generate_series(1,5) g2; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SQL Query Newbie Help
On Fri, 24 Mar 2006, Julie Robinson wrote: This works, but is there a better solution? select * from quality_control_reset T where date = ( select max(date) from quality_control_reset where qualitycontrolrange = T.qualitycontrolrange); If you can use PostgreSQL extensions (and don't care that you might not get two rows if two ids had the same date equaling the max date for a given range), maybe something like: select distinct on (qualitycontrolrange) id, date, qualitycontrolrange from quality_control_reset order by qualitycontrolrange,date desc; Otherwise, you might see how the above compares in plan to something like (not really tested): select T.* from quality_control_reset T inner join (select qualitycontrolrange, max(date) as date from quality_control_reset group by qualitycontrolrange) T2 on (T.qualitycontrolrange = T2.qualitycontrolrange and T.date=T2.date); Julie Robinson wrote: Given the two tables at the bottom of this email, I'm having trouble coming up with a SQL statement that returns all rows in the quality_control_reset table where there is only one row for the most recent quality_control_range. Help? Example: In table quality_control_reset: id | date| qualitycontrolrange - 1| 02/23/2006 | 20 2| 02/23/2006 | 6 3| 02/28/2006 | 18 4| 03/01/2006 | 18 5| 03/23/2006 | 12 6| 03/23/2006 | 20 I want the results of the following from the query: id | date| qualitycontrolrange - 2| 02/23/2006 | 6 4| 03/01/2006 | 18 5| 03/23/2006 | 12 6| 03/23/2006 | 20 CREATE TABLE quality_control_reset ( id int8 NOT NULL, date timestamp, qualitycontrolrange int8, CONSTRAINT quality_control_reset_pkey PRIMARY KEY (id), CONSTRAINT fk42a706efb62efa94 FOREIGN KEY (qualitycontrolrange) REFERENCES quality_control_range (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) CREATE TABLE quality_control_range ( id int8 NOT NULL, code varchar(255), CONSTRAINT quality_control_range_pkey PRIMARY KEY (id) ); ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Referential integrity broken (8.0.3), sub-select help
On Tue, 21 Mar 2006 [EMAIL PROTECTED] wrote: I've got 2 tables, url (U), and bookmark (B), with bookmark pointing to url via FK. That's not what your schema below has. Your fragment below has URL pointing to bookmark. Somehow I ended up with some rows in B referencing non-existent rows in U. With the below, this is entirely possible, since you're only guaranteeing that URLs have valid bookmarks not the other way around. Are you sure the below is actually what you have? This sounds super strange and dangerous to me, and it's not clear to me how/why PG let this happen. I'm using 8.0.3. Here are the table references I just mentioned: Table bookmark: id SERIAL CONSTRAINT pk_bookmark_id PRIMARY KEY Table url: url_id INTEGER CONSTRAINT fk_bookmark_id REFERENCES bookmark(id) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Referential integrity broken (8.0.3), sub-select help
On Tue, 21 Mar 2006 [EMAIL PROTECTED] wrote: I mistakenly swapped the tables in my email. Here they are, corrected: Table url: id SERIAL CONSTRAINT pk_url_id PRIMARY KEY Table bookmark: url_id INTEGER CONSTRAINT fk_url_id REFERENCES url(id) I see my questions got chopped off from this email below, so let me restate them: Problem #1: Strange that PG allowed this to happen. Maybe my DDL above allows this to happen and needs to be tightened? I thought the above would ensure referential integrity, but maybe I need to specify something else? That seems like it should have worked. I don't know of any cases that'd fail without referential actions (there are some cases with actions and before triggers or rules), so if you have any leads, that'd be useful. Problem #2: I'd like to find all rows in B that point to non-existent rows in U. I can do it with the following sub-select, I believe, but it's rather inefficient (EXPLAIN shows both tables would be sequentially scanned): SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u WHERE b.url_id=u.id); Is there a more efficient way to get the rows from bookmark? I think something like the following would work SELECT * FROM bookmark WHERE url_id NOT IN (SELECT id FROM url u); Raising work_mem may help get a better plan as well. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Using a parameter in Interval
On Tue, 21 Mar 2006, Davidson, Robert wrote: No matter how I try to concatenate, I can't seem to get a parameter to be used by INTERVAL in a function: CREATE OR REPLACE FUNCTION Testing(TrailingWeeks int) RETURNS date AS $$ BEGIN RETURN current_date - INTERVAL (CAST(TrailingWeeks AS varchar) || ' weeks'); END; $$ LANGUAGE plpgsql; --select * from testing(1); ERROR: syntax error at or near CAST at character 34 QUERY: SELECT current_date - INTERVAL (CAST( $1 AS varchar) || ' weeks') CONTEXT: SQL statement in PL/PgSQL function testing near line 2 I have tried concatenating it as a declared variable (with and without apostrophes) 1 weeks And '1 weeks' With no success. Any tips? You'd need a cast, not INTERVAL foo as the latter is for interval literals (and CAST... is not a valid interval literal even if the output of the concatenation looks like an interval literal). I'd go with the suggestion of using int * interval instead of concatenation in any case. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] functions in WHERE clause
On Sun, 5 Mar 2006 [EMAIL PROTECTED] wrote: On Sun, Mar 05, 2006 at 01:16:40PM -0500, Tom Lane wrote: [EMAIL PROTECTED] writes: That would work fine if you said RETURNS SETOF ltree. That should work too, except that you are trying to return a record not an ltree value. Try RETURN NEXT tree.ltree. Because SETOF won't work in a WHERE context. Possibly you need to read the error messages you are getting more closely, because I'm pretty sure whatever it said had nothing to do with either SETOF or WHERE ... I think it does, actually. I can write functions that return ltrees, records, or sets of ltree, and they'll work in any part of the query -- except the WHERE clause. If the function returns anything other than a bool, it complains that the function must return a bool. Which makes sense to me, actually, because the result of something like: x = 42 and y = 77 (to quote your earlier example) should be true or false -- not a set of rows or records or types. At least I think. But let's return to your example for a moment, because it really does look like the kind of rewrite rule that I want here. You suggested replacing: SELECT * from some_table WHERE x = 42 AND y = 77 with create function mytest(int,int) returns bool as $$select $1 = 42 AND $2 = 77$$ language sql; So you could then do: SELECT * from some_table WHERE mytest(x,y); But imagine instead that this function is more generic. You know that you're trying to get something that's equal to x and equal to y, but you don't know (until the function is called) what those rvalues should be. In other words, it's the 42 and the 47 that you don't know until runtime -- you always know what columns your searching on. Then you need to pass those in as well as something representing the row that's being tested -- where clauses are filters on rows. The whole row representation might be better than columns for some cases. For example: create table tt1(a int, b int); create function f1(tt1, int) returns bool as 'select $1.a = $2' language 'sql'; select * from tt1 where f1(tt1, 1); --- The other option is to do this as a set returning function in the first place rather than trying to do a wierd where clause thing. create function f2(int) returns setof tt1 as 'select * from tt1 where a = $1' language 'sql'; select * from f2(1); ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Help with trigger that updates a row prior to a potentially
On Wed, 1 Mar 2006, Simon Kinsella wrote: Hi all, I have a situation where a DELETE operation may (correctly) fail due to a RESTRICT FK constraint. If so, I need to set a flag in the row indicating that it has been marked for deletion so that I can disregarded in subsequent queries. I'm trying to achieve this with a BEFORE DELETE trigger, which would set the 'marked_for_deletion' field to TRUE before attempting the delete proper. Then if the DELETE fails the row would still be tagged and I'd be happy. Problem is, when the DELETE operation fails with a RESTRICT VIOLATION error the entire operation is rolled back, including the BEFORE triggers, leaving me back where I started. Is there anyway to get the DELETE operation, or more specifically the FK constraint, to fail silently, i.e. to skip over the failed operation and not throw an exception? Not with the standard constraint trigger, no. In general, constraint checks happen after the action and as such can't skip over an operation since it's already happened. You might be able to do this within a function however if you do the update and then start an exeption checking block to do the delete. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [SQL] Interval subtracting
On Wed, 1 Mar 2006, Hannu Krosing wrote: Ühel kenal päeval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian: Stephan Szabo wrote: justify_days doesn't currently do anything with this result --- it thinks its charter is only to reduce day components that are = 30 days. However, I think a good case could be made that it should normalize negative days too; that is, the invariant on its result should be 0 = days 30, not merely days 30. What about cases like interval '1 month -99 days', should that turn into interval '-3 mons +21 days' or '-2 mons -9 days'? I think it should be the later. It is best to have a single sign, and I think it is possible in all cases: '2 mons -1 days' could be adjusted to '1 mons 29 days'. But unfortunately '2 mons -1 days' '1 mons 29 days' If I want something to happen 1 day less than two months from dome date, then the only way to say that consistently *is* '2 mons -1 days'. Right, but would you call justify_days on such an interval? '2 months -1 days' '1 mon 29 days', but '1 mon 60 days' is also '3 mons' in general usage. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] how to add primary key to existing table with multiple
On Thu, 23 Feb 2006, Daniel Joo wrote: I am trying to add another primary key to an existing table with two other primary keys. I got the following error when I tried this command: You only can have one primary key. The table you gave has a single primary key with two columns. Are you trying to guarantee that expid along is unique or that the set (probeid, tissueid, expid) is unique? You can make expid UNIQUE and NOT NULL for the first case which is similar to a primary key (although it's not a default foreign key target). In the second you can drop the current constraint and add one on the three columns. Indexes: extprobe2tissue_pkey PRIMARY KEY, btree (probeid, tissueid) extprobe2tissue_probeid btree (probeid) extprobe2tissue_tissueid btree (tissueid) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] After Trigger assignment to NEW
On Sat, 25 Feb 2006, Achilleus Mantzios wrote: O Owen Jacobson ?? Feb 24, 2006 : Achilleus Mantzios wrote: O Tom Lane ?? Feb 24, 2006 : By definition, an AFTER trigger is too late to change what was stored. Use a BEFORE trigger. Too late if someone wants to store it. I wanna store the intented original values, thats why i use AFTER trigger. But i would like to alter what a final AFTER trigger would see. I'll elabarote a little. An update happens. The row is stored. An after trigger is fired that alters some NEW columns (nullifies them), aiming for a subsequent trigger to see the altered results . It should be something like a pointer to a HeapTuple, (right?), so that would be feasible i suppose. I would not even make a post if it was something that trivial. I hope you get my point. Your real problem is that the subsequent trigger has behaviour you don't like. That's what you should be fixing. If dbmirror has no way to exclude specific tables from mirroring, take it up with them as a feature request, or patch dbmirror to work how you want it to. AFTER triggers *must* receive the row that was actually inserted/updated/deleted. If they could receive a modified row that didn't reflect what was actually in the database, all sorts of useful trigger-based logging and replication patterns wouldn't work, and there's really no other way to implement them. See also Tom Lane's other message for further implications of being able to modify the rows seen by AFTER triggers. As i have explained my dbmirror is FK null values gnostic(=aware) already as we speak. [...] So nullifying a value just before the dbmirror trigger would do exactly the right thing (for me) Yes it does what you want for this very specific case. But, would it do what you want if someone put a trigger before it that changed the values to some non-NULL thing? That seems likely to break your mirroring. I'd also be hesitant to write triggers that have to execute in a specific order. Meaning that would hurt portability? Most people need features rathen than the relief to know they can migrate to another database (which they probably never will) In this case, you're giving up the feature that users can write constraints, logging or mirroring after triggers that are guaranteed to get the data that was actually inserted in order to get the feature that a trigger can affect the data to the next trigger. This seems like a general loss in functionality for a larger fraction of users than those who gain. Back to AFTER trigger changing values issue, i think things are not so dramatic if FK triggers could just be fired first. Actually, I think we technically fire the checks too early as it is, so I don't see enshrining that or making it earlier is a good idea. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Given 02-01-2006 to 02-28-2006, output all days.
On Sun, 19 Feb 2006, Henry Ortega wrote: I was able to find a suitable 7.3.2 plpgsql.so and now plpgsql works. (supposedly) I am trying out some really basic function creation such as this: create function dng2(start_date DATE) returns setof date as $$ declare aa date:=start_date; I don't think the beginning is a valid function definition in 7.3.x as I'm pretty sure it didn't have the grammar support for named parameters. Also, I think dollar quoting came in 8.0, so that's not going to work either. You may be looking at a different version of the docs than the version you're using. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Interval subtracting
On Sat, 18 Feb 2006, Tom Lane wrote: Milen A. Radev [EMAIL PROTECTED] writes: Milorad Poluga : SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval ?column? --- 3 mons -14 days Why not '2 mons 16 days' ? Please read the last paragraph in section 8.5.1.4 of the manual (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775) . It mentions the functions named justify_days and justify_hours that could do what you need. justify_days doesn't currently do anything with this result --- it thinks its charter is only to reduce day components that are = 30 days. However, I think a good case could be made that it should normalize negative days too; that is, the invariant on its result should be 0 = days 30, not merely days 30. What about cases like interval '1 month -99 days', should that turn into interval '-3 mons +21 days' or '-2 mons -9 days'? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] alter table
On Wed, 15 Feb 2006, Owen Jacobson wrote:
Maciej Piekielniak wrote:
Wednesday, February 15, 2006, 8:31:17 PM, you wrote:
OJ Note that prior to 8.0 PostgreSQL does not support
multiple ALTER actions in a single query. To get an
equivalent effect, wrap separate ALTER TABLE queries in a transaction:
OJ BEGIN;
OJ alter table xyz alter column id set default nextval('xyz_seq');
OJ alter table xyz alter column foo set default '';
OJ COMMIT;
OJ Also, are you sure you want '' as a column default, and
not ALTER COLUMN foo DROP DEFAULT?
OJ -Owen
OK. THX. Second question:
First, maybe set many fields with the same action - ex. set default?
Ex. on mysql
ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL,
MODIFY specific_name char(64) DEFAULT '' NOT NULL,
MODIFY sql_data_access
enum('CONTAINS_SQL',
'NO_SQL',
'READS_SQL_DATA',
'MODIFIES_SQL_DATA'
) DEFAULT 'CONTAINS_SQL' NOT NULL
Under PostgreSQL 7.4 you'd need to do those as three separate ALTER TABLE
statements:
BEGIN;
ALTER TABLE proc ALTER name DEFAULT '' NOT NULL;
ALTER TABLE proc ALTER specific_name DEFAULT '' NOT NULL;
... and so on ...
COMMIT;
Note that ALTER TABLE under postgresql cannot change a column's type
(including precision or length).
Not in 7.4, but I believe 8.1 allows that (ALTER TABLE ALTER COLUMN TYPE
with semi-optional USING)
---(end of broadcast)---
TIP 1: 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] Trigger/Sequence headache
On Sun, 12 Feb 2006, Foster, Stephen wrote: This is going to be one of those stupid problems of mine. I have an insert trigger setup to verify that duplicate or repeating information isn't storage in the table. If trigger function finds the information as a duplicate it returns a NULL and the information isn't added; that works. The problem I'm having is that it is incrementing the sequence counter even when the data isn't added. Is this something that I have to live with or should I be returning something other than a NULL? The sequence is going to increment upon getting the value. However, I think, if instead of using a default, you got the next value in the trigger after you determined that it wasn't a duplicate and set the field, it wouldn't increment for this case. This changes some other behaviors a little (for example DEFAULT in updates as well), so you'd need to see whether it'd be acceptable. Of course, errors, rollbacks and deletes will still leave holes. ---(end of broadcast)--- TIP 1: 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] executing dynamic commands
On Wed, 1 Feb 2006 [EMAIL PROTECTED] wrote: Hi, I user PostgreSQl 8.0.4 on Win2003 Server and write a function to copy rows from one table into another table with the same column definition. My first approach was to use something like: query_value := 'INSERT INTO ' || tabledest || ' SELECT * FROM ' || tablesrc; EXECUTE query_value; This only works if the column definition AND the order between source and destination is the same ! In my case I have always the same column definitions but they are not in the same order between source and destination table. What I tryed then is to loop through the column definition of the source and query the sourcetable for the value. For that I have to execut a query with dynamic tablename and dynamic columname to generate two stings one with the columndefinitin and one with the columnvalues to exececute something like: INSERT INTO tabelfoo (columndefinitinstring) VALUES (columnvaluesstring) You might have better luck with a INSERT ... SELECT where you've reordered the columns in the select list INSERT INTO tabledest SELECT reordered columns to match dest order FROM tablesrc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Question about check constraints
On Fri, 27 Jan 2006, Kashmira Patel (kupatel) wrote: Both concerns. 1) There are actually more than two columns with such checks, and each one calls a few functions which execute some more queries. So I would like to invoke these checks only when necessary. 2) The bigger concern is the side effect: Here's my schema: CREATE TABLE vm_device ( device_id INTEGER UNIQUE NOT NULL REFERENCES device_table(device_id) ON UPDATE CASCADE ON DELETE CASCADE, preference VARCHAR(1) NOT NULL DEFAULT 'U' CHECK (CASE WHEN preference = 'U' THEN true ELSE validate_preference() END), enabledBOOLEAN NOT NULL DEFAULT false CHECK (CASE WHEN enabled = false THEN true ELSE validate_system_enabled() AND validate_enabled(device_id) END), attach_vm BOOLEAN NOT NULL DEFAULT false CHECK (CASE WHEN attach_vm = false THEN true ELSE validate_attach_vm() END), PRIMARY KEY (device_id) ) WITHOUT OIDS; This table contains some information about a device in my system. The issue is with the enabled column. It basically enables/disables the device. The device can be enabled only when the two check conditions pass. But once it is enabled, the conditions of the system might change such that if executed again, these conditions might not pass. We want to allow such situations. The problem arises when we want to change the value of some other column, say attach_vm. Although the check constraints for the attach_vm column pass, those for enabled column fail, and I cannot complete my updates. In that case check constraints are probably not the correct tool. IIRC the theoretical model from the spec implies that all check constraints in the system are supposed to be satisfied at their check time (statement end in most cases) and that it doesn't matter what the change was, and as such, the conditions of the system shouldn't have been allowed to change such that a row with enabled=true existed when its constraint would be violated at this moment. I don't think that's practically reasonable to enforce in general, but we do the best we can which is fail the later update. Any suggestions on the best way to overcome this? I'd second Michael's suggestion of a trigger. -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Friday, January 27, 2006 4:40 PM To: Kashmira Patel (kupatel) Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Question about check constraints On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel) wrote: I have a table where two columns have two different check constraints associated with them. When I update one column, the check constraint on the other column is also executed. Is there a way to avoid this? I want to check only for the condition defined for the column being updated. I don't think you can change this behavior: each CHECK constraint is evaluated for the new row regardless of whether a particular column changed or not. However, you could enforce the constraints with a trigger and skip checks where NEW.column is the same as OLD.column. Why the concern? Are the checks expensive? Do they have side effects? What do they do? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to get a count() where column ''?
On Sun, 4 Dec 2005, Joost Kraaijeveld wrote: Hi, I want the number of customers that have a zipCode smaller tha a given value. The foolowing query doe snot work : I get an error (ERROR: column addresses.zipcode must appear in the GROUP BY clause or be used in an aggregate function) and I do not know how to solve it. SELECT COUNT(customers.objectid) FROM prototype.customers, prototype.addresses WHERE customers.contactaddress = addresses.objectid AND zipCode '2716BN' ORDER By zipCode, houseNumber Anyone an idea? In a non-grouped query like the above, I don't think that the order by is meaningful. You only get one row back anyway without a group by, and there's no single zipCode or houseNumber to associate with the row. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to get a count() where column ''?
On Sun, 4 Dec 2005, Joost Kraaijeveld wrote: Hi Stephan, On Sun, 2005-12-04 at 13:33 -0800, Stephan Szabo wrote: SELECT COUNT(customers.objectid) FROM prototype.customers, prototype.addresses WHERE customers.contactaddress = addresses.objectid AND zipCode '2716BN' ORDER By zipCode, houseNumber In a non-grouped query like the above, I don't think that the order by is meaningful. You only get one row back anyway without a group by, and there's no single zipCode or houseNumber to associate with the row. What do you mean by a non-grouped query? The query below gives the same error: A query without a group by, in other words one on which the count is done over the entire set of rows that pass the where clause. SELECT zipcode, COUNT(*) FROM prototype.customers, prototype.addresses WHERE customers.contactaddress = addresses.objectid AND zipCode '2716BN' Yes, because without a group by there's one count and it has no associated zipcode to put in the select list. I believe select count(*) from prototype.customers, prototype.addresses where customers.contactaddress = addresses.objectid and zipCode '2716BN'; will work and give you an overall count. select zipcode, count(*) from prototype.customers, prototype.addresses where customers.contactaddress = addresses.objectid and zipCode '2716BN' group by zipcode order by zipcode; should give you a list broken up with a count by zipcode in order of zipcode. ---(end of broadcast)--- TIP 1: 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] argument type problem with plpgsql function
On Tue, 22 Nov 2005, Luca Pireddu wrote: I wrote a little function that has to work with big numbers CREATE OR REPLACE FUNCTION blast_evalue(seq_len bigint, db_size bigint, bit_score double precision) RETURNS double precision AS $$ BEGIN RETURN 2^(bit_score) * db_size * seq_len; END; $$ LANGUAGE 'plpgsql' IMMUTABLE RETURNS NULL ON NULL INPUT; but it doesn't work properly unless I cast the db_size parameter when I call the function: select blast_evalue(273, 8903836, -55.4546); blast_evalue -- 2430747228 - wrong number. This is 273 * 8903836 (1 row) select blast_evalue(273, 8903836::bigint, -55.4546); blast_evalue - 4.9231356421437e-08 - that's correct (1 row) I don't understand why the cast is necessary. Is there a way to make this work without it? I got the same answer (the second) for both calls from my 8.0 and 8.1 setups, what version were you trying on? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem with NOT IN (subquery)
On Sun, 13 Nov 2005, Steve SAUTETNER wrote: Hi, I have a table named famille whose structure and content is : famille_code | famille_mere_famille_code | famille_libelle | famille_niveau --+---+---+- --- 00 | | Mhre | Is that a NULL famille_mere_famille_code? The first col is the family id and the second is the mother family id. I would get a list of all families that are never in the col n?2, so the families that aren't node but leaf. The query, i made was SELECT * FROM famille WHERE famille_code NOT IN (SELECT DISTINCT famille_mere_famille_code FROM famille); But the DB returns 0 records instead of 15. If i use a list instead of a subquery it works normaly but it's not easy to manage it like this. So if anyone can help me please ... In the case where the subselect returns a NULL, the behavior of IN and NOT IN is rather unfortunate. A NOT IN B is basically NOT(A IN B) and A IN B is basically A =ANY B IIRC. However, A=ANY B only returns false if A = Bi returns false for all Bi contained in B and A = NULL returns unknown, not false, so NOT IN cannot return true if the subselect contains a NULL. If that is a null above, probably the best solution is to exclude NULLs from the subselect results. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] RETURNS SETOF primitive returns results in parentheses
On Wed, 26 Oct 2005, Mario Splivalo wrote: Consider this function: CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4, varchar) RETURNS SETOF varchar AS $BODY$ DECLARE aRecordID ALIAS FOR $1; aSubFieldId ALIAS FOR $2; returnValue record; subFieldNumber char(3); subFieldLetter char(1); BEGIN subFieldNumber = substr(aSubFieldId, 1, 3); subFieldLetter = substr(aSubFieldId, 4); FOR returnValue IN SELECT subfieldValue::varchar FROM records_sub WHERE fieldTag = subFieldNumber AND subfieldTag = subFieldLetter AND recordId = aRecordId LOOP RETURN NEXT returnValue; I think the root cause is that you're not returning a varchar here, but instead a record containing a varchar (if I return next returnValue.subfieldValue I don't seem to get parens). I'm not sure why it's allowing you to do so, though, it seems like that shouldn't match the return type. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] NULL in IN clause
On Wed, 19 Oct 2005, [iso-8859-2] Havasv?lgyi Ott? wrote: Hi, I have just run this command on 8.0.4 : SELECT 'foo' WHERE 0 NOT IN (NULL, 1); And it resulted is zero rows. Without NULL it is OK. Is this a bug, or the standard has such a rule? This is standard behavior. Seeing if I can do this from memory... a NOT IN b is equivalent in the spec to NOT(a IN b). a IN b is equivalent to a =ANY b. a =ANY b returns true if a = x is true for any x in b. a =ANY b returns false if a = x is false for all x in b. Otherwise it returns unknown. 0 = NULL returns unknown 0 = 1 returns false So, 0 IN (NULL,1) returns unknown. NOT(unknown) is unknown. WHERE clauses only return rows for which the search condition is true, so a row is not returned. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pg, mysql comparison with group by clause
On Tue, 11 Oct 2005, Rick Schumeyer wrote: I'm not sure what I was thinking, but I tried the following query in pg: SELECT * FROM t GROUP BY state; pg returns an error. Mysql, OTOH, returns the first row for each state. (The first row with AK, the first row with PA, etc.) I'm no SQL expert, but it seems to me that the pg behavior is correct, and the mysql result is just weird. Am I correct? In your case, it sounds like the mysql result is wrong. I believe SQL99 would allow it if the other columns were functionally dependant upon state (as there'd by definition only be one value for the other columns per group). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Use of partial index
On Wed, 5 Oct 2005, Leif B. Kristensen wrote: I'm a little confused about partial indexes. I have a couple of tables, like this: CREATE TABLE events ( event_idINTEGER PRIMARY KEY, tag_type_fk INTEGER REFERENCES tag_types (tag_type_id), place_fkINTEGER REFERENCES places (place_id), event_date CHAR(18) NOT NULL DEFAULT '31', sort_date DATE NOT NULL DEFAULT '40041024BC', event_text TEXT NOT NULL DEFAULT '', sentenceTEXT NOT NULL DEFAULT '' ); To this table I have created a partial index: CREATE INDEX events_born ON events (tag_type_fk) WHERE tag_type_fk = 2; Another table: CREATE TABLE participants ( -- the TMG 'E' file participant_id INTEGER PRIMARY KEY, person_fk INTEGER REFERENCES persons (person_id), event_fkINTEGER REFERENCES events (event_id), role_type_fkINTEGER REFERENCES role_types (role_type_id), is_principalBOOLEAN NOT NULL DEFAULT 'f', is_primary_eventBOOLEAN NOT NULL DEFAULT 'f', participant_noteTEXT NOT NULL DEFAULT '', participant_nameTEXT NOT NULL DEFAULT '', age_meanINTEGER NOT NULL DEFAULT 0, age_deviINTEGER NOT NULL DEFAULT 0, CONSTRAINT person_event UNIQUE (person_id, event_id) ); And a view: CREATE OR REPLACE VIEW principals AS SELECT participants.person_fk AS person, events.event_id AS event, events.place_fk AS place, events.event_date AS event_date, events.sort_date AS sort_date, events.tag_type_fk AS tag_type FROM events, participants WHERE events.event_id = participants.event_fk AND participants.is_principal IS TRUE; Now, here's an explain select: pgslekt= explain select event_date, place from principals where person=2 and tag_type=2; QUERY PLAN --- Nested Loop (cost=0.00..23.15 rows=2 width=26) - Index Scan using person_event on participants (cost=0.00..13.63 rows=3 width=4) Index Cond: (person_fk = 2) Filter: (is_principal IS TRUE) - Index Scan using events_pkey on events (cost=0.00..3.16 rows=1 width=30) Index Cond: (events.event_id = outer.event_fk) Filter: (tag_type_fk = 2) (7 rader) Why doesn't this SELECT use the partial index events_born above? Is there any way to make this happen? I would think you'd want an index ON events(event_id) WHERE tag_type_fk=2 for the query given. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] plpgsql function not accepting NULL value
On Fri, 16 Sep 2005, Kenneth Dombrowski wrote: I can't get this one to work at all: create or replace function update_rate (integer, integer, integer, integer, numeric, integer) returns void as ' declare x_admin_id alias for $1; x_developer_id alias for $2; x_client_id alias for $3; x_project_idalias for $4; x_rate alias for $5; x_rate_id alias for $6; x_meta_id alias for $7; begin perform update_lastmod (x_meta_id, x_admin_id); update rates_tbl set developer_id= x_developer_id , client_id = x_client_id , project_id = x_project_id , rate= x_rate where rate_id = x_rate_id ; return ; end; ' language plpgsql; invoicer= select update_rate(1, 1, 10, NULL, numeric '90.00', 6, 216); ERROR: function update_rate(integer, integer, integer, unknown, numeric, integer, integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. Umm, it looks to me like you're trying to pass 7 parameters to a function that takes 6 in the above. ---(end of broadcast)--- TIP 1: 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] ERROR: syntax error at or near select at character 9
On Tue, 6 Sep 2005, Harald Fuchs wrote:
In article [EMAIL PROTECTED],
Stephan Szabo [EMAIL PROTECTED] writes:
On Fri, 2 Sep 2005, [ISO-8859-2] Graf László wrote:
CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
BEGIN
NEW.id := select nextval('test_azon_seq');
I think you want to remove select here, you're already effectively doing a
select of the right hand side in the assignment.
NEW.nev := nev;
I think you want to remove this line entirely. What nev were you
expecting on the right hand side? If it's the new one, well, NEW.new is
already that.
László could also remove the entire trigger and use something like
CREATE TABLE test (
id SERIAL NOT NULL,
nev VARCHAR(25),
datum TIMESTAMP NOT NULL DEFAULT current_timestamp,
PRIMARY KEY (id)
);
That's slightly different though. The trigger forces the value whether or
not a value was assigned in the insert, the defaults only apply if the
column does not have a value given to it.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Table Constraint CHECK(SELECT()) alternative?
On Sat, 3 Sep 2005, Robert D. Kennedy wrote: I have seen in another thread that sub-queries in a CHECK constraint have implementation ramifications that make them awkward to implement and support. OK, fair enough, c'est la vie. ERROR: cannot use subquery in check constraint is the result. I have a model which seems to BEG for just such a feature though. What alternatives are there to the use of CHECK(SELECT()) in this model? The simplest thing is to place the select into a function and use a check constraint on that (with appropriate row locks). However, that won't prevent removal or change of a referenced row. You can use triggers to prevent that (in your case to prevent removal or change of id of a referenced node or the change of type of a referenced mode away from directory). At that point, you might be just as well off using a trigger to do the initial check as well. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Weird Trigger Behaviour using IF ... THEN ... ESLEIF...
On Mon, 5 Sep 2005, Antony Sohal wrote: Please can you help me with the following trigger I have written in PostgreSQL 7.4.8 running under Fedora Linux, using pgAdmin III as client. Now I create a trigger on event table as : CREATE OR REPLACE FUNCTION fn_event() RETURNS trigger AS ' DECLARE -- Declare a variable to hold the event id. eventid INTEGER; logme VARCHAR; eventvalue_id INTEGER; delimiter VARCHAR DEFAULT \'|\'; -- Declare a variable to key and value. eventkey VARCHAR; eventvalueVARCHAR; BEGIN eventid := NEW.event_id; logme := substring(NEW.log_statement FROM position(delimiter IN NEW.log_statement)+1 FOR length(NEW.log_statement)); WHILE length(logme) 0 AND position(delimiter IN logme) 0 LOOP BEGIN eventkey := substring(logme FROM 0 FOR position(delimiter IN logme)); logme := substring(logme FROM (position(delimiter IN logme)+1) FOR length(logme)); IF position(delimiter IN logme) = 0 THEN BEGIN eventvalue := logme; END; ELSE BEGIN eventvalue := substring(logme FROM 0 FOR position(delimiter IN logme)); END; END IF; logme := substring(logme FROM position(delimiter IN logme) + 1 FOR length(logme)); SELECT INTO eventvalue_id nextval(\'event_sequence\'); EXECUTE \'INSERT INTO event_value (event_value_id, event_id, event_key, event_value) VALUES (\' || eventvalue_id || \',\' || eventid || \',\' || quote_literal(eventkey) || \',\' || quote_literal(eventvalue) || \')\'; END; END LOOP; RETURN NULL; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER tg_event AFTER INSERT ON event FOR EACH ROW EXECUTE PROCEDURE fn_event(); Then I create a trigger on event_value: CREATE OR REPLACE FUNCTION fn_event_value() RETURNS TRIGGER AS ' DECLARE -- Declare a variable to key and value. id INTEGER; BEGIN EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || quote_literal(NEW.event_key) || \')\'; IF \'APPLICATION\' = NEW.event_key THEN EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || quote_literal(NEW.event_value) || \')\'; RETURN NULL; END IF; IF \'CLI\' = NEW.event_key THEN EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || quote_literal(NEW.event_value) || \')\'; RETURN NULL; END IF; -- do nothing, nothing at all... EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || quote_literal(\'EMPTY\') || \')\'; RETURN NULL; END; ' LANGUAGE plpgsql; I don't see the ELSEIF in either of the above. Now if I change the trigger with this I get the excepted result: CREATE OR REPLACE FUNCTION fn_event_value() RETURNS TRIGGER AS ' DECLARE -- Declare a variable to key and value. id INTEGER; BEGIN EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || quote_literal(NEW.event_key) || \')\'; IF \'APPLICATION\' = NEW.event_key THEN EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || quote_literal(NEW.event_value) || \')\'; RETURN NULL; END IF; IF \'CLI\' = NEW.event_key THEN EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || quote_literal(NEW.event_value) || \')\'; RETURN NULL; END IF; -- do nothing, nothing at all... EXECUTE \'INSERT INTO strings_tbl VALUES ( \' || quote_literal(\'EMPTY\') || \')\'; RETURN NULL; END; ' LANGUAGE plpgsql; However, please can some one explain why IF THEN ELSEIF . THEN ELSEIF THEN ELSE END IF; doesn't work. IIRC Until 8.0 the correct spelling is ELSIF. I believe 8.0 added ELSEIF as an alternate spelling. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] ERROR: syntax error at or near select at character 9
On Fri, 2 Sep 2005, [ISO-8859-2] Graf L?szl? wrote:
CREATE FUNCTION test_verif() RETURNS trigger AS $test_verif$
BEGIN
NEW.id := select nextval('test_azon_seq');
I think you want to remove select here, you're already effectively doing a
select of the right hand side in the assignment.
NEW.nev := nev;
I think you want to remove this line entirely. What nev were you
expecting on the right hand side? If it's the new one, well, NEW.new is
already that.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem calling stored procedure
On Mon, 22 Aug 2005 [EMAIL PROTECTED] wrote: Hi all, I've written a stored procedure but am having trouble calling it. The procedure name is called insert_period and I am calling using: SELECT insert_period(1::INTEGER,'2005-09-13'::DATE,'2005-09-15'::DATE,'unavailable_periods'); But am getting the error message: - ERROR: syntax error at or near $1 at character 70 QUERY: SELECT * FROM bookings WHERE (start_date, end_date) OVERLAPS (DATE $1 - interval '1 day', DATE $2 + interval '1 day') AND property_id = $3 LIMIT 1 CONTEXT: PL/pgSQL function insert_period line 12 at select into variables -- I've used EMS PostgreSQL Manager to write the function, and have successfully used the debugger to step through the function using various calling arguments without issue - I only get this problem when trying to call the function through a client. Research on this revealed problems when variable names are named after existing postgres functions/tables/columns, but I to my knowledge there is nothing in the database named the same of my arguments. I've tried renaming them all to random names, but to no avail. I've also tried declaring the variables as ALIAS FOR in the DECLARE section, but again no luck. The other thing that concerns me is that the error shows $1 being used as a DATE argument, I would have thought 'prop_id' (See below) would have been $1? Me too, however in any case, DATE blah is for date literals so I don't believe it's what you want in this case anyway since you're using a variable. I think you'd just want new_start_date, etc, since they're already dates. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Why Doesn't SQL This Expression Work?
On Mon, 22 Aug 2005, Lane Van Ingen wrote: Hi, am trying to do a simple computation on two views, but for some reason the current_util_in computation always returns zero. All fields being used are integer. select a.if_id, a.in_count, a.time_incr, b.speed, ((a.time_incr * b.speed) / 8) as possible_bytes, (a.in_count / ((a.time_incr * b.speed) / 8) * 100) AS current_util_in, from if_history_view1 a, speed_history_view1 b where a.if_id = b.if_id and a.if_id = 2; The inner computation (a.time_incr * b.speed / 8) evaluated properly to 7675200. Add the in_count divide operation, and the result is zero. Integer division doesn't follow all the same rules as normal division would. In particular (a/b)*c is not the same as a*c/b. Also, I think you may be expecting rounding rather than truncation (and in the case of (a.time_incr*b.speed)/8 can that not be a multiple of 8, and if so what should happen?) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Parentheses in FROM clause and evaluation order.
On Wed, 10 Aug 2005 [EMAIL PROTECTED] wrote: I thought that the parenthesis in the table expression (FROM clause), could be used to indicate the desired evaluation order. But, I tried with a couple of samples and the explain command returned me the same result; no matter what parentheses association I used. I am using only INNER JOINs. In fact, I thought that the whole table expression was gonna be evaluated before the WHERE filter. Does the stantard says something about this evaluation order when the parentheses are present? Does PostgreSQL implements this behavior? AFAIK we only try to provide final results that are equivalent to following the steps in order, so it'll reorder joins or push clauses around as long as it thinks the semantics of the query won't change. For example, actually doing unconstrainted joins before where clauses is a very bad plan if you've got a FROM table1, table2, table3 style query. If you're seeing a place where the reorder affects the query results as opposed to the query plan, that's probably a bug, can you give more information? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Parentheses in FROM clause and evaluation order.
On Mon, 15 Aug 2005, Dario Bahena Tapia wrote: The final result seems to be the same, I just was curious about the standard behavior. Does the SQl says something about this execution order? I believe SQL defines the order to pay attention to parens, so A join (B join C) style clauses result in a table being derived from B join C and another from A joined with that table. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] after delete trigger behavior
On Wed, 22 Jun 2005, Russell Simpkins wrote: Hello, I have created a trigger function to update the sort_order column of a mapping table. I have table a that has a many to many relation ship with table b that is mapped as a_b where a_id, and b_id are the pk columns and there is a sort_order column. Since a_b is a mapping table there are foreign key constraints with a cascade option. So, if i delete an entry from b, an entry in a_b is deleted. What I want though is for the sort_order column to be updated so that all entries of a_b for a given a entry remain in order. a_id, b_id, sort_order 1, 2, 0 1, 3, 1 1, 4, 2 1, 7, 3 if I delete b_id = 4 then the b_id 7 should get a sort order of 2. I created an after delete trigger and the trigger works just fine when i delete only one row, but if I delete all using delete from a_b I am only able to delete one row. Here is an example: - -- a test table CREATE TABLE test1 ( a int, b int, c int); - -- a resort function CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS ' DECLARE eachrow RECORD; innerrow RECORD; sort INT := 0; BEGIN EXECUTE ''UPDATE portfolio.test1 set c = c - 1 where a = '' || OLD.a || '' and c '' || OLD.c; RETURN OLD; END; ' language 'plpgsql'; - -- the trigger CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE PROCEDURE resort_test1(); I think this will work in an after delete trigger, but not in a before delete trigger (and seems to in my tests). I'm not sure what the spec says about the visibility of rows in cases like this. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] rule or trigger?
On Tue, 7 Jun 2005, M.D.G. Lange wrote: It is not possible to create a constraint Foreign key for wordid. No problem there, but I want to be certain that a given wordid exists in tbldictionary. Would I have to create a RULE or a TRIGGER to be certain that the wordid is existing in tbldictionary in whatever language. I have the idea that a trigger will not prevent the insertion, or did I not read well enough? What you can do in an after trigger is test that the value exists in the other table or raise an error (which is basically what the foreign key triggers do). There are some issues with writing your own, the first being that to get full foreign key style semantics you need triggers on the referenced table as well, however if removing or updating the wordid in tbldictionary is uncommon, you can probably avoid it. ---(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
Re: [SQL] Discordance between the way select is called.
Hi, I have a java app that uses hibernate to do queries. One query on a 6.5 millions records takes about 15 seconds while the same one (take from the sql that shows in the consol - I configured hibernate to show_sql) takes about 50 ms when done with pgadmin3. We could answer better with explain output. Something like: prepare foo(int) as select notevalue0_.id as id, notevalue0_.value_note as value2_3_, notevalue0_.actif as actif3_, notevalue0_.id_note as id4_3_, notevalue0_.id_field_name as id5_3_ from note.note_value notevalue0_ where notevalue0_.id_note=$1 and notevalue0_.actif=1; explain analyze execute foo(put a representative value here); - As an initial guess, I'd wonder if it's guessing that for an arbitrary id_note value that it should do a sequence scan. Are there a large number of very common id_note values that might be throwing it off? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] multi-column unique constraints with nullable columns
On Sat, 30 Apr 2005, Tornroth, Phill wrote: I believe you can add partial unique indexes to cover the case where a column is null, but if you have multiple nullable columns you need to worry about you end up with a bunch of indexes. Hmmm. I hadn't thought of that, thanks. Yes, the indexes would be unnessecary though. Speaking of, should be concerned about indexing NULLABLE columns? I believe you're at least safe with btree indexes. The advantage of using the unique partial indexes is that it'll handle concurrent inserts without you having to worry about it. Also, is this in compliance with SQL92? I'm surprised constraints work this way. he I read that. I think you're right, it sounds like any comparison containing NULL at all will fail. I wrote the following procedure, which seems to do the trick. I guess my plan would be to write a bunch of these, and create the indexes manually. If anyone sees any problems with this, I'd love some input. Also, if anyone at the end of this email is a DBA/Consultant type and works in the San Diego area... Definitely let me know :) CREATE FUNCTION check_mytable_natkey() RETURNS opaque AS ' DECLARE conflictingpk integer; BEGIN SELECT INTO conflictingpk a FROM mytable WHERE ((b is null and NEW.b is null) or b = NEW.b) AND ((c is null and NEW.c is null) or c = NEW.c); Unfortunately, I don't think this will work if two sessions come in at the same time trying to insert the same values since they won't see each other's changes. I think it also will raise an error if the existing row has been deleted by a not yet committed transaction while our current implementation of unique constraints would wait to see if the transaction commits. On a side note, I believe (x is null and y is null) or x=y can be written a little more succintly with NOT(x IS DISTINCT FROM y). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question about update syntaxt
On Fri, 29 Apr 2005, Michael M Friedel wrote: I am trying to use an application (Through ODBC) that uses the following update syntax UPDATE MyTable SET MyTable.id=2 WHERE id=1 unfortunatly I get an error message ERROR: column mytable of relation mytable does not exist Question is, is ther something I can configure that will make Postgresql accept these kind of statments ? I don't believe there's a way to make the server itself accept that. IIRC, past discussions usually have bogged down into questions of whether to support the syntax since it doesn't appear to be valid SQL and if so what semantics to give cases where the tablename doesn't match. I'm not sure if there's any way to get the ODBC driver to do something about it, however. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Division in Postgre
On Sun, 24 Apr 2005, tuan wrote: In sql server my division select cast(3 as float)/10 is 0.299. But in postgres select cast(3 as float8)/10 is 0.3. How to get result like sql server? I believe you can control what precision is used in printing the float results with extra_float_digits. I believe setting it to 2 will give a 0.29... result however it has more 9s than the above. ---(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] multi-column unique constraints with nullable columns
On Fri, 29 Apr 2005, Tornroth, Phill wrote: I have many tables who's natural key includes a nullable column. In this cases it's a soft-delete or 'deprecated' date time. I'd like to add a table constraint enforcing this constraint without writing a custom procedure, but I've found that postgres treats NULLs very consistently with respect to the NULL != NULL behavior. As a result, when I define a constraint on the last two columns in these insert statements... they both succeed. insert into mytable values (1,300, null); insert into mytable values (1,300, null); This is frustrating, and while there may be someone who actually wants constraints to work this way... I can't understand why. Now, I understand that the best way to solve my problem would be to use only non-nullable columns for my natural keys. I actually plan to do that, and use a very high value for my 'undeprecated' date to solve most of my problems related to this. However, I can't release that version of software carelessly and I need to tighten up customer databases in the meantime. Is there a way to get the behavior I want? I believe you can add partial unique indexes to cover the case where a column is null, but if you have multiple nullable columns you need to worry about you end up with a bunch of indexes. Also, is this in compliance with SQL92? I'm surprised constraints work this way. As far as we can tell, this is explicitly what SQL wants to happen. The UNIQUE predicate (which the UNIQUE constraint is described in terms of) is defined as If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the corresponding column in the other row according to Subclause 8.2 ... then the result of the unique predicate is true; otherwise, the result of the unique predicate is false. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Money Data Type Problem
On Tue, 19 Apr 2005, sreejith s wrote: Hai friends, I have a field with data type 'Money' with my table. I select this field a select query and displays the same in a textbox. While doing this a dollar ($) is prefixed to the actual table value. How to avoid this symbol so as to display the actual value only. Any format change needed in config files. Pls forward me the reply. I believe money uses the currency symbol based on LC_MONETARY so there might exist one without a symbol, however you'd probably be best off replacing money with an appropriate numeric field. ---(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] Order of items in FROM causes error?
On Fri, 1 Apr 2005, Rosser Schwarz wrote: A colleague has the following query, which errors with: relation dl does not exist. (See the second item in the FROM clause.) If that item is moved to immediately precede the first JOIN item however, the query works as expected. select u.usersfirstname || ' ' || u.userslastname as userssupervisorsname , l.locationsname || ' ' || lv.listvaluesname as locationdepartment , lvcat.listvaluesname as usersemploymentcategory , lvclass.listvaluesname as usersemploymentclass , lvacdcat.listcategoriesname as usersacdcategory from intranet.tbl_users u , intranet.tbl_departmentslocations dl , intranet.tbl_listvalues lvcat , intranet.tbl_listvalues lvclass , intranet.tbl_listcategories lvacdcat join intranet.tbl_listvalues lv on dl.listvaluesid = lv.listvaluesid join intranet.tbl_locations l on dl.locationsid = l.locationsid where u.usersid = 199 and dl.listvaluesid = 13 and lvcat.listvaluesid = 23 and lvclass.listvaluesid = 27 and lvacdcat.listcategoriesid = 6 This strikes me as a bug. Is it known behavior? A quick search doesn't turn up much, but that may be a weakness in my google-fu. SQL seems to say that join binds more tightly than commas, so I don't believe dl is in scope for either of those ON clauses in the explicit join syntax. ---(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] Query performance problem
On Fri, 18 Mar 2005, Kenneth Gonsalves wrote: On Thursday 17 Mar 2005 7:35 pm, Richard Huxton wrote: Not necessarily. NOT NULL here helps to ensure you can add values together without the risk of a null result. There are plenty of amount columns that should be not-null (total spent, total ordered etc). that makes sense - but is it necessary to have a not null constraint when there is a default value? It's also an added check which prevents you from explicitly setting the value to NULL in an insert or update, since insert into foo(col1) values (NULL); shouldn't insert the default value into col1. This is relatively minor generally, but if you have queries whose behavior is broken by NULLs (things using IN/NOT IN for example) it's better to be safe. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] update with subselect (long)
On Thu, 17 Mar 2005, Leif B. Kristensen wrote: CREATE TABLE name_part_types ( -- a key/label pair name_part_type_id INTEGER PRIMARY KEY, name_part_type VARCHAR(50) ); CREATE TABLE names ( -- one person can have multiple names name_id INTEGER PRIMARY KEY, person_id INTEGER REFERENCES persons, is_primary BOOLEAN NOT NULL -- but one must be primary ); CREATE TABLE name_parts ( -- a name has multiple name-parts name_part_id INTEGER PRIMARY KEY, name_id INTEGER REFERENCES names, name_part_type INTEGER REFERENCES name_part_types, name_sequence INTEGER NOT NULL, -- internal sort order of name part name_part VARCHAR(100) ); My name_part_types table presently looks like this: slekta= select * from name_part_types; name_part_type_id | name_part_type ---+ 1 | prefix 2 | given 3 | surname 4 | suffix 5 | patronym 6 | toponym (6 rows) My current genealogy program, The Master Genealogist (TMG), stores names in the conventional pigeon-hole way, within the fields Prefix / Given / Surname / Suffix. This form is quite awkward regarding old Norwegian naming practice, and I have been using the Surname field mainly for recording patronyms, and the Suffix field for toponyms (ie. farm names). I've written a FoxPro to SQL conversion script (using Perl and the XBase module) to dump the data from the TMG database. A typical name_parts set may look like this: slekta= select * from name_parts where name_id = 1652; name_part_id | name_id | name_part_type | name_sequence |name_part --+-++---+-- 3643 |1652 | 2 | 0 | Christen 3644 |1652 | 5 | 1 | Jonsen 3645 |1652 | 6 | 2 | Stavdal (3 rows) Now I'm starting to approach my point. The values (2,3,4) in the name_part_type column should be changed to (2,5,6). As the Suffix field in the overwhelming majority of instances is used only if the name is on the Given / Patronym / Toponym form, I figure that it should be easy to change the name_part_type here. Initially, I ran this update: slekta= update name_parts set name_part_type=6 where name_part_type=3; So far, so good. But how do I change the name_part_type from 3 to 5 for the names with the same name_id that were altered by the previous command? This is my latest try: slekta= begin work; BEGIN slekta= update name_parts set name_part_type=5 slekta- from (select name_id where name_part_type=6) as gpt_type slekta- where name_id=gpt_type and name_part_type=3; ERROR: subquery in FROM may not refer to other relations of same query level The above needs some work. The below should be acceptable to the system. update name_parts set name_part_type=5 from (select name_id from name_parts where name_part_type=6) as gpt_type where name_parts.name_id=gpt_type.name_id and name_part_type=3; I'm a bit worried about blindly changing the type for anything that has a name_part_type=6 record, but given your usage that might be okay. ---(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] plpgsql date-time functions
On Tue, 8 Mar 2005, Fatih Cerit wrote: I have a function and I want to update a table's two rows but having problem with plpgsql date-time functions. First field of the table must be now() this is ok.. but the second field must be now() + '60 days' if the query like this : SELECT INTO to_day now() + interval '60 days' ; it works but if I use variable instead of '60 days' it doesn't work. And I must use variable If you want a variable number of days, something like now() + variable * interval '1 day' will probably work. ---(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] Simple delete takes hours
On Thu, 3 Mar 2005, Thomas Mueller wrote: Hi there, I have a simple database: CREATE TABLE pwd_description ( id SERIALNOT NULL UNIQUE PRIMARY KEY, name varchar(50) NOT NULL ); CREATE TABLE pwd_name ( id SERIALNOT NULL UNIQUE PRIMARY KEY, description integer NOT NULL REFERENCES pwd_description(id), name varchar(50) NOT NULL, added timestamp DEFAULT now() ); CREATE TABLE pwd_name_rev ( id SERIALNOT NULL UNIQUE PRIMARY KEY, description integer NOT NULL REFERENCES pwd_description(id), rev_of integer NOT NULL REFERENCES pwd_name(id) ON DELETE CASCADE, name varchar(50) NOT NULL ); The indexes shouldn't matter I think. pwd_name_rev is filled by a stored procedure and a trigger (ON INSERT) when something is inserted to pwd_name. Both tables contain about 4.500.000 emtries each. I stopped 'delete from pwd_name where description=1' after about 8 hours (!). The query should delete about 500.000 records. Then I tried 'delete from pwd_name_rev where description=1' - this took 23 seconds (!). Then I retried the delete on pwd_name but it's running for 6 hours now. I use PostgreSQL 7.4.7 on Linux 2.6.10. The machine is a Celeron 2 GHz with 512 MB RAM. PostgreSQL should do a full table scan I think, get all records with description=1 and remove them - I don't understand what's happening for 8 hours. It's going to remove rows in pwd_name_rev based on the rev_of not description (and you really should make sure to have an index on rev_of). Without being able to see triggers and rules on the tables, I can't tell if it's even legal to remove the rows with description=1 from pwd_name_rev, but it isn't with just the constraints defined above. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] what does ONLY do
On Fri, 18 Feb 2005, Bret Hughes wrote: I can't seem to find an explanation of what adding ONLY does for an sql statement for instance : ALTER TABLE [ONLY] ADD COLUMN ... or what ever. Does anyone have a pointer to docs on this. I am simply curious since there is obviously something I am missing. Using the ALTER TABLE page as an example... name The name (possibly schema-qualified) of an existing table to alter. If ONLY is specified, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are updated. * can be appended to the table name to indicate that descendant tables are to be altered, but in the current version, this is the default behavior. (In releases before 7.1, ONLY was the default behavior. The default can be altered by changing the configuration parameter sql_inheritance.) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [HACKERS] Function .. AS..?
On Wed, 9 Feb 2005, Ing. Jhon Carrillo wrote: Those instructions are good but i want to call this function only for select consulta_contacto(1) nothing more, Is really necesary to use AS ...? If it absolutely needs to be setof record, yes. It may be more appropriate to make a composite type with CREATE TYPE AS and then make the function return SETOF newtypename instead. ---(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
Re: [SQL] plpgsql functions and NULLs
On Sun, 30 Jan 2005, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; Hmm, perhaps some form like: WHERE not(candidate.omcr_id is distinct from omcr_id) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] plpgsql functions and NULLs
On Mon, 31 Jan 2005, Don Drake wrote: You learn something new everyday. I've never seen that syntax before, and it works like a charm!! Actually, now that I think about it, I wonder if that's a good thing to use because I don't think that'll use indexes to do the search. You may want to do some testing to see how it runs for you. On Mon, 31 Jan 2005 13:31:34 -0800 (PST), Stephan Szabo [EMAIL PROTECTED] wrote: On Sun, 30 Jan 2005, Don Drake wrote: OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; Hmm, perhaps some form like: WHERE not(candidate.omcr_id is distinct from omcr_id) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Foreign Key relationship between two databases
On Thu, 27 Jan 2005, Sandeep Gaikwad wrote: I can give foreign key relationship between two tables of same database. Can I give foreign key relationship between tables of two databases ? Plz, let me know if possible send me how can I do that? Unfortunately, that's not really currently possible. You might be able to fake some portion of it with custom triggers using dblink but I don't think the locking would entirely work. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
