[postgis-users] clone schema - pg_get_serial_sequence returns null

[Ahsan Hadi]

You can try using this extension available on github for schema cloning, it
provides complete schema clone (DDL and Data).

https://github.com/vibhorkum/cloneschema

-- Ahsan

On Mon, Jul 31, 2017 at 2:09 PM, Regina Obe <lr at pcorp.us> wrote:

> I think you'd have better luck asking this on pgsql-general news list.
> https://www.postgresql.org/list/pgsql-general/
>
>
>
>
>
> There is no PostGIS related question here.
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Olivier Leprêtre
> *Sent:* Sunday, July 30, 2017 2:24 PM
> *To:* postgis-users at lists.osgeo.org
> *Subject:* [postgis-users] clone schema - pg_get_serial_sequence returns
> null
>
>
>
> Hi,
>
>
>
> I want to clone a schema, so I wrote the script below. My problem is that
> I can’t get sequences with pg_get_serial_sequence for the cloned primary
> keys.
>
>
>
> The sequence seems to be ok in the cloned schema :
>
>   idapp integer NOT NULL DEFAULT nextval('pr_1_1.ptbt_idapp_
> seq'::regclass),
>
>   CONSTRAINT adn_ptbt_pkey PRIMARY KEY (idapp)
>
>
>
> But if I run select pg_get_serial_sequence('pr_1_1.ptbt', 'idapp') it
> returns <NULL>
>
> Conversely, running pg_get_serial_sequence('source.ptbt', 'idapp') (from
> the source schema) returns the correct sequence.
>
>
>
> I’m using 9.6, I saw that it could be a problem because column is not
> directly as “serial”, is this an answer without solution ?
>
>
>
> Any help will be appreciated !
>
>
>
> Thanks,
>
>
>
> Olivier
>
>
>
>
>
> CREATE OR REPLACE FUNCTION params.prm_colne_schema(source_schema text,
> dest_schema text) RETURNS boolean AS
>
> $BODY$
>
>
>
> DECLARE
>
>   object text;
>
>   buffer text;
>
>   default_ text;
>
>   column_ text;
>
>   sqls text;
>
> BEGIN
>
>             IF dest_schema <> 'public' THEN
>
>                         EXECUTE 'DROP SCHEMA if exists ' || dest_schema ||
> ' CASCADE';
>
>                         EXECUTE 'CREATE SCHEMA ' || dest_schema;
>
>             END IF;
>
>
>
>             -- copy sequences
>
>             FOR object IN
>
>                         SELECT sequence_name::text FROM
> information_schema.SEQUENCES WHERE sequence_schema = source_schema
>
>             LOOP
>
>                         EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.'
> || object;
>
>             END LOOP;
>
>
>
>             -- copy tables without constraints
>
>             FOR object IN
>
>                         SELECT TABLE_NAME::text FROM
> information_schema.TABLES WHERE table_schema = source_schema  and
> substr(table_name,1,4) <> 'src_'
>
>             LOOP
>
>                         buffer := dest_schema || '.' || object;
>
>
>
>                         EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
> source_schema || '.' || object || ' INCLUDING DEFAULTS)';
>
>
>
>                         FOR column_, default_ IN
>
>                                    SELECT column_name::text,
> REPLACE(column_default::text, source_schema, dest_schema) FROM
> information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME
> = object
>
>                         LOOP
>
>                                    EXECUTE 'ALTER TABLE ' || buffer || '
> ALTER COLUMN ' || column_ ||' DROP NOT NULL;';
>
>                         END LOOP;
>
>
>
>             END LOOP;
>
>
>
>             -- copy tables with constraints
>
>             FOR object IN
>
>                                    SELECT TABLE_NAME::text FROM
> information_schema.TABLES WHERE table_schema = source_schema  and
> substr(table_name,1,4) = 'src_'
>
>             LOOP
>
>                         buffer := dest_schema || '.' || object;
>
>
>
>                         EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
> source_schema || '.' || object || ' INCLUDING ALL)';
>
>                         -- copy sequences
>
>                         FOR column_, default_ IN
>
>                                    SELECT column_name::text,
> REPLACE(column_default::text, source_schema, dest_schema) FROM
> information_schema.COLUMNS WHERE table_schema = dest_schema AND TABLE_NAME
> = object AND column_default LIKE 'nextval(%' || source_schema ||
> '%::regclass)'
>
>                         LOOP
>
>                                    sqls='ALTER TABLE ' || buffer || '
> ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
>
>                                    RAISE NOTICE 'default=%',sqls;
>
>                                    EXECUTE (sqls);
>
>                         END LOOP;
>
>             END LOOP;
>
>
>
>             RETURN true;
>
> EXCEPTION
>
>             WHEN others THEN
>
>             RETURN false;
>
> END;
>
> $BODY$
>
> LANGUAGE plpgsql VOLATILE;
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
Ahsan Hadi
Snr Director Product Development
EnterpriseDB Corporation
The Enterprise Postgres Company

Phone: +92-51-8358874
Mobile: +92-333-5162114

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of the
individual or entity to whom it is addressed. This message contains
information from EnterpriseDB Corporation that may be privileged,
confidential, or exempt from disclosure under applicable law. If you are
not the intended recipient or authorized to receive this for the intended
recipient, any use, dissemination, distribution, retention, archiving, or
copying of this communication is strictly prohibited. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete this message.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170731/1e16c5c0/attachment.html>