[SQL] FW: How to install Postgres that supports 64-bit integer/date-time.
Continued….. I solved this problem by myself. A. For 64 bit development you need these packages installed on Solaris server: SUNWarcx, SUNWbtoox, SUNWdplx, SUNWscpux, SUNWsprox, SUNWtoox, SUNWlmsx, SUNWlmx, SUNWlibCx Pls confirm these using the following command. pkginfo SUNWarcx pkginfo SUNWbtoox pkginfo SUNWdplx pkginfo SUNWscpux pkginfo SUNWsprox, pkginfo SUNWtoox pkginfo SUNWlmsx pkginfo SUNWlmx pkginfo SUNWlibCx B. Install missing Solaris packages. Insert Solaris software CD 1/2. Log in as root. pkgadd -d /cdrom/Solaris_9/Product <> eject cdrom C. Check Solaris OS mode and supported bits isainfo -v 64-bit sparcv9 applications 32-bit sparc applications D. To compile a 64-bit application on an UltraSparc with a recent Sun Compiler. getconf -a | grep v9 XBS5_LP64_OFF64_CFLAGS: -xarch=v9 XBS5_LP64_OFF64_LDFLAGS: -xarch=v9 …. …. E. The transitional compilation environment is obtained with the following compiler and linker flags: getconf LFS64_CFLAGS -D_LARGEFILE64_SOURCE F. The large file compilation environment is obtained with (A 64-bit application automatically has large file support built in by default.) getconf LFS_CFLAGS -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 G. Re-Configure the Postgres with 64-bit integer date/time support from its source: bash-2.03# cd postgres 8.0.1/ bash-2.03# ./configure --enable-integer-datetimes --prefix=/usr/local/pgsql --with-perl --with-tclconfig=/usr/local/lib --with-tcl checking build system type... sparc-sun-solaris2.8 checking host system type... sparc-sun-solaris2.8 checking which template to use... solaris checking whether to build with 64-bit integer date/time support... yes checking whether NLS is wanted... no checking for default port number... 5432 .… …. bash-2.03# make install Kindly let me know if you need any further clarification or any problem occurs. Thanks Dinesh Pandey From: Dinesh Pandey [mailto:[EMAIL PROTECTED]] Sent: Friday, April 22, 2005 11:00 AM To: 'PostgreSQL'; 'pgsql-general@postgresql.org' Subject: How to install Postgres that supports 64-bit integer/date-time. How to install Postgres 8.0.1 that supports 64-bit integer/date-time. # ./configure --prefix=/usr/local/pgsql --with-tclconfig=/usr/local/lib --with-tcl checking build system type... sparc-sun-solaris2.8 checking host system type... sparc-sun-solaris2.8 checking which template to use... solaris checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc while installation I am getting this message:” checking whether to build with 64-bit integer date/time support... no” Thanks
Re: [SQL] RULE for mtime recording
Enrico Weigelt wrote:
>
> Hi folks,
>
> I'd like to write an update rule, which touches the a mtime field
> (=current_timestamp) on normal update - when the mtime field isnt
> explicitly set. If the update query explictly sets an mtime value,
> this value has to be let through.
>
> my tables look like:
>
> -- base class
> CREATE TABLE inode
> (
> inode_idoid not null default nextval('inode_id_seq'),
> mtime timestamp not null default current_timestamp
> );
>
> -- example class
> CREATE TABLE foo
> (
> bar text
> ) INHERITS ( inode );
>
> now if I do
>
> UPDATE foo SET bar = 'xyz' WHERE ...
>
> the mtime should be set to the current time, but on
>
> UPDATE foo SET bar = '123', mtime = '2001-09-11' WHERE ...
>
> we shall have 9/11 as mtime.
>
> Is this possible with rules ?
>
> thx
Just ReadingTFM, I'd say this should do:
CREATE OR REPLACE RULE foo_update_mtime_is_null
AS ON UPDATE TO foo
WHERE mtime IS NULL
DO INSTEAD
UPDATE foo SET bar = NEW.bar WHERE ... ;
CREATE OR REPLACE RULE foo_update_mtime_is_not_null
AS ON UPDATE TO foo
WHERE mtime IS NOT NULL
DO INSTEAD
UPDATE foo SET bar = NEW.bar, mtime = NEW.mtime WHERE ... ;
I have very few experience with rules, so if this works,
please let me know.
Regards, Christoph
> --
> -
> Enrico Weigelt== metux IT service
>
> phone: +49 36207 519931 www: http://www.metux.de/
> fax: +49 36207 519932 email: [EMAIL PROTECTED]
> cellphone: +49 174 7066481
> -
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] string to date conversion
Hi, I'm working on an existing Pg database which, please don't ask why, stores dates as varchar attributes in the form '/MM/DD' I'm not allowed to modify the tables to use 'timestamp' instead, so I'd like to convert on the fly when retrieving datas with select. When I do : select mydatefield::timestamp from mytable; There's an error saying that converting from varchar to date is not possible How can I do this ? Thanks in advance Jerome Alet ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How do I connect with something like JDBCManager to Postgres on Fedora Linux
Hi Guys I'm trying to setup my postgres on Linux, I get it to create the database and the postgres user etc. But when I'm trying to connect via TCP/IP I get connection errors, Can you please help Adriaan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] string to date conversion
On Fri, Apr 22, 2005 at 02:38:43PM +0530, Ramakrishnan Muralidharan wrote:
> Hi,
>
> I have tried the same in PostgreSQL 8.0.1 and it is working fine. I have
> used following example for testing
>
> create table test1
> (
> date varchar(10)
> )
>
> insert into test1 values('2005/04/22')
>
> select date::timestamp from test1
Sorry, I should have said that I use 7.4.7...
I know it's old but as already explained I can't do much about it
for now...
Thanks for your help
bye
Jerome Alet
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] string to date conversion
Jerome Alet wrote: > > Hi, > > I'm working on an existing Pg database which, please don't ask why, > stores dates as varchar attributes in the form '/MM/DD' > > I'm not allowed to modify the tables to use 'timestamp' instead, > so I'd like to convert on the fly when retrieving datas with > select. > > When I do : > > select mydatefield::timestamp from mytable; > > There's an error saying that converting from varchar to date is > not possible > > How can I do this ? > > Thanks in advance > > Jerome Alet > select version(); version - PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (SuSE Linux) (1 row) select '2005/04/22'::text::timestamp ; timestamp - 2005-04-22 00:00:00 (1 row) works for me. Regards, Christoph ---(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] string to date conversion
On Fri, Apr 22, 2005 at 11:21:26AM +0200, Christoph Haller wrote: > > select '2005/04/22'::text::timestamp ; > timestamp > - > 2005-04-22 00:00:00 > (1 row) > > works for me. It works fine !!! Thanks so much for your help. I missed the '::text' intermediate conversion it seems bye Jerome Alet ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [despammed] [SQL] How do I connect with something like JDBCManager to Postgres on Fedora Linux
am 22.04.2005, um 11:22:50 +0200 mailte Adriaan Botha folgendes: > Hi Guys > > I'm trying to setup my postgres on Linux, I get it to create the > database and the postgres user etc. But when I'm trying to connect via > TCP/IP I get connection errors, You should edit the pg_hba.conf. Read this about -i and TCPIP_SOCKET and edit this to enable access via tcp/ip. 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How do I connect with something like JDBCManager to Pos
You are most probably missing entries in pg_hba.conf and the listen= directive in postgres.conf |-Original Message- |From: Adriaan Botha [mailto:[EMAIL PROTECTED] |Sent: Freitag, 22. April 2005 11:23 |To: 'PostgreSQL' |Subject: [SQL] How do I connect with something like JDBCManager to |Postgres on Fedora Linux | | |Hi Guys | |I'm trying to setup my postgres on Linux, I get it to create the |database and the postgres user etc. But when I'm trying to connect via |TCP/IP I get connection errors, | |Can you please help |Adriaan | |---(end of |broadcast)--- |TIP 5: Have you checked our extensive FAQ? | | http://www.postgresql.org/docs/faq | ---(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
Re: [SQL] string to date conversion
On Fri, Apr 22, 2005 at 11:30:02AM +0200, Jerome Alet wrote: > On Fri, Apr 22, 2005 at 11:21:26AM +0200, Christoph Haller wrote: > > > > select '2005/04/22'::text::timestamp ; > > timestamp > > - > > 2005-04-22 00:00:00 > > (1 row) > > > > works for me. > > It works fine !!! > > Thanks so much for your help. > > I missed the '::text' intermediate conversion it seems This is fixed in 8.0 (in 8.0.2, anyway). Casts from varchar no longer need an intermediate cast to text. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] C function - other process
Hello,
I wrote a simple function:
PG_FUNCTION_INFO_V1(my_fcn);
Datum
my_fcn()
{
int i,ret;
i=0;
signal(SIGCHLD,SIG_IGN);
switch(fork())
{
case 0:
{
SPI_connect();
for(i=0;i<10;i++)
{
SPI_exec("insert into my_tmp values ('asdasd');",1);
sleep(1);
}
ret = SPI_finish();
exit(ret);
}
default:
{
;
}
}
}
Next I create a function in my database:
CREATE FUNCTION my_fcn() RETURNS void
AS '$libdir/my_fcn', 'my_fcn'
LANGUAGE c;
Now I execute "select my_fcn();" and I don't see records in table
'my_tmp', but function works.
I checked returns values from SPI function, tehere are correct.
What is wrong?
Best regards,
jakub
--
[EMAIL PROTECTED]
---(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
Re: [SQL] C function - other process
Michael Fuhr <[EMAIL PROTECTED]> writes: > What about fork() followed by exec*(), either explicitly or via > popen(), system(), etc.? Should these be avoided as well, or is > there a safe way to do them? I'm thinking of the case where a > user-defined function wants to invoke some external command -- I've > done that in experiments but I've never been sure how safe it was. Execing some new program is safe enough, although you might wish to explicitly close the various sockets the backend holds to make sure the new program doesn't maliciously scribble on them. (It may be worth marking them close-on-exec, although doing this without breaking the Windows port might be hard.) The problem with the hack at hand is that you can't have two processes sharing the same backend slot (not to mention the same transaction ID). But launching something that isn't a backend is no problem. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] C function - other process
On Fri, Apr 22, 2005 at 04:07:48PM -0400, Tom Lane wrote: > > Execing some new program is safe enough, although you might wish to > explicitly close the various sockets the backend holds to make sure > the new program doesn't maliciously scribble on them. Is there a way to find out which fds to close, or should a function just close everything from, say, 3 to (assuming that 0, 1, and 2 are stdin, stdout, and stderr)? I could think of non-portable ways like looking under /proc//fd, but I was wondering if the backend keeps track of its fds somewhere. Thanks. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] C function - other process
Jakub =?ISO-8859-2?Q?Wo=BCny?= <[EMAIL PROTECTED]> writes: > I wrote a simple function: > signal(SIGCHLD,SIG_IGN); > switch(fork()) This will NOT work. It WILL corrupt your database. You do not get to randomly introduce new processes into the backend set. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] C function - other process
On Fri, Apr 22, 2005 at 03:39:55PM -0400, Tom Lane wrote: > Jakub =?ISO-8859-2?Q?Wo=BCny?= <[EMAIL PROTECTED]> writes: > > I wrote a simple function: > > > signal(SIGCHLD,SIG_IGN); > > switch(fork()) > > This will NOT work. It WILL corrupt your database. You do not get to > randomly introduce new processes into the backend set. What about fork() followed by exec*(), either explicitly or via popen(), system(), etc.? Should these be avoided as well, or is there a safe way to do them? I'm thinking of the case where a user-defined function wants to invoke some external command -- I've done that in experiments but I've never been sure how safe it was. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Insert psql commands inside a script
Title: Insert psql commands inside a script I have used the psql -f /tmp/SelectCommands.sql before, but now I want to put the sql statement right in the shell script. I haven't had any luck. Is there a command I can use that will not point to a file for the sql instructions but right on the same line. I use very short psql commands and would like to do it all with 1 file. Thank You David THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the sender and delete the e-mail and its attachments from all computers.
Re: [SQL] 'Select INTO" in Execute (dynamic query )
Hi, It is not possible to access a local variable in EXECUTE Command and give a syntax error. FOR..IN..LOOP is the best option CREATE OR REPLACE FUNCTION TestQry( vCon teXt ) RETURNS VARCHAR AS $$ DECLARE var1 varchar(10); var2 varchar(10); result varchar( 20 ); rRec RECORD; BEGIN FOR rRec IN EXECUTE( 'SELECT A1,A2 FROM '||vCon ) LOOP var1 = rRec.A1; var2 = rRec.A2; END LOOP; RETURN VAR1||VAR2; END; $$ LANGUAGE 'plpgsql'; Regards, R.Muralidharan. -Original Message-From: Dinesh Pandey [mailto:[EMAIL PROTECTED]Sent: Monday, April 18, 2005 9:35 PMTo: pgsql-sql@postgresql.org; pgsql-general@postgresql.orgSubject: [SQL] 'Select INTO" in Execute (dynamic query ) Hi What’s wrong with this code (ERROR: syntax error at or near "INTO" at character 8)? Problem: I want to put A1, A2 values in two variables vara, varb. CREATE OR REPLACE FUNCTION test(text) RETURNS VARCHAR AS $$ Declare vara VARCHAR(10) :=''; varb VARCHAR(10) :=''; result VARCHAR(10) :='Result'; BEGIN EXECUTE( 'Select INTO vara, varb A1, A2 from '|| $1 ); RETURN result||': '|| vara ||' '|| varb; END; $$ LANGUAGE plpgsql; RegardsDinesh Pandey
[SQL] Looking for a way to sum integer arrays....
I'd like to be able to sum up an integer array. Like so:
{3,2,1}
+ {0,2,2}
---
{3,4,3}
The following solution I've been hacking on works, although I think it
is far from "ideal". Is there a built in way to sum up arrays? If not,
is there a better way than my crude method? I have tested this on 7.4
and 8.0. I'd also be appreciate if any insight on why my aggregate
fails to work when I have an empty initcondition. P.S. I have never
written an aggregate and I was lost trying to follow the complex_sum
example in the docs.
-
CREATE OR REPLACE FUNCTION sum_intarray(INTEGER[],INTEGER[]) RETURNS
INTEGER[] LANGUAGE 'plpgsql' AS '
/*
|| Author: Tony Wasson
||
|| Overview: Experiment with arrays and aggregates
|| 3,2,1
||+ 0,2,2
|| ---
|| 3,4,3
||
|| Revisions: (when, who, what)
|| 2005/04/21 -- TW - Create function
*/
DECLARE
inta1 ALIAS FOR $1;
inta2 ALIAS FOR $2;
out_arr INTEGER[];
out_arr_textTEXT := ;
i INTEGER;
nextnum INTEGER;
BEGIN
FOR i IN array_lower(inta1, 1)..array_upper(inta1, 1)
LOOP
RAISE NOTICE ''looking at element %'',i;
nextnum := COALESCE(inta1[i],0) + COALESCE(inta2[i],0);
RAISE NOTICE ''nextnum %'',nextnum;
out_arr_text := out_arr_text || nextnum::TEXT || '','';
RAISE NOTICE ''text %'',out_arr_text;
END LOOP;
RAISE NOTICE ''text %'',out_arr_text;
--drop the last comma
IF SUBSTRING(out_arr_text,length(out_arr_text),1) = '','' THEN
out_arr_text := substring(out_arr_text,1,length(out_arr_text)-1);
END IF;
out_arr_text := ''{'' || out_arr_text || ''}'';
RAISE NOTICE ''text %'',out_arr_text;
out_arr := out_arr_text;
RAISE NOTICE ''out_arr %'',out_arr;
RETURN out_arr;
END
';
SELECT sum_intarray('{1,2}','{2,3}');
SELECT sum_intarray('{3,2,1}','{0,2,2}');
--- Now I make a table to demonstrate an aggregate on
CREATE TABLE arraytest (
id character varying(10) NOT NULL,
somearr integer[]
);
INSERT INTO arraytest (id, somearr) VALUES ('a', '{1,2,3}');
INSERT INTO arraytest (id, somearr) VALUES ('b', '{0,1,2}');
CREATE AGGREGATE sum_integer_array (
sfunc = sum_intarray,
basetype = INTEGER[],
stype = INTEGER[],
initcond =
'{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}'
);
--
# SELECT sum_integer_array(somearr) FROM arraytest;
sum_integer_array
-
{1,3,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
Thanks in advance to anyone who reads this far.
Tony Wasson
[EMAIL PROTECTED]
---(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] Help to drop table
Please help to drop table with soace inside name. List of relations Schema | Name | Type | Owner +--+---+-- public | Facility Info| table | postgres > DROP TABLE ?? Thank you, Igor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] php wrapper
Hi, I need a PHP wrapper for PostgreSQL... I found 1000 small "+/- identicals" wrappers but incompleted There's an "ufficial" or an suggested PHP wrapper? ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(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] How to configure postgresql to accept TCP/IP connections
Hello sir
actually i am working with Qt using postgresql i have to get connection
{(database("trainee"),user("trainee"))}
from the network (database remote accessing) so i have configured
postgresql.conf and pg_hba.conf upto some extents
the fields that i have configured r as follows even then
while connecting by giving any address (even localhost) it flashes error
no pg_hba.conf entry for host 127.0.0.1 user"trainee" database "trainee"
SSL off
QPSQL:Unable to connect
in pg_hba.conf
# Using sockets credentials for improved security. Not available everywhere,
# but works on Linux, *BSD (and probably some others)
local all all ident sameuser
#local all all 127.0.0.1 sameuser
only this entry i have made
in postgresql.conf
# - Connection Settings -
tcpip_socket = true
max_connections = 100
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
superuser_reserved_connections = 2
#port = 5432
#unix_socket_directory = '/home/trainee/trainee'
#unix_socket_group = 'trainee'
#authentication_timeout = 60 # 1-600, in seconds
#ssl =true
if i do ssl true active then even ir is not giving psql prompt
please help me i am in trouble
Re: [SQL] can a function return a virtual table?
Hi, and thanks for the answer ;) (*upps* just noticed, that I sent the answer accidently direct to poster^^ *sorry*) Michael Fuhr schrieb: >> I'll pick a nit and point out that the above isn't a valid query: >> >> test=> SELECT xyz, abc FROM (SELECT * FROM tablex WHERE status > -1); >> ERROR: subquery in FROM must have an alias >> HINT: For example, FROM (SELECT ...) [AS] foo. *yeah* sure you're right. I shortened it to much^^ >> In this simple example you could omit the subquery altogether: >> SELECT xyz, abc FROM tablex WHERE status > -1; It is not about such a simple example. When joining tables (especially with outer joins) it isn't trivial ;) >> See the documentation for writing set-returning functions (SRFs). >> The following links should get you started (if you're using a version >> of PostgreSQL older than 8.0, then see the documentation for that >> version): Thanks, I think this is what I've searched for (btw. we use 7.3). But what I want to do is: CREATE FUNCTION active(char) RETURNS setof $1 AS ' SELECT * FROM $1 WHERE status>-1; ' LANGUAGE SQL; But this does not work. So I'll use views instead ;) It also should be more performant. Thanks... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] string to date conversion
Hi,
I have tried the same in PostgreSQL 8.0.1 and it is working fine. I have used
following example for testing
create table test1
(
date varchar(10)
)
insert into test1 values('2005/04/22')
select date::timestamp from test1
Regards,
R.Muralidharan
-Original Message-
From: Jerome Alet [mailto:[EMAIL PROTECTED]
Sent: Friday, April 22, 2005 2:13 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] string to date conversion
Hi,
I'm working on an existing Pg database which, please don't ask why,
stores dates as varchar attributes in the form '/MM/DD'
I'm not allowed to modify the tables to use 'timestamp' instead,
so I'd like to convert on the fly when retrieving datas with
select.
When I do :
select mydatefield::timestamp from mytable;
There's an error saying that converting from varchar to date is
not possible
How can I do this ?
Thanks in advance
Jerome Alet
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(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] Getting the output of a function used in a where clause
Thanks Tom and Rod. There are indeed several additional conditions on the "real" query which prune the search space (I formulate a quick search box and filter on Lat/Lon's within the box). Since my user interface limits the search to a 30 mile radius, there are at most 81 results (in New York city, far fewer, for other regions of the US), so I've elected to post process the results in my script (calculating the distance for each returned record) and display only the closest 20 results (I intended to use a LIMIT clause combined with an ORDER BY in my SQL... LOL). Again, Thanks for all the great advice! -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, April 18, 2005 8:00 PM To: Rod Taylor Cc: Bill Lawrence; Scott Marlowe; PFC; pgsql-sql@postgresql.org Subject: Re: [SQL] Getting the output of a function used in a where clause Rod Taylor <[EMAIL PROTECTED]> writes: > You can force it with a subselect though: > SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) > as distance > from zipcodes) AS tab where distance <= $dist; The above will *not* stop zipdist from being run twice, because the planner will happily flatten the subquery into the outer query, resulting in the same situation of zipdist() being present twice in the text of the query. You could force the issue with SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist; since LIMIT/OFFSET clauses presently disable the flattening optimization. Keep in mind though that the OFFSET is an absolute optimization fence: it will result in the subquery being evaluated completely, even if there were outer conditions that might have avoided the need to look at some rows. For example, if the query is SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist AND some-other-conditions then not letting the some-other-conditions migrate down below the evaluation of zipdist could result in making the query be far slower, not faster, than you would get if you weren't trying to outsmart the planner. In general the system operates on the assumption that function calls are cheap relative to disk I/O. If that's not true for you, you're going to have some issues ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL group select question
Hi, The following SQL query will fetch you result based on the example given below, SELECT T.ID , T.VALUE FROM ( SELECT MAX( NUMB ) AS NUMB , NAME FROM TEST3 GROUP BY NAME ) AS A , TEST3 T WHERE A.NAME = T.NAME AND A.NUMB = T.NUMB ORDER BY T.ID DESC Regards, R.Muralidharan ---(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] Coming from Oracle SQL
I have a dumb question to ask - as I'm certain all of my future ones will be as well. I am coming from the Oracle SQL PL/SQL side of things and trying to get wrapped around PostgreSQL. I used to find tables in Oracle by using the following query and hoped to find a replacement. Better would be someplace to reference these sorts of "key" tables, etc that keep track of the indexes, tables, columns and more. select distinct table_name from all_tables; Thank you for the help - you will never know how appreciated it is! Ronni
Re: [SQL] Debet-Credit-Balance Calculation
Hi,
Using a table with larger data in
the sub-query always eats up CPU time and it is time consuming. The below given
statement uses the transaction table two times in the sub-query and for
processing every record, it have to go through the same table twice in the
sub-query. It is better to write a
function to process the data and return back the record
set.
Regards,
R.Muralidharan
-Original Message-From: Muhyiddin A.M Hayat
[mailto:[EMAIL PROTECTED]Sent: Tuesday, April 19, 2005 10:18
PMTo: pgsql-sql@postgresql.orgSubject: [SQL]
Debet-Credit-Balance Calculation
Dear All,I have problem to calculation
balance from debet and credit.my transaction table:
id |
trx_timestamptz |
account | trx_type_id |
amount++--+-+-
3 | 2005-04-14 17:16:49+08 | 01.2010100.2
| 1 |
100.00 4 | 2005-04-14 17:17:12+08 | 01.2010100.2
| 1
| 5.00 5 | 2005-04-14 17:17:12+08 |
01.2010100.3 |
1 | 60.00 6 | 2005-04-14 17:17:47+08 |
01.2010100.2 |
2 | 7000.00 7 | 2005-04-16 00:32:50+08 |
01.2010100.3 |
1 | 2.00 11 | 2005-04-16 02:45:06+08 |
01.2010100.2 |
1 | 10.00 12 | 2005-04-16 02:46:02+08 |
01.2010100.2 |
1 | 2.00 13 | 2005-04-16 02:46:59+08 |
01.2010100.2 |
2 | 163000.00 14 | 2005-04-16 02:50:17+08 |
01.2010100.5 |
1 | 10.00 15 | 2005-04-16 02:53:42+08 |
01.2010301.1 |
1 | 10.00 16 | 2005-04-16 02:57:22+08 |
01.2010100.1 |
1 | 200.00 17 | 2005-04-16 23:56:44+08 | 01.2010200.0
| 1 |
1000.00 18 | 2005-04-17 18:58:57+08 | 01.2010100.3
| 1 |
10.00 19 | 2005-04-17 19:13:05+08 | 01.2010100.2
| 1 |
10.00 20 | 2005-04-17 19:13:45+08 | 01.2010100.2
| 1 |
20.00 21 | 2005-04-17 19:15:36+08 | 01.2010100.2
| 1
| 5.00 22 | 2005-04-17 19:17:17+08 |
01.2010100.5 |
2 | 1.00 23 | 2005-04-17 19:18:06+08 |
01.2010100.4 |
1 | 20.00 24 | 2005-04-17 21:45:31+08 |
01.2010100.2 |
1 | 9000.00 25 | 2005-04-17 22:16:08+08 |
01.2010100.6 |
1 | 10.00
-
CREATE TABLE "public"."transactions" (
"id" SERIAL, "trx_timestamptz" TIMESTAMP(0) WITH TIME ZONE DEFAULT
('now'::text)::timestamp(6) with time zone NOT NULL, "account"
CHAR(16) NOT NULL, "trx_type_id" INTEGER NOT NULL,
"amount" NUMERIC(15,2) DEFAULT 0 NOT NULL, "uid" INTEGER NOT NULL,
CONSTRAINT "transactions_pkey" PRIMARY KEY("id")) WITH
OIDS;
---
and transaction type :
id | trx_name |
t_type+--+ 1 | Credit |
CR 2 | Debet | DB
-CREATE TABLE "public"."trx_type" ( "id" SERIAL,
"trx_name" VARCHAR(32), "t_type" CHAR(2),
CONSTRAINT "trx_type_pkey" PRIMARY KEY("id"), CONSTRAINT
"trx_typei_check0" CHECK ((t_type = 'CR'::bpchar) OR (t_type =
'DB'::bpchar))) WITH OIDS;
-
so, i'm using this query:
SELECT trans.id,
trans.trx_timestamptz, trans.account,
trans.debet, trans.creditFROM (
SELECT
transactions.id,
transactions.trx_timestamptz,
transactions.account,
CASE WHEN trx_type.t_type
= 'DB' THEN
transactions.amount
ELSE
0 END AS
debet,
CASE WHEN trx_type.t_type = 'CR'
THEN
transactions.amount
ELSE
0 END AS credit
FROM
transactions INNER JOIN trx_type ON
(transactions.trx_type_id = trx_type.id)
) AS trans
result from above query :
id |
trx_timestamptz |
account | debet
|
credit++--+---+-
3 | 2005-04-14 17:16:49+08 | 01.2010100.2
| 0 |
100.00 4 | 2005-04-14 17:17:12+08 | 01.2010100.2
| 0 |
5.00 5 | 2005-04-14 17:17:12+08 | 01.2010100.3
| 0 |
60.00 6 | 2005-04-14 17:17:47+08 | 01.2010100.2
| 7000.00
| 0 7 |
2005-04-16 00:32:50+08 | 01.2010100.3
| 0 |
2.00 11 | 2005-04-16 02:45:06+08 | 01.2010100.2
| 0 |
10.00 12 | 2005-04-16 02:46:02+0
[SQL] select the last post
hi there, hi have a table called forum, and i've got this query: this query select the messages and the number of replies to the message individualy. i'm trying to select the last post of each set of replies, like we see in the foruns. does anyone can help me best regards, etur SELECT id, subject, to_char(post_time, '.MM.DD @ HH24:MI:SS'::text) AS post_time, content, login, messages.parent_id, replies.answered FROM ( SELECT f.id, f.subject, f.post_time, f.content, u.login, f.parent_id FROM forum f, system_users u WHERE f.parent_id IS NULL AND f.user_id = u.id) messages LEFT JOIN ( SELECT forum.parent_id, count(forum.id) AS answered FROM forum GROUP BY forum.parent_id) replies ON messages.id = replies.parent_id; ---(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
Re: [SQL] SQL subquery (count distinct) - Any Ideas?
Hi
I have modified the SQL query given by you and I hope the query given below
will give you the correct count.
SELECT U.USER_ID , U.NAME , COUNT(*) FROM USER U , CALL C , CALLER_SESSION CS
WHERE C.CALLER_SESSION_ID = CS.CALLER_SESSION_ID AND U.USER_ID = CS.USER_ID AND
CAST( CS.SESSION_DATE AS DATE ) = CAST( '2005-04-13' AS DATE )
GROUP BY U.USER_ID
Regards,
R.Muralidharan
-Original Message-
From: Matt Fulford [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 13, 2005 10:59 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] SQL subquery (count distinct) - Any Ideas?
I'm trying to write a query to return the number of different customers
called on a single day. The database has a table called 'user', a table
called 'caller_session' which references a 'user' record, and a table called
'call' which references a 'caller_session'. This is my current attempt:
select
user.name,
sum((select count(distinct call.customer_id) from call where
call.caller_session_id=cs.caller_session_id)) as contacted
from user, caller_session cs
where cs.user_id=user.user_id
and date(cs.session_date) = date('2005-04-13')
group by user.name;
I get back a list of names and a call count (as desired), but the count is
not correct! The subqery is counting the number of different customers
called for each caller_session, and summing them based on the user.name
field. If the same customer is called in 2 different sessions, this is
being counted as 2 customers, but should be 1!
The 'contacted' count calculation has to be in a subquery as above, because
there are other subqueries after this one to calculate other data based on
the outer query (the outer query has to be as it is here). Not quite sure
where the sum, count and distinct should really go to get the required
results!
Any help greatly appreciated!
---(end of broadcast)---
TIP 8: explain analyze is your friend
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] tsearch2
Thanks Oleg!
Did as you recommended:
set_curcfg('default');
got new error running query containing:
to_tsquery('advanced|tech');
rank(avectors,a2);
ERROR: ExecMakeTableFunctionResult: expression is not a function call
Past message board suggests this is a pg error, not tsearch2 -- do you know
of any workarounds?
Thanks for all your help Oleg,
Dan
- Original Message -
From: "Oleg Bartunov"
To: "Dan Feiveson" <[EMAIL PROTECTED]>
Sent: Monday, April 18, 2005 10:13 PM
Subject: Re: [SQL] tsearch2
> On Mon, 18 Apr 2005, Dan Feiveson wrote:
>
> > Hi Oleg,
> >
> > Am getting when running certain functions in tsearch2:
> >
> > ERROR: could not find tsearch config by locale
> >
> > Message boards suggest problem comes from LC_CTYPE and LC_COLLATE not
being
> > specifiec in cfg (which in our case they are not). Wondering what they
> > should be set to and if that might affect our existing settings.
>
> I see no problem if you follow instructions from documentation.
> A lot of people use tsearch2 and I don't believe your case is very
> specific. You may, at least, use set_curcfg('your_configuration_name')
> to set default configuration and show_curcfg() to see current config:
>
> test=# select oid,* from pg_ts_cfg;
> oid | ts_name | prs_name |locale
> -+-+--+--
> 6214174 | default | default | C
> 6214175 | default_russian | default | ru_RU.KOI8-R
> 6214176 | simple | default |
> (3 rows)
>
> test=# select show_curcfg();
> show_curcfg
> -
> 6214175
> (1 row)
>
> My default config is 'default_russian' which corresponds to my locale
> setup 'ru_RU.KOI8-R' and I have no problem. If your locale is 'C', than
> you should use 'default' configuration.
>
> test=# select set_curcfg('default');
> set_curcfg
>
>
> (1 row)
>
> test=# select show_curcfg();
> show_curcfg
> -
> 6214174
> (1 row)
>
> You need to grant permission to use on tseach2 configuration tables (
pg_ts_* ).
>
>
> >
> > Thanks a lot Oleg,
> >
> > Dan
> >
> > - Original Message -
> > From: "Oleg Bartunov"
> > To: "Dan Feiveson" <[EMAIL PROTECTED]>
> > Cc:
> > Sent: Monday, April 18, 2005 4:19 PM
> > Subject: Re: [SQL] tsearch2
> >
> >
> >> Dan,
> >>
> >> I don't remember what the probem you have ?
> >>
> >> Oleg
> >> On Mon, 18 Apr 2005, Dan Feiveson wrote:
> >>
> >>> Hi Oleg,
> >>>
> >>> Still trying to get tsearch2 to work ... from archived message board
it
> > looks like our problem is that we don't have LC_CTYPE and LC_COLLATE
> > established.
> >>>
> >>> We're running 7.3.4 - are there any potential pitfalls if we set
> > LC_CTYPE and LC_COLLATE (in conjunction with other settings? -- We set
the
> > locale of C on our current configuration to allow LIKE searches to use
> > indexes - will this be affected? Also what grants do we need to run to
get
> > a non super user working with tsearch2?
> >>>
> >>> Thanks again for all your help,
> >>>
> >>> Dan
> >>>
> >>> Dan Feiveson
> >>> DataJoe LLC
> >>>
> >>>
> >>
> >> Regards,
> >> Oleg
> >> _
> >> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> >> Sternberg Astronomical Institute, Moscow University (Russia)
> >> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> >> phone: +007(095)939-16-83, +007(095)939-23-83
> >>
> >> ---(end of
broadcast)---
> >> TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]
> >
>
> Regards,
> Oleg
> _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] How to select from many database ??
i want to select from many databases, for example , i want to select table master in database A, and table master in database B. can i do it like this : " select * from A.Master, B.Master " just like in SQL Server ?? or can somebody help me how to select many database in postgresSQL ?? Thank u ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to select from many database ??
On Tue, Apr 19, 2005 at 10:13:52AM +0700, Art - Feping wrote: > > i want to select from many databases, for example , i want to select table > master in database A, and table master in database B. > can i do it like this : " select * from A.Master, B.Master " just like > in SQL Server ?? PostgreSQL doesn't directly support queries to multiple databases, but see contrib/dblink for a way to do it. See also the third-party dbi-link module. http://pgfoundry.org/projects/dbi-link -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Coming from Oracle SQL
On Tue, Apr 19, 2005 at 09:37:19AM -0600, Veronica L Bounmixay wrote: > > I have a dumb question to ask - as I'm certain all of my future ones will > be as well. I am coming from the Oracle SQL PL/SQL side of things and > trying to get wrapped around PostgreSQL. I used to find tables in Oracle > by using the following query and hoped to find a replacement. Better > would be someplace to reference these sorts of "key" tables, etc that keep > track of the indexes, tables, columns and more. > > select distinct table_name from all_tables; See the "System Catalogs" and "The Information Schema" chapters in the documentation. If you're using psql, see the "Meta-Commands" section in its documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
Re: [SQL] Help to drop table
On Fri, Apr 22, 2005 at 03:24:10PM +1000, Igor Kryltsov wrote: > > Please help to drop table with soace inside name. > > > List of relations > Schema | Name | Type | Owner > +--+---+-- > public | Facility Info| table | postgres > > > DROP TABLE ?? See "Identifiers and Key Words" in the "SQL Syntax" chapter of the documentation, in particular the discussion of quoted identifiers. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] Insert psql commands inside a script
On Thu, Apr 21, 2005 at 05:19:23PM -0500, Letnes, David G. wrote: > > I have used the psql -f /tmp/SelectCommands.sql before, but now I want > to put the sql statement right in the shell script. I haven't had any > luck. Is there a command I can use that will not point to a file for > the sql instructions but right on the same line. I use very short psql > commands and would like to do it all with 1 file. For simple queries you can use psql -c: psql -c 'SELECT * FROM foo' You can embed an SQL script with a "here document" if your shell supports it (it probably does): #!/bin/sh echo "before database connection" psql
Re: [despammed] [SQL] Insert psql commands inside a script
am 21.04.2005, um 17:19:23 -0500 mailte Letnes, David G. folgendes: > > I have used the psql -f /tmp/SelectCommands.sql before, but now I want > to put the sql statement right in the shell script. I haven't had any > luck. Is there a command I can use that will not point to a file for > the sql instructions but right on the same line. I use very short psql > commands and would like to do it all with 1 file. echo "select bla from fasel" | psql -U database You can also use here documents, shell-variables and so on. Regards, 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: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to install Postgres that supports 64-bit
On Fri, 2005-04-22 at 00:30, Dinesh Pandey wrote: > How to install Postgres 8.0.1 that supports 64-bit integer/date-time. > > > > # ./configure --prefix=/usr/local/pgsql > --with-tclconfig=/usr/local/lib --with-tcl > > checking build system type... sparc-sun-solaris2.8 > > checking host system type... sparc-sun-solaris2.8 > > checking which template to use... solaris > > checking whether to build with 64-bit integer date/time support... no > > checking whether NLS is wanted... no > > checking for default port number... 5432 > > checking for gcc... gcc > > > > while installation I am getting this message:â checking whether to > build with 64-bit integer date/time support... noâ > --enable-integer-datetimes enable 64-bit integer date/time support ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
