CREATE DATABASE udig;
CREATE EXTENSION dblink;
CREATE EXTENSION postgis; -- assume 2.0+ for TypMod

-- optional setting: schema
-- CREATE SCHEMA hoge;
-- SET search_path TO hoge, public;

-- connect to remote DB
SELECT dblink_connect('
    host=www.refractions.net
    port=5432
    dbname=demo-bc
    user=demo
    password=demo
');

-- border, 5199 rows
-- this srid is 4326, the others 3005)
CREATE TABLE bc_border AS
SELECT * FROM dblink('SELECT * FROM bc_border') t (
    gid int,
    border_id int,
    the_geom geometry(Linestring, 4326));
ALTER TABLE bc_border ADD PRIMARY KEY (gid);

-- hospitals, 44 rows
CREATE TABLE bc_hospitals AS
SELECT * FROM dblink('SELECT * FROM bc_hospitals') t (
    gid int,
    id int2,
    authority varchar(25),
    name varchar(42),
    the_geom geometry(Point, 3005));
ALTER TABLE bc_hospitals ADD PRIMARY KEY (gid);

-- municipality, 162 rows
CREATE TABLE bc_municipality AS
SELECT * FROM dblink('SELECT * FROM bc_municipality') t (
    gid int,
    code int2,
    name varchar(40),
    the_geom geometry(Polygon, 3005));
ALTER TABLE bc_municipality ADD PRIMARY KEY (gid);

-- pubs, 417 rows
CREATE TABLE bc_pubs AS
SELECT * FROM dblink('SELECT * FROM bc_pubs') t (
    gid int,
    id int2,
    name varchar(55),
    address varchar(47),
    city varchar(20),
    province varchar(17),
    postal varchar(7),
    the_geom geometry(Point, 3005));
ALTER TABLE bc_pubs ADD PRIMARY KEY (gid);

-- voting areas, 7986 rows
CREATE TABLE bc_voting_areas AS
SELECT * FROM dblink('SELECT * FROM bc_voting_areas') t (
    gid int,
    code varchar(3),
    id varchar(8),
    riding varchar(29),
    region varchar(29),
    "number" varchar(4),
    ndp int,
    lib int,
    gp int,
    upbc int,
    vtotal int,
    vreject int,
    vregist int,
    the_geom geometry(Multipolygon, 3005));
ALTER TABLE bc_voting_areas ADD PRIMARY KEY (gid);

-- disconnect from remote DB
SELECT dblink_disconnect();

-- PostGIS 1.x
CREATE TABLE hoge (
    gid int,
    geom geometry,
    
    -- Dimension
    CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
    
    -- Geometry Type
    CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'LINESTRING'::text OR geom IS NULL),
    
    -- SRID
    CONSTRAINT enforce_srid_geom CHECK (srid(geom) = 4326)
);

-- PostGIS 2.x using TypMod
CREATE TABLE hoge (
    gid int,
    geom geometry(LineString, 4326) -- 2D LineString, SRID 4326
);

-- connect to remote DB
SELECT dblink_connect('
    host=
    port=
    dbname=
    user=
    password=
');

-- create table and import data
CREATE TABLE fuga AS
SELECT * FROM dblink('SELECT * FROM remote_table') AS t (
    column1 columntype,
    column2 columntype,
    column3 columntype,,,
    -- paste from DDL using TypMod
);

-- disconnect from remote DB
SELECT dblink_disconnect();