How make a complete CREATE TABLE script

Question

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?

Maybe you should try upgrading to version 1.1 (the authors of pgAdmin don't seem to understand how version numbers work...) — IMSoP
– IMSoP, Commented Nov 15, 2016 at 20:20
@IMSoP Looks like 1.1 is for pgAdmin 4, I have pgAdmin 3 can you try creating a script for a serial field and let me know if give the correct script? I dont want make the change if doesnt fix it. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 15, 2016 at 20:30
Yeah, I was being mildly pedantic, because I think it's ridiculous that they're not called 3.22.1 and 4.1, respectively. But I thought it might be worth installing and seeing if you got different behaviour (I imagine you can just install both side-by-side, they're just desktop client apps, not bound to the server in any way). — IMSoP
– IMSoP, Commented Nov 16, 2016 at 9:50

Community · Accepted Answer · 2017-05-23 12:07:14Z

2

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;

CommunityBot

11 silver badge

answered Nov 15, 2016 at 20:41

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Juan Carlos Oropeza Over a year ago

You put same link twice. Not sure if double control-v or copy the wrong link.

Juan Carlos Oropeza Over a year ago

That mean pgAdmin do the reverse-engineer on only one direction?. The problem is I want the script so I can create the table in other db. Of course I can create the sequence myself, but we all know what happen when you do things manually :(

Erwin Brandstetter Over a year ago

You can fix the source DB, you can fix the SQL script, or you can fix the target DB. Your choice. pgAdmin just displays what it finds. Read the detailed description in the linked answer: - stackoverflow.com/questions/14649682/…

Collectives™ on Stack Overflow

How make a complete CREATE TABLE script

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related