Could not create a table named "USER" under postgreSQL

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

Thanks a lot. That is what I am looking for :)

Emi

Dann Corbit 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.

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+'.

"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

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:

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

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

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).