Re: [SQL] Help On Postgresql
a) Either of these should return the yaer (2001). See section 4.7 of the
user guide
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
SELECT date_part('year', TIMESTAMP '2001-02-16 20:38:40');
b) The serial datatype will do this. It is actually just an int4 with a
default value of nextval('').
- Stuart
> -Original Message-
> From: Jaydip [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, August 23, 2001 8:51 AM
> To: [EMAIL PROTECTED]
> Subject: Help On Postgresql
>
> Dear Friends
>
> I am Jaydip Dewanji working as a Sr. Programmer for Ontrack Systems Ltd.
>
> I have some questions regarding Postgresql which are listed below:
>
> a) How do I retrieve the Year,Month,Day,Hr,Minute or Second value from a
> Date/DateTime variable.
>
> For Example : i> In Oracle to_char(dt,'') - for extracting the
> year value from a date variable dt
> ii> In SqlServer datepart(hh,dt) - for extracting
> the Hour value from a date variable dt
>
> b) Can I create an identity column in Postgresql the value of which column
> will start from 1 say and keep
> on incrementing by 1 OR how can I serve my purpose in any other way.
>
>
> Please do let me know if any one of you have any solution.
>
> Regards
>
> Jaydip
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] 2 tables, joins and same name...
Hello, Here is 2 tables: airport - airport_id name code city_id destination --- destination_id dest_name ... airport_dep_id // using airport.airport_id (departure) airport_arr_id // using airport.airport_id has well (arrival) I have 2 columns in the second table that uses the same name column in the first table... I dont know how to formulate my SQL query... I want to select the destinations in the destination table with not the ID of each airport but their names. I can do a join with one but with the second one, I get no results... And this is confusing! select dest.dest_name, air.name as airport1, air.name as airport2 from destination, airport air where dest.airport_dep_id_id=air.airport_id and dest.airport_arr_id=air.airport_id; This is not good... Any help? Thanks! -- Marc Andre Paquin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Index Scan Backward vs. Sort/Sequential Scan when using ORDER BY
Greetings, I have stumbled upon a confusing aspect of PostgreSQL queries involving ORDER BY. In trying to figure out just why my ORDER BY queries were so slow, I came across something interesting. First, let me give you 2 very similar queries: 1) SELECT p.uid FROM client_profiles p INNER JOIN client_profiles_2 c USING(uid) WHERE (p.profiles_gender='M') AND (p.profiles_orientation[2] = 'F' OR p.profiles_orientation[1]='M') ORDER BY c.profiles_2_last_update DESC LIMIT 5; 2) SELECT p.uid FROM client_profiles p INNER JOIN client_profiles_2 c USING(uid) WHERE (p.profiles_gender='F') AND (p.profiles_orientation[2] = 'F' OR p.profiles_orientation[1]='M') ORDER BY c.profiles_2_last_update DESC LIMIT 5; The only difference is in #1, p.profiles_gender='M' while in #2 p.profiles_gender='F'. SELECT count(uid) FROM client_profiles WHERE profiles_gender='M'; -- 408526 SELECT count(uid) FROM client_profiles WHERE profiles_gender='F'; -- 54713 Here are the EXPLAINs: 1) EXPLAIN #1: Limit (cost=0.00..1763.83 rows=5 width=24) -> Nested Loop (cost=0.00..2203068.58 rows=6245 width=24) -> Index Scan Backward using index_client_profiles_2_last_up on client_profiles_2 c (cost=0.00..239553.52 rows=394263 width=16) -> Index Scan using client_profiles_pkey on client_profiles p (cost=0.00..4.97 rows=1 width=8) 2) EXPLAIN #2: Limit (cost=36046.44..36046.44 rows=5 width=24) -> Sort (cost=36046.44..36046.44 rows=160 width=24) -> Nested Loop (cost=0.00..36040.58 rows=160 width=24) -> Index Scan using index_client_profiles_gender on client_profiles p (cost=0.00..35064.98 rows=198 width=8) -> Index Scan using client_profiles_2_pkey on client_profiles_2 c (cost=0.00..4.91 rows=1 width=16) Now the only reason I can see to explain this is because there are many more p.profiles_gender='M' than p.profiles_gender='F', Postgres knows its faster to do a Index Scan Backward than a Sort/Sequential Scan (and trust me, it is!). However it thinks the opposite when I am searching for p.profiles_gender='F' and form my results, that just isn't true. Does anyone have any insight as to how I can 'force' postgres to use Index Backward Scan for #2??? Or, perhaps another method of making my ORDER BY faster ?? Your help would be greatly appreciated, Thanks -- Keith Bussey [EMAIL PROTECTED] Programmer - WISOL.com (514) 398-9994 ext. 225 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] 2 tables, joins and same name...
On Thu, Aug 30, 2001 at 04:25:41PM -0400, Marc André Paquin wrote: > Hello, > > Here is 2 tables: > > airport > - > airport_id > name > code > city_id > > destination > --- > destination_id > dest_name > ... > airport_dep_id // using airport.airport_id (departure) > airport_arr_id // using airport.airport_id has well (arrival) > > I have 2 columns in the second table that uses the same name column in > the first table... > > I dont know how to formulate my SQL query... I want to select the > destinations in the destination table with not the ID of each airport > but their names. I can do a join with one but with the second one, I get > no results... And this is confusing! > > select dest.dest_name, air.name as airport1, air.name as airport2 from > destination, airport air where dest.airport_dep_id_id=air.airport_id and > dest.airport_arr_id=air.airport_id; You have to join against the airport table twice: SELECT dest.dest_name, air1.name as airport1, air2.name as airport2 FROM desination dest, airport air1, airport 2 WHERE dest.airport_dep_id = air1.airport_id AND dest.airport_arr_id = air2.airport_id; Richard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 2 tables, joins and same name...
Marc, > Yes, but I used this instead: > select dest.dest_name, air1.name as airport1, air2.name as airport2 > from > destination, airport air1, airport air2 where dest.airport_dep_id= > air1.airport_id and dest.airport_arr_id=air2.airport_id; > > This is very similar to your query... I think the join is implicit > instead of explicit like yours. You are correct. That query should work fine. It will be useful for you to know both join syntaxes. There are times when an explicit join is required (LEFT OUTER JOIN, for example) and usually it's clearer for others to read in your code. > PS for the book, yes It could be useful but when you dont know what > to > look for (I have 2 SQL book reference), I just found out that this is > a > self join. That's why we have the list. Personally, I'm still looking for a comprehensive introductory SQL book to recommend. The ones I know are either too simple and not that accurate (SQL for Dummies), proprietary (MS SQL Server in 24 hours), too short (PostgreSQL Introduction and Concepts*), or too advanced for the newbie (SQL for Smarties). I reccomended the PostgreSQL book for a variety of reasons, not the least of which is the number of languages it's been translated into. I know from personal experience that it is hard enough finding the right reference in your native language. Plus Bruce provides quite a number of good examples. Plus Bruce is on this list. Hi, Bruce! And, technically, what you did is not a "self join". This would be a Self Join: SELECT node1.id, node2.id FROM nodes node1 JOIN nodes node2 ON node1.id = node2.parent_id; ... where you are joining a table to itself. Makes sense, yes? What you did was join the same table, twice, to a third table. I don't believe that this structure has a particular name. It's very common. -Josh Berkus *= Bruce, what I mean by "too short" is that you only have about 80 pages of introduction to SQL, which makes it a good first intro but does not bridge the gap between "What's a query?" and Fabian Pascal. Which makes it good but not comprehensive. __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] 2 tables, joins and same name...
Marc André Paquin wrote: > Here is 2 tables: > > airport > - > airport_id > name > code > city_id > > destination > --- > destination_id > dest_name > ... > airport_dep_id // using airport.airport_id (departure) > airport_arr_id // using airport.airport_id has well (arrival) > > I have 2 columns in the second table that uses the same name column in > the first table... > > I dont know how to formulate my SQL query... I want to select the > destinations in the destination table with not the ID of each airport > but their names. I can do a join with one but with the second one, I > get no results... And this is confusing! May be I'm wrong, but I think you mean something like this one select dest_name , air1.name as airport1 , air2.name as airport2 from destination join airport as air1 on air1.airport_id = destination.airport_dep_id join airport as air2 on air2.airport_id = destination.airport_arr_id Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 2 tables, joins and same name...
Marc, > I dont know how to formulate my SQL query... I want to select the > destinations in the destination table with not the ID of each airport > but their names. I can do a join with one but with the second one, I > get > no results... And this is confusing! Whenever you want to join to the same table twice, you need to use your table aliases to distinguish between instances of the same table. The way it's written, the query parser cannot distinguish between the two instances of the airport table ... so it thinks you're asking for all flights where the departure and arrival airport are the same. Which, of course, is none. I'll help with your immediate problem, and then I *highly* suggest you go out and buy (and read!) Bruce Momjian's book "PostgreSQL: Introduction and Concepts." (which I believe has been translated if languages are an issue) > select dest.dest_name, air.name as airport1, air.name as airport2 > from > destination, airport air where dest.airport_dep_id_id=air.airport_id > and > dest.airport_arr_id=air.airport_id; SELECT dest.dest_name, depart_air.name as airport1, arrive_air.name as airport2 FROM desitination dest JOIN airport depart_air ON dest.airport_dep_id=depart_air.airport_id JOIN airport arrive_air ON dest.airport_arr_id=arrive_air.airport_id Got it? -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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] 2 tables, joins and same name...
On Thu, 30 Aug 2001, Marc [iso-8859-1] André Paquin wrote: > Hello, > > Here is 2 tables: > > airport > - > airport_id > name > code > city_id > > destination > --- > destination_id > dest_name > ... > airport_dep_id // using airport.airport_id (departure) > airport_arr_id // using airport.airport_id has well (arrival) > > I have 2 columns in the second table that uses the same name column in > the first table... > > I dont know how to formulate my SQL query... I want to select the > destinations in the destination table with not the ID of each airport > but their names. I can do a join with one but with the second one, I get > no results... And this is confusing! > > select dest.dest_name, air.name as airport1, air.name as airport2 from > destination, airport air where dest.airport_dep_id_id=air.airport_id and > dest.airport_arr_id=air.airport_id; You probably want to join airport twice because you want two different airports. Your query would only get flights from one airport to itself (look at the where condition, you're saying that the row in airport must have an id that is equal to the departure id *and* is equal to the arrival id). Probably this: select dest.dest_name, air1.name as airport1, air2.name as airport2 from destination, airport air1, airport air2 where dest.airport_dep_id= air1.airport_id and dest.airport_arr_id=air2.airport_id; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] 2 tables, joins and same name...
On Fri, Aug 31, 2001 at 08:29:21AM -0700, Josh Berkus wrote: > > That's why we have the list. Personally, I'm still looking for a > comprehensive introductory SQL book to recommend. The ones I know are > either too simple and not that accurate (SQL for Dummies), proprietary > (MS SQL Server in 24 hours), too short (PostgreSQL Introduction and > Concepts*), or too advanced for the newbie (SQL for Smarties). > Have you seen "Database Design for Mere Mortals" by Michael Hernandez? And there's another one, that I can only remember as 'the pink book', I can never remember the title! that struck me as a reasonably good intro to intermediate level book. Ross ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
