Hi all!

When I want to use "copy" to move data in .csv, which has been formatted
to tab-formatted file, to my postgresql as follow (DB name: test,
tablename: hsi):

copy hsi from 'c:\java\hsi.txt'

it generated error:

ERROR: relation "hsi" does not exist
copy hsi from 'c:\java\hsi.txt'


if I use double quoted:

copy hsi from "c:\java\hsi.txt"

it generated:

ERROR: syntax error at or near ""c:\java\hsi.txt"" at character 15


Can anyone please tell me what's wrong with the above statements?

Thanks in advance for your kind help!

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Wed, 13 Apr 2005 22:29:05 +0800, Leung Wing Lap Ellery wrote
> Hi all!
>
> When I want to use "copy" to move data in .csv, which has been formatted
> to tab-formatted file, to my postgresql as follow (DB name: test,
> tablename: hsi):
>
> copy hsi from 'c:\java\hsi.txt'
>
> it generated error:
>
> ERROR: relation "hsi" does not exist
> copy hsi from 'c:\java\hsi.txt'

It seems to me that it cannot find the table. Try schema qualifying the
tablename.

COPY myschema.hsi FROM 'c:\java\hsi.txt';

http://www.postgresql.org/docs/8.0/i.../sql-copy.html

Kind Regards,
Keith

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Wed, Apr 13, 2005 at 10:38:02AM -0400, Keith Worthington wrote:
> On Wed, 13 Apr 2005 22:29:05 +0800, Leung Wing Lap Ellery wrote
> >
> > copy hsi from 'c:\java\hsi.txt'
> >
> > it generated error:
> >
> > ERROR: relation "hsi" does not exist
> > copy hsi from 'c:\java\hsi.txt'
>
> It seems to me that it cannot find the table. Try schema qualifying the
> tablename.

Another possibility is that the table name is mixed-case -- if so,
then it'll have to be quoted.

http://www.postgresql.org/docs/8.0/i...AX-IDENTIFIERS

What's the result of the following query?

SELECT schemaname, tablename
FROM pg_tables
WHERE tablename ILIKE '%hsi%';

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Thanks for your reply.

Run the sql:

SELECT schemaname, tablename
FROM pg_tables
WHERE tablename ILIKE '%hsi%';


get:

schemaname tablename

public



HSI


when run the sql:

copy public.HSI from 'c:\java\hsi.txt'


error:

ERROR: relation "public.hsi" does not exist



er...did I do something silly? What is the problem?

Thanks in advance.

Ellery Leung


Michael Fuhr wrote:

>On Wed, Apr 13, 2005 at 10:38:02AM -0400, Keith Worthington wrote:
>
>
>>On Wed, 13 Apr 2005 22:29:05 +0800, Leung Wing Lap Ellery wrote
>>
>>
>>>copy hsi from 'c:\java\hsi.txt'
>>>
>>>it generated error:
>>>
>>>ERROR: relation "hsi" does not exist
>>>copy hsi from 'c:\java\hsi.txt'
>>>
>>>
>>It seems to me that it cannot find the table. Try schema qualifying the
>>tablename.
>>
>>
>
>Another possibility is that the table name is mixed-case -- if so,
>then it'll have to be quoted.
>
>http://www.postgresql.org/docs/8.0/i...AX-IDENTIFIERS
>
>What's the result of the following query?
>
>SELECT schemaname, tablename
>FROM pg_tables
>WHERE tablename ILIKE '%hsi%';
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Wed, 13 Apr 2005 09:34:42 -0600, Michael Fuhr wrote
> On Wed, Apr 13, 2005 at 10:38:02AM -0400, Keith Worthington wrote:
> > On Wed, 13 Apr 2005 22:29:05 +0800, Leung Wing Lap Ellery wrote
> > >
> > > copy hsi from 'c:\java\hsi.txt'
> > >
> > > it generated error:
> > >
> > > ERROR: relation "hsi" does not exist
> > > copy hsi from 'c:\java\hsi.txt'
> >
> > It seems to me that it cannot find the table. Try schema qualifying the
> > tablename.
>
> Another possibility is that the table name is mixed-case -- if so,
> then it'll have to be quoted.
>
> http://www.postgresql.org/docs/8.0/i...ntax.html#SQL-
> SYNTAX-IDENTIFIERS
>
> What's the result of the following query?
>
> SELECT schemaname, tablename
> FROM pg_tables
> WHERE tablename ILIKE '%hsi%';
>
> --
> Michael Fuhr

