Thread: Could not create a table named "USER" under postgreSQL

Could not create a table named "USER" under postgreSQL

From
Ying Lu
Date:
Hello,

I have a question about "date" & "timestamp" types in PostgreSQL. I want
to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for
them. However, it seems that PostgreSQL does not support it. Could
someone helps me please?

The example table:

T1 (col1      varchar(7) not null,
    col2      varchar(4) not null,
    col3      date not null,
    col 4     varchar(3),
    primary key(col1, col2, col3)
)

In my design model, "col3" has to be one of the primary key part. Since
at the beginning of the data population, we do not know the value of
"col3"; values for "col3" are input throught GUI. Therefore, when I use
MySQL, the default values I gave is "0000-00-00". However, after I
migrate to postgreSQL, I could not setup the default values as
"0000-00-00" any more. Could somebody help me about it please? I'd like
to know how I can save '0000-00-00' as the default value for "date" and
"timestamp" types.

By the way, I also tried "my2pg.pl" to migrate table structures got by
mysqldump to postgreSQL. The places I have '000-00-00' have been changed
to '0001-01-01' by this perl script.

Thanks a lot in advance,
Emi Lu


Re: Could not create a table named "USER" under postgreSQL

From
"Dann Corbit"
Date:
According to the current SQL language ANSI/ISO standard, the following
are reserved words:

<reserved word> ::=
ABS | ALL | ALLOCATE | ALTER | AND | ANY | ARE | ARRAY | AS | ASENSITIVE
| ASYMMETRIC | AT | ATOMIC | AUTHORIZATION | AVG
| BEGIN | BETWEEN | BIGINT | BINARY | BLOB | BOOLEAN | BOTH | BY
| CALL | CALLED | CARDINALITY | CASCADED | CASE | CAST | CEIL | CEILING
| CHAR | CHAR_LENGTH | CHARACTER | CHARACTER_LENGTH | CHECK | CLOB |
CLOSE
| COALESCE | COLLATE | COLLECT | COLUMN | COMMIT | CONDITION | CONNECT
| CONSTRAINT | CONVERT | CORR | CORRESPONDING | COUNT | COVAR_POP |
COVAR_SAMP
| CREATE | CROSS | CUBE | CUME_DIST | CURRENT | CURRENT_DATE
| CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE |
CURRENT_TIME
| CURRENT_TIMESTAMP | CURRENT_TRANSFORM_GROUP_FOR_TYPE | CURRENT_USER
| CURSOR | CYCLE
| DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT | DELETE
| DENSE_RANK | DEREF | DESCRIBE | DETERMINISTIC | DISCONNECT | DISTINCT
| DOUBLE | DROP | DYNAMIC
| EACH | ELEMENT | ELSE | END | END-EXEC | ESCAPE | EVERY | EXCEPT |
EXEC
| EXECUTE | EXISTS | EXP | EXTERNAL | EXTRACT
| FALSE | FETCH | FILTER | FLOAT | FLOOR | FOR | FOREIGN | FREE | FROM
| FULL | FUNCTION | FUSION
| GET | GLOBAL | GRANT | GROUP | GROUPING
| HAVING | HOLD | HOUR
| IDENTITY | IN | INDICATOR | INNER | INOUT | INSENSITIVE | INSERT
| INT | INTEGER | INTERSECT | INTERSECTION | INTERVAL | INTO | IS
| JOIN
| LANGUAGE | LARGE | LATERAL | LEADING | LEFT | LIKE | LN | LOCAL
| LOCALTIME | LOCALTIMESTAMP | LOWER
| MATCH | MAX | MEMBER | MERGE | METHOD | MIN | MINUTE
| MOD | MODIFIES | MODULE | MONTH | MULTISET
| NATIONAL | NATURAL | NCHAR | NCLOB | NEW | NO | NONE | NORMALIZE | NOT
| NULL | NULLIF | NUMERIC
| OCTET_LENGTH | OF | OLD | ON | ONLY | OPEN | OR | ORDER | OUT | OUTER
| OVER | OVERLAPS | OVERLAY
| PARAMETER | PARTITION | PERCENT_RANK | PERCENTILE_CONT |
PERCENTILE_DISC
| POSITION | POWER | PRECISION | PREPARE | PRIMARY | PROCEDURE
| RANGE | RANK | READS | REAL | RECURSIVE | REF | REFERENCES |
REFERENCING
| REGR_AVGX | REGR_AVGY | REGR_COUNT | REGR_INTERCEPT | REGR_R2 |
REGR_SLOPE
| REGR_SXX | REGR_SXY | REGR_SYY | RELEASE | RESULT | RETURN | RETURNS
| REVOKE | RIGHT | ROLLBACK | ROLLUP | ROW | ROW_NUMBER | ROWS
| SAVEPOINT | SCOPE | SCROLL | SEARCH | SECOND | SELECT | SENSITIVE
| SESSION_USER | SET | SIMILAR | SMALLINT | SOME | SPECIFIC |
SPECIFICTYPE
| SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | SQRT | START | STATIC
| STDDEV_POP | STDDEV_SAMP | SUBMULTISET | SUBSTRING | SUM | SYMMETRIC
| SYSTEM | SYSTEM_USER
| TABLE | TABLESAMPLE | THEN | TIME | TIMESTAMP | TIMEZONE_HOUR |
TIMEZONE_MINUTE
| TO | TRAILING | TRANSLATE | TRANSLATION | TREAT | TRIGGER | TRIM |
TRUE
| UESCAPE | UNION | UNIQUE | UNKNOWN | UNNEST | UPDATE | UPPER | USER |
USING
| VALUE | VALUES | VAR_POP | VAR_SAMP | VARCHAR | VARYING
| WHEN | WHENEVER | WHERE | WIDTH_BUCKET | WINDOW | WITH | WITHIN |
WITHOUT
| YEAR

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mike Mascari
> Sent: Wednesday, August 18, 2004 11:24 AM
> To: Ying Lu
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Could not create a table named "USER"
> under postgreSQL
>
>
> Ying Lu wrote:
>
> > Hello,
> >
> > I have a table named "USER" under MySQL database. When I am
> trying to
> > move tables from MySQL to PostgreSQL, I found that I could
> not create a
> > table namely "USER". I guess "USER" is a key string used by
> PostgreSQL
> > system so that we could not create a table named "USER". Is
> that true?
>
> You'll have to quote it in all the SQL you use if you insist on
> using it:
>
> [test@lexus] create table user (key integer);
> ERROR:  syntax error at or near "user" at character 14
> [test@lexus] create table "user" (key integer); CREATE TABLE
> [test@lexus] insert into user values (1);
> ERROR:  syntax error at or near "user" at character 13
> [test@lexus] insert into "user" values (1);
>
> HTH,
>
> Mike Mascari
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Re: Could not create a table named "USER" under postgreSQL

