CODEWITHSUNDEEP
sqlpostgresqlcreate-tablepgadminpostgresql-9.5
Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

How make a complete CREATE TABLE script

In pgAdmin when you create a new script for your table SCRIPTS | CREATE scripts

You get something like

CREATE TABLE public.merchant_tracker_avl
(
  avl_id bigint NOT NULL DEFAULT nextval('merchant_tracker_avl_avl_id_seq'::regclass),
  x_lat numeric(10,6) NOT NULL,
  y_long numeric(10,6) NOT NULL,
  event_time timestamp without time zone NOT NULL,
  CONSTRAINT merchant_tracker_avl_pk PRIMARY KEY (avl_id)
);

The problem is in the other server where I try to create the table the sequence doesnt exist.

So I have to manually change the script to...

CREATE TABLE public.merchant_tracker_avl
(
  avl_id serial NOT NULL,
  ....

And that script generate the table and the sequence.

So how I can make the pgAdmin generate the correct script, so doesnt have to do that kind of manual changes?

Upvotes: 1

Views: 1837

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656241

The script you display is what you get if there is something different from an actual serial column. A serial is not an actual data type, just a convenient syntax shorthand.

pgAdmin does reverse-engineer the SQL DDL code with a serial if all criteria are met.

Typically it's not OWNED by the column - which you can repair with:

ALTER SEQUENCE merchant_tracker_avl_avl_id_seq
OWNED BY public.merchant_tracker_avl.avl_id;

Related:

Upvotes: 2

Related Questions