To quote my kids; "Yikers!" :-)

I vaguely remember colliding with case sensitivity in 7.3.X when I first
started using postgresql. I changed all my table and column names to
lowercase to avoid the issue and I haven't thought about it since. That
appears to be changed in 8.0.0 as SELECT * FROM myschema.tbl_name; and SELECT
* FROM MySchema.Tbl_Name; work equally well. Is this a new supported now and
forever behavior? IT would be nice for readability and compactness to be able
to use SalesOrder.TblDetail.ItemID instead of sales_order.tbl_detail.item_id.

Kind Regards,
Keith

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

How about

COPY "HSI" from 'c:\java\hsi.txt'

You need the double quote to get the capitalization. I generally use
all lower-case so I don't have to think about it.

Sean

On Apr 13, 2005, at 12:24 PM, Leung Wing Lap Ellery wrote:

> Thanks for your reply.
>
> Run the sql:
>
> SELECT schemaname, tablename
> FROM pg_tables
> WHERE tablename ILIKE '%hsi%';
>
>
> get:
>
> schemaname tablename
>
> public
>
>
>
> HSI
>
>
> when run the sql:
>
> copy public.HSI from 'c:\java\hsi.txt'
>
> error:
>
> ERROR: relation "public.hsi" does not exist
>
>
>
> er...did I do something silly? What is the problem?
>
> Thanks in advance.
>
> Ellery Leung
>
>
> Michael Fuhr wrote:
>
>> On Wed, Apr 13, 2005 at 10:38:02AM -0400, Keith Worthington wrote:
>>
>>> On Wed, 13 Apr 2005 22:29:05 +0800, Leung Wing Lap Ellery wrote
>>>
>>>> copy hsi from 'c:\java\hsi.txt'
>>>>
>>>> it generated error:
>>>>
>>>> ERROR: relation "hsi" does not exist
>>>> copy hsi from 'c:\java\hsi.txt'
>>>>
>>> It seems to me that it cannot find the table. Try schema qualifying
>>> the
>>> tablename.
>>>
>>
>> Another possibility is that the table name is mixed-case -- if so,
>> then it'll have to be quoted.
>>
>> http://www.postgresql.org/docs/8.0/i...ntax.html#SQL-
>> SYNTAX-IDENTIFIERS
>>
>> What's the result of the following query?
>>
>> SELECT schemaname, tablename
>> FROM pg_tables
>> WHERE tablename ILIKE '%hsi%';
>>
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Thu, Apr 14, 2005 at 12:24:42AM +0800, Leung Wing Lap Ellery wrote:
>
> SELECT schemaname, tablename
> FROM pg_tables
> WHERE tablename ILIKE '%hsi%';
>
> get:
>
> schemaname tablename
> public HSI
>
> when run the sql:
>
> copy public.HSI from 'c:\java\hsi.txt'
>
> error:
>
> ERROR: relation "public.hsi" does not exist

See the reference I posted about SQL identifiers, in particular
where it talks about quoted identifiers:

http://www.postgresql.org/docs/8.0/i...AX-IDENTIFIERS

Since the table name has uppercase letters, you'll have to quote
it. And now that I think about it, you might also have to escape
the backslashes in the file name or use dollar quotes (available
in 8.0 and later). Try one of these:

COPY "HSI" FROM 'c:\\java\\hsi.txt';
COPY "HSI" FROM $$c:\java\hsi.txt$$;

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

