1

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?

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • what version of pgadmin are you using? – maahl Nov 15 '16 at 19:47
  • @maahl version `1.22.1` came with postgres-9.5 – Juan Carlos Oropeza Nov 15 '16 at 20:09
  • Maybe you should try [upgrading to version 1.1](https://www.pgadmin.org) (the authors of pgAdmin don't seem to understand how version numbers work...) – IMSoP Nov 15 '16 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 Nov 15 '16 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 Nov 16 '16 at 09:50

1 Answers1

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;

Related:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • You put same link twice. Not sure if double `control-v` or copy the wrong link. – Juan Carlos Oropeza Nov 15 '16 at 21:03
  • 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 :( – Juan Carlos Oropeza Nov 15 '16 at 21:05
  • 1
    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: - http://stackoverflow.com/questions/14649682/safely-and-cleanly-rename-tables-that-use-serial-primary-key-columns-in-postgres/14651788#14651788 – Erwin Brandstetter Nov 16 '16 at 03:55