Thread: PostgresQL 9.2 table query - underscores
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';
===============================================================
On 17/07/13 10:04, Victoria S. wrote:
Please supply the SQL you used to create the table & for populating it with data, we don't know what datatypes you used.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'; ===============================================================
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
"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
@ 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.