Load/Modify

Load/Modify#

This section discusses options for loading data into databases and ways to modify tables if necessary.

docker run -d --rm\
    --name load_modify_examples_pg\
    -e POSTGRES_PASSWORD=postgres\
    postgres:15.4

sleep 5

535ec2a21e1ab2fd79d791b7c8c6ce1b91b69519fff0a9216c34017dfbb32650

CRUD#

As a database, any system supports Create, Read, Update, and Delete operations. In this section, we review how to perform such operations on table rows:

  • Create: Use INSERT statements. See the documentation.

  • Read: Use SELECT statements, which are detailed in a dedicated section of the SQL chapter.

  • Update: Use UPDATE statements. See the documentation.

  • Delete: Use DELETE statements. See the documentation.

The following cell creates a table that we’ll use as an example for crud operations.

docker exec -i load_modify_examples_pg psql -U postgres << EOF
-- creating table
DROP TABLE IF EXISTS crud_table;
CREATE TABLE crud_table (
    col1 INT, col2 VARCHAR(20)
);
EOF

DROP TABLE
CREATE TABLE

The following cell shows how to use the INSERT operation.

docker exec -i load_modify_examples_pg psql -U postgres << EOF
INSERT INTO crud_table(col1, col2)
VALUES
    (1, 'one'),
    (2, 'two'),
    (3, 'three');

SELECT * FROM crud_table;
EOF

INSERT 0 3
 col1 | col2  
------+-------
    1 | one
    2 | two
    3 | three
(3 rows)

To update values in the table, use the UPDATE statement.

docker exec -i load_modify_examples_pg psql -U postgres << EOF
UPDATE crud_table SET col2='modified' WHERE col1=2;
SELECT * FROM crud_table;
EOF

UPDATE 1
 col1 |   col2   
------+----------
    1 | one
    3 | three
    2 | modified
(3 rows)

Finally drop records by using DELETE keyword.

docker exec -i load_modify_examples_pg psql -U postgres << EOF
DELETE FROM crud_table WHERE col1=3;
SELECT * FROM crud_table;
EOF

DELETE 1
 col1 |   col2   
------+----------
    1 | one
    2 | modified
(2 rows)

Loading from csv#

This section shows how data may be loaded from csv file.

Learn more in:

You can specify file in the FROM section DELIMITER and CSV parameters specify how file should be loaded.

The following cell creates a CSV file in the container and reads it into the database.

docker exec -i load_modify_examples_pg sh -c "cat > /test.csv" << EOF
20,Riga
30,Vilnus
4,Minsk
43,Warsaw
EOF


docker exec -i load_modify_examples_pg psql -U postgres << EOF
-- creating table
DROP TABLE IF EXISTS csv_example;
CREATE TABLE csv_example (
    value REAL, city VARCHAR(20)
);

-- loading csv table to created table
COPY csv_example(value, city)
FROM '/test.csv'
DELIMITER ','
CSV;
EOF

NOTICE:  table "csv_example" does not exist, skipping
DROP TABLE
CREATE TABLE
COPY 4

Now let’s check the obtained results:

docker exec -i load_modify_examples_pg psql -U postgres << EOF
SELECT * FROM csv_example LIMIT 10;
EOF

 value |  city  
-------+--------
    20 | Riga
    30 | Vilnus
     4 | Minsk
    43 | Warsaw
(4 rows)
Contents