Creating PostgreSQL table from command line

Using the psql command, we can populate a table that has already been created in the database:

$ psql -h <server-hostname>  -d <database> -U <user> -c "COPY <table-name>(col1,  col2,  col3,  col4) FROM STDIN with delimiter as ','" < csvfile.csv

For example:
$ psql -h mypersonalserver.csdfklecf.us-east-1.rds.amazonaws.com  -d blogdb -U readonly -c "COPY posts(nid,  title,  created,  username,  author,  path,  series) FROM STDIN with delimiter as ','" < historical_posts.csv

I've found that I have used this many time to get a quick look at some data. It is one way to populate a table in seconds with one command!




Comments

Post a Comment

Popular posts from this blog

Upcasting in python

Upcasting Link to official Docs Have you ever run into a scenario where you have set your column type to int but when you go to display it either in a report or visualization it comes out a float? This happens because of something called upcasting. "Types can potentially be upcasted when combined with other types, meaning they are promoted from the current type (e.g. int to float)." Lets start with a DataFrame of 1 column and 8 rows where the values are random numbers from the normal distribution. >>> import pandas as pd >>>   import numpy as np >>>   df1 = pd.DataFrame(np.random.randn(8, 1), columns=['A'], dtype='float32') >>>   df1    A 0  0.406792 1  0.810450 2  1.161985 3 -1.402411 4  1.385434 5 -1.091746 6  0.018586 7 -0.606741 Now lets create a 3x8 DataFrame >>>   df2 = pd.DataFrame( dict(                  A =...
Read more

Chart Types & Styles

Image
Chart Types & Styles What is your process when you set out to make a data vizualization? Do you sketch what you are trying to show or discover? If using Tableau do you just start throwing variables into frame? Below is a useful though starter as you plan your data viz. Chart suggestions a thought starter Visualization of most common business data only requires 2 dimensional representation. Adding a third variable can be confusing for people who don't work with data everyday. So lets keep it simple and go through some of the decision making steps when deciding which visualization to use.  Three important things to consider are: Number of variables If you have more than four you may be in for a cluttered, unclear chart. Type of variables Numeric- Discrete or continuous Categorial - related or unrelated categories Association you are trying to show Relationship Multiple variables. Dependent or Independent. Comparison Difference or Similarity. Trends over...
Read more