From
Ying Lu
Date:
Thanks a lot. That is what I am looking for :)

Emi



Dann Corbit wrote:

>According to the current SQL language ANSI/ISO standard, the following
>are reserved words:
>
><reserved word> ::=
>ABS | ALL | ALLOCATE | ALTER | AND | ANY | ARE | ARRAY | AS | ASENSITIVE
>| ASYMMETRIC | AT | ATOMIC | AUTHORIZATION | AVG
>| BEGIN | BETWEEN | BIGINT | BINARY | BLOB | BOOLEAN | BOTH | BY
>| CALL | CALLED | CARDINALITY | CASCADED | CASE | CAST | CEIL | CEILING
>| CHAR | CHAR_LENGTH | CHARACTER | CHARACTER_LENGTH | CHECK | CLOB |
>CLOSE
>| COALESCE | COLLATE | COLLECT | COLUMN | COMMIT | CONDITION | CONNECT
>| CONSTRAINT | CONVERT | CORR | CORRESPONDING | COUNT | COVAR_POP |
>COVAR_SAMP
>| CREATE | CROSS | CUBE | CUME_DIST | CURRENT | CURRENT_DATE
>| CURRENT_DEFAULT_TRANSFORM_GROUP | CURRENT_PATH | CURRENT_ROLE |
>CURRENT_TIME
>| CURRENT_TIMESTAMP | CURRENT_TRANSFORM_GROUP_FOR_TYPE | CURRENT_USER
>| CURSOR | CYCLE
>| DATE | DAY | DEALLOCATE | DEC | DECIMAL | DECLARE | DEFAULT | DELETE
>| DENSE_RANK | DEREF | DESCRIBE | DETERMINISTIC | DISCONNECT | DISTINCT
>| DOUBLE | DROP | DYNAMIC
>| EACH | ELEMENT | ELSE | END | END-EXEC | ESCAPE | EVERY | EXCEPT |
>EXEC
>| EXECUTE | EXISTS | EXP | EXTERNAL | EXTRACT
>| FALSE | FETCH | FILTER | FLOAT | FLOOR | FOR | FOREIGN | FREE | FROM
>| FULL | FUNCTION | FUSION
>| GET | GLOBAL | GRANT | GROUP | GROUPING
>| HAVING | HOLD | HOUR
>| IDENTITY | IN | INDICATOR | INNER | INOUT | INSENSITIVE | INSERT
>| INT | INTEGER | INTERSECT | INTERSECTION | INTERVAL | INTO | IS
>| JOIN
>| LANGUAGE | LARGE | LATERAL | LEADING | LEFT | LIKE | LN | LOCAL
>| LOCALTIME | LOCALTIMESTAMP | LOWER
>| MATCH | MAX | MEMBER | MERGE | METHOD | MIN | MINUTE
>| MOD | MODIFIES | MODULE | MONTH | MULTISET
>| NATIONAL | NATURAL | NCHAR | NCLOB | NEW | NO | NONE | NORMALIZE | NOT
>| NULL | NULLIF | NUMERIC
>| OCTET_LENGTH | OF | OLD | ON | ONLY | OPEN | OR | ORDER | OUT | OUTER
>| OVER | OVERLAPS | OVERLAY
>| PARAMETER | PARTITION | PERCENT_RANK | PERCENTILE_CONT |
>PERCENTILE_DISC
>| POSITION | POWER | PRECISION | PREPARE | PRIMARY | PROCEDURE
>| RANGE | RANK | READS | REAL | RECURSIVE | REF | REFERENCES |
>REFERENCING
>| REGR_AVGX | REGR_AVGY | REGR_COUNT | REGR_INTERCEPT | REGR_R2 |
>REGR_SLOPE
>| REGR_SXX | REGR_SXY | REGR_SYY | RELEASE | RESULT | RETURN | RETURNS
>| REVOKE | RIGHT | ROLLBACK | ROLLUP | ROW | ROW_NUMBER | ROWS
>| SAVEPOINT | SCOPE | SCROLL | SEARCH | SECOND | SELECT | SENSITIVE
>| SESSION_USER | SET | SIMILAR | SMALLINT | SOME | SPECIFIC |
>SPECIFICTYPE
>| SQL | SQLEXCEPTION | SQLSTATE | SQLWARNING | SQRT | START | STATIC
>| STDDEV_POP | STDDEV_SAMP | SUBMULTISET | SUBSTRING | SUM | SYMMETRIC
>| SYSTEM | SYSTEM_USER
>| TABLE | TABLESAMPLE | THEN | TIME | TIMESTAMP | TIMEZONE_HOUR |
>TIMEZONE_MINUTE
>| TO | TRAILING | TRANSLATE | TRANSLATION | TREAT | TRIGGER | TRIM |
>TRUE
>| UESCAPE | UNION | UNIQUE | UNKNOWN | UNNEST | UPDATE | UPPER | USER |
>USING
>| VALUE | VALUES | VAR_POP | VAR_SAMP | VARCHAR | VARYING
>| WHEN | WHENEVER | WHERE | WIDTH_BUCKET | WINDOW | WITH | WITHIN |
>WITHOUT
>| YEAR
>
>
>
>>-----Original Message-----
>>From: pgsql-general-owner@postgresql.org
>>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mike Mascari
>>Sent: Wednesday, August 18, 2004 11:24 AM
>>To: Ying Lu
>>Cc: pgsql-general@postgresql.org
>>Subject: Re: [GENERAL] Could not create a table named "USER"
>>under postgreSQL
>>
>>
>>Ying Lu wrote:
>>
>>
>>
>>>Hello,
>>>
>>>I have a table named "USER" under MySQL database. When I am
>>>
>>>
>>trying to
>>
>>
>>>move tables from MySQL to PostgreSQL, I found that I could
>>>
>>>
>>not create a
>>
>>
>>>table namely "USER". I guess "USER" is a key string used by
>>>
>>>
>>PostgreSQL
>>
>>
>>>system so that we could not create a table named "USER". Is
>>>
>>>
>>that true?
>>
>>You'll have to quote it in all the SQL you use if you insist on
>>using it:
>>
>>[test@lexus] create table user (key integer);
>>ERROR:  syntax error at or near "user" at character 14
>>[test@lexus] create table "user" (key integer); CREATE TABLE
>>[test@lexus] insert into user values (1);
>>ERROR:  syntax error at or near "user" at character 13
>>[test@lexus] insert into "user" values (1);
>>
>>HTH,
>>
>>Mike Mascari
>>
>>
>>---------------------------(end of
>>broadcast)---------------------------
>>TIP 7: don't forget to increase your free space map settings
>>
>>
>>


