-1

I want create same external table in greenplum in different schema ,but it turns out ERROR: relation "my_external_table_name" already exists

click check the picture: this is before create external table

click check the picture: this is after create table

Why my previous table disappear?

Rocke
  • 1
  • 2

2 Answers2

2

Create schemas first

 gpadmin=# create schema foo;
 gpadmin=# create schema bar;

then create external tables in schemas

 gpadmin=# CREATE EXTERNAL TABLE foo.fre2(
 ngram text,
 year int4,
 match_count int4,
 page_count int4,
 volume_count int4)
 LOCATION ('gpfdist://mdw:8080/dat.txt')
 FORMAT 'TEXT' (DELIMITER E'\t')
 LOG ERRORS INTO load_e_fre2 SEGMENT REJECT LIMIT 500 rows;

 gpadmin=# CREATE EXTERNAL TABLE bar.fre2(
 ngram text,
 year int4,
 match_count int4,
 page_count int4,
 volume_count int4)
 LOCATION ('gpfdist://mdw:8080/dat.txt')
 FORMAT 'TEXT' (DELIMITER E'\t')
 LOG ERRORS INTO load_e_fre2 SEGMENT REJECT LIMIT 500 rows;

You should be able to see tables exist in their respective namespaces

gpadmin=# select c.relnamespace, c.relname, e.* from pg_class c join pg_exttable e on e.reloid = c.oid where c.relname = 'fre2';
 relnamespace | relname | reloid |           location           | fmttype |                fmtopts                 | command | rejectlimit | rejectlimittype | fmterrtbl | encoding | writable
--------------+---------+--------+------------------------------+---------+----------------------------------------+---------+-------------+-----------------+-----------+----------+----------
2200 | fre2    |  57474 | {gpfdist://mdw:8080/dat.txt} | t       | delimiter '     ' null '\N' escape '\' |         |         500 | r               |     49164 |        6 | f
57403 | fre2    |  57500 | {gpfdist://mdw:8080/dat.txt} | t       | delimiter '     ' null '\N' escape '\' |         |         500 | r               |     49164 |        6 | f
57404 | fre2    |  57526 | {gpfdist://mdw:8080/dat.txt} | t       | delimiter '     ' null '\N' escape '\' |         |         500 | r               |     49164 |        6 | f
(3 rows)


gpadmin=# select nspname from pg_namespace where oid in (2200, 57403, 57404);
 nspname
---------
 public
 foo
 bar
(3 rows)
dlynch
  • 156
  • 1
  • 1
  • 3
  • It do not work for me, my previous table disappear.. under different schema with the same table name can not exist in my condition...I have update my question , you can check it to see more details – Rocke Jul 26 '16 at 04:14
0

The following message is not an error as per the prefix "Notice" It just informing the user the error table defined in CREATE statement does not exist so it is creating that table for you. 

Notice: Error table "request_histories_external_error" does not exist. Autogenerating and error table with the same name
dlynch
  • 156
  • 1
  • 1
  • 3