[SQL] difference between EXCEPT and NOT IN?
Hi, The 2 following statements don't give the same result. I expected the second ti give the exact same result as the first one. What am I missing? development=> SELECT id FROM entrees except select entree_id from postes ORDER BY id desc; id -- 3651 (1 row) development=> SELECT id FROM entrees WHERE id not in (select entree_id from postes) ORDER BY id desc; id (0 rows) thanks in advance for the help. Raph -- Web database: http://www.myowndb.com Free Software Developers Meeting: http://www.fosdem.org -- 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, Apr 1, 2008 at 6:04 PM, Stephan Szabo <[EMAIL PROTECTED]> wrote: > 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. > My problem came from 2 entries in the table postes that had an entree_id NULL Thanks for your fast answer, it has helped me spot the problem! Raph -- Web database: http://www.myowndb.com Free Software Developers Meeting: http://www.fosdem.org -- 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] Trigger Procedure Error: NEW used in query that is not in a rule
HI,
On Sat, Aug 11, 2007 at 9:07 PM, hubert depesz lubaczewski
<[EMAIL PROTECTED]> wrote:
> On Sat, Aug 11, 2007 at 02:45:09AM -0500, Javier Fonseca V. wrote:
>> I think that it's working alright except for the next line:
>
> doing this in plpgsql is very complicated (or even impossible assuming
> that any table can have the same trigger). i would rather suggest using
> pl/perl - writing something like this in pl/perl is very simple.
>
I am in the same situation where I would like to execute a query similar to
EXECUTE 'INSERT INTO ' || quote_ident(SOMEDYNAMICNTABLENAME) || '
SELECT new.*';
I've looked at the plperl documentation, and experimented a bit, but
I'm not even sure how to start this in pl/perl. I hoped to extract
columns from $_TD->{new} but it doesn't seem to work.
Would you have a little example on how you would do it?
Thanks in advance!
Raph
> depesz
>
> --
> quicksil1er: "postgres is excellent, but like any DB it requires a
> highly paid DBA. here's my CV!" :)
> http://www.depesz.com/ - blog dla ciebie (i moje CV)
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
--
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] Trigger Procedure Error: NEW used in query that is not in a rule
On Wed, Sep 17, 2008 at 5:45 PM, hubert depesz lubaczewski
<[EMAIL PROTECTED]> wrote:
> On Wed, Sep 17, 2008 at 05:08:39PM +0200, Raphael Bauduin wrote:
>> Would you have a little example on how you would do it?
>
> show us what you have done - it will be easier to find/fix/explain than
> to write code for you.
Well, I experimented a lot but didn't come to any useful result.
Actually I'm working on table partitioning as described at
http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html , and
I wanted to write a trigger that would insert the data in the correct
table, and so I got the same problem with plpsql's NEW.* not usable in
a dynamically created query to be run by EXECUTE:
CREATE OR REPLACE FUNCTION part_test() RETURNS TRIGGER AS $$
DECLARE
current_time timestamp := now();
suffix text := date_part('month', now())||'_'||date_part('day', now()) ;
BEGIN
RAISE NOTICE '%', suffix;
execute 'insert into t1_'||suffix||' values( NEW.* )';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
I searched the archives here and after reading your previous mail in
this thread, I started to look at plperl, with which I have no
experience at all.
As $_TD{new}{column} gives the value of field column, I thought to
extract all columns from keys($_TD{new}), but it doesn't seem to see
$_TD{new} as a hash:
Type of arg 1 to keys must be hash (not hash element)
And that's where I'm at now.
Raph
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
> jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] inserting boolean values in dynamic queries
Hi, I'm building a dynamic query as described at http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN : EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue); It works fine, except when I want to include a boolean value: the cast of newvalue from boolean to text causes problem. I can change my code and work with 't' and 'f' values as text, but wondered if there wasa way to use boolean values in a dynamically generated query. Thanks Raphaël -- Web database: http://www.myowndb.com Free Software Developers Meeting: http://www.fosdem.org -- 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] inserting boolean values in dynamic queries
On Thu, Sep 18, 2008 at 2:35 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Raphael Bauduin" <[EMAIL PROTECTED]> writes:
>> EXECUTE 'UPDATE tbl SET '
>> || quote_ident(colname)
>> || ' = '
>> || quote_literal(newvalue)
>> || ' WHERE key = '
>> || quote_literal(keyvalue);
>
>> It works fine, except when I want to include a boolean value: the cast
>> of newvalue from boolean to text causes problem.
>
> What problem? 'true' and 'false' are accepted as input for boolean
> AFAICS.
>
yes, but I have problems to use them to build the query passed to execute.
For example, working on this table
create table test(b_val bool);
I want to create a function that I cal call as
select test_bool(true)
and that will insert an entry in this test table.
Below are several attemps, all unsuccessful. The way I've made it work
it by accepting a char as input, t or f:
create or replace function test_bool(val char(1)) returns void as $$
create or replace function test_bool(val bool) returns void as $$
BEGIN
RAISE INFO 'insert into test(b_val) values (''%'')', val;
execute 'insert into test (b_val) values ('|| val || ')';
END
$$
language plpgsql;
--> ERROR: array value must start with "{" or dimension information
create or replace function test_bool(val bool) returns void as $$
BEGIN
RAISE INFO 'insert into test(b_val) values (''%'')', val;
execute 'insert into test (b_val) values ('|| val::text || ')';
END
$$
language plpgsql;
--> ERROR: cannot cast type boolean to text
create or replace function test_bool(val bool) returns void as $$
BEGIN
RAISE INFO 'insert into test(b_val) values (''%'')', val;
execute 'insert into test (b_val) values ('|| quote_literal(val) || ')';
END
$$
language plpgsql;
--> ERROR: function quote_literal(boolean) does not exist
I guess I'm missing something
Thanks.
Raphaël
>regards, tom lane
>
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] exists and is not null equivalence in query
Hi, In some code I am taking over, I found this query: select count(t.trame_id) as count, v.voiture_num as voitureNum from arch_trames t left join voiture v on (v.tag_id=t.tag_id) where (t.recept_time >= 1243509320691) and exists (select v2.voiture_num from voiture v2 where v2.tag_id=v.tag_id) group by v.voiture_num order by v.voiture_num Am I right that I can replace the "and exists..." clause by "and v.voiture_num is not null " in this case? Thanks Raphaël -- Web database: http://www.myowndb.com Free Software Developers Meeting: http://www.fosdem.org -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
