Re: [SQL] Re: Data type confusion
Folks, Wow. Talk about asking dangerous questions ... > 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. Sure, that makes sense. In the meantime, I'll add a note to the FAQ which says "Adding and subtracting wildly disparate time values (e.g. '1 year'::INTERVAL - '3 seconds'::INTERVAL) may cause the database to make unusual interval value choices which could impair accuracy. Please test extensively before relying on operations of this sort." > 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. Peter is absolutely correct here. '1 year'::INTERVAL - '1 day'::INTERVAL is '364 days'::INTERVAL most of the time. However, on leap years it is '365 days'. > 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. One way to simplify this would be not to allow any division operations on INTERVALS that result in a modulo of a smaller increment than the INTERVAL value expressed. Thus, one could "'3 months'::INTERVAL / 3" but would not be allowed to "'2 months::INTERVAL / 3". However, this seems kind of unfair to hour, minute, and second values whose fractions are well-defined and easily manipulated. Or, to put it another way, 95% of the time users just want to do simple things. Like we want to know how many weeks an employee has been with us for: '2 years 3 months'::INTERVAL / '1 week'::INTERVAL (and we don't care about the fractional week left over). Thus we don't want to hold up simple and obvious date multiplication and division just to deal with the wierdo cases. -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 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Re: Data type confusion
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Peter is absolutely correct here. '1 year'::INTERVAL - '1 > day'::INTERVAL is '364 days'::INTERVAL most of the time. However, on > leap years it is '365 days'. Au contraire, it is always '1 year - 1 day'::INTERVAL. That is a two-part interval value and is not reduced further. When you add it to a date or timestamp, *then* you find out how many days are meant. > One way to simplify this would be not to allow any division operations > on INTERVALS that result in a modulo of a smaller increment than the > INTERVAL value expressed. Thus, one could "'3 months'::INTERVAL / 3" > but would not be allowed to "'2 months::INTERVAL / 3". However, this > seems kind of unfair to hour, minute, and second values whose fractions > are well-defined and easily manipulated. I was toying with the notion of allowing scalings whose results didn't introduce any fractional part to the "months" field. For example '2 months + 1 day' / 2.0 = '1 month + 12hrs' '3 months + 1 day' / 2.0 = error (can't have a half month) '61 days' / 2.0 = '30 days 12hrs' However, I fear that this would make no sense to anyone who hadn't thought about the issues as carefully as we have in this thread. > Or, to put it another way, 95% of the time users just want to do simple > things. Like we want to know how many weeks an employee has been with > us for: '2 years 3 months'::INTERVAL / '1 week'::INTERVAL (and we > don't care about the fractional week left over). Good point. Ugly as the "30 day" convention is, it is probably close enough for that sort of 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])
[SQL] prob with PERL/Postgres
PERL SNIPPET: # build arrays from file (OMITTED) use Pg; $dbhost='127.0.0.1'; $dbname='mpact'; #$connstr="dbname=$dbname"; $connstr="host=$dbhost dbname=$dbname"; $conn = Pg::connectdb($connstr); #more code related to date omitted $result=$conn->exec($sql); (PGRES_COMMAND_OK eq $result->resultStatus) or die $conn->errorMessage; WHY DO I GET PQsendQuery() -- There is no connection to the backend. I have tried leaving host blank, using IP 127.0.0.1 and hostname localhost. This script should work - the problem is something with postgres but I dont know what. Any ideas out there? Thanks, Kris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] prob with PERL/Postgres
I use the Perl DBI module to connect to a postgres data base on the local system. I don't specify the host name. This is the syntax I use to connect to the data base: use DBI; $dbname = "foo"; $connstr = "dbi:Pg:dbname=$dbname"; $dbh = DBI->connect($connstr); Kristopher Yates wrote: > PERL SNIPPET: > > # build arrays from file (OMITTED) > > use Pg; > $dbhost='127.0.0.1'; > $dbname='mpact'; > #$connstr="dbname=$dbname"; > $connstr="host=$dbhost dbname=$dbname"; > $conn = Pg::connectdb($connstr); > > #more code related to date omitted > > $result=$conn->exec($sql); > (PGRES_COMMAND_OK eq $result->resultStatus) > or die $conn->errorMessage; > > WHY DO I GET PQsendQuery() -- There is no connection to the > backend. I have tried leaving host blank, using IP 127.0.0.1 and > hostname localhost. This script should work - the problem is > something with postgres but I dont know what. Any ideas out > there? Thanks, Kris > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- = Katherine (Kate) L. Collins Senior Software Engineer/Meteorologist Weather Services International (WSI Corporation) 900 Technology Park Drive Billerica, MA 01821 EMAIL: [EMAIL PROTECTED] PHONE: (978) 262-0610 FAX: (978) 262-0700 http://www.intellicast.com ---(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: Data type confusion
Josh Berkus wrote:
> Or, to put it another way, 95% of the time users just want to do simple
> things. Like we want to know how many weeks an employee has been with
> us for: '2 years 3 months'::INTERVAL / '1 week'::INTERVAL (and we
> don't care about the fractional week left over).
> Thus we don't want to hold up simple and obvious date multiplication and
> division just to deal with the wierdo cases.
I see now what you are trying to do. It sort of makes sense, but I'm still really
reluctant to give (semantic or otherwise) meaning to "yesterday divided by tomorrow"
.
Would it be a better solution if there was a conversion function a la
convert(text, interval) RETURNS double precision
where text in ('day','week','hour', ). The function would convert the interval to
the specified unit, with some considerable degree of fuzziness as discussed in this
thread.
Then your query would be simply
convert('week', '2 years 3 months') / 1
and would return something in the vicinity of 117.0 :-)
It seems to me that such a function would be more generally useful than the division
of intervals. What you really want to do is not to divide intervals, but to express
them in different time units. Or am I missing something (again)?
Comments?
Allan.
---(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] prob with PERL/Postgres
Kristopher Yates wrote:
>PERL SNIPPET:
>
># build arrays from file (OMITTED)
>
>use Pg;
>$dbhost='127.0.0.1';
>$dbname='mpact';
>#$connstr="dbname=$dbname";
>$connstr="host=$dbhost dbname=$dbname";
>$conn = Pg::connectdb($connstr);
>
>#more code related to date omitted
>
>$result=$conn->exec($sql);
>(PGRES_COMMAND_OK eq $result->resultStatus)
>or die $conn->errorMessage;
>
>WHY DO I GET PQsendQuery() -- There is no connection to the
>backend. I have tried leaving host blank, using IP 127.0.0.1 and
>hostname localhost. This script should work - the problem is
>something with postgres but I dont know what. Any ideas out
>there? Thanks, Kris
>
>---(end of broadcast)---
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
>
have you checked your /etc/host file?
as well is your loopback on ?
what about your pg_hba.conf
it should probably have ,by default:
---pg_hba.conf
# By default, allow anything over UNIX domain sockets and localhost.
localall trust
host all 127.0.0.1 255.255.255.255 trust
---pg_hba.conf
you can use stonger security consult the Pg docs on that!
its been a while since ive looked at them
here is how i connect::
#!/usr/bin/perl
use strict;
use warnings;
use Pg;
my $command="select * from write;";
my $conninfo = "dbname=write user=www password=Apache1312 host=localhost";
my $conn = Pg::connectdb($conninfo);
if (Pg::PGRES_CONNECTION_OK == $conn->status){
my $result = $conn->exec($command);
if (Pg::PGRES_TUPLES_OK == $result->resultStatus) {
while (my @row = $result->fetchrow) {
print join (" ",@row);
}
}
}
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Re: Data type confusion
Allan, > I see now what you are trying to do. It sort of makes sense, but I'm > still really reluctant to give (semantic or otherwise) meaning to > "yesterday divided by tomorrow" . I don't agree. Consider, for example, this statement: '30 weeks ago'::INTERVAL / '2 weeks'::INTERVAL = -15 Just as -30 / 2 = -15 To phrase the equation above: "How many two week periods is thirty weeks ago? Minus fifteen, or fifteen ago." This makes perfect sense to me. > It seems to me that such a function would be more generally useful > than the division of intervals. What you really want to do is not to > divide intervals, but to express them in different time units. Or am > I missing something (again)? >From my pespective? Yes, you are. (For one thing, the CONVERT function in Postgres converts between unicode character sets, not data-types). Look, if I'm designing a payroll application for a company with bi-weekly payroll, I will want a report that shows how many payroll periods for which an employee has been employed. Thus I will want to: periods_employed := (current_timestamp - date_hired) / '2 weeks'::INTERVAL I don't want to go through a bunch of non-ANSI SQL-compliant conversion functions to do it. Especially not as this is just what the ANSI SQL data type and operator specs are designed to support. -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 4: Don't 'kill -9' the postmaster
Re: [SQL] prob with PERL/Postgres
On Mon, 6 Aug 2001, Kate Collins wrote:
> I use the Perl DBI module to connect to a postgres data base on the local
> system. I don't specify the host name. This is the syntax I use to connect to
> the data base:
>
> use DBI;
>
> $dbname = "foo";
> $connstr = "dbi:Pg:dbname=$dbname";
> $dbh = DBI->connect($connstr);
Kate, he uses a diff module by the same author (Edmund Mergl) but with
a very diff syntax. The advantage of the DBI - Kris, if you're interested -
is that the syntax is much like ESQL/C and the code is much more portable.
For example I use DBI to access both pg and oracle.
Cheers,
Tom
> Kristopher Yates wrote:
>
> > PERL SNIPPET:
> >
> > # build arrays from file (OMITTED)
> >
> > use Pg;
> > $dbhost='127.0.0.1';
> > $dbname='mpact';
> > #$connstr="dbname=$dbname";
> > $connstr="host=$dbhost dbname=$dbname";
> > $conn = Pg::connectdb($connstr);
> >
> > #more code related to date omitted
> >
> > $result=$conn->exec($sql);
> > (PGRES_COMMAND_OK eq $result->resultStatus)
> > or die $conn->errorMessage;
> >
> > WHY DO I GET PQsendQuery() -- There is no connection to the
> > backend. I have tried leaving host blank, using IP 127.0.0.1 and
> > hostname localhost. This script should work - the problem is
> > something with postgres but I dont know what. Any ideas out
> > there? Thanks, Kris
> >
> > ---(end of broadcast)---
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
>
> --
> =
> Katherine (Kate) L. Collins
> Senior Software Engineer/Meteorologist
> Weather Services International (WSI Corporation)
> 900 Technology Park Drive
> Billerica, MA 01821
> EMAIL: [EMAIL PROTECTED]
> PHONE: (978) 262-0610
> FAX: (978) 262-0700
> http://www.intellicast.com
>
>
>
> ---(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
>
SVCMC - Center for Behavioral Health
Thomas Good tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst Phone: 718-354-5528
Residential ServicesMobile: 917-282-7359
/* Die Wahrheit Ist Irgendwo Da Draussen... */
---(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: prob with PERL/Postgres
1. I'm assuming that psql works fine? psql -h 127.0.0.1 mpact? 1b. Have you checked the port?? You ARE running postmaster with the '-i' option, aren't you? The recommended solutions are typically to use the DBI and DBD modules. Try man DBD::Pg. Alternatively: have you tried the setdbLogin method instead: $conn = Pg::setdbLogin($pghost, $pgport, $pgoptions, $pgtty, $dbname, $login, $pwd) Opens a new connection to the backend. The connection identifier $conn ( a pointer to the PGconn structure ) must be used in subsequent commands for unique identifica- tion. Before using $conn you should call $conn->status to ensure, that the connection was properly made. Closing a connection is done by deleting the connection handle, eg 'undef $conn;'. Hope this helps a little.. Allan Kristopher Yates wrote: > PERL SNIPPET: > > # build arrays from file (OMITTED) > > use Pg; > $dbhost='127.0.0.1'; > $dbname='mpact'; > #$connstr="dbname=$dbname"; > $connstr="host=$dbhost dbname=$dbname"; > $conn = Pg::connectdb($connstr); > > #more code related to date omitted > > $result=$conn->exec($sql); > (PGRES_COMMAND_OK eq $result->resultStatus) > or die $conn->errorMessage; > > WHY DO I GET PQsendQuery() -- There is no connection to the > backend. I have tried leaving host blank, using IP 127.0.0.1 and > hostname localhost. This script should work - the problem is > something with postgres but I dont know what. Any ideas out > there? Thanks, Kris > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] prob with PERL/Postgres
Thomas Good <[EMAIL PROTECTED]> writes: > Kate, he uses a diff module by the same author (Edmund Mergl) but with > a very diff syntax. The advantage of the DBI - Kris, if you're > interested - is that the syntax is much like ESQL/C and the code is > much more portable. For example I use DBI to access both pg and > oracle. DBI is a good alternative, but is unlikely to act much differently as far as connection problems go. > use Pg; > $dbhost='127.0.0.1'; > $dbname='mpact'; > #$connstr="dbname=$dbname"; > $connstr="host=$dbhost dbname=$dbname"; > $conn = Pg::connectdb($connstr); > > #more code related to date omitted > > $result=$conn->exec($sql); > (PGRES_COMMAND_OK eq $result->resultStatus) > or die $conn->errorMessage; > > WHY DO I GET PQsendQuery() -- There is no connection to the > backend. It's hard to tell with only that much information. I think the real mistake in this code is not checking for failure of the connectdb() call. Had you checked at that point, you would have gotten a more useful error message. The examples in the Pg documentation recommend $conn = Pg::connectdb(whatever); die $conn->errorMessage unless PGRES_CONNECTION_OK eq $conn->status; Try that, and if you're still in the dark, let us see the error message... 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
"Josh Berkus" <[EMAIL PROTECTED]> writes: > I don't want to go through a bunch of non-ANSI SQL-compliant conversion > functions to do it. Especially not as this is just what the ANSI SQL > data type and operator specs are designed to support. Curiously enough, ANSI doesn't define an INTERVAL-divided-by-INTERVAL function either. Also, it rather looks like ANSI adopted the position Peter E. expressed: Year-month intervals are mutually comparable only with other year- month intervals. [...] Day-time intervals are mutually comparable only with other day- time intervals. [...] Operations involving items of type datetime require that the date- time items be mutually comparable. Operations involving items of type interval require that the interval items be mutually compara- ble. 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, > Curiously enough, ANSI doesn't define an INTERVAL-divided-by-INTERVAL > function either. Also, it rather looks like ANSI adopted the > position > Peter E. expressed: > > Year-month intervals are mutually comparable only with other > year- > month intervals. [...] > Day-time intervals are mutually comparable only with other > day- > time intervals. [...] > Operations involving items of type datetime require that the > date- > time items be mutually comparable. Operations involving > items of > type interval require that the interval items be mutually > compara- > ble. Hmmm ... does this mean that I couldn't divide '1 year' by '1 week'? I can certaily see not allowing division of '1 year' by '28 seconds' as it spares us a whole bunch of calendar-generated fuzziness. It seems to me that: years,months,weeks,days / years,months,weeks,days is OK, and days,hours,minutes,seconds / days,hours,minutes,seconds is also easy, but years,months,weeks / hours,minutes,seconds is where we get in trouble. So I propose that we suppot the first two and disallow the third. Thus I think that we can adhere to the spec, while still providing the functionality developers want and avoiding a whole lot of '5 months 11 minutes' type headaches. -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
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Hmmm ... does this mean that I couldn't divide '1 year' by '1 week'? That's exactly what it says. regards, tom lane ---(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] Re: Data type confusion
Tom, > > Hmmm ... does this mean that I couldn't divide '1 year' by '1 > week'? > > That's exactly what it says. If that's the case, we'd need to create some sort of function to specify the time unit to output timestamp operation into: to_weeks(current_timestamp - hire_date) / '2 weeks' ... otherwise division and multiplication operators for time values don't do us much good, as we'd be forced to integer-ize all intervals before we can perform any operations on them at all. -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: [warning: largely off-topic] Re: Data type confusion
Allan, > Thanks for your explanation. I'd like to get hold of a copy of > SQL99/PKG001 to see what they have actually defined. Tom posted a section of this. > I think the INTERVAL type sux :-) Long rant follows - consider > hitting the delete button now. Au contraire. I replying to the list because I think that your rant is actually relevant to all date/time development. (If your post wasn't to the list, I'll forward it). > In this area, there are a number of different concepts that it would > make sense to separate. > What's an INTERVAL supposed to be? DUTIME, DLDATE, TIMEU or > something that really belongs to the CALENDAR? > > All of the above? That is a very important question to answer. Here we have an unfortunate dilemma: On the one horn: Absolute time, or UTIME, is *much* easier for a database to deal with since it follows fixed rules. LTIME or DUTIME/DLDATE etc. follow rules which may change from year to year and country to country (or state to state in the US!), making it impossible to compile such date and time rules as part of a database. On the other horn: Of course, what many/most users want is their local time and date according to local rules (admins of worldwide web servers exempted). So ... my first question: surely someone has grappled with this problem already? Surely some professor of C.S. in a university somewhere has an answer for us? -Josh P.S. My head hurts, now. __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] [warning: largely off-topic] Re: Data type confusion
Josh, Thanks for your explanation. I'd like to get hold of a copy of SQL99/PKG001 to see what they have actually defined. I think the INTERVAL type sux :-) Long rant follows - consider hitting the delete button now. In this area, there are a number of different concepts that it would make sense to separate. Let's call one UTIME. It is time defined as (the time-coordinate of) an event in the history of the Universe. [We'll ignore Einstein for the following.] It has physical meaning. Let call another LDATE. It is what you and I normally call a date. Specifically, it is a legal (or social or religious) representation of a UTIME. You can make a contract (legal with man, or religiously with God) using LDATEs. Let's invent a CALENDAR. It translates between UTIME and LDATE. It changes all the time ;-( Well, maybe not *all* the time but it is sufficient unstable to be a problem for some applications. There is not only the problem of Julian vs Gregorian vs some other calendar. Consider the issue of changing the rules governing summer-time. It is not long ago that Britain changed the rule for ending summer time from being the fourth Sunday in October to being the last Sunday in October. Some countries seem to decide on a year to year basis if they want to have summer time. Finally, the international committee that governs the CALENDAR only have to give eight weeks notice when it introduces a leap-second. (Leap seconds are the reason that struct tm.tm_sec has a range of 0,...,61.) This means that the CALENDAR is, strictly speaking, only known eight weeks in advance. It also highlights the difference between LDATE and UTIME: if we have a contract for me to start a machine on a specific LDATE and a leap second is introduced between now and then, then I'd better change that sleep(n) statement in my control program to sleep(n+1) or the factory will start too early. I once spent an unhappy week debugging a problem related to this :-( It really would have started the factory one hour too late. Now of course you can define deltas. Unix systems kind of keep UTIME using a delta: a variable of type time_t holds the number of seconds since a specific event (defined as a given LDATE for a fixed CALENDAR). Let's call them DUTIME and DLDATE. They are conceptually different: One day of DLDATE may be 23, 24, or 25 hours of DUTIME, depending on summer time rules. It may be 86,400 or 86,401 (rarely: 86,402) seconds depending on leap seconds. Important: There is no meaningful translation between DUTIME and DLDATE except for a fixed (start- or end-) UTIME (or DLDATE). (No, really!!) Let's introduce a final concept: a unit of UTIME. Maybe we call it TIMEU. You need to measure UTIME is something, maybe seconds is the fundamental unit (as in SI) and you have other, derived units. We don't need a similar concept for LDATE - it is effectively provided by the CALENDAR. It is it that which defines years, months, or whatever the Incas used to use... Now BACK ON-TOPIC: What's an INTERVAL supposed to be? DUTIME, DLDATE, TIMEU or something that really belongs to the CALENDAR? All of the above? Methinks SQL99 (or PostgreSQL's implementation) is going for the latter option. Methinks it is confusing. But then, I'm thick. :-) Apologies for the interruption -- We now continue the regular scheduled program Allan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] views and null bothering
I have a bunch of tables which I give access through a view. The problem is that in the main table there are columns, that are referenced to another tables column, that have NULLs. In the SELECT inside the view's definition I put the join equality, but have lots of trouble makeing it put correctly the columns that have NULL values. This is what I tried: CREATE VIEW admin_view SELECT id_curso,car.carrera,titulo, car.categoria AS car_categ, categ.categoria, categ.descripcion AS categ_desc FROM carrera car,resol,inscripcion ins,niveles niv, categ WHERE resol.carr=car.id_curso AND niv.id_nivel=car.nivel AND area.id_subarea=car.area AND ins.carrera=car.id_curso AND categ.id_categ=car.categoria; But this one doesn't show rows with NULL on column car.categoria. CREATE VIEW admin_view SELECT id_curso,car.carrera,titulo, car.categoria AS car_categ, categ.categoria, categ.descripcion AS categ_desc FROM carrera car,resol,inscripcion ins,niveles niv, categ WHERE resol.carr=car.id_curso AND niv.id_nivel=car.nivel AND area.id_subarea=car.area AND ins.carrera=car.id_curso AND (categ.id_categ=car.categoria OR car.categoria IS NULL); This one repeats rows with NULLs on column car.categoria, one repeate for each row element in the categ table, which is not what I need. The last thing I thought about, but am not sure if it's exactly what I want (may do things I doen't want with some queries), is to put a DISTINCT ON (id_curso), which would be a solution for the multiple row problem, but I would need to add a CASE in the categ.categoria, so that it gives me NULL when car.categoria ISNULL. Am I complicating it to much? Is there an easier way out? Saludos... :-) -- Cualquiera administra un NT. Ese es el problema, que cualquiera administre. - Martin Marques |[EMAIL PROTECTED] Programador, Administrador | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] views and null bothering
Martin, > I have a bunch of tables which I give access through a view. The > problem is > that in the main table there are columns, that are referenced to > another > tables column, that have NULLs. > In the SELECT inside the view's definition I put the join equality, > but have > lots of trouble makeing it put correctly the columns that have NULL > values. You need to use LEFT OUTER JOIN, supported in Postgres 7.1.x. See the current postgresql docs, or your favorite ANSI-SQL handbook, for guidance. -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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Simple Insert Problem
Sorry but this is making me crazy... yes... I'm way new to SQL Why would this error out ?? This is the Query... INSERT INTO OP (op_num,op_name,start_time) Values (5400,Welding,06:00:00); And this is the pgSQL error... ERROR: parser: parse error at or near ":" The table has reasonable values in it already... I'm just adding a few more rows by hand. Thanks All, ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Simple Insert Problem
Gonzo, > Sorry but this is making me crazy... yes... I'm way new to SQL Buy PostgreSQL: Introduction and Concepts. Now. Read it cover to cover before posting any more questions, ok? > Why would this error out ?? > > This is the Query... > INSERT INTO OP (op_num,op_name,start_time) Values > (5400,Welding,06:00:00); Because you've forgotten the quote marks. -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 4: Don't 'kill -9' the postmaster
RE: [SQL] Simple Insert Problem
Gonzo: You need to make sure that you delimit your values correctly. To insert text fields (which may contain spaces) use 'single quotes'. You'll also want to enter date fields the same way. So, you should try INSERT INTO OP (op_num,op_name,start_time) VALUES (5400,'Welding','06:00:00'); Of course, you're probably using the "time" data type, which means that 6:00:00 really means 6:00 in the morning! Hope that helps! -Robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gonzo Rock Sent: Monday, August 06, 2001 7:18 PM To: [EMAIL PROTECTED] Subject: [SQL] Simple Insert Problem Sorry but this is making me crazy... yes... I'm way new to SQL Why would this error out ?? This is the Query... INSERT INTO OP (op_num,op_name,start_time) Values (5400,Welding,06:00:00); And this is the pgSQL error... ERROR: parser: parse error at or near ":" The table has reasonable values in it already... I'm just adding a few more rows by hand. Thanks All, ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Simple Insert Problem
Josh, Fuck you and the high horse you rode in on. Yes as a matter of fact I did forget the quote marks. Do you think reading that book will help with my silly syntactical errors? Do you think I did not pull my hair out prior to posting? Sometimes the obvious just eludes the smartest of us. Perhaps a better answer would have been: Gonzo, Maybe that question belongs in the pgsql-novice list instead. This list is for the experts like myself. Thanks, Josh Personally I think you are as Spiritually Bankrupt as that Silly website you have been working on. later, Gonzo Oh and P.S. Thanks for the clue. I'm not spinning my wheels on that one anymore, but I'm sure I'll run up aginst something else soon enough. At 05:59 PM 8/6/01 -0700, you wrote: >Gonzo, > >> Sorry but this is making me crazy... yes... I'm way new to SQL > >Buy PostgreSQL: Introduction and Concepts. Now. Read it cover to cover >before posting any more questions, ok? > > >> Why would this error out ?? >> >> This is the Query... >> INSERT INTO OP (op_num,op_name,start_time) Values >> (5400,Welding,06:00:00); > >Because you've forgotten the quote marks. > >-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 > ___ Send a cool gift with your E-Card http://www.bluemountain.com/giftcenter/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Simple Insert Problem
Gonzo, > F*** you and the high horse you rode in on. Please restrain yourself from posting strong language to the list. If you want to use stong language at me, please e-mail it directly. Further, I e-mailed an apology for touching a nerve, but you appear to have supplied an invalid reply-to address -- thus forcing me to reply to the list. > Gonzo, > > Maybe that question belongs in the pgsql-novice list instead. This > list is > for the experts like myself. Hey, you're right. I'll use something similar to that reply next time. -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] Delete coloumn
Please send to me how to delete coloumn in SQL. Thank's ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Delete coloumn
> Please send to me how to delete coloumn in SQL. > Thank's http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/ Bookmark the above URL. ALTER TABLE is what you are looking for: http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-altertable.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [SQL] Delete coloumn
Unfortunately, there's no easy way to delete a column in a table in PostgreSQL. The standard SQL syntax is: ALTER TABLE tablename DROP COLUMN columnname; But I repeat, this is NOT supported in postgresql. If you really need to delete a column you can always just create a new table with an identical definition but WITHOUT the offending column, and then SELECT INTO it. Example: CREATE TABLE sample ( id INTEGER, data TEXT, badcolumn DATE ); Now to delete the bad column table: CREATE TABLE sample_copy ( id INTEGER, data TEXT); and then copy it all over: SELECT id,data INTO sample_copy FROM sample; and then you can DROP TABLE sample; If you need the original table name, repeat the process of creating a new table now and copying the data over. Hope that helps! -Robby Slaughter -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Suhadi Sent: Monday, August 06, 2001 11:16 PM To: SQL Subject: [SQL] Delete coloumn Please send to me how to delete coloumn in SQL. Thank's ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Delete coloumn
My response was incorrect! That's what you get for using phppgadmin all day everyday. A transparent feeling that there is alter table drop column support in postgresql :) Sorry. > > Please send to me how to delete coloumn in SQL. > > Thank's > > http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/ > > Bookmark the above URL. > > ALTER TABLE is what you are looking for: > > http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/sql-altertable.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Delete Trigger Issue
I am developing a db application in postgresql and i need to write a delete trigger on one of the tables. the environment is table1 field1 varchar(64) other fields. table 2. field1 varchar(64) other fields I need a delete trigger on the table 1, so that if I delete a row from table 1 , the corresponding rows from table 2 should also be deleted. This is the code I have tried. DROP FUNCTION ApplicationsDeleteFn(); CREATE FUNCTION ApplicationsDeleteFn() RETURNS OPAQUE AS ' BEGIN delete from ports where appName=OLD.appName; RETURN OLD; END; ' LANGUAGE 'plpgsql'; Please help me with this, as my work is time bound.Even if the trigger is written is SQL Thanks sundar ---(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] Delete Trigger Issue
Let me make sure I get this right: CREATE TABLE table1 ( field1 varchar(64), ... ); CREATE TABLE table2 ( field2 varchar(64), ... ); and you want that whenever a row is deleted from table1 you want the SAME row to be deleted from table2? here's what you want. First, a trigger: CREATE TRIGGER update_table2 BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE update_table2_proc(); That trigger will make sure that each time a row is deleted from table1, the proceudre update_table2_proc will be called. And here is that procedure CREATE FUNCTION update_table2_proc() RETURNS opaque AS 'BEGIN DELETE FROM table2 WHERE field2 = new.field1; RETURN new; END;' LANGUAGE 'plpgsql'; That procedure just DELETEs all the rows in table2 that match up to field1 in the first table. Of course, you might want to do a broader LIKE matching if they are really VARCHAR fields. Hope that helps! -Robby Slaughter -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sundararajan Sent: Tuesday, August 07, 2001 12:16 AM To: [EMAIL PROTECTED] Subject: [SQL] Delete Trigger Issue I am developing a db application in postgresql and i need to write a delete trigger on one of the tables. the environment is table1 field1 varchar(64) other fields. table 2. field1 varchar(64) other fields I need a delete trigger on the table 1, so that if I delete a row from table 1 , the corresponding rows from table 2 should also be deleted. This is the code I have tried. DROP FUNCTION ApplicationsDeleteFn(); CREATE FUNCTION ApplicationsDeleteFn() RETURNS OPAQUE AS ' BEGIN delete from ports where appName=OLD.appName; RETURN OLD; END; ' LANGUAGE 'plpgsql'; Please help me with this, as my work is time bound.Even if the trigger is written is SQL Thanks sundar ---(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 ---(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] Delete Trigger Issue
On Tue, 7 Aug 2001, Sundararajan wrote: > I am developing a db application in postgresql and i need to write a delete > trigger on one of the tables. > > I need a delete trigger on the table 1, so that if I delete a row from table > 1 , the corresponding rows from table 2 should also be deleted. Well, if you want the other semantics (not allow rows in 2 that don't match rows in 1, etc) you could use a foreign key rather than an explicit trigger. > > This is the code I have tried. > > DROP FUNCTION ApplicationsDeleteFn(); > CREATE FUNCTION ApplicationsDeleteFn() > RETURNS OPAQUE > AS ' > BEGIN > delete from ports where appName=OLD.appName; > RETURN OLD; > > END; > ' > LANGUAGE 'plpgsql'; Are you actually making the trigger? What does it do when you try to make the trigger and then when you try to delete? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
