Identifying Reason for Column Name Returned by SELECT

I suspect you have a multi-line entry and the '+' is just indicating
that the field continues.

Try ...where site_id ~ 'GW-22'... (this may take a while if the table is
very large).

Cheers,
Steve

Your example suggests that the "GW-22" is a substring of the field followed by
trailing spaces so you'll want something that searches substrings, whereas "="
will always just test on matching the entire field.

Try "like" by default, such as "where site_id like 'GW-22 %'". I added the
space between the 22 and the wildcard % so that the field containing just
'GW-22' isn't also matched.

If you need something more specific than simple substring match, you can use a
regular expression, which I think is spelled like "where site_id ~ '...'" but
check the manual to be sure about regexp syntax. But "like" will probably do
you here.

-- Darren Duncan

Rich Shepard wrote:

On Wed, Sep 14, 2011 at 9:42 AM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

I suspect you have a multi-line entry and the '+' is just indicating that
the field continues.

Try ...where site_id ~ 'GW-22'... (this may take a while if the table is
very large).

You might be able to get an index scan if you include a few more criteria:

AND site_id BETWEEN 'GW-22' AND 'GW-23'

Also WHERE site_id '^GW-22' may use and index scan also.

--
Regards,
Richard Broersma Jr.

Darren Duncan wrote:

Try "like" by default, such as "where site_id like 'GW-22 %'". I added
the space between the 22 and the wildcard % so that the field containing
just 'GW-22' isn't also matched.

Sorry, I should have said "where site_id like 'GW-22%' and site_id != 'GW-22'"
(no explicit space) as a better way to exclude 'GW-22' from the results. --
Darren Duncan

On Wed, 14 Sep 2011, Steve Crawford wrote:

I suspect you have a multi-line entry and the '+' is just indicating that the
field continues.

Steve, et al.:

It's not multi-line, but malformed.

Try ...where site_id ~ 'GW-22'... (this may take a while if the table is very
large).

This found the appropriate rows. Now, my question is DDL-related:

What is the appropriate syntax to change 'GW-22 +' to GW-22? Can I use
'like' or '~' in an ALTER TABLE RENAME <column> ... statement?

Thanks guys,

Rich

On Wed, Sep 14, 2011 at 10:04 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

 This found the appropriate rows. Now, my question is DDL-related:

 What is the appropriate syntax to change 'GW-22  +' to GW-22? Can I use
'like' or '~' in an ALTER TABLE RENAME <column> ... statement?

I'm confused. Do you want to UPDATE the affected records to GW-22. Or
do you want to ALTER the table to add a column constraint to prevent
malformed site_id's in the future?

--
Regards,
Richard Broersma Jr.

On Wed, 14 Sep 2011, Richard Broersma wrote:

I'm confused.

Richard,

Apparently, I am also confused. Doing too many things simultaneoulsy.

Do you want to UPDATE the affected records to GW-22. Or do you want to
ALTER the table to add a column constraint to prevent malformed site_id's
in the future?

Update. That makes my question moot.

Thanks,

Rich

On Wed, Sep 14, 2011 at 12:04 PM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

On Wed, 14 Sep 2011, Steve Crawford wrote:

I suspect you have a multi-line entry and the '+' is just indicating that
the field continues.

Steve, et al.:

 It's not multi-line, but malformed.

It *is* mult-line. psql uses a '+ to show line breaks:

postgres=# select E'a\nb';
?column?
----------
a +
b
(1 row)

To fix your data, i'd consider using the replace() function to knock
out newlines:
postgres=# select replace(E'a\nb', E'\n', '');
replace
---------
ab
(1 row)

Also consider adjusting the app and/or the database to block them in the future.

merlin

On Wed, 14 Sep 2011, Merlin Moncure wrote:

It *is* mult-line. psql uses a '+ to show line breaks:

Merlin,

Yep. I discovered this when I dumped the table as an ASCII text file and
saw the '\n' after the site_id string on some rows. I've no idea how it got
there.

Thanks,

Rich