This is issue number 40 of General Bits--that is quite a few threads read, correspondence, articles written, code tested, and web pages adorned.

It is time for another survey. As in previous surveys, I'd like to encourage you to email me with your experience and opinions about General Bits. Below are survey questions to answer and also feel free to add anything else that is not covered by the list.

• Where did you hear about General Bits?
• Is the technical level too high or too low or a good mix?
• Are the thread topics selected pertinent to what you need to know?
• Are you learning about things you would not have otherwise researched?
• Is the format and language easy to read?
• Is the format too long? too short? Too many items? too few?
• Do you read the column each week? Have you read all of the columns?
• Do you read the English or the Portuguese edition?
• Have you used the google search? Did it work well for you?
• Would you find a more closely indexed search of issues more useful?
• How long have you been a postgresql user?
• What work do you do with Postgresql? (DBA, Consultant, Application Developer, etc.)
• What postgresql mailing lists or news groups do you read?
• Do you host your database with a database hosting company? If so, which one?
• Do you purchase technical support for postgresql from a company? If so, which one?
• Have you downloaded anything from the General TidBits area?
• What would you like to see an article written about?

I always welcome comments, suggestions of articles and people who write guest articles. But especially I'd like to thank everyone who sends me corrections. Thank you!

Editor: elein at varlena.com

Creating CSV files Exporting Data 21-Aug-2003

psql is really a very nice little tool. One of the nice features is the ability to export data from a query in a machine readable format. If you create data for export into a spreadsheet or an html page or even something for your perl script to parse externally you can use psql to format the data to taste. Using psql with the shell makes a very versatile data import/export tool.

The key options for exporting data using psql are:

• -A -- unaligned mode
• -t -- tuples (data) only mode
• -F sep -- specification of a separator

	psql -At -F "," database -c "select ..." > query.csv

You can specify the query or query input to psql in the several ordinary ways. From the shell, you can use standard redirection;

	< file.sql 

	<<  END
		select ..
	END

	psql -At -F "," -c "select ..." > query.csv

Contributors: elein at varlena.com

Epoch to Timestamps and back [SQL] date calculation 22-Aug-2003

There is not a built in function to convert an epoch time into a timestamp. But this function will do the trick.

   CREATE FUNCTION epoch_to_timestamptz ( INTEGER )
   RETURNS timestamptz 
   AS '
      SELECT ''epoch''::timestamp WITH TIME ZONE + $1 * ''1 second''::INTERVAL;
   ' language 'sql' 
   IMMUTABLE RETURNS NULL ON NULL INPUT ;
   
   =# select epoch_to_timestamptz(967783763);
     epoch_to_timestamptz  
   ------------------------
    2000-08-31 21:49:23-07
   (1 row)
   
   =# select extract( epoch from timestamptz '2000-08-31 21:49:23-07');
    date_part 
   -----------
    967783763
   (1 row)

But if you find it necessary to do this conversion often, you can add it to your own "standard utility" functions. If you install your "standard utility" functions into template1, then your subsequent database creations will have them immediately available.

Contributors: MichaelHoeller at t-online.de, Tomasz Myrta jasiek at klaster.net, Tom Lane tgl at sss.pgh.pa.us

Input columns, output columns and Order by [GENERAL] Why lower's not accept an AS declaration ? 18-Aug-2003

The select list, or target list, is generally the list of columns and expressions between the SELECT statement and the FROM clause. Elements of the select list are in the form input column AS output column where the "AS output column" is optional.

The input column can be a correlated or unambiguous column name of a column in a table in the FROM clause or it can be a constant or it can be an expression using column name(s) or constant(s).

If the AS clause is not present, the output column name will usually be the same as the input column name and in the case where the input column is an expression, a best guess output column name is generated by the parser.

There is a distinction made between the input column names and the output column names in a statement. Sometimes the rules about which one to use are confusing, particularly when we are not used to making a clear distinction between the input and output column names.

In the case of the ORDER BY clause, there is a distinction made when you are using an expression. The parser allows input or output column names in the ORDER BY statement except when the ORDER BY is an expression. Only input columns may be used in an expression in the ORDER BY clause. If you use an output column in an ORDER BY expression it results in the error

 ERROR:  Attribute "foo" not found 

   OK: select kw from keys order by lower(kw);
   OK: select kw as foo from keys order by foo;

   OK: select kw as foo from keys order by lower(kw);
ERROR: select kw as foo from keys order by lower(foo);

   OK: select upper(kw) from keys order by lower(kw);
ERROR: select upper(kw) as foo from keys order by lower(foo);

ERROR: select (kw || kw) as foo from keys order by lower(foo);
   OK: select (kw || kw) as foo from keys order by (kw || kw);

In the middle of this thread, Stephan and Tom poked and prodded the various SQL standards with regard to the input and output column usage in the ORDER BY clause. The standards quoted were rather dense and seemed to define elaborate restrictions regarding the other activities the input and output columns were participating in such as JOINS/INTERSECTS, set functions, etc. and whether they were explicitly correlated.

Tom wrote finally, "If I am reading it right, then we already support every case that is legal per SQL99, and more besides." Then Stephan wrote, "How about we forget that I ever brought it up. ;) ".

Contributors: Hervé Piedvache herve at elma.fr, Martijn van Oosterhout kleptog at svana.org, Darko Prenosil darko.prenosil at finteh.hr, Stephan Szabo sszabo at megazone.bigpanda.com, Bruno Wolff III bruno at wolff.to, Tom Lane tgl at sss.pgh.pa.us, Joe Conway mail at joeconway.com

New French Language Mailing list [GENERAL] Mailing list in French 19-Aug-2003

There is now a French language list available for PostgreSQL called pgsql-fr-generale. Francois Suter says, " I hope many of you will join it so that we can make it an interesting place."

You can subscribe to the list by sending a mail to majordomo@postgresql.org, leave the subject line blank and put in the body "SUBSCRIBE pgsql-fr-generale". It will soon be available also on the postgresql.org website.

Contributors: Francois Suter dba at paragraf.ch

BTree Index is better than a Hash Index [GENERAL] Postgres Hanging on Inserts 29-Jul-2003

Hash indexes in Postgres are known to have poor concurrency, and were previously known to have internal deadlock problems. It is a bug that this case hangs rather than raise a deadlock detected error message.

Hash indexes are "mostly an academic exercise" and are not widely used. It is recommended, particularly for tables with high concurrency that the hash indexes be replaced with btree indexes. BTrees are more developed and stable than hashes and you probably will find a performance improvement as well.

Contributors: Adam Kavan akavan at cox.net, Tom Lane tgl at sss.pgh.pa.us, Alvaro Herrera Munoz alvherre at dcc.uchile.cl