PostgresQL 9.2 table query - underscores

On 17/07/13 10:04, Victoria S. wrote:

Hello: My first post; a Postgres newbie ...

I am teaching myself PostgresQL using a trial database, and I am having trouble with underscores:

IN the following example,

development=# SELECT created_at, username FROM tweets;
created_at | username
-----------------------------------+-------------------
created_at | username
“Tue, 12 Feb 2013 08:43:09 +0000″ | “_DreamLead”
“Tue, 12 Feb 2013 07:31:06 +0000″ | “GunnarSvalander”
“Tue, 12 Feb 2013 07:30:24 +0000″ | “GEsoftware”
“Tue, 12 Feb 2013 06:58:22 +0000″ | “adrianburch”
“Tue, 12 Feb 2013 05:29:41 +0000″ | “AndyRyder5″
“Tue, 12 Feb 2013 05:24:17 +0000″ | “AndyRyder5″
“Tue, 12 Feb 2013 01:49:19 +0000″ | “Brett_Englebert”
“Tue, 12 Feb 2013 01:31:52 +0000″ | “Brett_Englebert”
“Mon, 11 Feb 2013 23:15:05 +0000″ | “NimbusData”
“Mon, 11 Feb 2013 22:15:37 +0000″ | “SSWUGorg”
(11 rows)

... why doesn't his work? :

development=# SELECT created_at, username FROM tweets WHERE username='_DreamLead';
created_at | username
------------+----------
(0 rows)

I understand why this works:

development=# SELECT created_at, username FROM tweets WHERE username LIKE '%_DreamLead%';
created_at | username
-----------------------------------+--------------
“Tue, 12 Feb 2013 08:43:09 +0000″ | “_DreamLead”
(1 row)

... but can't I tweak this,to work, somehow [without using the WHERE or SIMILAR TO clause(s)]? :

SELECT created_at, username FROM tweets WHERE username='_DreamLead';

===============================================================

Please supply the SQL you used to create the table & for populating it
with data, we don't know what datatypes you used.

I would use text as the datatype for username.

Cheers,
Gavin

On 7/16/2013 3:04 PM, Victoria S. wrote:

Hello: My first post; a Postgres newbie ...

I am teaching myself PostgresQL using a trial database, and I am having trouble with underscores:

IN the following example,

development=# SELECT created_at, username FROM tweets;
created_at | username
-----------------------------------+-------------------
created_at | username
“Tue, 12 Feb 2013 08:43:09 +0000″ | “_DreamLead”
“Tue, 12 Feb 2013 07:31:06 +0000″ | “GunnarSvalander”
“Tue, 12 Feb 2013 07:30:24 +0000″ | “GEsoftware”
“Tue, 12 Feb 2013 06:58:22 +0000″ | “adrianburch”
“Tue, 12 Feb 2013 05:29:41 +0000″ | “AndyRyder5″
“Tue, 12 Feb 2013 05:24:17 +0000″ | “AndyRyder5″
“Tue, 12 Feb 2013 01:49:19 +0000″ | “Brett_Englebert”
“Tue, 12 Feb 2013 01:31:52 +0000″ | “Brett_Englebert”
“Mon, 11 Feb 2013 23:15:05 +0000″ | “NimbusData”
“Mon, 11 Feb 2013 22:15:37 +0000″ | “SSWUGorg”
(11 rows)

... why doesn't his work? :

development=# SELECT created_at, username FROM tweets WHERE username='_DreamLead';
created_at | username
------------+----------
(0 rows)

try ....

SELECT created_at, username FROM tweets WHERE username='“_DreamLead”';

it appears you've stored those cute-quotes “ ” in the field.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

On Wed, Jul 17, 2013 at 12:04 AM, Victoria S. <1@victoriasjourney.com> wrote:

Hello: My first post; a Postgres newbie ...

I am teaching myself PostgresQL using a trial database, and I am having trouble with underscores:

IN the following example,

development=# SELECT created_at, username FROM tweets;
created_at | username
-----------------------------------+-------------------
created_at | username
“Tue, 12 Feb 2013 08:43:09 +0000″ | “_DreamLead”
“Tue, 12 Feb 2013 07:31:06 +0000″ | “GunnarSvalander”
“Tue, 12 Feb 2013 07:30:24 +0000″ | “GEsoftware”
“Tue, 12 Feb 2013 06:58:22 +0000″ | “adrianburch”
“Tue, 12 Feb 2013 05:29:41 +0000″ | “AndyRyder5″
“Tue, 12 Feb 2013 05:24:17 +0000″ | “AndyRyder5″
“Tue, 12 Feb 2013 01:49:19 +0000″ | “Brett_Englebert”
“Tue, 12 Feb 2013 01:31:52 +0000″ | “Brett_Englebert”
“Mon, 11 Feb 2013 23:15:05 +0000″ | “NimbusData”
“Mon, 11 Feb 2013 22:15:37 +0000″ | “SSWUGorg”
(11 rows)

... why doesn't his work? :

development=# SELECT created_at, username FROM tweets WHERE username='_DreamLead';

try:

SELECT created_at, username FROM tweets WHERE username='“_DreamLead”';

I think the " " ended up in the table.

created_at | username
------------+----------
(0 rows)

I understand why this works:

development=# SELECT created_at, username FROM tweets WHERE username LIKE '%_DreamLead%';
created_at | username
-----------------------------------+--------------
“Tue, 12 Feb 2013 08:43:09 +0000″ | “_DreamLead”
(1 row)

... but can't I tweak this,to work, somehow [without using the WHERE or SIMILAR TO clause(s)]? :

SELECT created_at, username FROM tweets WHERE username='_DreamLead';

===============================================================

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Victoria S." <1@VictoriasJourney.com> writes:

IN the following example,

development=# SELECT created_at, username FROM tweets;
created_at | username
-----------------------------------+-------------------
created_at | username
“Tue, 12 Feb 2013 08:43:09 +0000″ | “_DreamLead”
“Tue, 12 Feb 2013 07:31:06 +0000″ | “GunnarSvalander”
“Tue, 12 Feb 2013 07:30:24 +0000″ | “GEsoftware”
“Tue, 12 Feb 2013 06:58:22 +0000″ | “adrianburch”
“Tue, 12 Feb 2013 05:29:41 +0000″ | “AndyRyder5″
“Tue, 12 Feb 2013 05:24:17 +0000″ | “AndyRyder5″
“Tue, 12 Feb 2013 01:49:19 +0000″ | “Brett_Englebert”
“Tue, 12 Feb 2013 01:31:52 +0000″ | “Brett_Englebert”
“Mon, 11 Feb 2013 23:15:05 +0000″ | “NimbusData”
“Mon, 11 Feb 2013 22:15:37 +0000″ | “SSWUGorg”
(11 rows)

... why doesn't his work? :

development=# SELECT created_at, username FROM tweets WHERE username='_DreamLead';
created_at | username
------------+----------
(0 rows)

It looks like you've put quotes into the stored data. Not a good idea.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

@ Gavin: Thank you ...

victoria:Programming$ sudo su postgres
postgres@victoria:/home/victoria/Programming$ psql
psql (9.2.4)
Type "help" for help.
postgres=#

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgresQL-9-2-table-query-underscores-tp5763975p5763983.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general