Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Indeed, it seems that I get the same result for a similar query.
I'm running version 7.3.4 on a rh 9 server.
Also: is the function date_part a function you wrote yourself? I get an error stating
that the function date_part(Unknown,date) is not recognized.
It maybe not a solution to the actual problem but you could try this:
save the date and the time in two seperate fields. I use a similar construction for
convenience.
Regards,
Stijn.
Hello,
Hm, doesn't work for me:
[EMAIL PROTECTED] mydb= select distinct date_part('year', uu.add_date),
date_part('month', uu.add_date), date_part('day',
uu.add_date) from uus inner join ui on uus.user_id=ui.id
inner join
uu on ui.id=uu.user_id where uus.subscriber_user_id=1 order by
uu.add_date desc;
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear
in target
list
I have this version of PostgreSQL installed: postgresql-7.3.4-3.rhl9
Thanks,
Otis
--- Tom Lane [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] writes:
I'd love to be able to do that, but I cannot just ORDER BY
uu.add_date,
because I do not have uu.add_date in the SELECT part of the
statement.
Sure you can. Back around SQL89 there was a restriction that ORDER
BY
values had to appear in the SELECT list as well, but no modern
database
has such a restriction anymore ...
regards, tom lane
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Hello,
Thank you for all your help, Stijn.
date_part is a standard PG function.
While not the most elegant, the DESC, DESC, DESC solution suggested the
other day works okay for me, so I think I'll use that for now.
Thanks again!
Otis
--- Stijn Vanroye [EMAIL PROTECTED] wrote:
Indeed, it seems that I get the same result for a similar query.
I'm running version 7.3.4 on a rh 9 server.
Also: is the function date_part a function you wrote yourself? I get
an error stating that the function date_part(Unknown,date) is not
recognized.
It maybe not a solution to the actual problem but you could try this:
save the date and the time in two seperate fields. I use a similar
construction for convenience.
Regards,
Stijn.
Hello,
Hm, doesn't work for me:
[EMAIL PROTECTED] mydb= select distinct date_part('year', uu.add_date),
date_part('month', uu.add_date), date_part('day',
uu.add_date) from uus inner join ui on uus.user_id=ui.id
inner join
uu on ui.id=uu.user_id where uus.subscriber_user_id=1 order by
uu.add_date desc;
ERROR: For SELECT DISTINCT, ORDER BY expressions must appear
in target
list
I have this version of PostgreSQL installed:
postgresql-7.3.4-3.rhl9
Thanks,
Otis
--- Tom Lane [EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] writes:
I'd love to be able to do that, but I cannot just ORDER BY
uu.add_date,
because I do not have uu.add_date in the SELECT part of the
statement.
Sure you can. Back around SQL89 there was a restriction that
ORDER
BY
values had to appear in the SELECT list as well, but no modern
database
has such a restriction anymore ...
regards, tom lane
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(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] Server Side C programming Environment Set up
Am Donnerstag, 22. April 2004 18:07 schrieb Tom Lane: I agree with the suggestion elsewhere in the thread about generalizing the contrib Makefile framework to the point that it could be installed as part of the -devel RPM, and then used to build user-written backend functions. It seems to me that you are proposing to recreate the same sort of framework that we have fought for years to get rid of in the cases of Perl, Python, and others. Some reasons for why this is not a good idea are: It would restrict users of that framework to use the same compiler that was used to build PostgreSQL. History shows that this assumptions fails surprisingly often. When someone wants to build a glue module between PostgreSQL and some other largish package (say, Perl, although that one exists already), then whose framework do you use? Sometimes building outside of these frameworks becomes extremely difficult. Without a configuration routine of its own, add-on packages are restricted to using the information that the main PostgreSQL configuration already provides. If someone needs to detect or evaluate additional libraries there is no chance. I'd be happy to write more documentation, howtos, or scripts and tools that enable users to set up a proper build system, but I don't think it's our business to try to write our own build system framework. ---(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
[SQL] Record Lock details
Hi all, I am using PostgreSQL 7.3.1. Is there a data dictionary in Postgres from where i can get the info about locked rows of any table ? If possible the value of those locked record ? My situation is like: I have an ODBC application working on Postgres. There are Master child tables. I want, if a user A is editing any document ( one master and set of child records) in front-end application, the same should not be available to other user for editing. I thought of using SELECT FOR UPDATE. But, in my case, i need to display the name of application user and other details about the locked row. From which data dictionary, i should query. Any help would be appreciated. Thanx Denis ---(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] Is there an easy way to normalize-space with given string functions
Am Freitag, 23. April 2004 04:34 schrieb Tom Lane:
Janning Vygen [EMAIL PROTECTED] writes:
i searched the function list and tried to combine to or more
functions, but i miss a replace function which uses regular
expressions.
There isn't one in the SQL standard. Most people who need one write a
one-liner function in plperl or pltcl.
Thank you.
(Mind you, I don't know why we don't offer a built-in one --- the needed
regex engine is in there anyway. I guess no one has gotten around to
getting agreement on a syntax.)
My suggestion:
Syntax:
substitute(string text, from text, to text);
Example:
substitute(' too many spaces ', '\s+', ' ');
Result:
' too many spaces '
But maybe its a bad idea to create new function names...
kind regards
janning
---(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] Record Lock details
Am Freitag, 23. April 2004 10:43 schrieb Denis P Gohel: Is there a data dictionary in Postgres from where i can get the info about locked rows of any table ? If possible the value of those locked record ? No, this information is not available for end users. I have an ODBC application working on Postgres. There are Master child tables. I want, if a user A is editing any document ( one master and set of child records) in front-end application, the same should not be available to other user for editing. I thought of using SELECT FOR UPDATE. But, in my case, i need to display the name of application user and other details about the locked row. Keeping in mind that long-running transactions are not a good idea anyway, you should probably consider a user-space cooperative locking model. There is something in contrib/userlock, but I'm not sure whether that fits what you need. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] converting unix seconds to timestamp
On Fri, Apr 23, 2004 at 11:46:52AM +0300, Alexei Chetroi wrote: Date: Fri, 23 Apr 2004 11:46:52 +0300 From: Alexei Chetroi [EMAIL PROTECTED] User-Agent: Mutt/1.5.5.1+cvs20040105i To: [EMAIL PROTECTED] Subject: [SQL] converting unix seconds to timestamp Hi, I wish to convert unix seconds which are from 1970 to timestamp and connot find appropriate function for it. I know how to convert timestamp to unix second with extract(epoch FROM ...) but not reverse. Sorry, already found answer to my question: SELECT TIMESTAMP WITH TIMEZONE 'epoch' + 1082709745 * interval '1 second'; Thanks -- Alexei Chetroi ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Server Side C programming Environment Set up
Peter Eisentraut [EMAIL PROTECTED] writes: I'd be happy to write more documentation, howtos, or scripts and tools that enable users to set up a proper build system, but I don't think it's our business to try to write our own build system framework. Any build framework is going to have limitations, obviously, and it would be bad to design things to prevent subprojects from having their own. But I think that is no argument for not having a build framework at all. If we try to go that route, we'll be killing a lot of useful code that isn't quite valuable enough (in isolation) for people to expend the work to create their own build system for. Even more to the point, we've already *got* a build framework, which by demonstration works for many of the bits of code that we are talking about spinning off. What we have to do is adjust it so it still works for them after they're spun off. It seems silly to abandon the not-trivial work you and other people have already put into the contrib build system; and also silly to expect gborg projects to individually adapt it to their needs. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Server Side C programming Environment Set up
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: I'd be happy to write more documentation, howtos, or scripts and tools that enable users to set up a proper build system, but I don't think it's our business to try to write our own build system framework. Any build framework is going to have limitations, obviously, and it would be bad to design things to prevent subprojects from having their own. But I think that is no argument for not having a build framework at all. If we try to go that route, we'll be killing a lot of useful code that isn't quite valuable enough (in isolation) for people to expend the work to create their own build system for. Even more to the point, we've already *got* a build framework, which by demonstration works for many of the bits of code that we are talking about spinning off. What we have to do is adjust it so it still works for them after they're spun off. It seems silly to abandon the not-trivial work you and other people have already put into the contrib build system; and also silly to expect gborg projects to individually adapt it to their needs. Bingo! We have often had the attitude If we can't do it perfectly, don't do it. While that applies is some cases, it doesn't apply everywhere, and we need to be wise in determining when we are helping people by giving them a 99% solution. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
[SQL] CONTEXT on PL/pgSQL
I have a simple function made with PL/pgSQL and when I call it I get this in the logs: 2004-04-23 10:15:32 [30669] LOG: statement: SELECT nodoSuperior(22) AS sup 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 CONTEXT: PL/pgSQL function nodosuperior line 7 at assignment 2004-04-23 10:15:32 [30669] LOG: statement: SELECT codigo,padre,nombre FROM procesos WHERE codigo= $1 CONTEXT: PL/pgSQL function nodosuperior line 10 at select into variables 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 IS NULL CONTEXT: PL/pgSQL function nodosuperior line 12 at exit 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 CONTEXT: PL/pgSQL function nodosuperior line 16 at return What does CONTEXT mean, and is everything ok? The function is this: CREATE OR REPLACE FUNCTION nodoSuperior(INT) RETURNS VARCHAR AS ' DECLARE COD INT; SUP RECORD; BEGIN COD:=$1; LOOP SELECT INTO SUP codigo,padre,nombre FROM procesos WHERE codigo=COD; EXIT WHEN SUP.padre IS NULL; COD:=SUP.padre; END LOOP; RETURN SUP.nombre; END; ' LANGUAGE 'plpgsql'; -- 10:16:01 up 45 days, 14:40, 3 users, load average: 0.54, 0.61, 0.63 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Multi ordered select and indexing
Hi! We have a complex problematic area. What is the simplest solution for the next query type: SELECT * FROM tablename ORDER BY col1 ASC, col2 DESC; In our experience, postgres cannot use a multi-colum index on (col1, col2) in this situation, becouse there are different directions after ORDER BY. Is custom operator class the easiest solution, which can solve the reverse indexing on col2? Our problem with this solution, is that we have to replace DESC with USING myoperator. Is it possible, that postgres can recognize myoperator without replacing DESC? We made new operators on int4 type starting with letter /: CREATE OPERATOR CLASS int4_reverse_order_ops FOR TYPE int4 USING btree AS OPERATOR1 / , OPERATOR2 /= , OPERATOR3 /= , OPERATOR4 /= , OPERATOR5 / , FUNCTION1 int4_reverse_order_cmp(int4, int4); Create an index: CREATE INDEX idx_test ON tablename (col1, col2 int4_reverse_order_ops); Postgres can use this index in this query: EXPLAIN SELECT * FROM tablename ORDER BY col1 ASC, col2 USING / limit 10; QUERY PLAN Limit (cost=0.00..0.52 rows=10 width=8) - Index Scan using idx_test on tablename (cost=0.00..52.00 rows=1000 width=8) An other problem: we have to replace the operators after the WHERE conditions, if that contains conditions on col2. EXPLAIN SELECT * FROM tablename WHERE col1 10 and col2 10 ORDER BY col1, col2 using / limit 10; QUERY PLAN --- Limit (cost=0.00..4.14 rows=10 width=8) - Index Scan using idx_test on tablename (cost=0.00..46.33 rows=112 width=8) Index Cond: (col1 10) Filter: (col2 10) You can see, it use filtering on col2, but in the next case it can indexing on col2 condition: EXPLAIN SELECT * FROM tablename WHERE col1 10 and col2 / 10 ORDER BY col1, col2 using / limit 10; QUERY PLAN --- Limit (cost=0.00..3.82 rows=10 width=8) - Index Scan using idx_test on tablename (cost=0.00..42.78 rows=112 width=8) Index Cond: ((col1 10) AND (col2 / 10)) Can we do this easier? If can, how? After that, we have an other unsolved problem, if the col2's type is TEXT, and we try to use a LIKE operator on it. But we coludn't replace the LIKE with own operator, because postgres exchange the LIKE with an expression which contains = and . We made own like operator: /~~, but we cannot tell postgres to use our own /= and '/' operators instead of /~~. CREATE OPERATOR /~~ ( leftarg = text, rightarg = text, procedure = textlike, commutator = /~~ , negator = !~~ , restrict = scalarltsel, join = scalarltjoinsel ); Thanks in advance. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CONTEXT on PL/pgSQL
El Vie 23 Abr 2004 11:18, Tom Lane escribió: Martin Marques [EMAIL PROTECTED] writes: I have a simple function made with PL/pgSQL and when I call it I get this in the logs: 2004-04-23 10:15:32 [30669] LOG: statement: SELECT nodoSuperior(22) AS sup 2004-04-23 10:15:32 [30669] LOG: statement: SELECT $1 CONTEXT: PL/pgSQL function nodosuperior line 7 at assignment log_statement currently logs everything the parser sees, which includes SQL commands generated by plpgsql. Arguably these facilities should be separated, but until someone makes a serious effort to provide plpgsql debugging features, it's likely that nothing will be done about it. Right now this is almost the only technique available for seeing what's going on inside a plpgsql function, and crummy as it is, it's better than nothing... So the CONTEXT line just tells where the statement was made? -- 12:06:01 up 45 days, 16:30, 2 users, load average: 0.50, 0.46, 0.45 - Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar' Centro de Telematica | DBA, Programador, Administrador Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Multi ordered select and indexing
On Fri, 23 Apr 2004, Antal Attila wrote: Hi! We have a complex problematic area. What is the simplest solution for the next query type: SELECT * FROM tablename ORDER BY col1 ASC, col2 DESC; In our experience, postgres cannot use a multi-colum index on (col1, col2) in this situation, becouse there are different directions after ORDER BY. Is custom operator class the easiest solution, which can solve the reverse indexing on col2? Our problem with this solution, is that we have to replace DESC with USING myoperator. Is it possible, that postgres can recognize myoperator without replacing DESC? Yes. You should be able to make an opclass where the operators are the standard operators but in a different order which should get used when doing DESC. I think there should be examples in the archives. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Multi ordered select and indexing
On Fri, Apr 23, 2004 at 16:33:14 +0200, Antal Attila [EMAIL PROTECTED] wrote: In our experience, postgres cannot use a multi-colum index on (col1, col2) in this situation, becouse there are different directions after ORDER BY. Is custom operator class the easiest solution, which can solve the reverse indexing on col2? Our problem with this solution, is that we have to replace DESC with USING myoperator. Is it possible, that postgres can recognize myoperator without replacing DESC? Another option you might have is using functional indexes. If you are using 7.4.x or greater and one of the columns has a reasonable - operator (pretty much this is the numeric types) then you can have an index on (col1, (-col2)) and then order by col1, -col2. The main advantage is that this is simpler than making a new opclass. If you have already gone to that trouble it may be better to stick with it. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] lifetime of temp schema versus compiled image of plpgsql proc
Hi all, I have a similar problem, i'm connecting to postgresql with th psql-odbc, create a temp table outside the transaction, insert into the temp, commit then i try to use the temp and it isn't there. Maybe an odbc problem?? are you using the odbc? _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
