Dean Gibson (DB Administrator) (postg...@ultimeth.com)

If I "ALTER DATABASE ... SET client_encoding TO DEFAULT", is the default
the "client_encoding" in postgresql.conf when the server was last
started, or the value at the time the "ALTER DATABASE ... SET
client_encoding TO DEFAULT" statement is executed?

In other words, if I "ALTER DATABASE ... SET client_encoding TO DEFAULT"
and then restart the server with a different "client_encoding" value in
postgresql.conf, will client_encoding of the database follow the new
"client_encoding" value in postgresql.conf?

My guess is no;  that it will keep the "client_encoding" it received 
when the "ALTER DATABASE ... SET client_encoding TO DEFAULT" statement
was executed.

What I'm looking for is a way to have the client_encoding of all
databases track some common setting (eg, the "client_encoding" value in
postgresql.conf), but that may not be possible.  I know I can use 
"PGCLIENTENCODING=${LANG#*.}" (and that's not a bad solution), but I'm
curious if there's a way to avoid that (and similar client methods).  
Right now I'm basically ignoring the database's "client_encoding"
setting, and always explicitly setting it in a session where needed.

-- Dean

On 2008-02-21 13:37, Justin wrote:
> ... I'm wondering if there is a way to create this in a single select
> statement??
> I can't think of a way to do it???
Break down your problem using VIEWs.  Create a VIEW that gets just ONE 
of the averages, based on a starting date.  Then create a SELECT that 
gets data from the VIEW as though it was an actual table.

Once you get it working, you can replace the reference to the VIEWs in
the SELECT statement, with the definition of the VIEWs, but I would not
do that unless the result is relatively simple and easy to understand.  
Maintainability should be your goal.

-- Dean

No, not on a numeric type.  The database stores a single byte code from 
a gov't DB.  In a VIEW, I do a table lookup on the code and suffix an 
English explanation of the code.  However, some of the users of the VIEW 
(eg, php) would like to do a SELECT based on the original value, and I
use CAST( ... AS CHAR ) to get just the original code back.  I use the 
CAST as a shorthand for SUBSTRING.  I don't know if that is easier for 
the planner to flatten than a function call, but it's easier (for me) to
read (especially if I use the PostgreSQL "::" cast extension).


>>> > What is wrong with using VARCHAR for your
>>> purpose????????????????????????????
>>>
>>> Simply that a commonly used database (my) does not support it.
>>
>> By "my", do you mean "MySQL", or "MyDatabase"? If the latter, then
>> while it's your business decision (and/or that of your customers),
>> the availability of decent, free databases should make a compelling
>> case for anyone using anything else, to migrate (and never look back)
>> to something full-featured.
> Yes, Mysql, and yes, it's customer driven.
>
Then I don't understand.  While I've never used MySQL, the MySQL web 
pages apparently indicate that VARCHAR has been supported since version
3.2: http://dev.mysql.com/doc/refman/4.1/en/char.html

Actually, I just write "CHAR" for a length of 1.

> > What is wrong with using VARCHAR for your
> purpose????????????????????????????
>
> Simply that a commonly used database (my) does not support it.

By "my", do you mean "MySQL", or "MyDatabase"?  If the latter, then 
while it's your business decision  (and/or that of your customers), the 
availability of decent, free databases should make a compelling case for
anyone using anything else, to migrate (and never look back) to
something full-featured.

It's like requiring portable C code to use the old, pre-ANSI style of
function declarations, because some old C compilers might not support
the ANSI style.  I think Richard Stallman of the FSF takes that 
position, but I don't know of anyone else (although I'm sure there are
exceptions).

This is a multi-part message in MIME format.
--------------020901030109020800050702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2008-02-12 07:30, Ken Johanson wrote:
>>
>> Sure, but you're a prime candidate for understanding the value of
>> following the spec if you're trying to write software that works with
>> multiple databases.
>
> The spec has diminished in this (CAST without length) context:
> a) following it produces an output which has no usefulness whatsoever
> (123 != 1)
I *OFTEN* use a cast of CHAR to get just the first character.

> b) all the other databases chose to not follow the spec in the context
> of cast and char with implicit length.

I doubt that:

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/castsp.htm
http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx

Your specific example is covered here:
http://vista.intersystems.com/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_cast

and here:
http://answers.yahoo.com/question/index?qid=20071017084134AA4mCJC

>
> When the length is unqualified, a cast to char should one of:
>
> 1) failfast
> 2) auto-size to char-count (de facto)
> 3) pad to the max-length

What is wrong with using VARCHAR for your
purpose????????????????????????????  If you want the string auto-sized, 
that is what VARCHAR is for.

CHAR is, BY DEFINITION, a DECLARED fixed length.



--------------020901030109020800050702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
On 2008-02-12 07:30, Ken Johanson wrote:
<blockquote cite="mid:47B1BB89.6000203@kensystem.com" type="cite">
  <blockquote type="cite"><br>
Sure, but you're a prime candidate for understanding the value of
following
the spec if you're trying to write software that works with multiple
databases.
    <br>
  </blockquote>
  <br>
The spec has diminished in this (CAST without length) context:
  <br>
a) following it produces an output which has no usefulness whatsoever
(123 != 1)
  <br>
</blockquote>
I <b>OFTEN</b> use a cast of CHAR to get just the first character.<br>
<br>
<blockquote cite="mid:47B1BB89.6000203@kensystem.com" type="cite">b)
all the other databases chose to not follow the spec in the context of
cast and char with implicit length.<br>
</blockquote>
<br>
I doubt that:<br>
<br>
<a class="moz-txt-link-freetext" href="http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/castsp.htm">http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/castsp.htm</a><br>
<a class="moz-txt-link-freetext" href="http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx">http://msdn2.microsoft.com/en-us/library/aa258242(SQL.80).aspx</a><br>
<br>
Your specific example is covered here:<br>
<a class="moz-txt-link-freetext" href="http://vista.intersystems.com/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_cast">http://vista.intersystems.com/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_cast</a><br>
<br>
and here:<br>
<a class="moz-txt-link-freetext" href="http://answers.yahoo.com/question/index?qid=20071017084134AA4mCJC">http://answers.yahoo.com/question/index?qid=20071017084134AA4mCJC</a><br>
<br>
<blockquote cite="mid:47B1BB89.6000203@kensystem.com" type="cite"><br>
When the length is unqualified, a cast to char should one of:
  <br>
  <br>
1) failfast
  <br>
2) auto-size to char-count (de facto)
  <br>
3) pad to the max-length
  <br>
</blockquote>
<br>
What is wrong with using VARCHAR for your
purpose????????????????????????????  If you want the string auto-sized,
that is what VARCHAR is for.<br>
<br>
CHAR is, BY DEFINITION, a DECLARED fixed length.<br>
<br>
<br>
</body>
</html>

--------------020901030109020800050702--