[SQL] Query Problem
If I've 2 tables like this: Country table : with 2 fields (country and id) Country id EnglandE France F JapanJ FlightTo table : with 2 fields(Flight ID and Destination) FlightIDDestination B1 E,J B2 E,F B3 J,F How do I make a query, so that I can get a result something like this : FlightID CountryVisited B1England,Japan B2England,France B3Japan, France Any help very much appreciated. Thanks ---(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] Query Problem
Abdul Wahab Dahalan wrote: If I've 2 tables like this: Country table : with 2 fields (country and id) Country id EnglandE France F JapanJ FlightTo table : with 2 fields(Flight ID and Destination) FlightIDDestination B1 E,J B2 E,F B3 J,F Simplest solution is to correct this table - "Destination" should be two fields (e.g. dest_from,dest_to). Then you could do something like: SELECT flight_id, c1.country as from_country, c2.country as to_country FROM flight_to, country c1, country c2 WHERE dest_from = c1.id AND dest_to = c2.id ; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [despammed] [SQL] Crosstab function
I have install postgresql-contrib finally... i'm newbie in server. I use freebsd 4.8, I saw in documentation it is said use tablefunc.sql but in freebsd i found file tablefunc.so , it is already compiled. is there suggestion to install tablefunc.so ? Thank you Ricky On Apr 6, 2005 3:32 PM, Andreas Kretschmer <[EMAIL PROTECTED]> wrote: > am 06.04.2005, um 13:55:35 +0700 mailte bandeng folgendes: > > hello guys, > > > > I want to use crosstab function but that function it doesnt exist. my > > version is 7.3 so how do i get the tablefunc.sql from postgre contrib? > > apt-get install postgresql-contrib > > Regards, Andreas > -- > Andreas Kretschmer(Kontakt: siehe Header) > Heynitz: 035242/47212, D1: 0160/7141639 > GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net > ===Schollglas Unternehmensgruppe=== > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- Gutten Aben Sugeng Sonten, Jangane Kurang Santen bandeng ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [despammed] [SQL] Crosstab function
Hi, Bandeng, bandeng schrieb: > I have install postgresql-contrib finally... i'm newbie in server. > I use freebsd 4.8, I saw in documentation it is said use tablefunc.sql > but in freebsd i found file tablefunc.so , it is already compiled. is > there suggestion to install tablefunc.so ? I think you misunderstood the relationship between tablefunc.so and tablefunc.sql, as the former one is not the compiled form of the latter one. The .sql file contains the statements that create functions, datatypes, aggregates etc. For functions programmed in C, SQL function definitions eference the .so file and the function symbol name therein so postgresql can load the library and jump into the C code functions, but the SQL files could also define functions by including their source (for languages such as plpgsql). So to "install" the .so file, you have use psql -f tablefunc.sql yourdb. Markus signature.asc Description: OpenPGP digital signature
[SQL] Table PARTITION
Title: Table PARTITION
How can we create oracle's table with partition in Postgres. (How to create Table partion in postgres)
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/')) TABLESPACE users
Regards
Dinesh Pandey
--
Dinesh Pandey
Sr. Software Engineer
Second Foundation (India) Pvt. Ltd.
Plot# 52
Industrial Area, Phase II
Chandigarh. (India)
PH: (O) 0172-2639202, Extn: 233
Re: [SQL] Table PARTITION
Dinesh Pandey wrote:
How can we create oracle's table with partition in Postgres. (How to create
Table partion in postgres)
CREATE TABLE invoices
(invoice_noNUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
'DD/MM/')) TABLESPACE users
By hand, I'm afraid (although I think people are looking into this
feature for version 8.1)
There's lots of discussion in the mail archives about how people are
handling this. You're basically looking at some combination of
- partial/conditional indexes
- unions
- inheritance
--
Richard Huxton
Archonet Ltd
---(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] Table PARTITION
Hi Richard,
I am using postgres 8.0.1. It allows us to create TABLESPACE but I don't
know how to create PARTITION in postgres.
Can you pls tell me little more.
Thanks
Dinesh Pandey
-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 07, 2005 5:36 PM
To: [EMAIL PROTECTED]
Cc: 'PostgreSQL'
Subject: Re: [SQL] Table PARTITION
Dinesh Pandey wrote:
> How can we create oracle's table with partition in Postgres. (How to
> create Table partion in postgres)
>
> CREATE TABLE invoices
> (invoice_noNUMBER NOT NULL,
> invoice_date DATE NOT NULL,
> comments VARCHAR2(500))
>
> PARTITION BY RANGE (invoice_date)
> (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
> 'DD/MM/')) TABLESPACE users
By hand, I'm afraid (although I think people are looking into this feature
for version 8.1)
There's lots of discussion in the mail archives about how people are
handling this. You're basically looking at some combination of
- partial/conditional indexes
- unions
- inheritance
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Table PARTITION
This is a totally selfish question, but IF someone has a few minutes,
could he/she explain why table partitioning is such an important tool?
Thanks,
Sean
On Apr 7, 2005, at 8:06 AM, Richard Huxton wrote:
Dinesh Pandey wrote:
How can we create oracle's table with partition in Postgres. (How to
create
Table partion in postgres)
CREATE TABLE invoices
(invoice_noNUMBER NOT NULL, invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
'DD/MM/')) TABLESPACE users
By hand, I'm afraid (although I think people are looking into this
feature for version 8.1)
There's lots of discussion in the mail archives about how people are
handling this. You're basically looking at some combination of
- partial/conditional indexes
- unions
- inheritance
--
Richard Huxton
Archonet Ltd
---(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] TIMESTAMP / summertime
T E Schmitz wrote:
>
> Hello,
>
> I *detest* British summertime. This year it took me two days to adjust.
> Now I am realizing that my program might need some adjusting too:
>
> Joking aside, I need some advice regarding TIMESTAMP colums and I can't
> quite get my head round this at the moment:
>
> I created a table TRANSAKTION with a TIMESTAMP column without qualifying
> "with/without time zone".
> My understanding is that this is equivalent to "TIMESTAMP without time
> zone"? (I am using Postgres 7.4.)
>
> I am accessing the database via a Java client program. The DB access
> code is generated by an O/R mapper. Client and server are in the same
> timezone.
>
> One of the things I need to do select records from TRANSAKTION, which
> fall within a certain time period, specified in days: e.g. 1st Mar 2005
> to 31st Mar 2005. In other words, I want to grab TRANSAKTIONs >= 1 Mar
> 00:00 and < 1 Apr 00:00.
>
> The generated WHERE clause is:
>
> WHERE (TRANSAKTION.THE_TIME>={ts '2005-03-01 00:00:00.0'} AND
> TRANSAKTION.THE_TIME<{ts '2005-04-01 01:00:00.0'})
>
> Should it be '2005-04-01 00:00:00.0' or 2005-04-01 01:00:00.0' ??
>
> Also, in autumn, when the clocks go back, I need to be able to
> distinguish between the two double hours.
>
> Sorry, if I sound confused. Unfortuantely, this is what I am ;-)
>
> --
>
> Regards/Gruß,
>
> Tarlika Elisabeth Schmitz
>
IIRC the countless threads regarding timezone adjustments,
which AFAIK include DST settings (Daylight Saving Time resp.
summertime),
this cannot ever be handled correctly, if one does not
specify the column in question as "with time zone".
In addition, IIRC in versions later than 7.4,
the whole timezone calculations have been re-written,
because using the UNIX tztab utilities ended up with
various mistakes on many UNIX derivates.
You might want to search the archive on "DST".
HTH
Regards, Christoph
Forgot to reply to the list, sorry.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Table PARTITION
Dinesh Pandey wrote: Hi Richard, I am using postgres 8.0.1. It allows us to create TABLESPACE but I don't know how to create PARTITION in postgres. Can you pls tell me little more. You can't I'm afraid. You can use inheritance / a view unioning tables to do something similar though. Check the mailing-list archives for notes on these. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Table PARTITION
Sean Davis wrote: This is a totally selfish question, but IF someone has a few minutes, could he/she explain why table partitioning is such an important tool? Say you have a large log-table, you could partition it by month. If most queries only search the last month or two, a lot of your partitioned data could be moved to cheaper/slower disks (via tablespaces). After (say) 24 months, you can start dropping the oldest partitions, and this should take place without affecting any other partitions. How you partition your data depends on how you plan to access it. If you don't query your log-tables by date there's not much point in partitioning by date. -- Richard Huxton Archonet Ltd ---(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] DROP TYPE without error?
In article <[EMAIL PROTECTED]>,
Mischa <[EMAIL PROTECTED]> writes:
> I've got a similar request for other objects that do/do not exist.
> Maybe it's just that I got lazy using MSSQL, but it sure was convenient
> to have:
>IF object_id('WorkTable') IS NULL
>CREATE TABLE WorkTable(...
> etc.
I got lazy using MySQL, where it was convenient to have
CREATE TABLE IF NOT EXISTS tbl (...)
and
DROP TABLE IF EXISTS tbl
This is the only feature of MySQL I really miss.
---(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] 9.17.5. Row-wise Comparison
I believe I started the multi-column index thread a few months back, but now that I have it working so well, I'm a bit nervous it might break in future. >if (b > 1) then true >else if (b = 1 and c > 2) then true >else if (b = 1 and c = 2 and d > 3) then true >else false Your spec sql snippet is like an OR, isn't it, instead of an AND as I'm reyling on? After PG is to spec, will the behaviour I now see change? Or is just that PG now allows itself the option to test in any order, not ensuring a sorted result? As I see it, the results will be the same according to spec, just not necessarily in the same order. Thanks for the info, and for getting my meaning in spite of the typos in my sql. TJ Tom Lane wrote: "TJ O'Donnell" <[EMAIL PROTECTED]> writes: I've been using syntax like select a from tbl where (b,c,d) > (1,2,3) to mean select a from t where b>1 and b>2 and d>3 But I see in the manual at: http://www.postgresql.org/docs/7.4/interactive/functions-comparisons.html#AEN12735 that only = and <> operators are supported. Does this section of the manual not properly apply to this query? Is the manual in error, or am I not understanding? PG's current code acts as you are supposing, but it is broken because it doesn't follow the SQL spec, and we will change it as soon as someone gets around to working on it. The spec says that this syntax implies a column-by-column ordering, essentially if (b > 1) then true else if (b = 1 and c > 2) then true else if (b = 1 and c = 2 and d > 3) then true else false You can find related discussions in the archives from a few months back. The spec's semantics correspond exactly to the sort ordering of a multiple-column btree index, and so there are good reasons why we'd want to provide that behavior even if it weren't mandated by the spec. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] 9.17.5. Row-wise Comparison
On Thu, 2005-04-07 at 06:44 -0700, TJ O'Donnell wrote: > it might break in future. > > >if (b > 1) then true > >else if (b = 1 and c > 2) then true > >else if (b = 1 and c = 2 and d > 3) then true > >else false > Your spec sql snippet is like an OR, isn't it, instead > of an AND as I'm reyling on? not really. > After PG is to spec, will the behaviour I now see change? yes > > "TJ O'Donnell" <[EMAIL PROTECTED]> writes: > > > >>I've been using syntax like > >>select a from tbl where (b,c,d) > (1,2,3) > >> to mean > >>select a from t where b>1 and b>2 and d>3 if b=2, c=1 and d=1 then the expression (b,c,d) > (1,2,3) currently evaluates to false, but according to spec, should evaluate to true. gnari ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] DROP TYPE without error?
Philippe Lang wrote: Hi, Since it is not possible to use CREATE OR REPLACE TYPE, is there a way of using DROP TYPE on a non-existing type, without causing the entire script to abort? It may sound crazy to ask for this, but it could be really useful in my case, where Pl/Pgsql and Pl/Perl code is being generated automatically, based on data found in a database. If I understand you correctly, then this might be useful: begin; select now(); \o tmp.tmp \qecho 'drop type \"foofookitty\";' \o \! psql -f tmp.tmp select now(); commit; -- Jeff Boes Vox 269-226-9550 x24 Director of Software Development Fax 269-349-9076 Exfacto! Exceptional Online Content http://www.exfacto.com Nexcerpt ...Extend Your Expertise...http://www.nexcerpt.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Question on triggers and plpgsql
Hello, A question from a complete newbie on this area. I'm trying to implement a mechanism that would allow me to keep track of the last time each row of a table was modified. I have many applications modifying the data, and I would like to avoid having to modify each of those applications (with the risk of forgetting one of them). So, I figured a better approach would be a trigger that gets activated on update (before update, to be specific). Below is what I came up with, but being the very first time I do (or even read about) something with triggers or with plpgsql, I'd like to check if there are any obvious red flags, or if what I'm doing is hopelessly wrong. I added a column last_modified (timestamp data type), and create the following function: create function set_last_modified() returns trigger as ' begin new.last_modified = now(); return new; end; ' language plpgsql; (this is similar to an example from the PG documentation; I'm not sure the keyword "new" is the right thing to use in my case, but it would look like it's a standard way to refer to the "new row" that is about to replace the old one) Then, I created the trigger as follows: create trigger last_modified_on_update before update on table_name for each row execute procedure set_last_modified(); The thing seems to work -- I had to go in a shell as user postgres and execute the command: $ createlang -d dbname plpgsql (I'm not sure I understand why that is necessary, or what implications -- positive or negative -- it may have) Am I doing the right thing? Have I introduced some sort of catastrophe waiting to happen? Thanks for any guidance you may offer to this PL/PGSQL beginner! Carlos -- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Table PARTITION
* Richard Huxton wrote: > Sean Davis wrote: > >This is a totally selfish question, but IF someone has a few minutes, > >could he/she explain why table partitioning is such an important tool? > > Say you have a large log-table, you could partition it by month. If most > queries only search the last month or two, a lot of your partitioned > data could be moved to cheaper/slower disks (via tablespaces). You can solve this problem with multiple tables rules quite easily. At this point you can also filter out some unused data (often historical data requires less information than live data, because only the end result of certain finished things is interesting for the future, but many things needed as long as things are open are completely irrelevant for later usage, i.e. an archive of accounting information for webhosters wont require datails of single http requests) Lets give me some examples on one of my customer's projects: At fXignal - an forex market trading platform - we're maintaining an long-time archive of all run orders. An "open" trade (you've bought some position) has one order, while an "closed" trade (things are sold again) has two. I.g we've got two kind of accesses to trade information: a) viewing and manipulating open trades - active trading (must be fast!) b) only viewing closed trades for reports (account report, etc) Also we've got some information which are only interesting for open trades, ie. limits (points where trade should be closed automatically). We've solved this by having two tables: one for open trades and one for archived (closed) trades. When an trade is opened, it goes to the open-trade table and resides there until it goes to closed state (by setting a "closed" flag). Once the trades has reached closed state its copied to the archive table and removed from the open trade table by an rule. (see CREATE RULE). When archived trades get old (3 month) we need less information from that, which has to be kept very long (several years). For that we catch the DELETE on the archive table and copy data to the longtime archive before it gets removed from the archive table. For long time analyses we've got some views which map together interesting information from all tables. Well, this way we've got the same benefits as with partitions, with a little bit more coding work, but then with better control and filtering out unneeded stuff. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Table PARTITION
Yes, I will do in this way Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Enrico Weigelt Sent: Friday, April 08, 2005 7:18 AM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Table PARTITION * Richard Huxton wrote: > Sean Davis wrote: > >This is a totally selfish question, but IF someone has a few minutes, > >could he/she explain why table partitioning is such an important tool? > > Say you have a large log-table, you could partition it by month. If > most queries only search the last month or two, a lot of your > partitioned data could be moved to cheaper/slower disks (via tablespaces). You can solve this problem with multiple tables rules quite easily. At this point you can also filter out some unused data (often historical data requires less information than live data, because only the end result of certain finished things is interesting for the future, but many things needed as long as things are open are completely irrelevant for later usage, i.e. an archive of accounting information for webhosters wont require datails of single http requests) Lets give me some examples on one of my customer's projects: At fXignal - an forex market trading platform - we're maintaining an long-time archive of all run orders. An "open" trade (you've bought some position) has one order, while an "closed" trade (things are sold again) has two. I.g we've got two kind of accesses to trade information: a) viewing and manipulating open trades - active trading (must be fast!) b) only viewing closed trades for reports (account report, etc) Also we've got some information which are only interesting for open trades, ie. limits (points where trade should be closed automatically). We've solved this by having two tables: one for open trades and one for archived (closed) trades. When an trade is opened, it goes to the open-trade table and resides there until it goes to closed state (by setting a "closed" flag). Once the trades has reached closed state its copied to the archive table and removed from the open trade table by an rule. (see CREATE RULE). When archived trades get old (3 month) we need less information from that, which has to be kept very long (several years). For that we catch the DELETE on the archive table and copy data to the longtime archive before it gets removed from the archive table. For long time analyses we've got some views which map together interesting information from all tables. Well, this way we've got the same benefits as with partitions, with a little bit more coding work, but then with better control and filtering out unneeded stuff. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
