[SQL] Using functions in SQL statements
I would dearly love to do CREATE GROUP foo WITH USER CURRENT_USER; in a script to psql(1), but this does not appear to be supported by the parser. Two questions: 1. Does anybody have a good work-around for this? 2. Is there a document somewhere that says where functions are allowed in SQL statements? --- Allan. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Creating foreign key constraint to child table?
I would like to create a FOREIGN KEY constraint to an inherited column, like: test=# CREATE TABLE foo(id INTEGER PRIMARY KEY); test=# CREATE TABLE bar() INHERITS (foo); test=# CREATE TABLE baz (bar INTEGER, CONSTRAINT fk_bar FOREIGN KEY (bar) REFERENCES bar(id)); ERROR: UNIQUE constraint matching given keys for referenced table "bar" not found This obvioulsy doesn't work. I *can* create a FOREIGN KEY contraint to the parent table: test=# create table baz(bar integer, constraint fk_bar foreign key (bar) references foo(id)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE but this is not exactly what I want: I need to ensure that baz.bar is a bar and not just any foo. Do I need to write my own INSERT/UPDATE triggers on baz to check the tableoid, or is there a nice way to do this? Any examples on how to do this? In particular, do I need to do a SELECT on pg_class for every INSERT / UPDATE in baz, just to get the tableoid for bar ? There *is* an index on pg_class.relname but still... --- Allan. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Creating foreign key constraint to child table?
I obviously haven't had enough coffee yet... :-) The following script works as expected. drop database test; create database test; \c test create table foo (id integer primary key); create table bar () inherits (foo); create unique index bar_id_idx ON bar(id); create table baz (bar integer, constraint fk_bar foreign key (bar) references bar(id)); insert into foo values (1); insert into bar values (2); insert into baz values (2); insert into baz values (1); -- fails Sorry. --- Allan. I wrote: > I would like to create a FOREIGN KEY constraint to an inherited column, like: > > test=# CREATE TABLE foo(id INTEGER PRIMARY KEY); > test=# CREATE TABLE bar() INHERITS (foo); > test=# CREATE TABLE baz (bar INTEGER, CONSTRAINT fk_bar FOREIGN KEY (bar) >REFERENCES bar(id)); > ERROR: UNIQUE constraint matching given keys for referenced table "bar" not >found > > This obvioulsy doesn't work. I *can* create a FOREIGN KEY contraint to the parent >table: > > test=# create table baz(bar integer, constraint fk_bar foreign key (bar) >references foo(id)); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > CREATE > > but this is not exactly what I want: I need to ensure that baz.bar is a bar and not >just any foo. > > Do I need to write my own INSERT/UPDATE triggers on baz to check the tableoid, or is >there a nice way to do this? > > Any examples on how to do this? In particular, do I need to do a SELECT on pg_class >for every INSERT / UPDATE in baz, just to get the tableoid for bar ? There *is* an >index on pg_class.relname but still... > > --- Allan. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Creating foreign key constraint to child table?
On Sun, 5 Aug 2001, Allan Engelhardt wrote: > test=# create table baz(bar integer, constraint fk_bar foreign key (bar) >references foo(id)); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > CREATE > > but this is not exactly what I want: I need to ensure that baz.bar is > a bar and not just any foo. > Not that this is related to what you asked about precisely (I saw the response you made), but the query above also doesn't do what you think it does right now. It currently makes a reference to only foo, not any subchildren of foo. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: Creating foreign key constraint to child table?
Stephan Szabo wrote: > On Sun, 5 Aug 2001, Allan Engelhardt wrote: [see new example below] > Not that this is related to what you asked about precisely (I saw the > response you made), but the query above also doesn't do what you think > it does right now. It currently makes a reference to only foo, not > any subchildren of foo. Oh, man! You are right, but this sux big time: there should not be an asymmetry between a FOREIGN KEY constraint and the SELECT statement. Now that the default is SQL_INHERITANCE ON, it should be the default for the constraint as well, IMHO. 1. Am I the only one who thinks this is a bug? 2. How would I get the behaviour I expect? Write my own trigger? :-P --- Allan. test=# create table foo (id integer primary key); test=# create table bar () inherits (foo); test=# create table baz (bar integer, constraint fk_bar foreign key (bar) references foo(id)); test=# insert into foo values (1); test=# insert into bar values (2); test=# insert into baz values (2); ERROR: fk_bar referential integrity violation - key referenced from baz not found in foo test=# select * from foo where id = 2; id 2 (1 row) test=# ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Creating foreign key constraint to child table?
05 Aug 2001 13:49:22 +0100, Allan Engelhardt > I would like to create a FOREIGN KEY constraint to an inherited column, like: > > test=# CREATE TABLE foo(id INTEGER PRIMARY KEY); > test=# CREATE TABLE bar() INHERITS (foo); > test=# CREATE TABLE baz (bar INTEGER, CONSTRAINT fk_bar FOREIGN KEY (bar) >REFERENCES bar(id)); > ERROR: UNIQUE constraint matching given keys for referenced table "bar" not >found > > This obvioulsy doesn't work. I *can* create a FOREIGN KEY contraint to the parent >table: > > test=# create table baz(bar integer, constraint fk_bar foreign key (bar) >references foo(id)); > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > CREATE > > but this is not exactly what I want: I need to ensure that baz.bar is a bar and not >just any foo. > > Do I need to write my own INSERT/UPDATE triggers on baz to check the tableoid, or is >there a nice way to do this? > > Any examples on how to do this? In particular, do I need to do a SELECT on pg_class >for every INSERT / UPDATE in baz, just to get the tableoid for bar ? There *is* an >index on pg_class.relname but still... > Now child table does not inherit constraints from parent. More of that, you can violate primary key in parent by inserting duplicate id in child. So inheritance is broken in current version. You should develop scheme without inheritance. Regards, Dmitry ---(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
[SQL] Re: Date Time Functions - ANSI SQL ?
Gonzo Rock writes: > I'm trying to find a pgSQL source that documents the Non-Standard > pgSQL stuff, the stuff that will break when attempting to execute > against mySQL/Oracle/MSSQLServer etc... While it would be an appreciated effort to create such a document (and we already try to document standards-compliance in other places), it would be of less practical use than it might seem at first. Most SQL database packages implement all kinds of functions that are found nowhere near the SQL standard. PostgreSQL has copied a number of these and added aliases and near-aliases in a number of other cases. We don't all program in ANSI C or C++ these days either. What is portable is a bit of a matter of experience and research, modulo good code organization so you can replace the unportable parts easily. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Fuzzy matching?
Josh Berkus writes: > For many of my programs, it would be extremely useful to have some form > of "fuzzy matching" for VARCHAR fields. For lexical similarity, check out the agrep algorithm. Last I checked the source code wasn't quite Free(tm), but the algorithm was published in an academic work. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Inheritance is completely broken [was: Re: Creating foreign key constraint to child table?]
Dimitri pointed out (the post does not seem to have appered yet) that you can also do: test=# create table foo(id integer primary key); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE test=# create table bar () inherits (foo); CREATE test=# insert into foo values (1); INSERT 12734236 1 test=# insert into foo values (1); ERROR: Cannot insert a duplicate key into unique index foo_pkey test=# insert into bar values (1); INSERT 12734238 1 test=# select * from foo; id 1 1 (2 rows) So inheritance does seem to be completely broken. There is also an entry in the TODO list Allow inherited tables to inherit index, UNIQUE constraint, and primary key, foreign key [inheritance] which seems to be related. It doesn't have a dash, so I guess I won't hold my breath Now I'm sad. Allan. Allan Engelhardt wrote: > Stephan Szabo wrote: > > > On Sun, 5 Aug 2001, Allan Engelhardt wrote: > > [see new example below] > > > Not that this is related to what you asked about precisely (I saw the > > response you made), but the query above also doesn't do what you think > > it does right now. It currently makes a reference to only foo, not > > any subchildren of foo. > > Oh, man! You are right, but this sux big time: there should not be an asymmetry >between a FOREIGN KEY constraint and the SELECT statement. Now that the default is >SQL_INHERITANCE ON, it should be the default for the constraint as well, IMHO. > > 1. Am I the only one who thinks this is a bug? > > 2. How would I get the behaviour I expect? Write my own trigger? :-P > > --- Allan. > > test=# create table foo (id integer primary key); > test=# create table bar () inherits (foo); > test=# create table baz (bar integer, >constraint fk_bar foreign key (bar) references foo(id)); > test=# insert into foo values (1); > test=# insert into bar values (2); > test=# insert into baz values (2); > ERROR: fk_bar referential integrity violation - key referenced from baz not found >in foo > test=# select * from foo where id = 2; > id > > 2 > (1 row) > > test=# ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Data type confusion
Tom, Stephan, I'm writing up the date/time FAQ, and I came across some operator behavior that confuses me: If INTERVAL / INTEGER = INTERVAL then why does INTERVAL / INTERVAL = ERROR? Shouldn't INTERVAL / INTERVAL = INTEGER? I'd like to answer this before I finish the FAQ, as it seems inconsistent behavior. -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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Re: Date Time Functions - ANSI SQL ?
Gonzo, > > I'm trying to find a pgSQL source that documents the Non-Standard > > pgSQL stuff, the stuff that will break when attempting to execute > > against mySQL/Oracle/MSSQLServer etc... Almost anything you port will break MS SQL Server (7.0 and 6.5, anyway). SQL Server is so far off the ANSI standard for Dates & Times that no application may be ported from another server which relies on dates and time manipulation. FYI. -Josh Berkus __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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Re: Data type confusion
Josh Berkus wrote: > This is a multi-part MIME message > > --_===97089davinci.ethosmedia.com===_ > Content-Type: text/plain; charset="ISO-8859-1" > Content-Transfer-Encoding: 8bit > > Tom, Stephan, > > I'm writing up the date/time FAQ, and I came across some operator > behavior that confuses me: > > If > INTERVAL / INTEGER = INTERVAL > > then why does > INTERVAL / INTERVAL = ERROR? > > Shouldn't > INTERVAL / INTERVAL = INTEGER? > > I'd like to answer this before I finish the FAQ, as it seems > inconsistent behavior. > > -Josh > > Josh, I'm not Tom or Stephan (sorry) but in your scenario what would be the result of, say, dividing '3 months ago' with '6 seconds' in the future? I don't think it makes conceptual sense to divide intervals Allan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Data type confusion
"Josh Berkus" <[EMAIL PROTECTED]> writes: > If > INTERVAL / INTEGER = INTERVAL Actually the operator appears to be INTERVAL / FLOAT8. > then why does > INTERVAL / INTERVAL = ERROR? Because no one got around to creating an INTERVAL / INTERVAL operator. There are plenty of such gaps in our operator set... > Shouldn't > INTERVAL / INTERVAL = INTEGER? I'd think the output should be FLOAT8, myself, since the result could be fractional. Anyway, the generic response to such questions is "feel free to code it up and submit a patch". regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Data type confusion
Tom, > > then why does > > INTERVAL / INTERVAL = ERROR? > > Because no one got around to creating an INTERVAL / INTERVAL > operator. > There are plenty of such gaps in our operator set... Bummer. If I could "C" then maybe I'd do something about it. > > > Shouldn't > > INTERVAL / INTERVAL = INTEGER? > > I'd think the output should be FLOAT8, myself, since the result > could be fractional. > > Anyway, the generic response to such questions is "feel free to > code it up and submit a patch". Sorry. :( I'm stricly a "high-level user". I can, however, document it so that others won't pester you for questions about why it doesn't work. -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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: Data type confusion
Allan Engelhardt <[EMAIL PROTECTED]> writes: > I don't think it makes conceptual sense to divide intervals It is kinda bogus, given the underlying semantics of intervals (integer months plus float seconds). The problem already arises for the existing interval * float8 and interval / float8 operators, though, so it'd be easy enough to make an interval / interval operator that is consistent with them. What those operators do is to convert any fractional-month result into seconds at an arbitrary conversion factor of 30 days to the month. For example, consider regression=# select '5 months 9 days'::interval; ?column? --- 5 mons 9 days (1 row) regression=# select '5 months 9 days'::interval * 0.5; ?column? -- 2 mons 19 days 12:00 (1 row) The initial product is effectively 2.5 months plus 4.5 days, and then we translate the .5 months into 15 days. This is pretty grotty, and AFAIK not documented anywhere --- I found it out by looking at the C code for these operators. But I'm not sure how to do better. 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] Re: Data type confusion
Tom Lane writes: > It is kinda bogus, given the underlying semantics of intervals > (integer months plus float seconds). > This is pretty grotty, and AFAIK not documented anywhere --- I found it > out by looking at the C code for these operators. But I'm not sure > how to do better. One day we will have to accept the fact that months and seconds must not be mixed, period. You can have year/month intervals or day/hour/minute/second intervals, not a combination. An interval of '5 years 3 minutes' has no meaning with the natural calendar rules. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Re: Data type confusion
Peter Eisentraut <[EMAIL PROTECTED]> writes: > One day we will have to accept the fact that months and seconds must not > be mixed, period. You can have year/month intervals or > day/hour/minute/second intervals, not a combination. An interval of '5 > years 3 minutes' has no meaning with the natural calendar rules. I don't agree --- five years and three minutes is perfectly meaningful. There are only certain things you can validly do with it, however, and scaling by a floating-point number isn't one of them, because fractional months aren't well-defined. But you can, for example, add it to or subtract it from a timestamp to produce a well-defined result timestamp. The real bogosity in the interval type is that months and seconds are not sufficient: it should be months, days, and seconds. As we get reminded twice a year by the regression tests, "1 day" and "24 hours" are not the same thing. regards, tom lane ---(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] Re: Data type confusion
Tom Lane writes: > I don't agree --- five years and three minutes is perfectly meaningful. > There are only certain things you can validly do with it, however, and > scaling by a floating-point number isn't one of them, because fractional > months aren't well-defined. But you can, for example, add it to or > subtract it from a timestamp to produce a well-defined result timestamp. Maybe. Or maybe not. Take 1 year and 3 seconds. E.g., '2001-08-06 03:03:03' - '1 year 3 seconds' = '2000-08-06 03:03:00' '2000-08-06 03:03:03' - '1 year 3 seconds' = '1999-08-06 03:03:00' but '2001-08-06 03:03:03' - '2000-08-06 03:03:00' = '365 days 3 seconds' '2000-08-06 03:03:03' - '1999-08-06 03:03:00' = '366 days 3 seconds' This means either a) A value such as '1 year 3 seconds' varies depending on context, which is not how our system is intended to work, or b) The normal rules of arithmetic do not hold. I doubt the following is was good idea: select timestamp '2000-08-06 03:03:03' - ( timestamp '2000-08-06 03:03:03' - interval '1 year 3 seconds' ); ?column? --- 366 days 00:00:03 select timestamp '2000-08-06 03:03:03' - timestamp '2000-08-06 03:03:03' + interval '1 year 3 seconds' ; ?column? - 1 year 00:00:03 On the other hand, in certain applications even fractional months may be useful. Banks sometimes organize a year as 360 days and months as 30 days, so talking about 0.5 months might make sense. However, in this case again, years/months and days/seconds must not be mixed. Another interesting tidbit here: select interval '1 year 00:00:03' = interval '360 days 00:00:03' ; ?column? -- t > The real bogosity in the interval type is that months and seconds are > not sufficient: it should be months, days, and seconds. As we get > reminded twice a year by the regression tests, "1 day" and "24 hours" > are not the same thing. Agreed. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Re: Data type confusion
Peter Eisentraut <[EMAIL PROTECTED]> writes: > but > '2001-08-06 03:03:03' - '2000-08-06 03:03:00' = '365 days 3 seconds' > '2000-08-06 03:03:03' - '1999-08-06 03:03:00' = '366 days 3 seconds' What I said was that timestamp plus or minus interval is well-defined (when "interval" is a multi-part symbolic interval). It's quite obvious that timestamp minus timestamp yielding interval is not uniquely defined: in the above examples one could express the result either as you show or as '1 year 3 seconds', which I would argue is preferable. For a 3-part (month/day/second) interval, I think the preferable rule for timestamp subtraction is to use the largest symbolic component possible, ie, use the largest number of months/years you can, then use the largest number of days fitting in the remainder, then express what's left as seconds. This is an arbitrary choice among the many possible 3-part representations of a given interval, but it seems like the most natural one for many applications. > a) A value such as '1 year 3 seconds' varies depending on context, which > is not how our system is intended to work, or Isn't it? The relationship between years, days, and seconds is *inherently* context dependent in the common calendar. It might not be too sensible, but sensibleness has never held sway in calendars, at least not since the Romans. I think that the actually useful operations for symbolic intervals have to do with adding them to (or subtracting them from) timestamps. For example, I know exactly what I think should happen when I write now() + '1 day'::interval, and that two days out of the year this should yield a different result from now() + '24 hours'::interval. Whatever else we do with intervals has to mesh with that as best we can make it happen. I'm not sure your notion of fractional months really holds water, at least not for this particular operation. When is 25 Feb 2000 plus 0.95 month? Is the 0.95 measured with respect to the length of February, or of March? Does it matter that 2000 is a leap year? There may be some other operations that have sensible interpretations for such a datatype, however. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
