[GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread Mark Morgan Lloyd
What is the correct behavior of a serial column when a table is created with LIKE? The manual is silent on this. What appears to be happening with 8.2 is that the column in the new table refers to the original sequence generator. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk

Re: [GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread silly8888
In 8.4, the sequence value is copied only when INCLUDING DEFAULTS is specified. Otherwise, only the not null constraint is copied. I think this is the most reasonable behavior and I don't see why it should have been explicitly stated in the manual. On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan

Re: [GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread Mark Morgan Lloyd
On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: What is the correct behavior of a serial column when a table is created with LIKE? The manual is silent on this. What appears to be happening with 8.2 is that the column in the new table refers to

Re: [GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread Thom Brown
2009/10/30 Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk: On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: What is the correct behavior of a serial column when a table is created with LIKE? The manual is silent on this. What appears

Re: [GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread Tom Lane
Thom Brown thombr...@gmail.com writes: I can see why you wouldn't expect it to end up sharing the same sequence. If you were to manually create a sequence and wanted to use it on a column, you probably wouldn't bother using the SERIAL datatype, but use integer instead. So really since we

Re: [GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread Thom Brown
2009/10/30 Tom Lane t...@sss.pgh.pa.us: Thom Brown thombr...@gmail.com writes: I can see why you wouldn't expect it to end up sharing the same sequence.  If you were to manually create a sequence and wanted to use it on a column, you probably wouldn't bother using the SERIAL datatype, but use

Re: [GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread Tom Lane
Thom Brown thombr...@gmail.com writes: Well I realise SERIAL is a convenience rather than a datatype in its own right, but I'm surprised that LIKE can't differentiate between a column created with integer and one created with serial. The table continues to report a serial datatype after its

Re: [GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread Thom Brown
2009/10/30 Tom Lane t...@sss.pgh.pa.us: Thom Brown thombr...@gmail.com writes: Well I realise SERIAL is a convenience rather than a datatype in its own right, but I'm surprised that LIKE can't differentiate between a column created with integer and one created with serial.  The table

Re: [GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread Mark Morgan Lloyd
Tom Lane wrote: Thinking of SERIAL as a type is your first mistake ;-). It is not a type. It is a shorthand for making a sequence and sticking a suitable default on a plain integer column. So what LIKE sees is an integer column with a default, and it copies that. That's entirely fair, and

Re: [GENERAL] CREATE TABLE LIKE and SERIAL

2009-10-30 Thread Tom Lane
Thom Brown thombr...@gmail.com writes: How is pgAdmin determining the serial type in this case? Most likely it's looking for the pg_depend entry that shows the sequence as being owned by the column. However, that's an oversimplification of reality. I would imagine that pgAdmin will lie to you