# Databases : PostgreSQL : Examples
## Schema
#### `created` Column
```sql
-- For a new table:
CREATE TABLE t (
created DEFAULT now(), -- column definition
...
);
-- For an existing table:
ALTER TABLE t ADD COLUMN created timestamptz;
ALTER TABLE t ALTER COLUMN created SET DEFAULT now();
```
#### `modified` Column
TODO (research more)
- https://stackoverflow.com/questions/52426656/track-last-modification-timestamp-of-a-row-in-postgres/56480954#56480954
- https://stackoverflow.com/questions/1035980/update-timestamp-when-row-is-updated-in-postgresql/1036010#1036010
- https://reddit.com/r/PostgreSQL/comments/pls7sl/comment/hcd9dtu/
- https://reddit.com/r/PostgreSQL/comments/10shxej/comment/j71ggl8/
## SELECT
```sql
-- instead of "foo IN ( ... )"
... WHERE foo = any($1::text[])
```
```sql
-- Select first non-null dest_pt for each log.
SELECT
( SELECT ST_AsText( logrow.dest_pt ) as dp
FROM logrow
WHERE ( logrow.log_id = log.id AND NOT logrow.dest_pt IS NULL )
ORDER BY logrow.ts LIMIT 1
) AS dp
FROM log;
```
## INSERT — Conditional
```sql
INSERT ... ON CONFLICT DO NOTHING
DO UPDATE SET ...
```
```sql
INSERT INTO <table> ( <column1>, ... )
SELECT <value1>, ...
WHERE NOT EXISTS ( SELECT <unique_column> FROM <table> WHERE <unique_column> = <unique_value> )
```
## DELETE
Using a join:
```sql
DELETE FROM films USING producers WHERE producer_id = producers.id AND producers.name = 'foo';
```
## Arguments
```sql
$1::timestamptz -- casting to timestamp with time zone
$1::text[] -- casting to array of text values
now() -- timestamptz
gen_random_uuid() -- random UUIDv4
```