Re: Date and Timestamps

From
Stephan Szabo
Date:
[As a note, it's a bad idea to put a new message with a new problem in the
same thread.]

On Wed, 18 Aug 2004, Ying Lu wrote:

> I have a question about "date" & "timestamp" types in PostgreSQL. I want
> to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for
> them. However, it seems that PostgreSQL does not support it. Could

Right, because those aren't valid values for those types.  I think you
have to choose between using a date (or timestamp) column and constraining
the values to valid ones (for example, possibly '0001-01-01') or using a
type that doesn't constrain the value to valid dates.

Re: Could not create a table named "USER" under postgreSQL

From
"gnari"
Date:
"Ying Lu" <ying_lu@cs.concordia.ca> wrote:
>
> I have a question about "date" & "timestamp" types in PostgreSQL. I want
> to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for
> them. However, it seems that PostgreSQL does not support it. Could
> someone helps me please?

if you want to store invalid dates and timestamps, you
are better off using varchar.

if you just want to store a null-value, use NULL

does mysql really allow you to store these values?

gnari




Re: Could not create a table named "USER" under postgreSQL

From
"Dann Corbit"
Date:
Are you aware that there is NO zero year?  The common era starts with
the year 1 AD.  There is also no zero month, and there is no zero day.
All three parts of your date are hence invalid.  E.g. the date
0000-00-00 does not exist, and neither does 0001-00-00 or 0000-01-00
etc.  If you are determined to insert bad data into these fields, you
could make them character.  Or you could choose a valid date as the
default.

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ying Lu
> Sent: Wednesday, August 18, 2004 11:36 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Could not create a table named "USER"
> under postgreSQL
>
>
> Hello,
>
> I have a question about "date" & "timestamp" types in
> PostgreSQL. I want
> to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for
> them. However, it seems that PostgreSQL does not support it. Could
> someone helps me please?
>
> The example table:
>
> T1 (col1      varchar(7) not null,
>     col2      varchar(4) not null,
>     col3      date not null,
>     col 4     varchar(3),
>     primary key(col1, col2, col3)
> )
>
> In my design model, "col3" has to be one of the primary key
> part. Since
> at the beginning of the data population, we do not know the value of
> "col3"; values for "col3" are input throught GUI. Therefore,
> when I use
> MySQL, the default values I gave is "0000-00-00". However, after I
> migrate to postgreSQL, I could not setup the default values as
> "0000-00-00" any more. Could somebody help me about it
> please? I'd like
> to know how I can save '0000-00-00' as the default value for
> "date" and
> "timestamp" types.
>
> By the way, I also tried "my2pg.pl" to migrate table
> structures got by
> mysqldump to postgreSQL. The places I have '000-00-00' have
> been changed
> to '0001-01-01' by this perl script.

