Обсуждение: Problems on "copy" statement

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!

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/interactive/sql-copy.html

Kind Regards,
Keith

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/interactive/sql-syntax.html#SQL-SYNTAX-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/

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/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS
>
>What's the result of the following query?
>
>SELECT schemaname, tablename
>FROM pg_tables
>WHERE tablename ILIKE '%hsi%';
>
>
>

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/interactive/sql-syntax.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

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/interactive/sql-syntax.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

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/interactive/sql-syntax.html#SQL-SYNTAX-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/

> 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/interactive/sql-syntax.html#SQL-SYNTAX-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

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/

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/

Thanks all your help. The command run successfully as follow:

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


By the way, since I use a HTML table in mail but it seems not parsed
well in community, so it was strange to see several blank lines when run
SQL and get the schemaname and tablename.

Thanks all again for your kind help.

Rgds

Ellery Leung



Michael Fuhr wrote:

>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%';
>
>
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

Which technically breaks the SQL standard, but is something which the
community has (I think correctly) decided that we won't fix. Read the
hackers archive if you want the reasoning behind it.

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFCXsmmgfzn5SevSpoRApQBAJoDUsYDbm0YkNx/4LWTo3sUL9ec0gCgguUX
bAn2UonTLEc++URvct9kS3U=
=Sl8O
-----END PGP SIGNATURE-----