Re: [SQL] Shorthand column labels (SELECT col1 foo, ...) vs (SELECT col1 AS foo, ...)
Ken Johanson wrote: Interesting thread(s)! What I didn't see discussed was the possibility of making a server and/or session option, where we could elect to turn-off the old behavior (PG specific behavior) and enable the standard/shorthand syntax. Users need a migration path. I personally cant ever see using those PGisms/features and would choose to enable the standard mode. I think I'd have fewer compatibility problems. Ken What was discussed is that the AS keyword is required because of the way the interpreter parses the commands. With the example given, how does it know that 1::character varying isn't casting to a character field with an alias of varying or a character varying field with no alias? If there was simply a switch to turn the requirement on or off that's not going to stop things from breaking - the postfix operators still need to be picked up somehow, it's a technical limitation rather than a "let's just be difficult and be incompatible with other dbms's" limitation I think it would be nice as well, I had to migrate a system that didn't bother putting AS in any of it's views/stored procedures etc and went through this pain - but I think having to change everything was worth it to make sure all my scripts were correctly written and free from possible misinterpretation. My thoughts anyway. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Support for SQL TOP clause?
On Thu, Jan 10, 2008 at 02:19:43PM +1100, Phillip Smith wrote: > SELECT * > > FROM Individual > > LIMIT 3 Note that you will have to add an 'order by' clause to guarantee predictable results... -- __ "Nothing is as subjective as reality" Reinoud van Leeuwen[EMAIL PROTECTED] http://www.xs4all.nl/~reinoud __ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Support for SQL TOP clause?
--- On Wed, 1/9/08, Chinyi Woo <[EMAIL PROTECTED]> wrote: > Does Postgresql support query like SELECT *TOP 3* * FROM > Individual ? If I use ORDER BY, I have to write non-sql > code to get the first row in the result set, which I try > to avoid. As you have seen, PostgreSQL's implementation of LIMIT predicate can produce the same results as TOP. This is another way to get these results using neither TOP or Limit, but the query will probably not perform as quickly. SELECT I1.name, ... FROM Individual AS I1 INNER JOIN Individual AS I2 ON I1.name < I2.name WHERE --any where criteria you might have GROUP BY I1.name, ... HAVING COUNT(*) < 3 ORDER BY I1.name; Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] trigger for TRUNCATE?
Pavel Stehule wrote:
On 08/01/2008, Chris Browne <[EMAIL PROTECTED]> wrote:
[EMAIL PROTECTED] (Gerardo Herzig) writes:
Hi all. Acording to the docs, TRUNCATE will not fire a DELETE trigger
on the table being truncated.
There is a way to capture a TRUNCATE in any way?
I think there's some sort of "to do" on that...
It ought to be not *too* difficult (I imagine!) to be able to
associate a trigger with the TRUNCATE action, and therefore run some
stored function any time TRUNCATE takes place.
For the Slony-I replication system, it would be attractive for this to
lead to attaching two functions:
- One function would return an exception so that TRUNCATE against
a subscriber node would fail...
- Another would pretty much be as simple as submitting an event;
perform createEvent('_ourcluster', 'TRUNCATE_TABLE', table_id);
A new event, TRUNCATE_TABLE, would do a TRUNCATE against the
subscribers.
This represents a pretty easy enhancement, given the new kind of
trigger.
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://www3.sympatico.ca/cbbrowne/nonrdbms.html
Frisbeetarianism: The belief that when you die, your soul goes up on
the roof and gets stuck...
Hello
theoretically you can have trigger on any statement, but I am not sure
about conformance with std. But, you can wrap TRUNCATE statement into
some procedure, and then call this procedure with some other actions.
Regards
Pavel Stehule
Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level
thing than i think.
Gerardo
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] trigger for TRUNCATE?
Gerardo Herzig escribió: > Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level > thing than i think. TRUNCATE currently does not fire triggers, but that doesn't mean it's impossible to do it. I think it would be fairly easy to add support for that. Currently, Mammoth Replicator does replicate TRUNCATE commands. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trigger for TRUNCATE?
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Gerardo Herzig escribió: >> Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level >> thing than i think. > TRUNCATE currently does not fire triggers, but that doesn't mean it's > impossible to do it. I think it would be fairly easy to add support > for that. The entire point of TRUNCATE is to not do a table scan, so making it fire per-row triggers seems pretty misguided to me. We could maybe make it fire per-statement ON DELETE triggers, but there's a future-proofing pitfall in that: someday it'd be nice for statement-level triggers to have access to the set of deleted rows, and then you'd be stuck either scanning the table or having TRUNCATE act differently from plain DELETE. My feeling is that if you want to know what was deleted, you shouldn't use TRUNCATE. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] trigger for TRUNCATE?
Tom Lane escribió: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Gerardo Herzig escribi�: > >> Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level > >> thing than i think. > > > TRUNCATE currently does not fire triggers, but that doesn't mean it's > > impossible to do it. I think it would be fairly easy to add support > > for that. > > The entire point of TRUNCATE is to not do a table scan, so making it > fire per-row triggers seems pretty misguided to me. My thinking is that a TRUNCATE trigger is a per-statement trigger which doesn't have access to the set of deleted rows (Replicator uses it that way -- we replicate the truncate action, and replay it on the replica). In that way it would be different from a per-statement trigger for DELETE. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] trigger for TRUNCATE?
Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: Gerardo Herzig escribió: Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level thing than i think. TRUNCATE currently does not fire triggers, but that doesn't mean it's impossible to do it. I think it would be fairly easy to add support for that. The entire point of TRUNCATE is to not do a table scan, so making it fire per-row triggers seems pretty misguided to me. We could maybe make it fire per-statement ON DELETE triggers, but there's a future-proofing pitfall in that: someday it'd be nice for statement-level triggers to have access to the set of deleted rows, and then you'd be stuck either scanning the table or having TRUNCATE act differently from plain DELETE. My feeling is that if you want to know what was deleted, you shouldn't use TRUNCATE. regards, tom lane I 100% agree, i can live using delete instead, but i can't ensure the whole team i work with will not use TRUNCATE. It was my bad naming the thread with such a contradictory name, im just looking the way to capture it in any form. I would even consider the posibility of *ignoring* a TRUNCATE command, if thats possible. Thanks you all, dudes! Gerardo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] trigger for TRUNCATE?
Alvaro Herrera <[EMAIL PROTECTED]> writes: > My thinking is that a TRUNCATE trigger is a per-statement trigger which > doesn't have access to the set of deleted rows (Replicator uses it that > way -- we replicate the truncate action, and replay it on the replica). > In that way it would be different from a per-statement trigger for > DELETE. Ah, right. I was thinking in terms of having TRUNCATE actually fire the existing ON DELETE-type triggers, but that's not really helpful --- you'd need a separate trigger-event type. So we could just say by fiat that an ON TRUNCATE trigger doesn't get any rowset information, even after we add that for the other types of statement-level triggers. Never mind ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] trigger for TRUNCATE?
Alvaro Herrera wrote: > My thinking is that a TRUNCATE trigger is a per-statement trigger which > doesn't have access to the set of deleted rows. > In that way it would be different from a per-statement trigger for > DELETE. Completely agree. A truncate trigger should run a different function to a delete trigger. This is an important feature for trigger-based replication systems. Not just slony, but bucardo and others too. It's an embarrassing hole in our high availability capabilities and we really need to fill the gap. We can't always control whether an application will issue truncates or not. Rather spookily that's what I've been working on this afternoon, though I didn't realise this thread was in progress until now, nor did I realise there might be possible objections. I do hope the importance of it is enough to overcome objections. Yes, it does look fairly straightforward. Should be ready for when 8.4 opens, assuming we agree. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Escape sequence for UTF-8 Character Literals?
I’ve a data set in a text file which uses the U+ syntax for UCS characters which I want to enter into a (utf8) db, using the actual characters rather than the codepoint names. The docs give the impression that eg E'\x91D1' ought to be the same as '金', but my tests show that \x only accepts 2 hex digits. Is doing the conversion client side the only way to do this? I’m on 8.2, if the answer is version-dependent. -JimC -- James Cloos <[EMAIL PROTECTED]> OpenPGP: 1024D/ED7DAEA6 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] trigger for TRUNCATE?
[EMAIL PROTECTED] (Tom Lane) writes:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
>> Gerardo Herzig escribió:
>>> Yes, the TRUNCATE statement is not sql ansi, maybe is a more low level
>>> thing than i think.
>
>> TRUNCATE currently does not fire triggers, but that doesn't mean it's
>> impossible to do it. I think it would be fairly easy to add support
>> for that.
>
> The entire point of TRUNCATE is to not do a table scan, so making it
> fire per-row triggers seems pretty misguided to me.
>
> We could maybe make it fire per-statement ON DELETE triggers, but
> there's a future-proofing pitfall in that: someday it'd be nice
> for statement-level triggers to have access to the set of deleted rows,
> and then you'd be stuck either scanning the table or having TRUNCATE
> act differently from plain DELETE.
>
> My feeling is that if you want to know what was deleted, you shouldn't
> use TRUNCATE.
No, what would be nice to have is NOT per-row triggering, but rather
simply the ability to run a stored function ON TRUNCATE.
This would be useful for Slony-I:
- On replica nodes, we might add a trigger:
create trigger t_trunc before truncate on my_table for each statement execute
_sl_cluster.deny_truncate();
which would raise the error: "Slony-I: Cannot TRUNCATE on subscriber node!"
- On the "master" we might add a trigger:
create trigger t_trunc before truncate on my_table for each statement execute
_sl_cluster.createEvent('sl_cluster', 'TRUNCATE_TABLE', 14);
which would generate a 'TRUNCATE_TABLE' event that would tell other nodes to
truncate table #14, that is, my_table.
For the case where people want to track "COUNT(*)" on a table using
triggers, TRUNCATE presently throws that off. With a truncate
trigger, we might implement the following:
create trigger t_trunc before truncate on my_table for each statement execute
purge_table('public', 'my_table');
create or replace function purge_table (text,text) returns null as $$
delete from count_summary_table where nspname = $1 and tabname = $2
$$ language sql;
That's three use cases, so far, none of which expect to have access to
the data that is being truncated.
--
"cbbrowne","@","acm.org"
http://linuxfinances.info/info/rdbms.html
Security-wise, NT is a server with a "Kick me" sign taped to it.
-- Peter Gutmann in the Scary Devil Monastery
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] JOIN a table twice for different values in the same query
Greetings.
I have two tables I'm having a little trouble figuring out how to JOIN.
One contains a list of airports along with their IATA codes, cities,
names, and so forth. This table also contains an id column, which is a
serial primary key.
The other table contains a list of flights, each of which has a
departure_port and an arrival_port, which are foreign keys referencing
the id field of the first table.
I would like to construct a query on the flight table that returns the
names of both the departure port and the arrival port.
The following query shows how I would get just the departure port.
js=# SELECT departure_date, jsports.code AS departure_code FROM
jsjourneys JOIN jsports ON jsjourneys.departure_port = jsports.id LIMIT
4;
departure_date | departure_code
+
2006-11-19 | ATL
2006-11-16 | ATL
2006-11-19 | BHM
2007-02-03 | BOS
(4 rows)
When I SELECT jsports.code, the result comes from the JOIN ... ON
jsjourneys.departure_port = jsports.id.
I would *also* like to include something in the query to get the
jsports.code for jsjourneys.arrival_port, but I'm unsure how to do this,
since SELECTing jsports.code twice would be ambiguous (and, in any case,
just duplicates the departure_code).
I'd like to produce a result set that looks something like the following
(which doesn't come from a real query).
departure_date | departure_code | arrival_code
++--
2006-11-19 | ATL| JFK
2006-11-16 | ATL| DFW
2006-11-19 | BHM| IAH
2007-02-03 | BOS| LAX
I'd appreciate some help.
FYI, table definitions for jsjourneys and jsports follow.
js=# \d jsjourneys
Table "public.jsjourneys"
Column| Type |
Modifiers
-+--+-
id | bigint | not null default
nextval('jsjourneys_id_seq'::regclass)
userid | bigint | not null
typeid | integer | not null
carrier | integer |
number | integer |
departure_port | integer | not null
arrival_port| integer | not null
departure_gate | character varying|
arrival_gate| character varying|
departure_date | date | not null
fare_class | integer |
scheduled_departure | timestamp with time zone |
scheduled_arrival | timestamp with time zone |
actual_departure| timestamp with time zone |
actual_arrival | timestamp with time zone |
equipment | integer |
notes | character varying(1500) |
seat| character varying(4) |
confirmation| character varying(20)|
Indexes:
"jsjourneys_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"jsjourneys_arrival_port_fkey" FOREIGN KEY (arrival_port)
REFERENCES jsports(id)
"jsjourneys_carrier_fkey" FOREIGN KEY (carrier) REFERENCES
jscarriers(id)
"jsjourneys_departure_port_fkey" FOREIGN KEY (departure_port)
REFERENCES jsports(id)
"jsjourneys_equipment_fkey" FOREIGN KEY (equipment) REFERENCES
jsequipment(id)
"jsjourneys_fare_class_fkey" FOREIGN KEY (fare_class) REFERENCES
jsfareclasses(id)
"jsjourneys_typeid_fkey" FOREIGN KEY (typeid) REFERENCES
jsjourneytypes(id)
"jsjourneys_userid_fkey" FOREIGN KEY (userid) REFERENCES jsusers(id)
js=# \d jsports
Table "public.jsports"
Column | Type| Modifiers
---+---+--
id| integer | not null default
nextval('jsports_id_seq'::regclass)
code | character varying | not null
city | character varying | not null
full_city | character varying | not null
name | character varying |
Indexes:
"jsports_pkey" PRIMARY KEY, btree (id)
"jsports_index_city" btree (city)
"jsports_index_code" btree (code)
Thanks!
Colin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] JOIN a table twice for different values in the same query
Colin Wetherbee wrote:
Greetings.
I have two tables I'm having a little trouble figuring out how to JOIN.
One contains a list of airports along with their IATA codes, cities,
names, and so forth. This table also contains an id column, which is a
serial primary key.
The other table contains a list of flights, each of which has a
departure_port and an arrival_port, which are foreign keys referencing
the id field of the first table.
I would like to construct a query on the flight table that returns the
names of both the departure port and the arrival port.
The following query shows how I would get just the departure port.
js=# SELECT departure_date, jsports.code AS departure_code FROM
jsjourneys JOIN jsports ON jsjourneys.departure_port = jsports.id LIMIT
4;
departure_date | departure_code
+
2006-11-19 | ATL
2006-11-16 | ATL
2006-11-19 | BHM
2007-02-03 | BOS
(4 rows)
When I SELECT jsports.code, the result comes from the JOIN ... ON
jsjourneys.departure_port = jsports.id.
I would *also* like to include something in the query to get the
jsports.code for jsjourneys.arrival_port, but I'm unsure how to do this,
since SELECTing jsports.code twice would be ambiguous (and, in any case,
just duplicates the departure_code).
I'd like to produce a result set that looks something like the following
(which doesn't come from a real query).
departure_date | departure_code | arrival_code
++--
2006-11-19 | ATL| JFK
2006-11-16 | ATL| DFW
2006-11-19 | BHM| IAH
2007-02-03 | BOS| LAX
I'd appreciate some help.
FYI, table definitions for jsjourneys and jsports follow.
js=# \d jsjourneys
Table "public.jsjourneys"
Column| Type | Modifiers
-+--+-
id | bigint | not null default
nextval('jsjourneys_id_seq'::regclass)
userid | bigint | not null
typeid | integer | not null
carrier | integer |
number | integer |
departure_port | integer | not null
arrival_port| integer | not null
departure_gate | character varying|
arrival_gate| character varying|
departure_date | date | not null
fare_class | integer |
scheduled_departure | timestamp with time zone |
scheduled_arrival | timestamp with time zone |
actual_departure| timestamp with time zone |
actual_arrival | timestamp with time zone |
equipment | integer |
notes | character varying(1500) |
seat| character varying(4) |
confirmation| character varying(20)|
Indexes:
"jsjourneys_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"jsjourneys_arrival_port_fkey" FOREIGN KEY (arrival_port) REFERENCES
jsports(id)
"jsjourneys_carrier_fkey" FOREIGN KEY (carrier) REFERENCES
jscarriers(id)
"jsjourneys_departure_port_fkey" FOREIGN KEY (departure_port)
REFERENCES jsports(id)
"jsjourneys_equipment_fkey" FOREIGN KEY (equipment) REFERENCES
jsequipment(id)
"jsjourneys_fare_class_fkey" FOREIGN KEY (fare_class) REFERENCES
jsfareclasses(id)
"jsjourneys_typeid_fkey" FOREIGN KEY (typeid) REFERENCES
jsjourneytypes(id)
"jsjourneys_userid_fkey" FOREIGN KEY (userid) REFERENCES jsusers(id)
js=# \d jsports
Table "public.jsports"
Column | Type| Modifiers
---+---+--
id| integer | not null default
nextval('jsports_id_seq'::regclass)
code | character varying | not null
city | character varying | not null
full_city | character varying | not null
name | character varying |
Indexes:
"jsports_pkey" PRIMARY KEY, btree (id)
"jsports_index_city" btree (city)
"jsports_index_code" btree (code)
Thanks!
Colin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Try joining twice, something like:
SELECT departure_date,
dp.code AS departure_code,
ap.code AS arrival_code
FROM jsjourneys
JOIN jsports dp ON jsjourneys.departure_port = jsports.id
JOIN jsports ap ON jsjourneys.arrival_port=jsports.id
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] JOIN a table twice for different values in the same query
js=# SELECT departure_date, departure.code AS departure_code, arrival.code as
arraival_codeFROM jsjourneys JOIN jsports as departure ON
jsjourneys.departure_port = departure.id JOIN jsports as arrival on
jsjourneys.arraival_port = arraival.id LIMIT4;
Regards,Daniel Hernández.San Diego, CA."The more you learn, more you
earn". --- On Thu 01/10, Colin Wetherbee < [EMAIL PROTECTED] >
wrote:From: Colin Wetherbee [mailto: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Thu,
10 Jan 2008 17:07:00 -0500Subject: [SQL] JOIN a table twice for different
values in the same queryGreetings.I have two tables I'm having a little trouble
figuring out how to JOIN.One contains a list of airports along with their IATA
codes, cities, names, and so forth. This table also contains an id column,
which is a serial primary key.The other table contains a list of flights, each
of which has a departure_port and an arrival_port, which are foreign keys
referencing the id field of the first table.I would like to construct a query
on the flight table that returns the names of both the departure port and the
arrival port.The following query shows how I would get just the departure
port.js=# SELECT departure_date, jsports.code AS
departure_code FROMjsjourneys JOIN jsports ON jsjourneys.departure_port =
jsports.id LIMIT4; departure_date |
departure_code+ 2006-11-19 | ATL
2006-11-16 | ATL 2006-11-19 | BHM 2007-02-03 | BOS(4 rows)When I
SELECT jsports.code, the result comes from the JOIN ... ON
jsjourneys.departure_port = jsports.id.I would *also* like to include something
in the query to get the jsports.code for jsjourneys.arrival_port, but I'm
unsure how to do this, since SELECTing jsports.code twice would be ambiguous
(and, in any case, just duplicates the departure_code).I'd like to produce a
result set that looks something like the following (which doesn't come from a
real query). departure_date | departure_code |
arrival_code++-- 2006-11-19 |
ATL| JFK 2006-11-16 | ATL| DFW 2006-11-19 |
BHM| IAH 2007-02-03 | BOS| LAXI'd appreciate some
help.FYI, table definitions for jsjourneys and jsports follow.js=# \d
jsjourneys Table
"public.jsjourneys"Column| Type |
Modifiers-+--+-
id | bigint | not null default
nextval('jsjourneys_id_seq'::regclass) userid | bigint
| not null typeid | integer | not null
carrier | integer | number | integer
| departure_port | integer | not null
arrival_port| integer | not null departure_gate
| character varying| arrival_gate| character varying|
departure_date | date | not null fare_class
| integer |
scheduled_departure | timestamp with time zone | scheduled_arrival |
timestamp with time zone | actual_departure| timestamp with time zone |
actual_arrival | timestamp with time zone | equipment | integer
| notes | character varying(1500) | seat
| character varying(4) | confirmation| character
varying(20)|Indexes: "jsjourneys_pkey" PRIMARY KEY, btree
(id)Foreign-key constraints: "jsjourneys_arrival_port_fkey"
FOREIGN KEY (arrival_port) REFERENCES jsports(id)
"jsjourneys_carrier_fkey" FOREIGN KEY (carrier) REFERENCES
jscarriers(id) "jsjourneys_departure_port_fkey" FOREIGN KEY
(departure_port) REFERENCES jsports(id)
"jsjourneys_equipment_fkey" FOREIGN KEY (equipment) REFERENCES
jsequipment(id) "jsjourneys_fare_class_fkey" FOREIGN KEY
(fare_class) REFERENCES jsfareclasses(id)
"jsjourneys_typeid_fkey" FOREIGN KEY (typeid) REFERENCES
jsjourneytypes(id) "jsjourneys_userid_fkey" FOREIGN KEY (userid)
REFERENCES jsusers(id)js=# \d jsports Table
"public.jsports" Column | Type|
Modifiers
---+---+--
id| integer | not null default
nextval('jsports_id_seq'::regclass) code | character varying | not null
city | character varying | not null full_city | character varying | not
null name | character varying |Indexes: "jsports_pkey"
PRIMARY KEY, btree (id) "jsports_index_city" btree (city)
"jsports_index_code" btree
(code)Thanks!Colin---(end of
broadcast)---TIP 4: Have you searc
Re: [SQL] JOIN a table twice for different values in the same query
Paul Lambert wrote: Colin Wetherbee wrote: I would like to construct a query on the flight table that returns the names of both the departure port and the arrival port. The following query shows how I would get just the departure port. js=# SELECT departure_date, jsports.code AS departure_code FROM jsjourneys JOIN jsports ON jsjourneys.departure_port = jsports.id LIMIT 4; Try joining twice, something like: SELECT departure_date, dp.code AS departure_code, ap.code AS arrival_code FROM jsjourneys JOIN jsports dp ON jsjourneys.departure_port = jsports.id JOIN jsports ap ON jsjourneys.arrival_port=jsports.id ---(end of broadcast)--- TIP 6: explain analyze is your friend Ah, I didn't realize you could alias tables inside the JOIN. Excellent. It works. :) js=# SELECT departure_date, dp.code AS departure_code, ap.code AS arrival_code FROM jsjourneys JOIN jsports dp ON jsjourneys.departure_port = dp.id JOIN jsports ap ON jsjourneys.arrival_port = ap.id LIMIT 4; departure_date | departure_code | arrival_code ++-- 2006-11-19 | BHM| ATL 2006-11-16 | PIT| ATL 2006-11-16 | ATL| BHM 2006-10-26 | PIT| BOS (4 rows) For archive completeness, note the query is joined relative to dp.id and ap.id, rather than jsports.id. Thanks for your help! Colin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] JOIN a table twice for different values in the same query
Try something like this where we alias the joined tables: SELECT departure_date, j1.code AS departure_code, j2.code AS arrival_code FROM jsjourneys LEFT OUTER JOIN jsports AS j1 ON jsjourneys.departure_port = j1.id LEFT OUTER JOIN jsports AS j2 ON jsjourneys.arrival_port = j2.id; As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the port names (not just the code) Cheers, ~p THINK BEFORE YOU PRINT - Save paper if you don't really need to print this ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments e-mail. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] JOIN a table twice for different values in the same query
Phillip Smith wrote: As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the port names (not just the code) This is true, but FWIW, my application will mostly be joining for the name of the airport or the city, not the code. I'll keep the idea of using the codes as keys in mind, though. Thanks for pointing that out. Colin ---(end of broadcast)--- TIP 1: 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] JOIN a table twice for different values in the same query
Colin Wetherbee wrote: Phillip Smith wrote: As a side note - all the IATA codes are unique for each airport - wouldn't it be better to use these as the Primary Key and Foreign Keys? Then you wouldn't have to even join the tables unless you wanted the port names (not just the code) This is true, but FWIW, my application will mostly be joining for the name of the airport or the city, not the code. I'll keep the idea of using the codes as keys in mind, though. Thanks for pointing that out. Oh, now I remember why I'm using IDs as keys. ;) The code isn't always going to be an airport, and, for example, a train station in Buenos Aires could conceivably have the same code as a shipping port in Rotterdam, which, in turn, might well be JFK. :) Colin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] SQL stored function inserting and returning data in a row.
Hi,
Is there any way to define a SQL stored function that inserts a row in a
table and returns the serial generated?
CREATE TABLE matchmaking_session
(
session_id bigint NOT NULL DEFAULT
nextval('seq_matchmaking_session_id'),
...
);
CREATE FUNCTION create_matchmaking_sesssion(...)
RETURNS bigint
AS $$
INSERT INTO matchmaking_session(...)
VALUES (...)
RETURNING session_id;
$$ LANGUAGE SQL;
2008-01-10 22:08:48 EST ERROR: return type mismatch in function
declared to return bigint
2008-01-10 22:08:48 EST DETAIL: Function's final statement must be a
SELECT.
2008-01-10 22:08:48 EST CONTEXT: SQL function
"create_matchmaking_sesssion"
I can easily convert this code into a PL/pgSQL function, but I'm
thinking that pure SQL is more natural (and faster?) for such a stored
function.
Regards,
--
Daniel
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] (possible) bug with constraint exclusion
Hi , looks like constraint exclusion is being too aggressive in excluding null values although its well known that check constraints apply on not null values only. Hope the minimal test session below explains the problem we facing. BTW: we are very impressed with the performance gains we achieved by partitioning a table recently. tradein_clients=> SELECT version(); version --- PostgreSQL 8.2.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) (1 row) tradein_clients=> \pset null NULL tradein_clients=> \d x Table "temp.x" Column | Type | Modifiers +-+--- id | integer | Check constraints: "x_id_check" CHECK (id > 0) tradein_clients=> SELECT * from x; id -- 1 2 NULL (3 rows) tradein_clients=> explain SELECT * from x where id is null; QUERY PLAN -- Result (cost=0.00..0.01 rows=1 width=0) One-Time Filter: false (2 rows) tradein_clients=> SELECT * from x where id is null; id (0 rows) tradein_clients=> SET constraint_exclusion to off; SET tradein_clients=> SELECT * from x where id is null; id -- NULL (1 row) tradein_clients=> Regds mallah. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] (possible) bug with constraint exclusion
Update the phenomenon does not exists in 8.2.0 but exists in 8.2.5. On Jan 11, 2008 12:28 PM, Rajesh Kumar Mallah <[EMAIL PROTECTED]> wrote: > Hi , > > looks like constraint exclusion is being too aggressive in excluding null > values > although its well known that check constraints apply on not null values only. > Hope the minimal test session below explains the problem we facing. > BTW: we are very impressed with the performance gains we achieved by > partitioning a table recently. > > > > tradein_clients=> SELECT version(); > version > --- > PostgreSQL 8.2.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) > 3.4.6 20060404 (Red Hat 3.4.6-9) > (1 row) > > tradein_clients=> \pset null NULL > > > tradein_clients=> \d x > Table "temp.x" > Column | Type | Modifiers > +-+--- > id | integer | > Check constraints: > "x_id_check" CHECK (id > 0) > > tradein_clients=> SELECT * from x; > id > -- > 1 > 2 > NULL > (3 rows) > > tradein_clients=> explain SELECT * from x where id is null; > QUERY PLAN > -- > Result (cost=0.00..0.01 rows=1 width=0) >One-Time Filter: false > (2 rows) > > tradein_clients=> SELECT * from x where id is null; > id > > (0 rows) > tradein_clients=> SET constraint_exclusion to off; > SET > tradein_clients=> SELECT * from x where id is null; > id > -- > NULL > (1 row) > > tradein_clients=> > > Regds > mallah. > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
