I had a project the other day that required me to shorten a description down to a certain length and add an elipsis. The problem I encountered was making sure that the truncation did not happen in the middle of a word.

So using a Postgres database, I used the following query to return the shortened description without ending in a partial word.

select 
    case when length(long_description)>= 100
    then reverse(substring(reverse(left(long_description,100)),position(' ' in reverse(left(long_description,100))))) || '...'
    else long_description
    end
    from data_feed

The steps involved were to

1.  Check if the field is over 100 characters,  if >100, then continue to step 2
2.  Use left function to get 100 characters of the string
3.  Reverse the string returned from left
4.  Get a substring of the reversed truncated string from the first space (‘ ‘) to the end of the reversed truncated string
5. Reverse the final string (So it is back to the correct direction of text)
6. Append the ellipsis to the string ( or add a link or leave off if no extra chars are required)

Tags: Postgres, SQL

I work with a large Postgres install that has many hundreds of schemas. there are times that I need to find a list of tables and fields from a specific schema for a web application that I am developing.

This will return the list of table names within the giving schema:

select relname 
from pg_stat_user_tables 
WHERE schemaname='schema1';

Then to get the given field names from the table that I have chosen:

select column_name 
from information_schema.columns 
where table_name='mytable';

Tags: SQL

So I didn’t have a table of dates set up, but I needed to get a list of dates going
back a certain number of days. I was looking at programming up a date function but
decided that a little SQL could do just what I wanted. I used the following query
to generate my list:

select (current_date-21) +s.a as date from generate_series(0,21) as s(A);

This returned a list of dates:

2012-06-01
2012-06-02
2012-06-03
2012-06-04
2012-06-05
2012-06-06
2012-06-07
2012-06-08
2012-06-09
2012-06-10
2012-06-11
2012-06-12
2012-06-13
2012-06-14
2012-06-15
2012-06-16
2012-06-17
2012-06-18
2012-06-19
2012-06-20
2012-06-21
2012-06-22

This dealt with end of month issues and end of year issues without having to generate a ton
of code to do the same thing.

Tags: Programming, SQL