Remarkably clever to repair all those defects to something remotely
sensible.  Whoever wrote that script, I give an 'A+'.

Re: Could not create a table named "USER" under postgreSQL

From
Ying Lu
Date:
All right. I will think of using either "0001-01-01" or changing the
column type to varchar(n).

To Stephan:  I had planed to give a new thread name, but I pasted the
wrong subject name, using the previous one :(

Thanks a lot,
Emi



Dann Corbit wrote:

>Are you aware that there is NO zero year?  The common era starts with
>the year 1 AD.  There is also no zero month, and there is no zero day.
>All three parts of your date are hence invalid.  E.g. the date
>0000-00-00 does not exist, and neither does 0001-00-00 or 0000-01-00
>etc.  If you are determined to insert bad data into these fields, you
>could make them character.  Or you could choose a valid date as the
>default.
>
>
>
>>-----Original Message-----
>>From: pgsql-general-owner@postgresql.org
>>[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ying Lu
>>Sent: Wednesday, August 18, 2004 11:36 AM
>>To: pgsql-general@postgresql.org
>>Subject: [GENERAL] Could not create a table named "USER"
>>under postgreSQL
>>
>>
>>Hello,
>>
>>I have a question about "date" & "timestamp" types in
>>PostgreSQL. I want
>>to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for
>>them. However, it seems that PostgreSQL does not support it. Could
>>someone helps me please?
>>
>>The example table:
>>
>>T1 (col1      varchar(7) not null,
>>    col2      varchar(4) not null,
>>    col3      date not null,
>>    col 4     varchar(3),
>>    primary key(col1, col2, col3)
>>)
>>
>>In my design model, "col3" has to be one of the primary key
>>part. Since
>>at the beginning of the data population, we do not know the value of
>>"col3"; values for "col3" are input throught GUI. Therefore,
>>when I use
>>MySQL, the default values I gave is "0000-00-00". However, after I
>>migrate to postgreSQL, I could not setup the default values as
>>"0000-00-00" any more. Could somebody help me about it
>>please? I'd like
>>to know how I can save '0000-00-00' as the default value for
>>"date" and
>>"timestamp" types.
>>
>>By the way, I also tried "my2pg.pl" to migrate table
>>structures got by
>>mysqldump to postgreSQL. The places I have '000-00-00' have
>>been changed
>>to '0001-01-01' by this perl script.
>>
>>
>
>Remarkably clever to repair all those defects to something remotely
>sensible.  Whoever wrote that script, I give an 'A+'.
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
>


Re: Could not create a table named "USER" under postgreSQL

From
Tom Lane
Date:
Ying Lu <ying_lu@cs.concordia.ca> writes:
> at the beginning of the data population, we do not know the value of
> "col3"; values for "col3" are input throught GUI. Therefore, when I use
> MySQL, the default values I gave is "0000-00-00".

Use NULL.  That's what it's for.

            regards, tom lane

Re: Date and Timestamps

From
Mike Mascari
Date:
Stephan Szabo wrote:

> [As a note, it's a bad idea to put a new message with a new problem in the
> same thread.]
>
> On Wed, 18 Aug 2004, Ying Lu wrote:
>
>>I have a question about "date" & "timestamp" types in PostgreSQL. I want
>>to setup the default value '0000-00-00' and "0000-00-00 00:00:00" for
>>them. However, it seems that PostgreSQL does not support it. Could
>
> Right, because those aren't valid values for those types.  I think you
> have to choose between using a date (or timestamp) column and constraining
> the values to valid ones (for example, possibly '0001-01-01') or using a
> type that doesn't constrain the value to valid dates.

If it is truly unknown data, then how about two relations:

-- Known set of [col1 - col2]'s

T1 (col1 varchar(7) not null,
     col2 varchar(4) not null,
     col4 varchar(3) not null,
     primary key (col1, col2));

-- Known set of dates of [col1 - col2]'s

T2 (col1 varchar(7) not null,
     col2 varchar(4) not null,
     col3 date not null,
     primary key (col1, col2),
     foreign key (col1, col2)
      references T1 (col1, col2) on delete cascade
     );

No NULLs and fully normalized. Create a view with outer joins as
appropriate.

Mike Mascari








Re: Could not create a table named "USER" under postgreSQL

From
Bruno Wolff III
Date:
On Wed, Aug 18, 2004 at 15:03:02 -0400,
  Ying Lu <ying_lu@cs.concordia.ca> wrote:
>
> To Stephan:  I had planed to give a new thread name, but I pasted the
> wrong subject name, using the previous one :(

Even if you change the subject, you still should start a new thread
(i.e. don't reply to an existing thread).