PostgreSQL: Write SQL statement

This project was successfully completed by lokeshk9936 for $10 USD / hour in a day.

Get free quotes for a project like this
Employer working
Completed by:
Skills Required
Project Budget
$8 - $15 USD / hour
Completed In
1 day
Total Bids
Project Description

I have lots of data in a table with the following structure:
id serial NOT NULL,
customer_id integer,
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 [url removed, login to view],
account integer,
vat_code character varying(10),
offset_account integer,
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 (
customer_id INT,
receipt_number SERIAL,
image_name CHARACTER VARYING(255),
entry_date DATE,
text VARCHAR(9999),
amount NUMERIC(20, 2) DEFAULT [url removed, login to view],
amount_original_currency NUMERIC(20, 2) DEFAULT [url removed, login to view],
currency currency_enum DEFAULT 'DKK', -- Use USD for dollar, DKK for danish kroner etc.
account INT,
vat_code VARCHAR(10), -- Used to record the VAT code of the company our customer has bought from or sold to.
offset_account INT,
s3_path_to_original_file VARCHAR(300),
s3_path_to_image VARCHAR(300),
received_date TIMESTAMP,
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$.

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online