Programming/PostgreSQL/Record Queries

All of the following assume you have first loaded a database.

These also require replacing <table_name> with the actual name of the table to query.

Display Records in Table

Basic query:

SELECT * FROM <table_name>;

Expanded query:

SELECT * FROM <table_name>
WHERE (<where_clause>)
ORDER BY <order_by_clause>
LIMIT <number>;

Where:

• <where_clause> is an optional arg, replaced by the actual columns to set in the table.
• <order_by_clause> is an optional arg, replaced by order of columns to sort by.
• <number> is an optional arg, replaced by some integer to limit the number of records displayed.

For example, to get records from a table of id, name, description, we can do:

SELECT * FROM <table_name>
WHERE (name = 'A Test Name')
ORDER BY "description" ASC, "name" DESC
LIMIT 10;

Insert Single Record into Table

INSERT INTO <table_name> (<columns>)
VALUES (<values>);

Where:

• <columns> is an optional arg, replaced by the actual columns to set in the table.
• <values> is replaced by the actual values desired to insert.

For example, to create a record for a table of id, name, description, we can do:

INSERT INTO <table_name> ("id", "name", "description")
VALUES (1, 'A Test Name', 'Some Test Description');

Insert Multiple Records into Table

INSERT INTO <table_name>
VALUES
    (<record_1_values>),
    (<record_2_values>),
    ...
    (<record_n_values)
;

Where each set is a full record to insert.

For example, to create three records for a table of id, name, description, we can do:

INSERT INTO test_table__basic
VALUES
    (101, 'Test Name 1', 'Test Desc 1'),
    (102, 'Test Name 2', 'Test Desc 2'),
    (103, 'Test Name 3', 'Test Desc 3')
;

Update One or More Similar Records in Table

UPDATE <table_name>
SET <set_clause>
WHERE (<where_clause>);

For example, to update a record for a table of id, name, description, we can do:

UPDATE <table_name>
SET description = 'Abc' 
WHERE (id = 1);