PostgreSQL table

From gnewarchaeology wiki
Revision as of 11:56, 28 January 2016 by Dncgst (Talk | contribs) (Alter table)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
PostgreSQL > PostgreSQL table



Backup table

# CREATE TABLE record_tmp AS SELECT * FROM record;

Create table with the geom column

PostgreSQL 9.1 + PostGIS 1.5

# CREATE TABLE points (
  id serial PRIMARY KEY
  );
# SELECT AddGeometryColumn ('public','points','geom','-1','POINT',3);
# \dt

PostgreSQL 9.4 + PostGIS 2.1

# CREATE TABLE site (
  site varchar(256) PRIMARY KEY, --Marathousa1
  city varchar(256) NOT NULL, --Megalopolis
  region varchar(256) NOT NULL, --Peloponneso
  country varchar(256) NOT NULL, --Greece
  lat double precision NULL,
  lon double precision NULL,
  elev double precision NULL,
  comment text NULL,
  -- add a geometry column (site location)
  geom geometry(PointZ,4326)
  );

Alter table

Add a column.

# CREATE TABLE geo_point_tmp AS SELECT * FROM geo_point;
# ALTER TABLE geo_point_tmp ADD COLUMN id serial PRIMARY KEY;

Alter a constraint.

# BEGIN;
# ALTER TABLE foo
  DROP CONSTRAINT your_constraint,
  ADD CONSTRAINT your_constraint FOREIGN KEY ...
  ON UPDATE CASCADE ON DELETE RESTRICT;
# COMMIT;

Insert from a csv file and make the geom

PostgreSQL 9.1 + PostGIS 1.5

# CREATE TABLE points (
  id serial PRIMARY KEY,
  x float NOT NULL,
  y float NOT NULL,
  z float NOT NULL);
# COPY points (codice,x,y,z) FROM '.../punti.csv' WITH DELIMITER AS ',' CSV HEADER ;
# SELECT AddGeometryColumn ('public','points','geom','-1','POINT',3);
# UPDATE points SET geom = ST_SetSRID(ST_MakePoint(x,y,z),-1);

PostgreSQL 9.4 + PostGIS 2.1

# UPDATE site SET geom = ST_SetSRID(ST_MakePoint(lon,lat,elev),4326);

Export to a csv file

\copy (SELECT id, code, raw_material, raw_material_type from lithic) to '~/path_to/raw_material.csv'  DELIMITER AS ',' CSV HEADER;

Insert/Update data from other tables

# INSERT INTO layer_point (layer, code, code_suffix, x, y, z, comment, geom)
  (
  SELECT r.layer, p.code, p.code_suffix, p.x, p.y, p.z, p.comment, p.geom
  FROM record r JOIN record_point p ON r.code = p.code
  WHERE r.layer = 'A'
  );

# UPDATE record AS foo SET geom = bar.geom
  FROM sieve AS bar
  WHERE foo.unit = bar.unit AND foo.edm_id = bar.edm_id;

Drop records

# DELETE FROM points WHERE codice = 'codice';

# DELETE FROM lithic_tech WHERE record IN (SELECT record FROM lithic WHERE length IS NULL and width IS NULL and thickness IS NULL and integrity IS NULL);

Drop table

# DROP TABLE points;


Join