[SQL] Boolean without default declared
If a column with a boolean datatype doesn't have a default value. What type of value is set if nothing is inserted into that column? Here is my test table and the queries I have tried. I can't seem to be able to select the rows where happy has no value. Table "public.users" Column | Type | Modifiers +---+--- id | character varying(32) | email | text | happy | boolean | money | numeric | *select * from users;* id | email | happy | money ++---+- 4 | me | | 1324.23 4 | me | | 1324.23 3 | as | | 123.2 1 | afjssd | t | *select * from users where happy;* id | email | happy | money ++---+--- 1 | afjssd | t | *select * from users where not happy;* id | email | happy | money +---+---+--- (0 rows) *select * from users where happy = NULL;* id | email | happy | money +---+---+--- (0 rows) *select * from users where happy = '';* ERROR: invalid input syntax for type boolean: "" ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Make a SQL statement not run trigger
Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? For example could I set a value DONOTRUN = True; insert into contacts Where the trigger on contacts would call a function that would have an IF statment for that DONOTRUN value? Or is there just a global variable I could set to disable triggers and then reset it? And would that be a per connection variable? Thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Make a SQL statement not run trigger
Thanks for the reply. It looks like this will disable the trigger for all connections to the database. So if the chance occured that another connection was using the table at the same time it wouldn't launch the trigger either? am I wrong? I hope so ;) Rodrigo De León wrote: On 8/21/07, Jon Collette <[EMAIL PROTECTED]> wrote: Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? alter table disable trigger ... http://www.postgresql.org/docs/8.2/static/sql-altertable.html ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Make a SQL statement not run trigger
Good Idea. However some of my triggers use the DELETE action as well. So I can't use this method. Thomas Kellerer wrote: Jon Collette wrote on 21.08.2007 23:26: Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? For example could I set a value DONOTRUN = True; insert into contacts Where the trigger on contacts would call a function that would have an IF statment for that DONOTRUN value? Or is there just a global variable I could set to disable triggers and then reset it? And would that be a per connection variable? What we have done once, was to include a column in the table for this purpose. If a special value for the column was provided during UPDATE or INSERT, the trigger would immediately terminate, not doing any work. Thus the trigger still "fires" every time, but simply won't do nothing. Not very elegant, but worked for our environment. Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Make a SQL statement not run trigger
I think this will work for what I need. I have been messing around with this using select into /select True as donothing into temporary table table_trigger_name; then run statement that I want to be ignored / The trigger would have a select upon the table_trigger_name to determine if it should run or not. I am having issues catching the exception when the table is not found. This is my test function of my trigger /Create or replace function trigger_test() returns boolean as $$ declare donothing boolean; begin donothing := False; select donothing into donothing from table_trigger_name; return boolean; end; $$ LANGUAGE plpgsql; / Which of course will error when there is no table_trigger_name made for that session. I couldn't find an exception in the exceptions list for table not found errors. Scott Marlowe wrote: On 8/21/07, Jon Collette <[EMAIL PROTECTED]> wrote: Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? For example could I set a value DONOTRUN = True; insert into contacts The closest thing to a session variable for pgsql is going to likely be a temp table. you could have a temp table for each session that stores such things and the trigger looks in the temp table to see what to do. Or is there just a global variable I could set to disable triggers and then reset it? And would that be a per connection variable? Yes, but that would open you up to race conditions. If another session wanted the trigger to fire it would not get it. Using a temp table would allow you to get around the race condition. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
