Thread: PostgresQL 9.2 table query - underscores

PostgresQL 9.2 table query - underscores

From
"Victoria S."
Date:
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';

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


Re: PostgresQL 9.2 table query - underscores

From
Gavin Flower
Date:
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

Re: PostgresQL 9.2 table query - underscores

From
John R Pierce
Date:
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

Re: PostgresQL 9.2 table query - underscores

From
Arjen Nienhuis
Date:
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


Re: PostgresQL 9.2 table query - underscores

From
Tom Lane
Date:
"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


Re: PostgresQL 9.2 table query - underscores

From
victoriastuart
Date:
@ 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.