Import Postgresql database

General
1

Hi,

is there a way to export PostgreSQL 11 database and import it into YugaByteDB without manual intervention? I dumped PostgreSQL database using pg_dump, but YugaByteDB does not support adding keys and indexes after a table is created (ALTER TABLE xxxx ADD CONSTRAINT). Since that is the way pg_dump dumps a database it would make me edit resulting SQL file manualy or write a script to convert all CREATE TABLE commands to add constraints and than rearange the dump in order for dependencies to be satisfied.
Is there a better way to do it?

2

Hi @puska

Can you try first dumping just the schema and then just the data ?
Having just the schema will make it easier to edit the file.

3

Hi,

I made a program to dump PostgreSQL schema and it works OK except for this issue:
I have a column named order and when importing schema I am getting this error:

ERROR:  syntax error at or near "order"
LINE 3: order integer  ,

The table definition looks as:

CREATE TABLE public.main_insttoinst (
id integer  DEFAULT nextval('main_insttoinst_id_seq'::regclass),
order integer  ,
inst_id integer  ,
spec_inst_id integer  ,
CONSTRAINT main_insttoinst_inst_id_3774c8baec31686b_uniq UNIQUE (inst_id, spec_inst_id),
CONSTRAINT main_insttoinst_pkey PRIMARY KEY (id),
CONSTRAINT main_instt_spec_inst_id_5a3ce40b077534e7_fk_main_institution_id FOREIGN KEY (spec_inst_id) REFERENCES main_institution(id),
CONSTRAINT main_insttoinst_inst_id_229c6080da340d98_fk_main_institution_id FOREIGN KEY (inst_id) REFERENCES main_institution(id),
CONSTRAINT main_insttoinst_order_check CHECK (("order" >= 0))
);

Is that a parser bug (confusing order (column name) with ORDER BY)?

4

Hey @puska,

ORDER is a reserved word in SQL, and this is expected behavior. To use a reserved word as a column name, you’d have to double-quote it: "order" integer

5

Thanks, that worked out nicely.