Home



2018/08 - Adding a new PostgreSQL table to your Rails app. And a few PostgreSQL tips

Hi folks!

Today we'll cover  how to connect to a PostgreSQL database hosted on an Ubuntu server. If you do not have such a set up and wish/need to do so, follow this guide from Digital Ocean. Firstly, we'll need to close any apps and end any processes relying on our PostgreSQL database. If you cannot shut down your apps gracefully, use PS aux grep followed by your app/technology name to get the process ID. Then use kill -9 followed by your desired process ID to end the process. MAKE SURE that you get the right process ID. If you use a generic term while grepping, you will likely get a bunch of processes back and the chances of you accidentally ending the wrong process will be greater. Ending the wrong process could mess up your whole server, so try to avoid it! 

Okay, so now that there are no more applications depending on our database, we can log into our servers PostgreSQL-specific account using sudo -i -u postgres (Or whatever you called the account when setting PostgreSQL up)and then log into the Psql client using the psql command. Then we can look at all of our databases before connecting to our desired one using \l. Once you've figured out which database you want to change, use \c yourDatabaseNameHere to connect to it and \d+  to see all tables for it. You can also append a table name to \d+ in order to see the fields for that table.

Once you've figured out what attributes you want your table to have, use the CREATE TABLE table_name_here(sampleid int PRIMARY KEY NOT NULL, samplefield text NOT NULL); to create a new table, using whatever data types you want (Check this link out for more types).

We'll need to create a new sequence if we want an automatically incrementing ID field, so we need to use CREATE SEQUENCE table_name_id_seq; so we have a sequence to use for the ID. Psql doesn't give you the option of simply setting a fields structure to automatically increment, it uses sequences to do so. This allows you a bit more control over your automatically incrementing fields, but also means that you have to configure it yourself too. Next, use ALTER TABLE table_name_here ALTER COLUMN sampleid SET NEXTVAL('table_name_id_seq'); to make your new table use the sequence for its ID field.

And that's basically it! You might not be using the same database account that your apps use, and if you find that to be the case, just use the an ALTER TABLE table_name_here ALTER FIELD samplefield OWNER TO yourOtherAccount; statement to change the owner of your new table. You can also use this statement on the table itself by ommitting the field from the statement. There are a few other modifiers you can change for your fields, you can find out more about altering tables here. Use the \q statement to quit Psql, then Ubuntu's exit command to return to your original account.

Congrats, you've made a new table!

Good luck!

Marc