Query - Create PostgreSQL - Mailing list pgsql-admin

  SELECT

  file.inode_id        AS file_id,

  file.parent_inode_id AS file_group,

  file.relative_path   AS file_type,

  file.file_data       AS file_binary,

  file.node_full_path  AS file_name,

  file.last_modified   AS date_created

FROM

  gorfs.nodes AS file

  INNER JOIN

  gorfs.inode_segments AS iseg ON iseg.st_ino = file.parent_inode_id

                                  AND file.object_type = 'S_IFREG'

                                  AND iseg.nfs_migration_date IS NULL

                                  AND (file.last_modified <

                                       (transaction_timestamp() AT TIME ZONE 'UTC' - '1 months' :: INTERVAL))

                                  AND iseg.st_ino_target = file.inode_id

LIMIT

  100;

CREATE TABLE gorfs.nfs_data

(

  owner_id integer NOT NULL,

  file_id integer NOT NULL,

  migration_path "text",

  migration_date timestamp with time zone,

  CONSTRAINT nfs_data_pkey PRIMARY KEY ("file_id")

)

CREATE INDEX ix_nfs_data_owner_id

  ON gorfs.nfs_data

  USING btree

  ("owner_id")

  WHERE "migration_date" IS NULL;

ALTER TABLE gorfs.nfs_data ADD COLUMN owner_id integer;

ALTER TABLE gorfs.nfs_data ALTER COLUMN owner_id SET NOT NULL;

ALTER TABLE gorfs.nfs_data ADD COLUMN file_id integer;

ALTER TABLE gorfs.nfs_data ALTER COLUMN file_id SET NOT NULL;

ALTER TABLE gorfs.nfs_data ADD COLUMN migration_path "text";

ALTER TABLE gorfs.nfs_data ADD COLUMN migration_date timestamp with time zone;

ALTER TABLE gorfs.nfs_data

  ADD CONSTRAINT nfs_data_pkey PRIMARY KEY("file_id");