[SQL] Extract date from week
Hi Looking the e-mail I remembered a question. I saw that "select extract (week from now()::date)" will return the week number of current year. But, how can I convert a week to the first reference date. Ex: select extract(week from '20050105'::date); -- 5 Jan 2005 --Returns-- date_part | 1 | It is the first week of year (2005), and how can I get what is the first date references the week 1? Ex: select week 1 --should return--- date | 20050103 | -- 3 Jan 2005 Thank you. Lucas Vendramin ---(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] Returning rows as columns
You're looking for a technique generally referred to as a "pivot table" which is really a non-normalized or aggregate view of relational data You'll find plenty of examples if you Google "pivot table". "Paul" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > I'm looking for a way to return rows as columns. My problem is the > following. I have three tables: > suppliers,products and productprices. A supplier and a product make a > productprice. A certain product may have an x number of suppliers and > prices. > > Now I have the following query: > select > products.int_artnr,products.str_name,suppliers.str_naam,productprices.flt_price > from products,productprices,suppliers > WHERE products.int_artnr = productprices.int_artnr > AND suppliers.int_crednr=productprices.int_crednr > ORDER BY int_artnr > > This gives me a result but not the one I wanted. I want to have a > dataset that returns the supplierprice and suppliername as a column > behind every product for all suppliers. So you get an x number of > columns. > > Example: > > Number Name Suppl1 Price1 Suppl2 Price2 > > 1 Beer 10 cases Heineken 3.33 Amstel 1.55 > 2 Beer 2 cases Heikenen 1.22 > > > Could someone please give me some pointers if this is achievable and if > yes, how I should do this? > > Thx, > > Paul > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Extract date from week
On 11/8/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi > Looking the e-mail I remembered a question. > I saw that "select extract (week from now()::date)" will return the > week number > of current year. But, how can I convert a week to the first reference > date. Ex: > select extract(week from '20050105'::date); -- 5 Jan 2005 > --Returns-- > date_part | > 1 | > > It is the first week of year (2005), and how can I get what is the first date > references the week 1? Ex: > select week 1 > --should return--- > date | > 20050103 | -- 3 Jan 2005 > > Thank you. > Lucas Vendramin > > Extracted from: http://www.postgresql.org/docs/8.0/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT --- begin extracted text --- week The number of the week of the year that the day is in. By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.) In other words, the first Thursday of a year is in week 1 of that year. (for timestamp values only) Because of this, it is possible for early January dates to be part of the 52nd or 53rd week of the previous year. For example, 2005-01-01 is part of the 53rd week of year 2004, and 2006-01-01 is part of the 52nd week of year 2005. SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 7 --- end extracted text --- -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] can not connect to pg on linux FC4
On Fri, 2005-11-04 at 21:12 -0800, Tai Huynh Phuoc wrote: > Hi, > I use pg 8.03 (come with FC4). I can not connect to pg > because of Ident authentication failed for user "root" > error. pg_hba.conf > host all all 127.0.0.1/32 ident sameuser > > Then I try another way by add adding a row to pg_indet > so that I can use root account to connection to db. > mymap root root > and chage "sameuser" to "mymap" in the pg_hba.conf. > But I can not connect too as the above error. > > Thank in advande someone help me. If you are going to use ident authentication on a TCP/IP connection (even localhost) you need an ident server running on the client machine. That is what supplies the authentication. You need to trust the administrator of the client machine, otherwise this can be very insecure. Since you are connecting to the local machine, you could just as easily use a Unix socket connection (do not supply any hostname) in which case ident authentication will use the ownership credentials associated with the socket. In pg_hba.conf, this is a "local" connection and is secure on Linux. > Onother question. Is there a way that I can create a > user using password authetication at the situation I > have no pg account like this sitation?. Assign a password for the user with ALTER USER. Then add a suitable line to pg_hba.conf. Oliver Elphick ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Design question: Scalability and tens of thousands of
On Thu, 3 Nov 2005 09:58:29 -0800 "zackchandler" <[EMAIL PROTECTED]> threw this fish to the penguins: > I'm designing an application that will allow users to create custom objects > on the application level. A custom object can have zero or more attributes. > Attributes can be one of 5-10 types (ex. String, Number, List, Date, Time, > Currency, etc...). This will allow users to track anything exactly as they > want. My first thought on how to structure this is to make a custom table > in the db for each custom object. The attributes would map to fields and > everything would work just dandy. > > The problem is I am very concerned about scalability with having a different > table created for each custom object. I want to design to site to handle > tens of thousands of users. If each user has 3-5 custom objects the > database would have to handle tens of thousands of tables. > > So it would appear that this is a broken solution and will not scale. Has > anyone designed a similar system or have ideas to share? This is a sticky problem. My app is a bit similar. Trying something like your solution, I found that zillions of tables, constantly created and destroyed by users, to be terrible to manage. Now I use one table that defines objects' attributes, e.g. something like: create table fields(owner text, obj text, name text, type text, seq int) create table objs(owner text, name text) create table values(owner text, obj text, name text, val text) That is, the values are stored in text type, not the native type. Yes, this takes a performance hit for conversion of values, but the simplicity of schema really wins for me. I suggest you seriously consider it unless you need blinding performance in all 20,000 applications... -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(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
[SQL] Migrating database from postgresql 7.4.6 to postgresql 8.0.3
Hi, I would like to migrating my database from postgresql 7.4.6 to postgresql 8.0.3. Are there any problem during migrating database? a casting problem or sintax problem, or other problem? Can somebody help me answering this question? Or Does anyone ever try to migrating database from postgresql 7.4.6 to postgresql 8.0.3? Could you tell me which part that change from postgresql 7.4.6 to postgre 8.0.3? Thank's a lot, Louise C.S. __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Migrating database from postgresql 7.4.6 to postgresql 8.0.3
am 08.11.2005, um 23:18:35 -0800 mailte Louise Catherine folgendes: > Hi, > I would like to migrating my database from postgresql > 7.4.6 to postgresql 8.0.3. Why not 8.1.0? > Are there any problem during migrating database? a > casting problem or sintax problem, or other problem? I dont know about problems. Install the new database (on the same machine, use a different port), and use the pg_dump from the newer version to dump the old db in the new db. HTH, 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 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
