CONTINUAL INTEGRATION

Problem scenario
You want to extract data from a .csv file.  In other words you want to  transform data from a flat file and import it into a database.  Is there an example of how to create a table, format a .csv file, and run a command to import the .csv file into the Postgresql database?

Solution
1.  Here is how to create a table to target for the import or ETL (Extract, Transform, Load) process:

CREATE TABLE continualtable (id int CONSTRAINT firstkey PRIMARY KEY, streetAddress varchar(200), city varchar(100), state char(2), zipcode int, phonenumber varchar(20));

2.  Here is the content of a .csv file that will be imported into the database table created with the above command:

id      streetAddress   city    state   zipcode phonenumber
1,123 Broadway,New York,NY,10001,212-555-5555
2,50 Sunset Blvd,Los Angeles,CA, 90210,310-555-5555
3,400 West Jackson Blvd,Chicago,IL,60611,312-555-5555

3.  From a command terminal of the Linux server with Postgresql, run these commands:

sudo -i -u postgres
psql
COPY continualTable FROM '/tmp/file.csv' WITH CSV HEADER; 

For the COPY command example above remember that "continualTable" is the table name of the target table for this import process, "/tmp/" is the path to your file and your file is named file.csv.  

Problem scenario
You want to create two tables in a Postgresql database.  You want the tables to share a column to relate to one another.  You want to implement a foreign key constraint to achieve this goal.  Is there an example of SQL statements to do this?

Solution
Run these two SQL commands that start with the words "CREATE TABLE":

CREATE TABLE table1 (id integer,
    name    varchar(40),
    PRIMARY KEY(id)
);

CREATE TABLE table2 (id int REFERENCES table1,
    name    varchar(40)
);

The second DDL command (i.e., "CREATE TABLE table2") works because the datatype of id is int.  The datatype has to be the same as the primary key's datatype of table1 the subject of the references keyword.  You'll notice that the keywords "CONSTRAINT" and "FOREIGN KEY" are absent from the commands above.

Problem scenario
You are trying to create a table using SQL.  You use the keywords "CONSTRAINT" and "PRIMARY KEY."  For example, your query (or SQL DML command) looks like this:

create table contint ( specialcode char(6) CONSTRAINT PRIMARY KEY, title varchar(40)) ;

When you run the SQL command you get the message '...syntax error at or near "PRIMARY" LINE...'  What should you do to fix this?

Solution
Make sure you give the constraint itself a name.  The syntax is for the name of the constraint to follow the word "CONSTRAINT" and appear before the "PRIMARY KEY" words.

create table contint ( specialcode char(6) CONSTRAINT initialkey PRIMARY KEY, title varchar(50)) ;

Identifying constraints by names, while mandatory, helps future SQL querying.  The error message that is thrown may include the potentially descriptive name of the CONSTRAINT that was violated.  A constraint's name can be modified later.