First day of PostgreSQL-relationships, crud, and joins

To create a table:

CREATE TABLE  Char(2PRIMARYKEYtextUNIQUE);

Insert data:

INSERT  intocountries (Country_code,country_name)VALUES('US','states'),('MX','Mexico'),('au','Australia'),('GB','Kingdom'),('de','Gemany'),('ll','Loompaland');

Or

INSERT  into countries VALUES ('US','states'), ('mx ','Mexico');

Query data:

SELECT *  from countries;

Delete data:

DELETE  from countries WHERE = ' ll ';

FOREIGN key: In order to ensure that all the country_code in cities have appeared in countries,
So add the references foreign KEY constraint to cities's country_code.

CREATE TABLE  text notNULLvarchar (9 CHECK <>     ' char (2 REFERENCES countries,  PRIMARYKEY  (Country_code,postal_code));

But the Country_code reference to the cities table can be null, representing a value that is vacant,
If Cities.country_code is not allowed to be empty, you can define this:

Char (2REFERENCESnotNULL

Insert FOREIGN KEY data:

INSERT  into Cities VALUES ('Portland','87200','US ');

Update the above data:

UPDATE Cities SET = ' 97205 ' WHERE = ' Portland ';

To use a join query:

SELECT cities. * , Country_name  from INNER JOIN countries  on = Countries.country_code;

Foreign keys reference two primary keys:

CREATE TABLEvenues (venue_id SERIALPRIMARY KEY, namevarchar(255), Street_addresstext, typeChar(7)CHECK(Typeinch(' Public','Private'))DEFAULT ' Public', Postal_CodeChar(2), Country_codeChar(2),FOREIGN KEY(Country_code,postal_code)REFERENCESCities (Country_code,postal_code) MATCH Full);

Left outer joins:

SELECT E.title,v.name  from  Left JOIN Venues v  on = v.venues_id;

When you create a new table, PostgreSQL indexes the primary key by default.
Use the unique force to create an index on a column.
To build an index:

CREATE INDEX Envents_title  on envents USING Hash (title);

For matching queries that have an operator greater than/less than/equal to this, use the B-tree index, which is equivalent to the assembly's segment register.

CREATE INDEX Events_starts  on Events USING btree (starts);

Use the B-Tree index query:

SELECT *  from Events WHERE >= ' 2012-04-01 ';

First day of PostgreSQL-relationships, crud, and joins