[SQL] Arithmetic operation on DATE
is it possible to add a date datatype column to a number resulting to a date.. theoretically it should do this,,, X is in months date + X = date --> 2001-08-20 + 6 = 2002-02-20 -- The information contained in this message (including any attachments) is confidential and intended solely for the attention and use of the named addressee(s). It must not be copied, distributed nor disclosed to any person. If you are not the intended recipient, please delete it from your system and notify sender immediately. Any disclosure, copying or distribution thereof or any action taken or omitted to be taken in reliance thereon is prohibited and may be unlawful. -- ---(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] Arithmetic operation on DATE
is it possible to add a date datatype column to a number resulting to a date.. theoretically it should do this,,, X is in months date + X = date --> 2001-08-20 + 6 = 2002-02-20 -- The information contained in this message (including any attachments) is confidential and intended solely for the attention and use of the named addressee(s). It must not be copied, distributed nor disclosed to any person. If you are not the intended recipient, please delete it from your system and notify sender immediately. Any disclosure, copying or distribution thereof or any action taken or omitted to be taken in reliance thereon is prohibited and may be unlawful. -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Arithmetic operation on DATE
On Mon, 13 Aug 2001 21:48, macky wrote: > is it possible to add a date datatype column to a number resulting to a > date.. chris=# select date(CURRENT_TIMESTAMP); date 2001-08-13 (1 row) chris=# select date(CURRENT_TIMESTAMP) + '6 @days'::interval; ?column? 2001-08-19 00:00:00+12 (1 row) chris=# select date(date(CURRENT_TIMESTAMP) + '6 @days'::interval); date 2001-08-19 (1 row) That what you want? > theoretically it should do this,,, > > X is in months > > date + X = date > > --> 2001-08-20 + 6 = 2002-02-20 > > -- > The information contained in this message (including any attachments) > is confidential and intended solely for the attention and use of the > named addressee(s). It must not be copied, distributed nor disclosed > to any person. If you are not the intended recipient, please delete > it from your system and notify sender immediately. Any disclosure, > copying or distribution thereof or any action taken or omitted to be > taken in reliance thereon is prohibited and may be unlawful. > -- > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(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] Arithmetic operation on DATE
Ok, so here's my small knowledge on this matter
select date(date('2001-08-20')+interval('6 months'));
date
2002-02-20
I'm sure there's more elegant and shorter ways of doing this, but a quick
answer, maybe you're stuck somewhere :)
Antti
Tested it on 7.0.2.
On Mon, 13 Aug 2001, macky wrote:
> is it possible to add a date datatype column to a number resulting to a
> date..
>
>
> theoretically it should do this,,,
>
> X is in months
>
> date + X = date
>
> --> 2001-08-20 + 6 = 2002-02-20
>
>
>
>
>
>
> --
> The information contained in this message (including any attachments)
> is confidential and intended solely for the attention and use of the
> named addressee(s). It must not be copied, distributed nor disclosed
> to any person. If you are not the intended recipient, please delete
> it from your system and notify sender immediately. Any disclosure,
> copying or distribution thereof or any action taken or omitted to be
> taken in reliance thereon is prohibited and may be unlawful.
> --
>
>
>
> ---(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 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Arithmetic operation on DATE
On Mon, 13 Aug 2001 21:38, macky wrote: > is it possible to add a date datatype column to a number resulting to a > date.. chris=# select date(date(CURRENT_TIMESTAMP) + '6 @months'::interval); date 2002-02-13 (1 row) More of what you want? > theoretically it should do this,,, > > X is in months > > date + X = date > > --> 2001-08-20 + 6 = 2002-02-20 > > > > > > > -- > The information contained in this message (including any attachments) > is confidential and intended solely for the attention and use of the > named addressee(s). It must not be copied, distributed nor disclosed > to any person. If you are not the intended recipient, please delete > it from your system and notify sender immediately. Any disclosure, > copying or distribution thereof or any action taken or omitted to be > taken in reliance thereon is prohibited and may be unlawful. > -- > > > > ---(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 4: Don't 'kill -9' the postmaster
Re: [SQL] Arithmetic operation on DATE
On Mon, Aug 13, 2001 at 05:48:57PM +0800, macky wrote: > is it possible to add a date datatype column to a number resulting to a > date.. > > > theoretically it should do this,,, > > X is in months > > date + X = date > > --> 2001-08-20 + 6 = 2002-02-20 test=# select '2001-08-20'::date + '6months'::interval; ?column? 2002-02-20 00:00:00+01 (1 row) ..see docs about the "interval" datetype. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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
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 4: Don't 'kill -9' the postmaster
[SQL] optimizing select ... not in (select ...)
I have this query : select distinct on (Pictures.PictureID) * from Pictures where Pictures.PictureID not in (select distinct PictureID from Keywords); and I find it a bit slow. Does anybody have suggestions to run this faster ? (I have indexes on PictureID on both Pictures and Keywords) -- Laurent Martelli [EMAIL PROTECTED] http://www.bearteam.org/~laurent/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Donations?
> "Josh" == "Josh Berkus" <[EMAIL PROTECTED]> writes: Josh> Core team, Josh> I may be able to build a small donation towards postgresql Josh> development into my next project fee. However, I am unsure of Josh> where I can send a check so that it will go directly towards Josh> PostgreSQL development, and not for any company's overhead. Josh> Suggestions? Maybe you could do that through www.cosource.com ? (the site is down today because they're moving the servers). Here's how it works : You post a request on the site, telling how much you are willing to spend to see it developped. Other users can join you so that's more money for the developpers. Developpers can submit proposals. You choose one, commit money on it, the developper does the work, you pay him and everybody's happy : the developpers got money and the users got features. -- Laurent Martelli [EMAIL PROTECTED] http://www.bearteam.org/~laurent/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: REFERENCES constraint
[EMAIL PROTECTED] (Cedar Cox) wrote in message news:<[EMAIL PROTECTED]>... > On Wed, 8 Aug 2001, Jan Wieck wrote: > > Josh Berkus wrote: > > > Cedar, > > > > > > > 1. Can a column reference more than one table? (This assumes you use > > > > a > > > > single sequence to generate the IDs for both "tbla" and "tblb". I > > > > guess > > > > you would also have the problem of enforcing a unique index. Say > > > > what?! > > > > A unique index across multiple tables.. absurd :) eg.. > > > > > > > > CREATE TABLE blah ( > > > > id int4, > > > > f_id int4 REFERENCES tbla (id) REFERENCES tblb (id) > > > > ) > > > > > > I'd reccomend, instead, having blah reference tbla and tbla reference > > > tblb. It'd have the same effect, without forcing you to monkey around > > > with custom triggers. > > > > Nobody said that primary keys are limited to the serial > > datatype. So in case that tbla and tblb could have different > > sets of keys with a possible intersection, and further given > > that blah.f_id shall be limited to values both have in > > common, there's no other way than having multiple foreign key > > constraints on that one column. > > > > Thus, it is possible. I'm not sure if the above syntax is > > supported, but at least you can put table level CONSTRAINT > > clauses into the statement and/or add the constraints later > > with ALTER TABLE. > > I guess I wasn't clear. Let me try to explain again: > > CREATE TABLE obj_weights ( > object_id int4 REFERENCES ( apple_objects(id) OR banana_objects(id) ) > weight float4, > ) > > "apple_objects" doesn't necessarily have anything to do with > "banana_objects". Ok, don't ask why you would want to store weights of > apples and bananas in the same table.. (and if you know, please tell me). > This is all actually for someone else's database that I just picked up. > They did something like this.. single sequence for the whole database, > multiple object tables, and a table(s) referencing objects that could come > from any of those tables. Maybe this is just bad design. Thoughts > anyone? What about this example: Say you want all addresses in one table. This is something that I've wanted. And you want both employees and vendors to reference the address entity. Using the mythical syntax in this thread: CREATE TABLE vendor ( vendor_id integer, namevarchar(32), PRIMARY KEY (company_id) ) CREATE TABLE employee ( employee_id integer, first_name varchar(32), last_namevarchar(32), PRIMARY KEY (employee_id) ) CREATE TABLE address ( address_id integer, object_idREFERENCES vendor OR REFERENCES employee, address varchar(32), city varchar(32), statevarchar(2), zip varchar(9), PRIMARY KEY (address_id) ) This is what I've done: Create an address table, it references no one. CREATE TABLE address ( address_id integer, address varchar(32), city varchar(32), statevarchar(2), zip varchar(9), PRIMARY KEY (address_id) ) Assuming an employee has only one address, the employee table references the address table. CREATE TABLE employee ( employee_id integer, first_name varchar(32), last_namevarchar(32), address_id integer REFERENCES address PRIMARY KEY (employee_id) ) Assuming that a vendor can have more than one address: CREATE TABLE vendor ( vendor_id integer, name varchar(32), PRIMARY KEY (company_id) ) CREATE TABLE vendor_address ( vendor_id integer REFERENCS vendor, address_id integer REFERNCES address, PRIMARY KEY (vendor_id, address_id) ) Just a thought Alan Gutierrez - [EMAIL PROTECTED] ---(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] optimizing select ... not in (select ...)
Laurent, > select distinct on (Pictures.PictureID) * from Pictures where > Pictures.PictureID not in (select distinct PictureID from Keywords); > > and I find it a bit slow. Does anybody have suggestions to run this > faster ? (I have indexes on PictureID on both Pictures and Keywords) NOT IN is always slow on all RDBMS that I know, unless the subselect has a very small (<100) return set. This is because the engine has to compare each row in the master query against every value returned by NOT IN, one row at a time. Try the WHERE NOT EXISTS construction instead: SELECT * FROM Pictures WHERE NOT EXISTS ( SELECT pictureID FROM keywords WHERE keywords.pictureID = Pictures.pictureID ); This uses the DB engine's JOIN functionality and thus runs considerably faster. BTW, all those "DISTINCT" in the query example you gave, assuming that PictureID is the unique index of Pictures, are completely superfluous and will only slow the query down. Particularly the use of DISTINCT in a subquery should only be used if the contents of the subquery will be displayed as part of the result set. -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
Re: [SQL] optimizing select ... not in (select ...)
Laurent Martelli <[EMAIL PROTECTED]> writes: > I have this query : > > select distinct on (Pictures.PictureID) * from Pictures where Pictures.PictureID not >in (select distinct PictureID from Keywords); > > and I find it a bit slow. Does anybody have suggestions to run this > faster ? (I have indexes on PictureID on both Pictures and Keywords) Try this instead: select distinct on (Pictures.PictureID) * from Pictures where not exists (select distinct PictureID from Keywords where Pictures.PictureID=Keywords.PictureID); I've found that it's usually faster, probably because it can use indices better. Tomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] optimizing select ... not in (select ...)
Can it be redone using NOT EXISTS? > I have this query : > > select distinct on (Pictures.PictureID) * from Pictures where Pictures.PictureID not >in (select distinct PictureID from Keywords); > > and I find it a bit slow. Does anybody have suggestions to run this > faster ? (I have indexes on PictureID on both Pictures and Keywords) > > -- > Laurent Martelli > [EMAIL PROTECTED] http://www.bearteam.org/~laurent/ > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: optimizing select ... not in (select ...)
> "Tomas" == Tomas Berndtsson <[EMAIL PROTECTED]> writes: Tomas> Laurent Martelli <[EMAIL PROTECTED]> writes: >> I have this query : >> >> select distinct on (Pictures.PictureID) * from Pictures where >> Pictures.PictureID not in (select distinct PictureID from >> Keywords); >> >> and I find it a bit slow. Does anybody have suggestions to run >> this faster ? (I have indexes on PictureID on both Pictures and >> Keywords) Tomas> Try this instead: Tomas> select distinct on (Pictures.PictureID) * from Pictures where Tomas> not exists (select distinct PictureID from Keywords where Tomas> Pictures.PictureID=Keywords.PictureID); Tomas> I've found that it's usually faster, probably because it can Tomas> use indices better. It is indeed much faster. Thanks to all of you who answered so fast. -- Laurent Martelli [EMAIL PROTECTED] http://www.bearteam.org/~laurent/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Mirroring the database?
Does the latest PostgreSQL 7.1.2 support database mirroring? I have machine A, B and C, they all have the same database and tables. Machine A is the operational machine, machine B and C are backup. If users do INSERT, UPDATE and DELETE in machine A, I want have the same SQL statements in machine B and C. 1. I can do pg_dump or COPY every hour. 2. I can use PerlDBI or JDBC open multiple connections for each SQL statement. 3. But I prefer if the system auto mirroring the database, then I can do nothing. All I need is set the auto mirroring configure. Please tell me how to do in 3. above. Thank you in advance! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Eh?
Tom, Stephan, SHould I be concerned about this? DEBUG: geqo_main: using edge recombination crossover [ERX] DEBUG: geqo_main: using edge recombination crossover [ERX] It happens when I run a truly massive (>2000 chars) query ... -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] Eh?
"Josh Berkus" <[EMAIL PROTECTED]> writes: > SHould I be concerned about this? > DEBUG: geqo_main: using edge recombination crossover [ERX] Nope. See http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/geqo.html regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Eh?
Tom, > > SHould I be concerned about this? > > > DEBUG: geqo_main: using edge recombination crossover [ERX] > > Nope. See > http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/geqo.html Cool! I love it when I actually get the chance to see some advance theory applied. And congrats on implementing it! Of course, we'll see if it really boosts query optimization when there's a full data population. This view has 3 UNION statements, 11 subselects, 35 JOINS, and calls on 3 custom formatting functions (two of them many times). A good "destruction test" for the GEQO, hey? The only thing I'm missing is a couple of LEFT OUTER JOINS and maybe a WHERE NOT EXISTS. -Josh P.S. The purpose of the view is to "flatten" a large chunk of complex relational data into a comma-delimited text table to be imported into an accounting program. __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] Eh?
On Mon, 13 Aug 2001, Josh Berkus wrote: > > > SHould I be concerned about this? > > > > > DEBUG: geqo_main: using edge recombination crossover [ERX] > > > > Nope. See > > http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/geqo.html > > Cool! I love it when I actually get the chance to see some advance > theory applied. And congrats on implementing it! Yeah, this is really excellent. > Of course, we'll see if it really boosts query optimization when there's > a full data population. This view has 3 UNION statements, 11 > subselects, 35 JOINS, and calls on 3 custom formatting functions ... This makes me wonder... in the case of a stored complex view, would it be helpful to ask PostgreSQL to spend extra time in query optimisation and then cache the result? Or does it do this already? -- Tod McQuillin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
