Postgres Column with Multiple Values

Question

I recently took over a database as admin and am unfamiliar with the columns structure in one of the tables. I would really like to know how to select one of the values in the column, as of now I can not build a where clause using this column. The column in question is the state column, I can use any of the other columns in a where clause.

-[ RECORD 1 ]--------------------------
id         | 8

name       | 13

state      | ---                       +
           | :name: California         +
           | :abbr: CA                 +
           | 

created_at | 2011-12-08 04:31:15.104002

updated_at | 2011-12-08 04:31:15.104002

Answer 1

A column can't contain multiple values, but a text column can contain values with multiple lines, separated by a linefeed, or a linefeed and carriage return. The state column from the question has such a value, with linefeeds apparently.

If you had to inject that content back in SQL form, this may work (unless there are additional non-visible spaces):

select * from table where state = E'---\n:name: California\n:abbr: CA\n'; 

\n represents a linefeed in a string enclosed by E'...', . (see "String Constants with C-Style Escapes" in the documentation)