Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Standards
On Wed, 6 Jun 2001, Mark Stosberg wrote: > > Hello, > > I'm a long time Postgres user who uses MySQL when I have to. I recently > ran into an issue with MySQL where this construct didn't do what I expect: > > WHERE date_column = NULL > > I expected it to work like "date_column IS NULL" like it does it > Postgres 7.0.2, but instead it returned an empty result set. > > After conversing with some folks on the MySQL list, it was mentioned that: > > * "NULL is *NOT* a value. It's an absence of a value, and doing *any* > comparisons with NULL is invalid (the result must always be NULL, even > if you say "foo = NULL")." > > * Postgres handling is non-standard (even if it's intuitive.) > > My questions then are: 1.) What IS the standard for handling NULLs? and > then 2.) If Postgres handling is different than the standard, what's the > reason? > > To me, having " = NULL" be the same as " IS NULL" is intuitive and thus > useful, but I also like appeal of using standards when possible. :) Yes, column = NULL should *never* return true according to the spec (it should always return NULL in fact as stated). The reason for breaking with the spec is AFAIK to work with broken microsoft clients that seem to think that =NULL is a meaningful test and generate queries using that. In general, =NULL should be avoided in favor of IS NULL by users that are generating their own queries. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] behavior of ' = NULL' vs. MySQL vs. Standards
Hello, I'm a long time Postgres user who uses MySQL when I have to. I recently ran into an issue with MySQL where this construct didn't do what I expect: WHERE date_column = NULL I expected it to work like "date_column IS NULL" like it does it Postgres 7.0.2, but instead it returned an empty result set. After conversing with some folks on the MySQL list, it was mentioned that: * "NULL is *NOT* a value. It's an absence of a value, and doing *any* comparisons with NULL is invalid (the result must always be NULL, even if you say "foo = NULL")." * Postgres handling is non-standard (even if it's intuitive.) My questions then are: 1.) What IS the standard for handling NULLs? and then 2.) If Postgres handling is different than the standard, what's the reason? To me, having " = NULL" be the same as " IS NULL" is intuitive and thus useful, but I also like appeal of using standards when possible. :) Thanks! -mark http://mark.stosberg.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Functions and Triggers
Title: Functions and Triggers I need help on creating a sql function that will check four rows in a table based on that check it will update another row on a different table. I want to use this function in a trigger statement. Table 1 (Test Type) id cat sub-cat test status timestamp Table 2 (Record) timestamp card comp_date status What I am trying to do is when someone enters data in on table 2 the trigger will verifty the record and then look at all records with the same id and card type, then it will determine the status of the test, or in other words it will change the value of status in table 1. Thanks, RAY HUNTER Automated Test Group ENTERASYS NETWORKS E-mail: [EMAIL PROTECTED] www.enterasys.com
[SQL] How to create a *pass-through-query* in postgresql
Hello all, I use postgresql as backend and MsAccess97 as frontend via ODBC. I know, how to use VBA and pass through queries to update or insert rows in postgresql-tables. But I don't know, how to do this the other way round, from postgresql to MsAccess. I want to develop a trigger, which updates or inserts rows in some local tables in MsAccess on the client side. How can I realize a *pass through query* in postgresql ( with plpgsql ?) to be executed in MsAccess? Is there a possibility, to send updates or inserts from postgresql-tables to local MsAccess-tables (without linked tables) I have the following situation: 1 postgresql database and some clients on different locations, connected via internet I want to hold some information realy local (no linked table) to the clients and to the host, because the connection is not very fast when client_1 changes a special table in the postgresql DB, this should be transmitted to a local MsAccess-table to client_2 Can I solve this problem with plpgsql Please, can anyone help me to solve this problem. Thanks in advance Irina E-Mail: [EMAIL PROTECTED]
Re: [SQL] Cascade constraint gone!!!
On Wed, 6 Jun 2001, Linh Luong wrote: > I recently wipe my database away. And some of the tables have a UPDATE > CASCADE on it to another table. > When I reload the data from my backup that I did with pg_dumpall. My > code started to give me an error because I tried to modify a table that > was reference from another table. But the referenced table should have > an UPDATE CASCADE. But according to the error it doesn't. Hmm, what does the create constraint triggers in the dump say? And what is in pg_trigger for the constraint? > Is there a way to add the ON UPDATE CASCADE back without dropping any > table again? Well, you can manually delete the constraint triggers for this constraint and use alter table add constraint to add the constraint again with the appropriate actions. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Cascade constraint gone!!!
Hi, I recently wipe my database away. And some of the tables have a UPDATE CASCADE on it to another table. When I reload the data from my backup that I did with pg_dumpall. My code started to give me an error because I tried to modify a table that was reference from another table. But the referenced table should have an UPDATE CASCADE. But according to the error it doesn't. Is there a way to add the ON UPDATE CASCADE back without dropping any table again? Thanks -- Linh Luong ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Tutorial : using foreign keys, retrictions etc
On Sat, Jun 02, 2001 at 06:49:55PM -0400, Mario Bittencourt wrote: > Hi, > > I've decided to switch from mysql to postgresql as my database server. Good for you :) > procedures, views) and I'd like to know if there is some tutorial/snippet > of code regarding such features specially the use of foreign keys and > referetial integrity. You might want to look at http://techdocs.postgresql.org. IIRC, a script to "port" MySQL code to PostgreSQL has been included in the contrib section of 7.1.x. For examples of functions, head to http://www.brasileiro.net/postgres -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Bad command or file name. Go sit in corner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [SQL] Distinct Values
> Author URL > --- > A http://www.xyz http://www.abc .com > - - > B http://www.def http://www.ijk http://www.rst .com > - - > > If that is what you want, you cannot manage it. Sure, in no way this is possible with SQL (in terms of relational DBMS). However, many people find dup'ed values on the `left pane' annoying and that's why report tools exist :) Actually, getting rid of dup's is fairly simple in this exact case if query output is run through a text processing script in PERL or awk. -- ÌĤ¯Ç¤ÏÁͤòÊá¤é¤Ì ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] php-nuke
On Wed, May 30, 2001 at 11:14:38PM +0530, Sharmad Naik wrote: > hi, > I wanted to know that does postgresql database support php-nuke This is a php-nuke question. Head to their website and it should be stated there somewhere. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer I wonder what this button does? *&^(&^)#@$*&_% NO CARRIER ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] audit trail and system catalogs
On Wed, 6 Jun 2001, Markus Wagner wrote:
> The problem is, how to get the names of all tables and their attributes? I
> looked into the system tables ("pg_*"), but there were many tables and
> many attributes for *my* tables, and I did not figure out how to
> distinguish my tables and my attributes from the other ones. None of the
> columns in pg_class and pg_attribute seems to give information on wether
> the item is system or user defined.
IIRC, attnum<0 means it's a system column. So you only want the ones with
positive attnum values.
---(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] Tutorial : using foreign keys, retrictions etc
On Sat, 2 Jun 2001, Mario Bittencourt wrote: > Hi, > > I've decided to switch from mysql to postgresql as my database server. I > do mostly web programming (who doesnt these days?) with more than a 100 > sites developed (using mysql). > > I've read about postgresql and it's features (such as triggers, stored > procedures, views) and I'd like to know if there is some tutorial/snippet > of code regarding such features specially the use of foreign keys and > referetial integrity. > > To give a more concrete example the next app I'd like to use postgresql > needs this behaviour : > > table A > id, name > > table B > id, foo > > table C > id, date > > If I change id in table A update table B to change it too (I think it's > called cascade). In this case it's pretty easy, table B needs a foreign key defined like: FOREIGN KEY (id) REFERENCES A(id) ON UPDATE CASCADE (you may want ON DELETE CASCADE as well, it depends). I believe someone had written a document on FK, but I'm not entirely sure what happened to it or whether it ended up in the docs. For examples without much explanation you can look at the foreign key regression test, but that's not alot of help. ---(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] "Day" from 8am to 3am
On Wed, 6 Jun 2001, Tom Lane wrote: > Judd Maltin <[EMAIL PROTECTED]> writes: > > I have a special application that considers a playday to be from 8am to > > 3pm the next morning. The system then does maintenance from 3am till 8am > > (if necessary). > > 8am is in the eye of the beholder. Why not run the system in a timezone > selected such that a playday starts at local midnight? > > regards, tom lane Isn't changing the system for the application breaking a cardinal rule of systems design? I need the real time of day anyway.. I just want to make SELECTs easier. -judd ---(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] seleting all dates between two dates
Jeff, > I am interested in a query where I can select all dates between two > dates. I > figure I can build a table of all valid dates with a resonable range > and > then select from that table, but I would like to use the power of sql > to get > the work done without building a date table. Any ideas? > > For example: > I want all dates between 05-29-2001 and 06-02-2001 > The result set would be: > 05-30-2001 > 05-31-2001 > 06-01-2001 If you browse last week's postings, you will find a thread called "SQL Date Challenge" with that very issue. The answer, in short, is that you can't do it in SQL. Your choices are to build the reference table, or to use and external procedural language (such as Perl). -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] audit trail and system catalogs
Hi,
we strongly need to implement an audit trail as a prerequisite for
clinical trials, that is a functionality which records any change of any
data item in a database into one single table containing these events.
I found that one could use rules for this, but this would require one rule
for each attribute of each table ("...ON UPDATE ... WHERE old.attr <>
new.attr...").
My first question:
Could one reduce the rule set with rules for tables, e. g. one rule which
fires whenever *some* attribute of a table changes? Would there be a
possibility to reference the changed attributes in the action clause?
Assuming that one really needs one rule for each attribute I would like to
create a script which generates all rules for all attributes of all tables
for a database.
The problem is, how to get the names of all tables and their attributes? I
looked into the system tables ("pg_*"), but there were many tables and
many attributes for *my* tables, and I did not figure out how to
distinguish my tables and my attributes from the other ones. None of the
columns in pg_class and pg_attribute seems to give information on wether
the item is system or user defined.
My second question:
How can I loop to all of *my* tables and *my* attributes, ignoring system
tables and system generated attributes within my tables?
Thank you very much for any hint,
Markus
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "Day" from 8am to 3am
Judd Maltin <[EMAIL PROTECTED]> writes: > I have a special application that considers a playday to be from 8am to > 3pm the next morning. The system then does maintenance from 3am till 8am > (if necessary). 8am is in the eye of the beholder. Why not run the system in a timezone selected such that a playday starts at local midnight? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Large Objects - lo_export
You must be logged in as the unix user postgres.
(Presuming /home/postgres/ is writable by postgres and
the file doesn't exist.)
Try doing
touch /home/postgres/junk.tgz
from the command line. It should also fail.
Troy
>
>
> Hi to list !
>
> while running psql as 'postgres' user, i executed the following query.
>
> 'select lo_export(img.image, '/home/postgres/junk.tgz') from img where
> data = 'tar file';
>
> The result of the above command is as follows.
>
> ERROR: lo_export: can't open unix file
> "/home/postgres/junk.tgz": Permission denied
>
> i performed the above query as superuser ('postgres'). I hope it' having
> all the superuser previleges.
>
> I am not sure why i am getting the above error.
>
> Any one got any clue for it.
> thanx in advance.
>
> guru.
> bk SYSTEMS (P) Ltd.
> P . N . G U R U P R A S A D
> ---
>
>
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Tutorial : using foreign keys, retrictions etc
Hi, I've decided to switch from mysql to postgresql as my database server. I do mostly web programming (who doesnt these days?) with more than a 100 sites developed (using mysql). I've read about postgresql and it's features (such as triggers, stored procedures, views) and I'd like to know if there is some tutorial/snippet of code regarding such features specially the use of foreign keys and referetial integrity. To give a more concrete example the next app I'd like to use postgresql needs this behaviour : table A id, name table B id, foo table C id, date If I change id in table A update table B to change it too (I think it's called cascade). Thanks. - RM ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Foreign Keys and Inheritance
If I have table A, which contains a primary key, and table B which inherits from A. How can I Create a Foreign Key on table C that references A, but will also pass if a record in inserted in to B. for instance: create table A ( prim_key char(20) not null primary key ); create table B ( ) INHERITS A; create table C ( data char(2) not null primary key, constraint fk_C FOREIGN KEY ( data ) REFERENCES A ( prim_key ) ON DELETE CASCADE ON UPDATE CASCADE ) With this setup, my record must be inserted in to A or I'll get a referential integrity problem when I insert in to C. I want to be able to insert in to A, B , or another table inherited from A, and have table C recognize that as it's foreign key. Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] seleting all dates between two dates
I am interested in a query where I can select all dates between two dates. I figure I can build a table of all valid dates with a resonable range and then select from that table, but I would like to use the power of sql to get the work done without building a date table. Any ideas? For example: I want all dates between 05-29-2001 and 06-02-2001 The result set would be: 05-30-2001 05-31-2001 06-01-2001 Thanks for the help. -Jeff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] help with a function
consider this function: CREATE FUNCTION "test" (integer) RETURNS text AS 'return "select * from testt limit $_[o];";' LANGUAGE 'plperl'; how would i get it to just run the query instead of just return it as text using plperl? ---(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] How to use the type int2vector ?
David BOURIAUD wrote: > > Hi the list ! > How can I make a test on a int2vector ? If I have a vector that contains > (1, 2, 5, 9), how can I check these values incivicually ? Thanks by > advance for your help. I answer myself by posting another question... Is there a way to know from the system tables the keys of a table ? If there is only one key, it is not hard to know, but when there are more than one, how can I do ? Thanks by advance. -- David BOURIAUD -- In a world without walls or fences, what use do we have for windows or gates ? -- ICQ#102562021 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
