MonetDB - table without column- NOT able to delete it or use it
All,
We are finding a strange issue where below table has been created via a shell script. Strangely it doesn't show up any columns and when we try to drop it's saying table doesn't exists.
Is there any way below can happen? Is this because it's corrupted? How can we remove this?
CREATE TABLE "DM_MON_TSV_STG_T1"."AMA_US_IRI_8135_2001" (
);
Regards, Sreejith
If MonetDB says the table does not exist, then it most probably indeed does not exist, and hence you cannot remove it (well, there is then obviously not need to remove the non-existing table). You can double-check with, e.g., select * from "DM_MON_TSV_STG_T1"."AMA_US_IRI_8135_2001"; select * from schemas s join tables t on s.id = t.schema_id where s.name = 'DM_MON_TSV_STG_T1' and t.name = 'AMA_US_IRI_8135_2001';
I would be surprised (and consider it a bug that should be reported and fixed), if MonetDB excepted the given create statement without issuing an error; cf.,
sql>create table t (); syntax error, unexpected ')', expecting FOREIGN or PRIMARY or UNIQUE in: "create table t ()"
Best, Stefan
----- On Mar 10, 2018, at 8:29 AM, Sreejith Sharma Sreejith.Sharma@harman.com wrote:
All,
We are finding a strange issue where below table has been created via a shell script. Strangely it doesn’t show up any columns and when we try to drop it’s saying table doesn’t exists.
Is there any way below can happen? Is this because it’s corrupted? How can we remove this?
CREATE TABLE "DM_MON_TSV_STG_T1"."AMA_US_IRI_8135_2001" (
);
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Another thing: you say you created the table with a shell script. How *exactly*? Quoting is important here, since both the shell and SQL have their own ideas about quoting. If the creation succeeded, it may be that the names were converted to all lowercase because of incorrect quoting in the shell. Check sys.tables.
On 10/03/18 09:55, Stefan Manegold wrote:
If MonetDB says the table does not exist, then it most probably indeed does not exist, and hence you cannot remove it (well, there is then obviously not need to remove the non-existing table). You can double-check with, e.g., select * from "DM_MON_TSV_STG_T1"."AMA_US_IRI_8135_2001"; select * from schemas s join tables t on s.id = t.schema_id where s.name = 'DM_MON_TSV_STG_T1' and t.name = 'AMA_US_IRI_8135_2001';
I would be surprised (and consider it a bug that should be reported and fixed), if MonetDB excepted the given create statement without issuing an error; cf.,
sql>create table t (); syntax error, unexpected ')', expecting FOREIGN or PRIMARY or UNIQUE in: "create table t ()"
Best, Stefan
----- On Mar 10, 2018, at 8:29 AM, Sreejith Sharma Sreejith.Sharma@harman.com wrote:
All,
We are finding a strange issue where below table has been created via a shell script. Strangely it doesn’t show up any columns and when we try to drop it’s saying table doesn’t exists.
Is there any way below can happen? Is this because it’s corrupted? How can we remove this?
CREATE TABLE "DM_MON_TSV_STG_T1"."AMA_US_IRI_8135_2001" (
);
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Sreejith,
the table you are trying to create does not define any columns, between the parenthesis I see no column definition, and in the relational model it is not permitted to define a table without columns.
Assuming that schema DM_MON_TSV_STG_T1 exists, then you will need to do for example
CREATE TABLE "DM_MON_TSV_STG_T1"."AMA_US_IRI_8135_2001" ("column-name" integer);
Also I would suggest to make your scripts to read if the command was successful or if there was an error, because monetdb is most likely returning a syntax error of type:
syntax error, unexpected ')'
which means that you cant have an empty column definition.
lefteris
On Sat, Mar 10, 2018 at 8:29 AM, Sharma, Sreejith Sreejith.Sharma@harman.com wrote:
All,
We are finding a strange issue where below table has been created via a shell script. Strangely it doesn’t show up any columns and when we try to drop it’s saying table doesn’t exists.
Is there any way below can happen? Is this because it’s corrupted? How can we remove this?
CREATE TABLE "DM_MON_TSV_STG_T1"."AMA_US_IRI_8135_2001" (
);
Regards,
Sreejith
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
Lefteris -
Sharma, Sreejith -
Sjoerd Mullender -
Stefan Manegold