Re: [SQL] SQL syntax extentions - to put postgres ahead in the race
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Thu, 5 Aug 2004, Ram Nathaniel wrote: >> 2) aggregated concatenation: > Theoretically, you should be able to do this right now in PostgreSQL with > user defined aggregates (although you can't pass a second argument > currently for the separator). There's nothing particularly stopping us from supporting multiple-argument aggregates, except a lack of round tuits. (I suppose we'd want to rethink the syntax of CREATE AGGREGATE, but otherwise it ought to be pretty straightforward.) > I believe that an ordered subquery in FROM > will currently allow you to get an ordered aggregate, or perhaps you'd > have to turn off hash aggregation, but I think you should be able to get > it to keep the ordering. I think you would want to ORDER BY twice: SELECT class, list(student) from (select class, student from grades order by class, student) ss order by class; It looks like (at least in CVS tip) planner.c will take into account the relative costs of doing a GroupAgg vs doing a HashAgg and re-sorting, but I'm too tired to try it right now... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] retrieve the tables names based on the owner
Hi guys, How can I retrieve (with a query) from a database, only the table names based on the owner name? Many Thanks Antonis ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] retrieve the tables names based on the owner
O kyrios Antonis Antoniou egrapse stis Aug 6, 2004 : > Hi guys, Kalhmera Antoni! > > How can I retrieve (with a query) from a database, only the table names > based on the owner name? > SELECT schemaname||'.'||tablename from pg_tables where tableowner='postgres'; > > Many Thanks > Antonis > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- -Achilleus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL syntax extentions - to put postgres ahead in the race
I wrote: > There's nothing particularly stopping us from supporting > multiple-argument aggregates, except a lack of round tuits. BTW, you can actually fake this pretty well in 8.0, by making an aggregate that uses a rowtype input. For example: regression=# create type twostrings as (s1 text, s2 text); CREATE TYPE regression=# create function list_concat(text, twostrings) returns text as $$ regression$# select case when $1 is null then $2.s1 regression$# when $2.s1 is null then $1 regression$# else $1 || $2.s2 || $2.s1 regression$# end$$ language sql; CREATE FUNCTION regression=# create aggregate concat ( regression(# basetype = twostrings, regression(# stype = text, regression(# sfunc = list_concat); CREATE AGGREGATE regression=# select * from text_tbl; f1 --- doh! hi de ho neighbor more stuff and more (4 rows) regression=# select concat((f1, '|')) from text_tbl; concat doh!|hi de ho neighbor|more stuff|and more (1 row) This is somewhat inefficient compared to native support for multi-argument aggregates, but at least we have something we can point people to until we find time to make that happen. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Make a column case insensitive
Is it possible to make a column case insensitive, without having to pepper your SELECTs with lots of lower() function calls (and forgetting to do it at times !) (I'm on 7.4.3) Thanks, GTG Gordon Ross, Network Manager/Rheolwr Rhydwaith Countryside Council for Wales/Cyngor Cefn Gwlad Cymru ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] New PHP + PostgreSQL group on Google Groups2
Hello. I have created a new group in the Google Groups beta site for PHP + PostgreSQL development. This group is for questions on advanced web development with PHP and PostgreSQL using Linux. Topics include functions, regular expressions, classes (OOP), speed, security, editor customization, SQL, and software installation/maintenance. Everyone is welcome. http://groups-beta.google.com/group/php-psql ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Grouping by week
I'm using SELECT EXTRACT(WEEK FROM trans_date), SUM(tran_amount) ... GROUP BY trans_date and it is being used to group sales results by week. It works really well. What I'm wondering is if I can shift the week from a Mon-Sun articulation(default with Postgre) to a Sun-Sat sequence. I need it that way in order to comply with a legacy stats system. Thanks, Caleb ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Grouping by week
On Fri, 2004-08-06 at 22:29, Caleb Simonyi-Gindele wrote: > I'm using > > SELECT EXTRACT(WEEK FROM trans_date), SUM(tran_amount) ... GROUP BY > trans_date > > and it is being used to group sales results by week. It works really well. > > What I'm wondering is if I can shift the week from a Mon-Sun > articulation(default with Postgre) to a Sun-Sat sequence. I need it that way > in order to comply with a legacy stats system. How about: SELECT EXTRACT(WEEK FROM trans_date + '1 day'::INTERVAL) -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Be still before the LORD and wait patiently for him; do not fret when men succeed in their ways, when they carry out their wicked schemes." Psalms 37:7 ---(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] surrogate key or not?
David, > But, once a surrogate key is assigned to a row, doesn't it become a > "real" data? For example, I have a bunch of invoices/receipts and I > write down a unique number on each of them. Doesn't the unique number > become part of the information contained by the invoice/receipt itself > (at least as long as I'm concerned)? Sure ... *if* it's being used that way. If, however, your table has that Invoice # *and* a seperate surrogate key that's redundant and can cause problems. > Change management IMO is perhaps the main reason of surrogate/artificial > key. We often need a PK that _never_ needs to change (because it can be > a royal PITA or downright impossibility to make this change; the PK > might already be printed on a form/card/document, recorded on some > permanent database, tattoed/embedded in someone's forehead, etc). Sure. But surrogate keys don't fix this problem; only good change management does. This is precisely why I say "use with caution"; all too often project leaders regard surrogate keys as a substitute for good change management and don't do any further work. > Meanwhile, every other aspect of the data can change (e.g. a person can > change his name, sex, age, email, address, even date & place of birth). > Not to mention data entry mistakes. So it's impossible to use any > "real"/natural key in this case. Absolutely false. It's quite possible, it's just a performance/schema/data management issue. This also applies to my comment above. > Okay, so surrogate key makes it easy for stupid people to design a > database that is prone to data duplication (because he doesn't install > enough unique constraints to prevent this). But I don't see how a > relation with a surrogate key is harder to "normalize" (that is, for the > duplicates to be removed) than a relation with no key at all. Compare: You're right here, both are equally hard to normalize. What I'm criticizing is the tendency of a lot of beginning DBAs -- and even some books on database design -- to say: "If you've created an integer key, you're done." Had I my way, I would automatically issue a WARNING on any time you create a table in PG without a key. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Grouping by week
Oliver Elphick <[EMAIL PROTECTED]> writes: > How about: >SELECT EXTRACT(WEEK FROM trans_date + '1 day'::INTERVAL) Note that if trans_date is actually a date, you are much better off just adding an integer to it: SELECT EXTRACT(WEEK FROM trans_date + 1) If you add an interval then the date will be promoted to a timestamp, and all of a sudden you have possible issues with funny behavior at DST boundaries. I think since 7.3 the DST issue is only serious if trans_date is actually stored as timestamp with time zone, but it has been able to bite you in the past. regards, tom lane ---(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] surrogate key or not?
On Saturday 07 August 2004 04:12 am, Josh Berkus wrote: > > > Meanwhile, every other aspect of the data can change (e.g. a person can > > change his name, sex, age, email, address, even date & place of birth). > > Not to mention data entry mistakes. So it's impossible to use any > > "real"/natural key in this case. > > Absolutely false. It's quite possible, it's just a > performance/schema/data management issue. This also applies to my comment > above. why shouldnt the primary key change? the only key that should never change is a key that is used as a foreign key in another table. In a table like this: id serial unique name varchar primary key name may change - id will never change. id is used as the foreign key -- regards kg http://www.onlineindianhotels.net - fastest hotel search website in the world http://www.ootygolfclub.org ---(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
[SQL] reply to setting
hi, any reason why the default reply-to on this list should not be set to the list? I keep replying to postings only to find later that the reply goes to the OP and not to the list. reply-all button results in needless duplication -- regards kg http://www.onlineindianhotels.net - fastest hotel search website in the world http://www.ootygolfclub.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
