[GENERAL] date problem
Hi, I thought this would be a classic sort of query but google did no give me the answer. I am attempting to select records where one of the dates is the latest date before today select max(date) from expo where date now() works for one record but other fields I need must be in aggregate or grouped. Is there a simple SQL request to get the most recent records from a set of joined tables? TIA Tony ---(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: [GENERAL] date problem
# [EMAIL PROTECTED] / 2005-05-16 09:48:08 +0200: I am attempting to select records where one of the dates is the latest date before today select max(date) from expo where date now() works for one record but other fields I need must be in aggregate or grouped. Is there a simple SQL request to get the most recent records from a set of joined tables? select * from expo where date = (select max(date) from expo where date now()) as x; -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] date problem
tony wrote: Hi, I thought this would be a classic sort of query but google did no give me the answer. I am attempting to select records where one of the dates is the latest date before today select max(date) from expo where date now() works for one record but other fields I need must be in aggregate or grouped. Is there a simple SQL request to get the most recent records from a set of joined tables? SELECT * FROM expo WHERE date = (SELECT max(date) FROM expo WHERE date now()); In fact, you might want to rewrite the subselect. Oh, and date is likely to cause trouble as a column-name. SELECT * FROM expo WHERE my_date = ( SELECT my_date FROM expo WHERE my_date now() ORDER BY my_date DESC LIMIT 1 ) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] date problem
Le lundi 16 mai 2005 à 10:30 +0200, Roman Neuhauser a écrit : select * from expo where date = (select max(date) from expo where date now()) as x; Thanks! I keep forgetting this for some strange reason... I was putting it in the from instead of the where Tony ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] date problem
Le lundi 16 mai 2005 à 09:44 +0100, Richard Huxton a écrit : works for one record but other fields I need must be in aggregate or grouped. Is there a simple SQL request to get the most recent records from a set of joined tables? SELECT * FROM expo WHERE date = (SELECT max(date) FROM expo WHERE date now()); In fact, you might want to rewrite the subselect. Oh, and date is likely to cause trouble as a column-name. SELECT * FROM expo WHERE my_date = ( SELECT my_date FROM expo WHERE my_date now() ORDER BY my_date DESC LIMIT 1 ) Thanks! I wasn't really using date but for clarity had put that instead of my French column name... Tony ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] UTF-8 and LC_CTYPE locale
Hi *,
we are using PostgreSQL for data in different
languages like English, German andFrench.
The encoding and locale parameters on our OS (UTF-8
and en_US.UTF-8) had problems e.g. with german umlaut.
After some tries we found encoding andlocale
parameters(LATIN1 and de_DE.iso88591) which work fine concerning
lc_collate and lc_type.
But our clients would prefer an UTF-8 encoding. We
have tried all de_*.UTF-8* encodings but none works ("ERROR: invalid
multibyte character for localeHINT: The server's LC_CTYPE locale is
probably incompatible with the database encoding.").
There are about 140 UTF-8 locales left to try.
Thereis the parameter client_encoding.
What shall we do?
Any hint is highly appreciated.
thx in advance
Stefan
[GENERAL] Sequences question problem
Hi there, I am having small trouble with sequences. I am inserting row into table, and I need to get value of its 'id' column (sequencen type). As far I know it have to be done by SELECT last_value FORM seq_name (or next_val before insert). Normally, sequences are created by 'table' 'column' '_seq' eg. 'table_id_seq'. Now let's rename 'table' to 'table2'. Sequence name wan't renamed. And it's my problem... I can't simple assume 'sequence id' by table/column... I see 2 solution: 1. udpate pg_class after renaming. Something like: UPDATE pg_class SET relname='table2_id_seq' WHERE relname='table_id_seq' Will this work well for all possible cases ? Is this good way ? I tried that that and then 'insert' itselfs generates error: ERROR: Relation public.table_id_seq does not exist It seems it was looking for OLD sequence name. I used psql for updating, while postgres was normally running. Is this problem on my side or I can't simple change 'relname' of pg_class ? 2. make code reading 'sequence names' live. I looked around pg_class but I am not sure which relation between reltype,relfilenode,reltoastrelid are proper ones... So, how to get 'sequence name' by its 'table name' ? Name of sequence and table are not related (we can't assume some prefix in name, because table was renamed) Will this pg_class reference cost many CPU ? Thank you, Best regards, Lada 'Ray' Lostak Unreal64 Develop group http://www.orcave.com http://www.unreal64.net -- In the 1960s you needed the power of two C64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI. Imagination is more important than knowledge... ---(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
[GENERAL] Relation between pgsql_tmp and work_mem
I read somewhere in the archives we can set work_mem to the largest size of tmp file created in the pgsql_tmp folder but how can i make those files in pgsql_tmp not be deleted after use... so that i can get the file listing for all files created during the run... currently i am doing ls -l sampling at 1s is there a better way Himanshu __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] connecting to server process via sockets
For fun and learning, I would like to connect to the Postgresql backend
and issue queries using sockets. I'm using Python's socket module. I'm
new to socket programming, but I'm experienced with Python and
Postgresql. I've been using the JDBC driver and the online
documentation as a guide, but I'm afraid my ignorance has led me to
failure thus far.
This is what I think I understand.
1. Send the startup message as such.
a. length of message.
b. protocol major (3)
c. protocol minor (0) (don't know what this is).
d. message
e. send 0 (Don't know why?)
O.K. Here I show my ignorance.
#!/usr/bin/python
import socket
# Connection string
cnstring = 'user=randall, database=dws, client_encoding=UNICODE,
DateStyle=ISO' # This just wrapped in my email.
msg_len = str(len(cnstring))
protocol_major = '3'
protocol_minor = '0'
pgsocket = socket.socket()
pgsocket.connect(('localhost', 5432))
pgsocket.send(msg_len)
pgsocket.send(protocol_major)
pgsocket.send(protocol_minor)
pgsocket.send(cnstring)
pgsocket.send('0')
pgsocket.close()
When I run this, this is what shows up in the logs.
2005-05-16 10:11:34 [2638] LOG: connection received: host=127.0.0.1
port=42607
2005-05-16 10:11:34 [2638] LOG: invalid length of startup packet
Please do not recommend that I use an existing API. I'm doing this for
fun and maybe to come up with a simple pure python database driver.
Randall
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] connecting to server process via sockets
On May 16, 2005, at 11:14 AM, Randall Smith wrote: When I run this, this is what shows up in the logs. 2005-05-16 10:11:34 [2638] LOG: connection received: host=127.0.0.1 port=42607 2005-05-16 10:11:34 [2638] LOG: invalid length of startup packet We just had a thread about this on the Novice (!) list. It should answer your question about what the startup packet needs to look like. http://archives.postgresql.org/pgsql-novice/2005-05/msg00130.php John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] connecting to server process via sockets
Randall Smith [EMAIL PROTECTED] writes: For fun and learning, I would like to connect to the Postgresql backend and issue queries using sockets. I'm using Python's socket module. I'm new to socket programming, but I'm experienced with Python and Postgresql. I've been using the JDBC driver and the online documentation as a guide, but I'm afraid my ignorance has led me to failure thus far. [...] No direct help, but a couple of suggestions: 1) Take a look at pg-dot-lisp, which does the same thing you are trying to do, but in Lisp. You might find it more readable than the JDBC driver. I am not sure which protocol versions it supports, though. It's at: http://www.chez.com/emarsden/downloads/ 2) Write a simple libpq app (or use one of the existing Python interfaces linked against libpq) and watch the session with Etherial or tcpdump. That'll show you the differences between your failing app and a working connection... -Doug ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] connecting to server process via sockets
Randall Smith wrote: For fun and learning, I would like to connect to the Postgresql backend and issue queries using sockets. I'm using Python's socket module. I'm new to socket programming, but I'm experienced with Python and Postgresql. Look in the postgres docs for the section on frontend/backend protocol. The handshaking and format of the bytes that go across the socket are explained in that doc. section. You also might want to look at: http://ca.geocities.com/[EMAIL PROTECTED]/pgsqueak/ where there are links to a class diagram and state diagram of an implementation done in Squeak Smalltalk. I don't know Python, but IMHO the basic design should be transferable. Note that frontend/backend protocol version 2 is implemented in that code, and the current docs will reflect version 3 (so you'd have to look at the older docs to match up the code). Hope that helps. --yanni ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Sequences question problem
Thank you.. I didn't know about race condition.. Shame on me :) If you're using PostgreSQL 8.0 or later then you can get the sequence name with the pg_get_serial_sequence() function: Is there any way how to do that (or add this function) in 7.3.x ? Best regards, Lada 'Ray' Lostak Unreal64 Develop group http://www.orcave.com http://www.unreal64.net -- In the 1960s you needed the power of two C64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI. Imagination is more important than knowledge... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] is in postgres solution
Hello. I have 2 variables type timestamp. Example date1 = 2005-01-01 23:00 and date2 = 2005-05-04 12:00. I want get something like age(date2,date1) but ouput format must by hours::minutes. Is that bossible? Reg, Margusja ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Sequences question problem
On Mon, May 16, 2005 at 12:29:27PM +0200, Lada 'Ray' Lostak wrote:
I am having small trouble with sequences. I am inserting row into table,
and I need to get value of its 'id' column (sequencen type). As far I know
it have to be done by
SELECT last_value FORM seq_name
(or next_val before insert).
The above queries are subject to race conditions. For example, if
transaction A inserts a record, then transaction B inserts a record,
then transaction A queries the sequence, then transaction A will
get the sequence value that was given to transaction B, which
probably isn't what you want. Use currval() or nextval() instead.
http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
http://www.postgresql.org/docs/faqs.FAQ.html#4.11.2
http://www.postgresql.org/docs/faqs.FAQ.html#4.11.3
Normally, sequences are created by 'table' 'column' '_seq' eg.
'table_id_seq'.
Now let's rename 'table' to 'table2'. Sequence name wan't renamed. And it's
my problem... I can't simple assume 'sequence id' by table/column...
If you're using PostgreSQL 8.0 or later then you can get the sequence
name with the pg_get_serial_sequence() function:
test= CREATE TABLE foo (id serial);
NOTICE: CREATE TABLE will create implicit sequence foo_id_seq for serial
column foo.id
CREATE TABLE
test= ALTER TABLE foo RENAME TO foo2;
ALTER TABLE
test= ALTER TABLE foo2 RENAME id TO id2;
ALTER TABLE
test= SELECT pg_get_serial_sequence('foo2', 'id2');
pg_get_serial_sequence
public.foo_id_seq
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Named Notation for Functions' Parameteres
On Sun, May 15, 2005 at 13:59:00 +0200, Samer Abukhait [EMAIL PROTECTED] wrote: Does PostgreSQL support by anyhow the parameter named notation in function calling?? (like oracle's: parameter_name = argument_value) If not, is it on the wish list? It is on the TODO list. ---(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
[GENERAL] Distinguishing between connections in pg_hba.conf
Hi, I have a web application (PHP) which runs on its own box, and connects to a database on a second box. The database box is behind the firewall and only accepts connections from the web server. I have set up stunnel on the web server and I would like to allow some limited external direct access to the db server, but I would like connections from stunnel to only access a specific database. The problem is that both the web server and the stunnel connections will come from the same box, and hence the same IP address, is there anyway I can distinguish between these two connection methods in pg_hba.conf? (I can't do it on username either) Thanks for any advice Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Increasing connection limit in postgres
How do you recompile postgres to allow more than 1024 connections? Thanks, Akash ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Increasing connection limit in postgres
Akash Garg [EMAIL PROTECTED] writes: How do you recompile postgres to allow more than 1024 connections? If you are using any remotely modern version of Postgres, there isn't any specific compile-time limit on that. So I think the correct answer is upgrade. 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: [GENERAL] Increasing connection limit in postgres
There should be no need to recompile anything. See the entry for max_connections: http://www.postgresql.org/docs/8.0/static/runtime-config.html#RUNTIME- CONFIG-CONNECTION -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On May 16, 2005, at 1:44 PM, Akash Garg wrote: How do you recompile postgres to allow more than 1024 connections? Thanks, Akash ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Distinguishing between connections in pg_hba.conf
On Mon, 2005-05-16 at 07:35, Adam Witney wrote: Hi, I have a web application (PHP) which runs on its own box, and connects to a database on a second box. The database box is behind the firewall and only accepts connections from the web server. I have set up stunnel on the web server and I would like to allow some limited external direct access to the db server, but I would like connections from stunnel to only access a specific database. The problem is that both the web server and the stunnel connections will come from the same box, and hence the same IP address, is there anyway I can distinguish between these two connection methods in pg_hba.conf? (I can't do it on username either) Add an alias to each machine's ethernet card, along with a name. So, if you've got 10.1.1.1 as the IP on the web server and 10.2.1.1 on the db server, add 10.1.1.2 and 10.2.1.2 on each respectively, and give them some similar name, like web02 and db02 if their names are web01 and db01. Set up routes to use the other IP addresses with those names and you should be able to do it. I haven't fleshed it out step by step, but you get the basic idea, right? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] is in postgres solution
On Mon, May 16, 2005 at 11:43:09 +0300, Margus Roo [EMAIL PROTECTED] wrote: Hello. I have 2 variables type timestamp. Example date1 = 2005-01-01 23:00 and date2 = 2005-05-04 12:00. I want get something like age(date2,date1) but ouput format must by hours::minutes. Is that bossible? extract the epoch from the difference in timestamps and that will give you a time in seconds that you can format as desired with a little math. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] is in postgres solution
On Mon, 2005-05-16 at 11:43 +0300, Margus Roo wrote:
Hello.
I have 2 variables type timestamp. Example date1 = 2005-01-01 23:00 and
date2 = 2005-05-04 12:00. I want get something like age(date2,date1) but
ouput format must by hours::minutes.
get the difference in minutes with:
extract('epoch' from date2-date1) / 60
and do the formatting with / and %
or
select extract('epoch' from date2-date1)/3600
|| '::'
|| extract('minutes' from date2-date1);
gnari
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Distinguishing between connections in pg_hba.conf
On 16/5/05 8:17 pm, Scott Marlowe [EMAIL PROTECTED] wrote: On Mon, 2005-05-16 at 07:35, Adam Witney wrote: Hi, I have a web application (PHP) which runs on its own box, and connects to a database on a second box. The database box is behind the firewall and only accepts connections from the web server. I have set up stunnel on the web server and I would like to allow some limited external direct access to the db server, but I would like connections from stunnel to only access a specific database. The problem is that both the web server and the stunnel connections will come from the same box, and hence the same IP address, is there anyway I can distinguish between these two connection methods in pg_hba.conf? (I can't do it on username either) Add an alias to each machine's ethernet card, along with a name. So, if you've got 10.1.1.1 as the IP on the web server and 10.2.1.1 on the db server, add 10.1.1.2 and 10.2.1.2 on each respectively, and give them some similar name, like web02 and db02 if their names are web01 and db01. Set up routes to use the other IP addresses with those names and you should be able to do it. I haven't fleshed it out step by step, but you get the basic idea, right? Hi, Thanks for your reply. So I see how you add an extra IP address to the web server box, but how do you assign it so that requests from apache appear on the db box as one IP address, and requests coming through stunnel appear as the second IP address? Thanks again Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(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: [GENERAL] Distinguishing between connections in pg_hba.conf
On Mon, 2005-05-16 at 15:05, Adam Witney wrote: On 16/5/05 8:17 pm, Scott Marlowe [EMAIL PROTECTED] wrote: On Mon, 2005-05-16 at 07:35, Adam Witney wrote: Hi, I have a web application (PHP) which runs on its own box, and connects to a database on a second box. The database box is behind the firewall and only accepts connections from the web server. I have set up stunnel on the web server and I would like to allow some limited external direct access to the db server, but I would like connections from stunnel to only access a specific database. The problem is that both the web server and the stunnel connections will come from the same box, and hence the same IP address, is there anyway I can distinguish between these two connection methods in pg_hba.conf? (I can't do it on username either) Add an alias to each machine's ethernet card, along with a name. So, if you've got 10.1.1.1 as the IP on the web server and 10.2.1.1 on the db server, add 10.1.1.2 and 10.2.1.2 on each respectively, and give them some similar name, like web02 and db02 if their names are web01 and db01. Set up routes to use the other IP addresses with those names and you should be able to do it. I haven't fleshed it out step by step, but you get the basic idea, right? Hi, Thanks for your reply. So I see how you add an extra IP address to the web server box, but how do you assign it so that requests from apache appear on the db box as one IP address, and requests coming through stunnel appear as the second IP address? That's kinda OS dependent. On RedHat you should have some kind of netconfig command or something that will make a setting in the /etc/sysconfig/network-scriptsifcfg-xxx files to set routes. In Fedora Core 2 the command that brings up the gui config too is system-config-network ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Relation between pgsql_tmp and work_mem
Himanshu Baweja wrote: I read somewhere in the archives we can set work_mem to the largest size of tmp file created in the pgsql_tmp folder but how can i make those files in pgsql_tmp not be deleted after use... so that i can get the file listing for all files created during the run... currently i am doing ls -l sampling at 1s is there a better way Our TODO has: * All ability to monitor the use of temporary sort files so we know we need to give users that ability someday. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS ROW_COUNT
Hi, I was asking this question some time ago and was under impression that this will be fixed in 8.x. In general problem is, CREATE TEMP TABLE AS SELECT does not report any rows to the engine, seems like, so GET DIAGNOSTICS ROW_COUNT after the statement returns 0 as well as FOUND false. This was working in 7.3, but behavior changed in 7.4. My question is, will it be fixed or should I consider not stop using get diagnostic after create temp table as select from now on. Honstly this was a very convinient feature especialy knowing that select count(*) not a fastes possible operation. Thank you. ---(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
