Re: [SQL] Describe Table
[EMAIL PROTECTED] wrote: I've reviewed much of the documentation and the forums, but unable to seem to find a easy way to obtain the same thing as the 'psql \d table' through sql. I know I can create through collecting info on table, but seems there must be an easier way. I desire to create a standard type SQL dump syntax. Briefly, you use the special pg_ tables [0]. The following query is probably not the most efficient way of doing it, but it shows the column names for the "wines" table. The first seven listed are system columns (tableoid - ctid), and the rest are data columns (name - score). You can look at the descriptions for each of the pg_ tables to refine your query a bit, exclude system columns, figure out data types, and so forth. cww=# SELECT pg_class.relname, attname FROM pg_attribute, pg_class WHERE attrelid = pg_class.reltype::integer - 1 AND pg_class.relname = 'wines'; relname | attname -+- wines | tableoid wines | cmax wines | xmax wines | cmin wines | xmin wines | oid wines | ctid wines | name wines | vintage wines | origin wines | specific_origin wines | color wines | type wines | description wines | vintner wines | entry_date wines | score (17 rows) This query works on 8.1.9. Colin [0] http://www.postgresql.org/files/documentation/books/aw_pgsql/node183.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[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
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
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
Re: [SQL] JOIN a table twice for different values in the same query
Magne Mæhre wrote: Colin Wetherbee wrote: 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. :) Note that IATA codes are _NOT_ unique. The current list of IATA trigrams list upward of 300 duplicate codes. If you include the train stations, there might be additional collisions. You could consider using the ICAO four-letter identifiers instead. They are unique, and are preferred by airspace management authorities. A mapping to the corresponding IATA code exists. I have both ICAO and IATA codes in my database, but users who typically won't know (or even be aware of) ICAO codes will be using the front end. In fact, in the front end, the users will see something like the following (with the respective, unique, application-specific port ID hidden in the background). Houston, TX (IAH - George Bush Intercontinental Airport) New York, NY (JFK - John F. Kennedy International Airport) Dubai, United Arab Emirates (DXB - Dubai International Airport) Which should be unique enough. :) Colin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] outer join issues
Tom Hart wrote: Let me preface this by saying hello SQL list, and I'm an idiot. My SQL knowledge is advanced to the point of being able to use a WHERE clause basically, so I appreciate your business. Now on to my issue I have 3 tables I'm trying to use in this query: loan, share and draft (for those of you not familiar with credit unions, share and draft are savings and checking accounts). What I'm trying to do is get a list of all loans that were charged off (ln_chgoff_dt > 0), and any share and draft accounts that have the same account number. My query looks something like this SELECT ln_acct_num, ln_num, ln_chrgoff_dt, ln_chrgoff_amt, sh_balance, sh_stat_cd, df_balance, df_stat_cd FROM loan LEFT OUTER JOIN share ON loan.ln_acct_num = share.sh_acct_num LEFT OUTER JOIN draft ON loan.ln_acct_num = draft.df_acct_num WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 AND share.dataset = 0 AND draft.dataset = 0 ; Now the query SELECT * FROM loan WHERE ln_chrgoff_dt > 0 AND loan.dataset = 0 returns 139 rows. Shouldn't the first query return at least that many? My understanding is that a LEFT OUTER JOIN will not drop any records that are only found in the first table, regardless of whether they match records on the second or third table. I end up with 14 results with the first query. I know I'm doing something wrong, but I'm not sure what. Anybody have a helpful kick in the right direction for me? My "I looked at this for 20 seconds" guess is that the following clauses are messing you up. > AND share.dataset = 0 > AND draft.dataset = 0 The LEFT OUTER JOIN isn't helping you if you're still comparing values in the JOINed tables in the WHERE clause. 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] Bouncing replies [was: SQL standards in Mysql]
Dean Gibson (DB Administrator) wrote: On 2008-02-22 21:34, Scott Marlowe wrote: Bouncing messages from a public list is kinda rude. No more so, than sending two copies of your reply to me, because you don't go up to your mailer's "To:" line and manually delete the extra address (as I do on EVERY reply I send to this list). This is twice in as many days my old iLamp mail machine has been set aflame by the fires of a heated discussion about how a mailing list's reply-to is set. It gets hot enough just running Thunderbird. If you're going to continue this off-topic discussion, might I suggest taking it off-list? Interestingly, yesterday's flame-war took place because someone was adamant about just the opposite of your argument. Colin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Bounce test
Scott Marlowe wrote: On Mon, Feb 25, 2008 at 9:17 PM, Adrian Klaver <[EMAIL PROTECTED]> wrote: On Monday 25 February 2008 7:13 pm, Dean Gibson (DB Administrator) wrote: > I have changed something in my eMail client regarding receiving > messages. If a couple people (who don't mind getting bounces if this > doesn't work) would just "Reply" and/or "Reply All" to this message, I'd > appreciate it. > > Sincerely, Dean Test So, your email client puts Dean's email address back in? Might I ask what option you chose? And if you have more than one? On gmail there's the reply link only. FWIW, if I hit "reply all" on the OP, the only address Thunderbird fills is [EMAIL PROTECTED] Seems "reply-to" works. Colin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Function returns error (view)
Professor Flávio Brito wrote:
When I Test my view I receive
SELECT seach_password('user_login_foo')
[...]
ERROR: column "user_login_foo" does not exist
SQL state: 42703
Context: PL/pgSQL function "search_password" line 14 at for over execute
statement
seach_password and
search_password are different.
Perhaps you have two functions with similar names, and one is broken?
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] finding columns that have three or fewer distinct characters
Jeff Frost wrote:
I've got an interesting one...I'm trying to find columns that have three
or fewer distinct characters (for example, "aa"). Wondering if
I need to write a function or if someone has an idea how to do it with
built in functions and/or pattern matching?
I think the thing to do would be to lowercase everything, then remove
all duplicate chars and spaces, then use length() on that, but it's not
obvious to me how I might remove the duplicate chars with the pattern
matching support in the docs.
It's interesting, indeed.
Here's how you might do it with a PL/Perl function. :)
CREATE OR REPLACE FUNCTION remove_duplicates(TEXT) RETURNS TEXT AS
$$
my ($text) = @_;
while ($text =~ s/(.)(.*)\1/$1$2/g != 0) {};
return $text;
$$ LANGUAGE plperl;
cww=# SELECT
remove_duplicates('[EMAIL PROTECTED]');
remove_duplicates
---
[EMAIL PROTECTED]
(1 row)
Colin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql
Re: [SQL] postgres server crashes unexpectedly
Chadwick Horn wrote: It looks to me like psql is managing to start a new connection before the postmaster notices the crash of the prior backend and tells everybody to get out of town. Which is odd, but maybe not too implausible if your kernel is set up to favor interactive processes over background --- it'd likely think psql is interactive and the postmaster isn't. Is there a way to disable this or to make both interactive and/or background? I'm not sure how applications tell the kernel whether they are interactive or background (or even if they do, at all), but you can set the kernel's preference for this in the kernel configuration. If you're not comfortable recompiling a new kernel, though, then you're out of luck. At any rate, you should look more thoroughly for problems with your database before blaming the kernel for something. Colin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] First day of month, last day of month
Frank Bax wrote:
Frank Bax wrote:
Nacef LABIDI wrote:
is there a better method to retrieve all the rows with dates in the
current month.
select * from mytable where extract(month from mydate) = extract(month
from now()) and extract(year from mydate) = extract(year from now());
Sorry; I was not thinking clearly - date_trunc is better for this:
select * from mytable where date_trunc('month',mydate) =
date_trunc('month',now());
I have some code that uses extract() for this sort of thing. Would you
mind explaining how date_trunc() is better for this?
Most of my extract() results end up in drop-down boxes in HTML.
Thanks.
Colin
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
