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 with id of 1, for a table of id, name, description, we can do:

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

Update Multiple Records in Table

This query is slightly complicated in syntax, but here goes:

UPDATE test_table__basic AS orig_update_table SET
    <column_1> = temp_update_table.<column_1>,
    <column_2> = temp_update_table.<column_2>,
    ...
    <column_n> = temp_update_table.<column_n>
FROM (VALUES
    (record_1_values),
    (record_2_values),
    ...
    (record_n_values)
) AS temp_update_table (<columns>)
WHERE (
    orig_update_table.<column_1> = temp_update_table.<column_2> AND
    orig_update_table.<column_2> = temp_update_table.<column_2> AND
    ...
    orig_update_table.<column_n> = temp_update_table.<column_n> AND
);

Where:

• Each set is a full record of values to either match or update.
• The column_x values correspond to values to match (as if they were a standard WHERE clause).
• The columns value is the set of all columns to reference. Both ones we're matching on, and ones we're updating.
• NOTE: If you want to update a value this way, you CANNOT also use it to match.

For example, to update the description column of records that match the provided id and name fields, we can do:

UPDATE test_table__basic AS orig_update_table SET
    "id" = temp_update_table."id",
    "name" = temp_update_table."name",
    "description" = temp_update_table."description"
FROM (VALUES
    (101, 'Test Name 1', 'A New Description'),
    (102, 'Test Name 2', 'Another New Value'),
    (103, 'Test Name 3', 'Something Else')
) AS temp_update_table ("id", "name", "description")
WHERE (
    orig_update_table."id" = temp_update_table."id" AND
    orig_update_table."name" = temp_update_table."name"
);