Re: Different error messages executing CREATE TABLE or ALTER TABLE to create a column "xmin"

From: Marc Balmer <marc(at)msys(dot)ch>
To: pgsql-bugs(at)postgresql(dot)org, brush(dot)tyler(at)gmail(dot)com
Subject: Re: Different error messages executing CREATE TABLE or ALTER TABLE to create a column "xmin"
Date: 2012-01-23 10:25:01
Message-ID: 4F1D357D.5080407@msys.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Am 22.01.12 14:22, schrieb Giuseppe Sucameli:
> Hi all,
>
> trying to create a table with a column xmin I get the
> following error message:
>
> test=> create table lx (xmin int);
> ERROR: column name "xmin" conflicts with a system
> column name
>
> Instead I get a different (and less understandable) error
> message if I try to add a column named xmin to an
> existent table:
>
> test=> create table lx (i int);
> CREATE TABLE
> test=> alter table lx add xmin int;
> ERROR: column "xmin" of relation "lx" already exists.
>
> The same problem occurs using "xmax" as column name.
>
> I'm on Ubuntu 11.04.
> Tried on both PostgreSQL 8.4.10 and 9.1.2

That is not a bug, but a feature. See section 5.4 of the documentation
"System Columns":

"Every table has several system columns that are implicitly defined by
the system. Therefore, these names cannot be used as names of
user-defined columns. (Note that these restrictions are separate from
whether the name is a key word or not; quoting a name will not allow you
to escape these restrictions.) You do not really need to be concerned
about these columns; just know they exist."

and further down:

"xmin

The identity (transaction ID) of the inserting transaction for this row
version. (A row version is an individual state of a row; each update of
a row creates a new row version for the same logical row.)"

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Vik Reykja 2012-01-23 14:04:27 Re: Different error messages executing CREATE TABLE or ALTER TABLE to create a column "xmin"
Previous Message Guillaume Lelarge 2012-01-23 09:54:10 Re: BUG #6406: Included pgAdmin does not have .sql file extension mapped

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2012-01-23 10:29:20 Re: Online base backup from the hot-standby
Previous Message pratikchirania 2012-01-23 10:09:12 Re: pgstat wait timeout