archive

PostGIS – Re-arranging columns

or: Restructuring with CREATE TABLE and INSERT INTO
If you require a more specifically defined table than that created by
CREATE TABLE AS (e.g., one with column constraints), you can replicate
the effect of the CREATE TABLE AS technique by issuing two SQL
statements rather than one. You can achieve this by first creating the new table
as you ordinarily would with CREATE TABLE, and then populating the
table with data via the INSERT INTO command and a valid SELECT
statement.
Example 4-15. Restructuring a table with CREATE TABLE and INSERT
INTO

booktown=# CREATE TABLE new_books
(

booktown(# id integer UNIQUE,
booktown(#
title text NOT NULL,
booktown(# author_id
integer,

booktown(# subject_id
integer,

booktown(# CONSTRAINT
books_
?id_?pkey PRIMARY KEY
(id)

booktown(# );
NOTICE: CREATE
TABLE/PRIMARY KEY will create implicit index 'books_id_pkey'

for
table 'new_books'

CREATE
booktown=# INSERT
INTO new_books

booktown-# SELECT id, title,
author_id, subject_id

booktown-# FROM
books;

INSERT 0 12
booktown=# ALTER TABLE
books RENAME TO old_books;

ALTER
booktown=#
ALTER TABLE new_books RENAME TO
books;

ALTER

(create table
"public"."countries_2" ( "gid" int4 not null , "name" varchar(49) ,
"official_name" varchar(255) , "sovereign" varchar(49) , "iso_2_code"
varchar(11) , "iso_3_code" varchar(11) , "un_code" int4 , "id" int2 not null ,
"geo_region" int8 , "geo_subreg" int8 , "the_geom" public.geometry , primary
key("id")) WITH OIDS;

INSERT INTO countries_2 SELECT gid,
name, 'NULL', sovereign, iso_2_code, iso_3_code, un_code, id, geo_region,
geo_subreg, the_geom FROM countries;)

Source

Comments are closed.