I have lots of data in a table with the following structure:
CREATE TABLE receipts
id serial NOT NULL,
receipt_number serial NOT NULL,
image_name character varying(255),
entry_date date DEFAULT ('now'::text)::date,
text character varying(9999),
amount numeric(20,2) DEFAULT 0.00,
vat_code character varying(10),
s3_path_to_original_file character varying(300),
received_date timestamp without time zone,
deleted boolean DEFAULT false,
typed integer DEFAULT 0,
checked boolean DEFAULT false,
reconciled boolean DEFAULT false,
accounted boolean DEFAULT false,
s3_path_to_image character varying(300),
CONSTRAINT receipts_pkey PRIMARY KEY (id)
I need to keep the data as it is, while converting to the following schema.
CREATE TABLE receipts (
id SERIAL PRIMARY KEY,
image_name CHARACTER VARYING(255),
amount NUMERIC(20, 2) DEFAULT 0.00,
amount_original_currency NUMERIC(20, 2) DEFAULT 0.00,
currency currency_enum DEFAULT 'DKK', -- Use USD for dollar, DKK for danish kroner etc.
vat_code VARCHAR(10), -- Used to record the VAT code of the company our customer has bought from or sold to.
deleted BOOLEAN DEFAULT false,
typed SMALLINT DEFAULT 0, -- Number of typers that have typed the amount
checked BOOLEAN DEFAULT false,
reconciled BOOLEAN DEFAULT false,
receipt_type SMALLINT DEFAULT 0, -- Receipt 0 is unknown, 1 means entry_date is before bank transaction (invoice/bill/cash payment), 2 is after, 3 is immediately after or same date
expense BOOLEAN DEFAULT true, -- If it is not an expense, it is an income
accounted BOOLEAN DEFAULT false,
degrees SMALLINT DEFAULT 0, -- Used for storing the number of degrees to rotate the image.
email_conversation VARCHAR(9999) DEFAULT '' -- Used for store the email conversation when the user ask to customer.
The first one to deliver working SQL statements (probably involving ALTER TABLE) to achieve this gets 15$.