> Michael Fuhr wrote:
>
> >On Wed, Apr 13, 2005 at 10:38:02AM -0400, Keith Worthington wrote:
> >
> >
> >>On Wed, 13 Apr 2005 22:29:05 +0800, Leung Wing Lap Ellery wrote
> >>
> >>
> >>>copy hsi from 'c:\java\hsi.txt'
> >>>
> >>>it generated error:
> >>>
> >>>ERROR: relation "hsi" does not exist
> >>>copy hsi from 'c:\java\hsi.txt'
> >>>
> >>>
> >>It seems to me that it cannot find the table. Try schema qualifying the
> >>tablename.
> >>
> >>
> >
> >Another possibility is that the table name is mixed-case -- if so,
> >then it'll have to be quoted.
> >
>
>http://www.postgresql.org/docs/8.0/i...AX-IDENTIFIERS
> >
> >What's the result of the following query?
> >
> >SELECT schemaname, tablename
> >FROM pg_tables
> >WHERE tablename ILIKE '%hsi%';
> >
On Thu, 14 Apr 2005 00:24:42 +0800, Leung Wing Lap Ellery wrote
> Thanks for your reply.
>
> Run the sql:
>
> SELECT schemaname, tablename
> FROM pg_tables
> WHERE tablename ILIKE '%hsi%';
>
> get:
>
> schemaname tablename
>
> public
>
>
>
> HSI
>
> when run the sql:
>
> copy public.HSI from 'c:\java\hsi.txt'
>
> error:
>
> ERROR: relation "public.hsi" does not exist
>
> er...did I do something silly? What is the problem?
>
> Thanks in advance.
>
> Ellery Leung

Well, your table is in the public schema so obviously the schema qualification
is not required.

I wonder if there are some hidden characters or perhaps whitespace in the
table name. It seems odd that the output of Michael's suggestion is split
across several lines. I am sorry but you will have to wait for someone other
than me to tell you how to figure that out.

How was the table created? Can you show us the commands? That might help.

Kind Regards,
Keith

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Wed, Apr 13, 2005 at 12:36:09PM -0400, Keith Worthington wrote:
>
> I vaguely remember colliding with case sensitivity in 7.3.X when I first
> started using postgresql. I changed all my table and column names to
> lowercase to avoid the issue and I haven't thought about it since. That
> appears to be changed in 8.0.0 as SELECT * FROM myschema.tbl_name; and SELECT
> * FROM MySchema.Tbl_Name; work equally well. Is this a new supported now and
> forever behavior? IT would be nice for readability and compactness to be able
> to use SalesOrder.TblDetail.ItemID instead of sales_order.tbl_detail.item_id.

This isn't new behavior. If you don't quote identifiers then they're
folded to lowercase.

test=> SELECT version();
version
---------------------------------------------------------------------------
PostgreSQL 7.2.7 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
(1 row)

test=> CREATE TABLE MyTable (myColumn text);
CREATE

test=> SELECT MYCOLUMN FROM MYTABLE;
mycolumn
----------
(0 rows)

test=> SELECT mycolumn FROM mytable;
mycolumn
----------
(0 rows)

test=> SELECT MyCoLuMn FROM mYtAbLe;
mycolumn
----------
(0 rows)

test=> \d
List of relations
Name | Type | Owner
---------+-------+-------
mytable | table | mfuhr
(1 row)

test=> \d mytable
Table "mytable"
Column | Type | Modifiers
----------+------+-----------
mycolumn | text |

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Wed, Apr 13, 2005 at 12:43:09PM -0400, Keith Worthington wrote:
> On Thu, 14 Apr 2005 00:24:42 +0800, Leung Wing Lap Ellery wrote
> >
> > SELECT schemaname, tablename
> > FROM pg_tables
> > WHERE tablename ILIKE '%hsi%';
> >
> > get:
> >
> > schemaname tablename
> >
> > public
> >
> >
> >
> > HSI
>
> I wonder if there are some hidden characters or perhaps whitespace in the
> table name. It seems odd that the output of Michael's suggestion is split
> across several lines.

Hmmm...when I saw that I assumed it was just a matter of message
formatting. Is that what the query output really looks like? What
client are you using? If you run the query in psql then the output
should look like this:

schemaname | tablename
------------+-----------
public | HSI
(1 row)

If the COPY command I suggested in another message still fails with
"relation does not exist" then please copy and paste the output of
the following query:

SELECT schemaname, quote_ident(tablename)
FROM pg_tables
WHERE tablename ILIKE '%hsi%';

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)