CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.5
Greg
Greg

Reputation: 47124

copy table (create table like) - not keeping auto incrementing primary key

I'm new to postgres (on 9.5) and I can't find this in the docs anywhere.

Basically create a table like this:

CREATE TABLE test (
        id serial primary key, 
        field1 CHARACTER VARYING(50)
        );

Then copy it:

create table test_copy (like test);

The table test has these columns:

COLUMN_NAME id  field1
DATA_TYPE   4   12
TYPE_NAME   serial  varchar
COLUMN_SIZE 10  50
IS_NULLABLE NO  YES
IS_AUTOINCREMENT     YES    NO

But test_copy has these:

COLUMN_NAME id  field1
DATA_TYPE   4   12
TYPE_NAME   int4    varchar
COLUMN_SIZE 10  50
IS_NULLABLE NO  YES
IS_AUTOINCREMENT     NO NO

Why am I losing serial and autoincrement? How can I make a copy of a table that preserves these?

Upvotes: 2

Views: 2160

Answers (2)

user330315
user330315

Reputation:

This is because serial isn't really a datatype. It gets "expanded" to an integer + a sequence + a default value.

See the manual for details

To get the default definition you need to use create table test_copy (like test INCLUDING DEFAULTS).

However, that will then use the same sequence as the original table.

You can see the difference when you display the table definition in psql:

psql (9.5.3)
Type "help" for help.

postgres=> CREATE TABLE test (
postgres(>         id serial primary key,
postgres(>         field1 CHARACTER VARYING(50)
postgres(>         );
CREATE TABLE
postgres=> create table test_copy_no_defaults (like test);
CREATE TABLE
postgres=> create table test_copy (like test including defaults);
CREATE TABLE
postgres=> \d test
                                Table "public.test"
 Column |         Type          |                     Modifiers
--------+-----------------------+---------------------------------------------------
 id     | integer               | not null default nextval('test_id_seq'::regclass)
 field1 | character varying(50) |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

postgres=> \d test_copy
                             Table "public.test_copy"
 Column |         Type          |                     Modifiers
--------+-----------------------+---------------------------------------------------
 id     | integer               | not null default nextval('test_id_seq'::regclass)
 field1 | character varying(50) |

postgres=> \d test_copy_no_defaults
    Table "public.test_copy_no_defaults"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               | not null
 field1 | character varying(50) |

Upvotes: 3

Vao Tsun
Vao Tsun

Reputation: 51529

you can try:

create table test_inh () inherits (test); and then

alter table test_inh no inherit test;

should leave same sequence default value for you

Upvotes: 1

Related Questions