Re: select statement sorting

On Wed, Mar 31, 2004 at 10:31:43 -0500,
Alexander Cohen <alex(at)toomuchspace(dot)com> wrote:
> is it possible to have postgres return a "SELECT * FROM table ORDER BY
> table_column" query that is not case sensitive order?
>
> if i have these words in a column:
>
> Alex
> alex
> Barbara
> Cohen
>
> i will get them back in the following order in the tuples:
>
> Alex
> Barbara
> Cohen
> alex
>
> But i want them back likke this:
>
> Alex
> alex
> Barbara
> Cohen
>
> Is this possible or do i need to sort them myself after the query
> returns?

You can do an order by lower(table_column) if you don't care whether
"Alex" or "alex" comes first. You can use a functional index on lower
to speed this up if your table is large.

> Also, i noticed that postgres will let me create groups, databases and
> users with spaces in their names, is this ok or should i check this
> beforehand and not allow this?

They will work, but you will need to quote the names when you use them.
My sugestion would be to use underlines instead of spaces in the names.
I think that will be more readable than quoted names with blanks in them.

If an application is creating these names on the fly using user input, I
think you need to be very careful. In that case you probably shouldn't
be using meaningful names but rather create names in a pattern that
can't duplicate any other objects and keep a table with information about
these objects with such things as the type, name and description.