# How to drop a column in PostgreSQL

## Dropping or deleting a column in PosgreSQL

Dropping or deleting a column is typically performed as part of a deliberate schema management process, data cleanup, or in response to changing business requirements or compliance needs. This is a significant and irreversible operation that should be carefully considered.

When a column is no longer needed or contains redundant information but consumes storage and affects query performance, it can be a good idea to drop it. Dropping a column simplifies the table structure, optimizes storage, and aids schema evaluation. If a column contains sensitive or **Personally Identifiable Information (PII)** and you need to comply with data privacy regulations like GDPR, you might need to drop the column to ensure data privacy and security.

## Instructions

Dropping a column is a fairly straightforward operation.

1. To drop a column in PostgreSQL, you can use the `ALTER TABLE` statement with the `DROP COLUMN` clause. Here's the basic syntax for dropping a column:

    ```sql
    ALTER TABLE table_name
    DROP COLUMN column_name;
    ```

2. You need to replace `table_name` with the name of the table that contains the column you want to drop, and `column_name` with the name of the column you wish to remove.

    Here's an example of how to drop a column from a table. Suppose you have a table named `employees` and you want to drop the `phone_number` column.

```sql ALTER TABLE employees DROP COLUMN phone_number; ```
## Errors while dropping a table

When attempting to drop a table in PostgreSQL, several errors can occur, especially if there are dependencies or issues related to permissions, active connections, or other factors. Here are common errors you might encounter when trying to drop a table in PostgreSQL:

### Table Does Not Exist

If the table you're trying to drop does not exist in the database, you'll receive an error like "table does not exist."

 ```sql
ERROR:  table "table_name" does not exist
```

### Dependency Exists

If there are dependencies on the table, such as foreign keys, views, or indexes, PostgreSQL won't allow you to drop the table until you've addressed these dependencies. You might see an error like:

 ```sql
ERROR:  cannot drop table "table_name" because other objects depend on it
DETAIL:  constraint "constraint_name" of table "referencing_table_name" depends on table "table_name"
```

In this case, you need to drop the dependent objects first, or you may use the `CASCADE` option with the `DROP TABLE` command to automatically remove dependent objects. However, be cautious when using `CASCADE` as it will remove all dependent objects without confirmation.

### Access Privileges

If you do not have the required permissions to drop the table, you'll receive an error like:

 ```sql
ERROR:  permission denied for table "table_name"
 ```

You should have the `DROP` privilege on the table to delete it.

### Active Connections

If there are active sessions or open transactions that are using the table, you won't be able to drop it until those sessions are closed or the transactions are completed. You might see an error indicating that there are active connections.

 ```sql
ERROR:  table "table_name" is being used by active queries
```

Ensure that there are no active transactions or connections using the table before attempting to drop it.

### Table in Use by Views or Rules

If the table is being used by views or rules, you'll encounter an error indicating that the table is in use.

 ```sql
ERROR:  cannot drop table "table_name" because it is being used by object "view_name"
```

You must first drop or modify the dependent views or rules before deleting the table.

### Table Contains Data

If the table contains data, PostgreSQL will require you to specify whether you want to delete the data (and the table) or just the table structure. You may receive an error like:

 ```sql
ERROR:  cannot drop table "table_name" because other objects depend on it

DETAIL:  constraint "constraint_name" of table "referencing_table_name" depends on table "table_name"

HINT:  Use DROP ... CASCADE to drop the dependent objects too.
```

In this case, you can either drop the data and table or use the `CASCADE` option to remove dependent objects.

### Recovery Mode

If PostgreSQL is in recovery mode, you won't be able to drop a table. You'll receive an error like:

 ```sql
ERROR:  cannot execute DELETE in a read-only transaction
```

Ensure that you're not in recovery mode when attempting to drop the table.

### Reserved Keywords

If your table name is a reserved keyword in PostgreSQL, you might encounter issues. It's generally a good practice to avoid using reserved keywords as table names.

 ```sql
ERROR:  syntax error at or near "select"
```

To successfully drop a table in PostgreSQL, make sure to address these potential errors by removing dependencies, granting necessary permissions, and ensuring there are no active connections or transactions using the table. Always exercise caution and back up your data before attempting to drop a table, as it is an irreversible operation.

## Dropping a table is permanent

It's important to emphasize that dropping a column is a potentially destructive operation. You should always perform a thorough analysis of the impact before proceeding as this operation cannot be undone. Consider the following:

* **Data Dependencies**: Check for any dependencies on the column, such as in views, indexes, triggers, or stored procedures. You may need to update or remove these objects.
* **Backup**: Ensure you have a recent backup of the data in case you need to restore it.
* **Application Impact**: Be aware of how dropping a column may affect the applications that rely on the table. You may need to modify your application code or queries.
* **Testing**: Consider testing the column drop in a development or staging environment to verify that it won't cause unexpected issues.

## Further table manipulation

Now that you know how to drop a column in PostgreSQL, let us explore how to add a column with this [next tutorial](https://popsql.com/learn-sql/postgresql/how-to-add-a-column-in-postgresql).

## FAQs

### 1. What happens to the data in the column when I delete it?

Deleting a column in PostgreSQL also removes all the data stored in that column. It is an irreversible operation, so make sure to back up your data before proceeding if you need to keep the information.

### 2. What if there are constraints or dependencies on the column I want to delete?

If there are constraints (e.g., foreign keys) or dependencies (e.g., views) that reference the column, you won't be able to delete it directly. You'll need to address these dependencies first, either by dropping or modifying the dependent objects.

### 3. Do I need specific privileges to delete a column?

Yes, you need the appropriate privileges to modify the table. Typically, you must have the `ALTER` privilege on the table to delete a column.

### 4. What is the impact of deleting a column on my application or queries?

Removing a column can impact any SQL queries, views, or applications that reference the deleted column. You will need to update your code and queries to accommodate the change, ensuring they no longer reference the deleted column.


How to drop a column

# How to Delete in PostgreSQL

```sql
delete from users where id = 1;
```

How to Delete

# How to query a JSON column in PostgreSQL

## Querying JSON in PostgreSQL

PostgreSQL is a powerful relational database management system. One of its standout features is its ability to handle unstructured data by allowing you to store it in a JSON column. This means you can enjoy the benefits of a structured relational database while leveraging the flexibility of JSON for certain data types. Below are some common ways to 

## Using PostgreSQL JSON to query a column

### Retrieving a Specific JSON Key as Text

If you have a table named **<code>events</code> and you want to retrieve the value associated with the key <code>name</code> from the JSON column <code>params</code>, you can use the following query:

```sql
SELECT params->>'name' FROM events;
```

This will return the value of `params.name` as text from the `events` table.

### Filtering rows based on a specific JSON key value

If you want to find all events with a specific name, for instance, 'Click Button', you can use:

```sql
SELECT * FROM events WHERE params->>'name' = 'Click Button';
```

This will return all rows from the `events` table where the `name` key in the `params` JSON column has the value 'Click Button'.

### Accessing an element from a JSON array

If your JSON column contains arrays and you want to retrieve the first element (index 0) of the array associated with the key `ids` from the `params` column, you can use:

```sql
SELECT params->'ids'->0 FROM events;
```

This will return the first element of the `ids` array from the `params` column in the `events` table.

### Filtering rows based on a nested JSON key

Sometimes, your JSON might have nested structures. For instance, if you have a table named `users` with a JSON column `preferences` and you want to find users where the nested key `beta` is set to true, you can use:

```sql
SELECT preferences->'beta' FROM users WHERE (preferences->>'beta')::boolean IS TRUE;
```

This query first type casts the value of `preferences.beta` from JSON to boolean and then filters the rows where it's true.

### Querying a JSONb column in PostgreSQL

In PostgreSQL, `jsonb` is a data type used to store JSON (JavaScript Object Notation) data in a more efficient and optimized binary format. It is an extension of the json data type. jsonb stands for **JSON binary**. It provides several advantages over the standard json type, especially when it comes to querying and indexing JSON data.

You can query` jsonb` columns using various JSON functions and operators provided by PostgreSQL. Some commonly used functions and operators include:

* `->`: Extracts a JSON element by key or array index.
* `->>`: Extracts a JSON element as text.
* `#>`: Extracts a JSON sub-object at a specified path.
* `#>>`: Extracts a JSON sub-object as text.
* `@>`: Checks if a JSON document contains another JSON document.
* `&lt;@`: Checks if a JSON document is contained within another JSON document.
* `jsonb_array_elements()`: Expands a JSON array into a set of rows.
* `jsonb_each()`: Expands a JSON object into key-value pairs.

Suppose you have a table called employees with a `jsonb` column named` employee_data`.

```sql
-- Extract the employee's name
SELECT employee_data->>'name' AS employee_name
FROM employees;

-- Check if the employee has a skill in "Sales"
SELECT *
FROM employees
WHERE employee_data->'skills' @> '["Sales"]';

-- Find employees in the "Marketing" department
SELECT *
FROM employees
WHERE employee_data->>'department' = 'Marketing';
```

`jsonb` is a powerful tool for working with JSON data in PostgreSQL, especially when you need to query and manipulate complex JSON structures.

## Fixing issues in querying JSON columns

Troubleshooting JSON column querying in PostgreSQL can involve identifying and addressing issues related to data integrity, query performance, and syntax errors. Troubleshooting JSON column querying in PostgreSQL often requires a combination of SQL knowledge, understanding of JSON data structures, and careful query optimization. By addressing these common issues and best practices, you can improve your ability to work effectively with JSON data in PostgreSQL. Here are some common troubleshooting steps and potential issues to watch out for when querying JSON columns.

### Nested JSON structures

As shown in the tutorial, querying JSON columns is fairly straightforward. However, it can get a bit difficult to query nested JSON structures. It is important to use appropriate JSON operators and functions to navigate and query nested JSON objects and arrays. Functions like` ->`, `->>`, `#>`, and` #>>` can help access nested elements. The` ->` operator returns a JSON object, and` ->>` returns the value as text. By chaining these operators, you can navigate through nested JSON structures to retrieve the desired information.

### Incorrect JSON path

While it seems obvious, more often than you would want, specifying the wrong JSON path in your queries results in incorrect output or failed queries. Examples of such error messages are - `cannot extract elements from a scalar` or `JSON path not found`. Double-check the JSON path you're using in your queries, especially when dealing with nested structures. Use tools like JSON viewers to visualize the JSON structure.

### Error handling

Data quality is an industry wide problem. While we fight this issue daily, lack of error handling in queries can cause errors. Poor quality data results in random missing keys. A single missing key in a large query can disrupt query execution and raise an error. To ensure your queries don’t fail entirely for a few missing keys, implement error handling in your queries. To handle such situations more gracefully, you can use the `COALESCE` function or conditional logic to provide a default value when a JSON key is missing. Instead of complete failure, the query will then return `"Uncategorized"` and you still get the values for which keys are present.

### Database version

Some JSON functions and operators may not be available in older PostgreSQL versions.

Ensure that you're using a PostgreSQL version that supports the JSON functionality you need. Consider upgrading, if necessary.

### Performance bottlenecks

The reason for slow query performance can be inefficient JSON queries. To resolve this, profile your queries using tools like `EXPLAIN` to identify potential bottlenecks. Consider optimizing queries by creating appropriate indexes, rewriting queries, or denormalizing data where necessary. 

## Manage unstructured data like a pro

PostgreSQL's ability to seamlessly integrate structured relational data with the flexibility of unstructured JSON data offers developers a unique advantage. By understanding how to query JSON columns effectively, one can harness the full potential of Postgres, making it easier to manage, retrieve, and analyze diverse datasets. Whether you're accessing specific JSON keys, filtering based on specific values, or diving into nested structures, PostgreSQL provides the tools to do so with precision and efficiency. As data continues to evolve and become more complex, mastering these techniques will be invaluable for any database professional.

## Going beyond JSON querying

Not that you have queried JSON columns successfully, it is time to use the results to gain further insights. Filtering this data is one of the ways to refine the results of JSON querying. Take a look at the tutorial [How to Use Filter to Have Multiple Counts in PostgreSQL](https://popsql.com/learn-sql/postgresql/how-to-use-filter-to-have-multiple-counts-in-postgresql).

## FAQs

### 1. How do I extract a specific JSON key from a JSON column?

 You can use the ``->`` or` `->>`` operators to extract JSON keys.` `->`` returns the value as JSON, while` `->>`` returns the value as text.

### 2. Can I query nested JSON structures in PostgreSQL?
Yes, PostgreSQL allows you to query and extract data from nested JSON structures using nested `->` or `->>` operators.

### 3. How can I filter rows based on JSON data criteria?

 Use the `WHERE` clause to filter rows based on JSON data criteria. For example,` `WHERE json_column->>'key' = 'value'``.

### 4. What's the difference between the `json` and `jsonb` data types for querying JSON in PostgreSQL?

``jsonb`` is a binary JSON data type optimized for querying and indexing, while ``json`` is a plain text JSON data type. ``jsonb`` is recommended for querying JSON data.

### 5. How do I handle missing JSON keys or errors when querying JSON columns?

You can use the` `COALESCE`` function or conditional logic to provide default values or handle missing keys when querying JSON columns.

How to Query a JSON Column

# How to Calculate Cumulative Sum-Running Total in PostgreSQL
Let's say we want to see a hockey stick graph of our cumulative user sign ups by day in PostgreSQL. First, we'll need a table with a day column and a count column:

```sql
select
  date_trunc('day', created_at) as day,
  count(1)
from users
group by 1
```

```sql
         day         | count
---------------------+-------
 2018-01-01 00:00:00 |     10
 2018-01-02 00:00:00 |     10
 2018-01-03 00:00:00 |     10
```

Next, we'll write a [PostgreSQL common table expression (CTE)](https://popsql.com/learn-sql/postgresql/how-to-write-a-common-table-expression-in-postgresql/) and use a window function to keep track of the cumulative sum/running total:

```sql
with data as (
  select
    date_trunc('day', created_at) as day,
    count(1)
  from users
  group by 1
)

select
  day,
  sum(count) over (order by day asc rows between unbounded preceding and current row)
from data
```

How to Calculate Cumulative Sum-Running Total

# How to Use Filter to Have Multiple Counts in PostgreSQL
Using `filter` is useful when you want to do multiple counts on a table:

```sql
select
  count(1), -- Count all users
  count(1) filter (where gender = 'male'), -- Count male users
  count(1) filter (where beta is true) -- Count beta users
  count(1) filter (where active is true and beta is false) -- Count active non-beta users
from users
```

How to Use Filter to Have Multiple Counts

# How to Write a Case Statement in PostgreSQL
Case statements are useful when you're reaching for an if statement in your select clause.

```sql
select
 case
 when precipitation = 0 then 'none'
 when precipitation <= 5 then 'little'
 when precipitation > 5 then 'lots'
 else 'unknown'
 end as amount_of_rain
from weather_data;
```

How to Write a Case Statement

# How to Use Coalesce in PostgreSQL
Say you're looking at a PostgreSQL integer column where some rows are null:

```sql
select
  day,
  tickets
from stats;
```

```sql
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |   null
 2018-01-03 |     3
```

Instead of having that null, you might want that row to be `0`. To do that, use the `coalesce` function, which returns the first non-null argument it's passed:

```sql
select
  day,
  coalesce(tickets, 0)
from stats;
```

```sql
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |     0
 2018-01-03 |     3
```

How to Use Coalesce

# How to Compare Two Values When One Is Null in PostgreSQL
Imagine you're comparing two PostgreSQL columns and you want to know how many rows are different. No problem, you think:

```sql
select count(1)
from items
where width != height;
```

Not so fast. If some of the widths or heights are null, they won't be counted! Surely that wasn't your intention. That's where `is distinct from` comes into play:

```sql
select count(1)
from items
where width is distinct from height;
```

Now, your count will be "null aware" and you'll get the result you want 💥

How to Compare Two Values When One Is Null

# How to Import a CSV into PostgreSQL using Copy
Importing a CSV into PostgreSQL requires you to [create a table](https://popsql.com/learn-sql/postgresql/how-to-create-a-table-in-postgresql/) first. [Duplicating an existing table's structure](https://popsql.com/learn-sql/postgresql/how-to-duplicate-a-table-in-postgresql/) might be helpful here too.

The commands you need here are `copy` (executed server side) or `\copy` (executed client side). The former requires your database to be able to access the CSV file, which is rarely going to work for you in a production environment like Amazon RDS because you're not going to be uploading random CSV files to your database server. However, if you use `psql` on your local machine and you're connected to your remote database, you can use the latter `\copy` command, assuming your machine can access the CSV file.

In your terminal, let's open `psql`:

```sql
psql your_database_name # or postgres://username:password@amazonaws.com
```

Now it's time to use the `\copy` command:

```sql
-- Assuming you have already created an imported_users table
-- Assuming your CSV has no headers
\copy imported_users from 'imported_users.csv' csv;

-- If your CSV does have headers, they need to match the columns in your table
\copy imported_users from 'imported_users.csv' csv header;

-- If you want to only import certain columns
\copy imported_users (id, email) from 'imported_users.csv' csv header;
```

How to Import a CSV using Copy

# How to Write a Common Table Expression in PostgreSQL
Common table expressions (CTEs) are a great way to break up complex PostgreSQL queries. Here's a simple query to illustrate how to write a CTE:

```sql
with beta_users as (
  select *
  from users
  where beta is true
)

select events.*
from events
inner join beta_users on beta_users.id = events.user_id
```

You can find a real world example of a CTE in [How to Calculate Cumulative Sum/Running Total in PostgreSQL](https://popsql.com/learn-sql/postgresql/how-to-calculate-cumulative-sum-running-total-in-postgresql).

How to Write a Common Table Expression

# How to Do Type Casting in PostgreSQL
Here are some examples of common types in PostgreSQL:

```sql
-- Cast text to boolean
select 'true'::boolean;

-- Cast float to integer
select 1.0::integer;

-- Cast integer to float
select '3.33'::float;
select 10/3.0; -- This will return a float too

-- Cast text to integer
select '1'::integer;

-- Cast text to timestamp
select '2018-01-01 09:00:00'::timestamp;

-- Cast text to date
select '2018-01-01'::date;

-- Cast text to interval
select '1 minute'::interval;
select '1 hour'::interval;
select '1 day'::interval;
select '1 week'::interval;
select '1 month'::interval;
```

How to Do Type Casting

# How to Use generate_series to Avoid Gaps In Data in PostgreSQL
If you're [grouping by time](https://popsql.com/learn-sql/postgresql/how-to-group-by-time-in-postgresql/) and you don't want any gaps in your data, PostgreSQL's `generate_series` can help. The function wants three arguments: `start`, `stop`, and `interval`:

```sql
select generate_series(
  date_trunc('hour', now()) - '1 day'::interval, -- start at one day ago, rounded to the hour
  date_trunc('hour', now()), -- stop at now, rounded to the hour
  '1 hour'::interval -- one hour intervals
) as hour
```

```sql
          hour
------------------------
 2017-12-22 13:00:00-08
 2017-12-22 14:00:00-08
 2017-12-22 15:00:00-08
 2017-12-22 16:00:00-08
 2017-12-22 17:00:00-08
 ...
```

Now you can use a [common table expression](https://popsql.com/learn-sql/postgresql/how-to-write-a-common-table-expression-in-postgresql/) to create a table that has a row for each interval (ie each hour of the day), and then left join that with your time series data (ie new user sign ups per hour).

```sql
with hours as (
  select generate_series(
    date_trunc('hour', now()) - '1 day'::interval,
    date_trunc('hour', now()),
    '1 hour'::interval
  ) as hour
)

select
  hours.hour,
  count(users.id)
from hours
left join users on date_trunc('hour', users.created_at) = hours.hour
group by 1
```

How to Use generate_series to Avoid Gaps In Data

# How to Get the First Row per Group in PostgreSQL
Let's say we have an `events` table that belongs to a `user_id`, and we want to see the first event for each user for that day. The function we need here is `row_number`. It's got a tricky syntax that I always forget. Here's an example PostgreSQL query:

```sql
select
  *,
  row_number() over (partition by user_id order by created_at desc) as row_number
from events
where day = '2018-01-01'::date
```

This gives us all the event IDs for the day, plus their `row_number`. Since we only want the first event for the day, we only want rows that have `row_number: 1`. To do that, we can use a [common table expression](https://popsql.com/learn-sql/postgresql/how-to-write-a-common-table-expression-in-postgresql/):

```sql
with _events as (
  select
    *,
    row_number() over (partition by user_id order by created_at desc) as row_number
  from events
  where day = '2018-01-01'::date
)

select *
from _events
where row_number = 1
```

How to Get the First Row per Group

# How to Calculate Percentiles in PostgreSQL
Let's say we want to look at the percentiles for query durations. We can use PostgreSQL's `percentile_cont` function to do that:

```sql
select
  percentile_cont(0.25) within group (order by duration asc) as percentile_25,
  percentile_cont(0.50) within group (order by duration asc) as percentile_50,
  percentile_cont(0.75) within group (order by duration asc) as percentile_75,
  percentile_cont(0.95) within group (order by duration asc) as percentile_95
from query_durations
```

If we want to view those percentiles by day:

```sql
select
  day,
  percentile_cont(0.25) within group (order by duration asc) over (partition by day) as percentile_25,
  percentile_cont(0.50) within group (order by duration asc) over (partition by day) as percentile_50,
  percentile_cont(0.75) within group (order by duration asc) over (partition by day) as percentile_75,
  percentile_cont(0.95) within group (order by duration asc) over (partition by day) as percentile_95
from query_durations
group by 1
order by 1 asc
```

How to Calculate Percentiles

# How to Use Lateral Joins in PostgreSQL
Once upon a time, my queries were a mess. I didn’t know how to use lateral joins, so I would copy-and-paste the same calculations over and over again in my queries.

_Co-workers were starting to talk._

Lateral joins allow you to reuse calculations, making your queries neat and legible. Let's learn about lateral joins by rewriting an atrocious query together.

## Data Set

We'll use a [cool sample dataset](https://popsql.com/blog/cool-sample-data) of real Kickstarter projects, if you'd like to follow along.

Relevant columns:

![data table](//images.ctfassets.net/iv1sg9nibjwl/3F0Cn2OhTutbvN29thKDRS/6462da6f00b96d6a7e2e213eac6566d8/data_table.png)

For each Kickstarter project, we want to calculate:

*   total pledged in USD
*   average pledge in USD
*   USD over or under goal
*   duration of the project in days
*   daily shortfall / surplus, _the extra USD needed daily to hit goal_

## Queries, Before and After

**Before:**

```sql
select
    (pledged / fx_rate) as pledged_usd,
    (pledged / fx_rate) / backers_count as avg_pledge_usd,
    (goal / fx_rate) - (pledged / fx_rate) as amt_from_goal,
    (deadline - launched_at) / 86400.00 as duration,
    ((goal / fx_rate) - (pledged / fx_rate)) / ((deadline - launched_at) / 86400.00) as usd_needed_daily
from kickstarter_data;
```

Without lateral joins, see how often I reuse the same calculations:

![repetitive computations](//images.ctfassets.net/iv1sg9nibjwl/5tiBwCsD5VfW8oB0woKuEO/92b5175fb600e9cf83e233286ef29022/repetitive_computations.png)

Yuck. Not only does this make the query difficult to read, it introduces risk of typos or other errors if I ever need to make an update.

**After:**

```sql
select
    pledged_usd,
    avg_pledge_usd,
    amt_from_goal,
    duration,
    (usd_from_goal / duration) as usd_needed_daily
from kickstarter_data,
    lateral (select pledged / fx_rate as pledged_usd) pu
    lateral (select pledged_usd / backers_count as avg_pledge_usd) apu
    lateral (select goal / fx_rate as goal_usd) gu
    lateral (select goal_usd - pledged_usd as usd_from_goal) ufg
    lateral (select (deadline - launched_at)/86400.00 as duration) dr;
```

With lateral joins, I can define the calculation just once. I can then reference those calculations in other parts of my query.

## What's happening?

The `lateral` keyword allows us to access columns after the `FROM` statement, and reference these columns "earlier" in the query ("earlier" meaning "written higher in the query").

SQL queries run in a different order than you might expect. In fact, `FROM` and `JOIN` are the first statements run. Therefore it's no problem to reference columns after the `FROM` statement.

![query order](//images.ctfassets.net/iv1sg9nibjwl/1Ll3rLDmU4J36jRbTlQfxg/eea51ce12cf5cb5ece843886a89e18fa/query_order.png)

Image Credit: [Julia Evans](https://twitter.com/b0rk/status/1179449535938076673)

Word of warning: stick to simple mathematical operations when writing lateral joins for calculations. Aggregate functions like `COUNT()`, `AVG()`, or `SUM()` are not supported.

Happy querying! 🍭

How to Use Lateral Joins

# How to Use nullif() in PostgreSQL
The `nullif()` function returns a null value, if a the value of the field/column defined by the first parameter equals that of the second. Otherwise, it will return the original value. Here's an example below:

```sql
select
  name,
  platform,
  nullif(platform,'Did not specify') as platform_mod
from users;
```

```sql
   name    |    platform     | platform_mod
-----------+-----------------+------------
 Steve     | Mac             | Mac
 Bill      | Windows         | Windows
 Linus     | Linux           | Linux
 Beth      | Did not specify |
```

Note that `nullif()` is only capable of replacing one value with null. If you need to replace multiple values, you can use the [CASE function](https://popsql.com/learn-sql/postgresql/how-to-write-a-case-statement-in-postgresql/).

```sql
select
  name,
  platform,
  case
    when platform = 'Mac' then null
    when platform = 'Windows' then null
    when platform = 'Linux' then null
    else platform
  end as platform_mod
from users;
```

```sql
   name    |    platform     | platform_mod
-----------+-----------------+------------
 Steve     | Mac             |
 Bill      | Windows         |
 Linus     | Linux           |
 Beth      | Did not specify | Did not specify
```

How to Use nullif()

How to Convert UTC to Local Time Zone

# How to Round Timestamps in PostgreSQL
Rounding/truncating timestamps are especially useful when you're [grouping by time](https://popsql.com/learn-sql/postgresql/how-to-group-by-time-in-postgresql/). The function you need here is `date_trunc`:

```sql
select date_trunc('second', now()) -- or minute, hour, day, month
```

How to Round Timestamps

# How to Group by Time in PostgreSQL
When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc. The PostgreSQL function you need here is `date_trunc`.

```sql
select
  date_trunc('minute', created_at), -- or hour, day, week, month, year
  count(1)
from users
group by 1
```

If you don't have new users every minute, you're going to have gaps in your data. To have one row per minute, even when there's no data, you'll want to use [generate\_series](https://popsql.com/learn-sql/postgresql/how-to-use-generate-series-to-avoid-gaps-in-data-in-postgresql/).

How to Group by Time

# How to Query Date and Time in PostgreSQL
Get the date and time time right now:

```sql
select now(); -- date and time
select current_date; -- date
select current_time; -- time
```

Find rows between two absolute timestamps:

```sql
select count(1)
from events
where time between '2018-01-01' and '2018-01-31'
```

Find rows created within the last week:

```sql
select count(1)
from events
where time > now() - interval '1 week'; -- or '1 week'::interval, as you like
```

Find rows created between one and two weeks ago:

```sql
select count(1)
from events
where time between (now() - '1 week'::interval) and (now() - '2 weeks'::interval);
```

Extracting part of a timestamp:

```sql
select date_part('minute', now()); -- or hour, day, month
```

Get the day of the week from a timestamp:

```sql
-- returns 0-6 (integer), where 0 is Sunday and 6 is Saturday
select date_part('dow', now());

-- returns a string like monday, tuesday, etc
select to_char(now(), 'day');
```

Converting a timestamp to a unix timestamp (integer seconds):

```sql
select date_part('epoch', now());
```

Calculate the difference between two timesetamps:

```sql
-- Difference in seconds
select date_part('epoch', delivered_at) - date_part('epoch', shipped_at); -- or minute, hour, week, day, etc

-- Alternatively, you can do this with `extract`
select extract(epoch from delivered_at) - extract(epoch from shipped_at);
```

How to Query Date and Time

# How to Use BETWEEN Correctly in PostgreSQL
Be careful when using `BETWEEN` with timestamps. You might end up chopping off a whole day of data 😬

Imagine you were chief safety inspector at a local trampoline park (bonus points if that is your job in real life). You might write a query like this to get a report of accidents in December:

```sql
SELECT *
FROM accidents
WHERE created_at BETWEEN '2019-12-01' AND '2019-12-31'
```

Looks good, right? Nope.

This query would omit any mishaps the whole day of December 31. Why? **Your query only looks from midnight** on Dec 1 to midnight on Dec 31. Any bump, abrasion, or mid-air collision that occurred after midnight on the 31st won't be in your results. The query above is the same as:

```sql
SELECT *
FROM accidents
WHERE created_at >= '2019-12-01 00:00:00.000000'
AND created_at <= '2019-12-31 00:00:00.000000'
```

You can avoid this problem by writing the query:

```sql
SELECT *
FROM accidents
WHERE created_at >= '2019-12-01'
AND created_at < '2020-01-01'
```

The lesson: save `BETWEEN` for discrete quantities like integers. And stay away from trampoline parks. We’ve seen the data and it doesn't look pretty.

How to Use BETWEEN Correctly

# How to Exclude Current or Partial Weeks in PostgreSQL
Let's say you have a simple query that [groups by week](https://popsql.com/learn-sql/postgresql/how-to-group-by-time-in-postgresql/) and looks back at the last 4 weeks:

```sql
select
  date_trunc('week', created_at), -- or hour, day, month, year
  count(1)
from users
where created_at > now() - interval '4 weeks'
group by 1;
```

If you ran this query midweek, say on a Wednesday, the current week would only return data from Monday through Wednesday (~3 days). Given the incomplete week, the last data point would look artificially low:

![query with dip](//images.ctfassets.net/iv1sg9nibjwl/2ESGPfVAGarGbPzzOf0pWd/9395c5666129f966596dd0702769a852/query_with_dip.png)

To avoid this dip (and the inevitable questions from your manager), use the `date_trunc()` function in the `where` clause:

```sql
select
  date_trunc('week', created_at),
  count(1)
from users
where date_trunc('week', created_at) != date_trunc('week', now())
and created_at > now() - interval '4 weeks'
group by 1;
```

You now omit any data from the current incomplete week, there's no more dip:

![query without dip](//images.ctfassets.net/iv1sg9nibjwl/58OFuSoEfNEcKNlBo94rEZ/2c6736c87246f44af155aecc2ea4e230/query_without_dip.png)

There's one more problem. If you ran this query mid-week, the starting point of your "look back period" would be in the middle of the week 4 weeks ago. To guard against incomplete weeks in the _beginning_ of your time range, `date_trunc()` can help again:

```sql
select
  date_trunc('week', created_at),
  count(1)
from users
where date_trunc('week', created_at) != date_trunc('week', now())
and created_at > date_trunc('week',now()) - interval '4 weeks'
group by 1;
```
Instead of looking back 4 weeks from `now()`, your query look backs 4 weeks from the _beginning of current week_. See the difference in below:

```sql
select now(); -- Result: 2020-02-05 19:38:26.423589+00
select date_trunc('week',now()); -- Result: 2020-02-03 00:00:00+00
```

How to Exclude Current or Partial Weeks

# PostgreSQL: Reset Sequence Command
If you have a serial ID column (ie auto incrementing ID), they'll start at 1 by default, but sometimes you may want them to start at a different number. These numbers are known as "sequences" and have their own designated table.

If you have a `users.id` column, you'll have a `users_id_seq` table. Some helpful columns in there are `start_value`, which will usually be `1`, and `last_value`, which _could_ be a fast way to see how many rows are in your table if you haven't altered your sequence or deleted any rows.

```sql
select * from users_id_seq;
```

```sql
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 users_id_seq  |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t
(1 row)
```

To alter the sequence so that IDs start a different number, you can't just do an `update`, you have to use the `alter sequence` command.

```sql
alter sequence users_id_seq restart with 1000;
```

How to Reset Sequence

# How to Drop a View in PostgreSQL
```sql
drop view enriched_users;
```

How to Drop a View

# How to Create a View in PostgreSQL
```sql
create or replace view enriched_users as (
  select *
  from users
  inner join enrichments on enrichments.user_id = users.id
);
```

How to Create a View

# How to Drop an Index in PostgreSQL
```sql
drop index index_created_at_on_users;
```

How to Drop an Index

# PostgreSQL: Add an Index
Having the right indexes are critical to making your queries performant, especially when you have large amounts of data. Here's an example of how to create an index in PostgreSQL:

```sql
create index concurrently "index_created_at_on_users"
on users using btree (created_at);
```

If you want to index multiple columns:

```sql
create index concurrently "index_user_id_and_time_on_events"
on events using btree (user_id, time);
```

Unique indexes to prevent duplicate data:

```sql
create unique index concurrently "index_stripe_event_id_on_stripe_events"
on stripe_events using btree(stripe_event_id);
```

Partial indexes to only index rows where a certain condition is met:

```sql
create index concurrently "index_active_users"
on users using btree(created_at) where active is true;
```

You can also have a _unique_ partial index. For example, imagine if each user can only have one active credit card:

```sql
-- This will prevent any user from having more than one active credit card
create unique index concurrently "index_active_credit_cards"
on credit_cards using btree(user_id) where active is true;
```

How to Add an Index

# How to Remove a Not Null Constraint in PostgreSQL
```sql
alter table users alter column email drop not null;
```

How to Remove a Not Null Constraint

# How to Add a Not Null Constraint in PostgreSQL
Not null constraints are a great way to add another layer of validation to your data. Sure, you could perform this validation in your application layer, but shit happens: somebody will forget to add the validation, somebody will remove it by accident, somebody will bypass validations in a console and insert nulls, etc. If you're validating nulls on the database layer as well, you're protected 💪

```sql
alter table users alter column email set not null;
```

How to Add a Not Null Constraint

# How to Remove a Default Value From a Column in PostgreSQL
Assuming `orders.total_cents` had a default value, this will drop the default for future inserts.

```sql
alter table orders alter column total_cents drop default;
```

How to Remove a Default Value From a Column

# How to Add a Default Value to a Column in PostgreSQL
```sql
-- Example: Orders have a default total of 0 cents
alter table orders alter column total_cents set default 0;

-- Example: Items are available by default
alter table items alter column available set default true;
```

How to Add a Default Value

# PostgreSQL: Change a Column Name
Here's an example of renaming a column in PostgreSQL:

```sql
alter table users rename column registered_at to created_at;
```

How to Change a Column Name

# How to Add a Column in PostgreSQL
Here's an example of adding a `created_at` timestamp column to your `users` table in PostgreSQL.

```sql
alter table users add column created_at timestamp without time zone;
```

Adding a string (varchar) column with a not null constraint:

```sql
alter table users add column bio character varying not null;
```

Adding a boolean column with a default value:

```sql
alter table users add column active boolean default true;
```

For reference, check out the [full list of Postgres data types](https://www.postgresql.org/docs/current/static/datatype.html#DATATYPE-TABLE).

How to Add a Column

# How to Duplicate a Table in PostgreSQL
Sometimes it's useful to duplicate a table:

```sql
create table dupe_users as (select * from users);

-- The `with no data` here means structure only, no actual rows
create table dupe_users as (select * from users) with no data;
```

How to Duplicate a Table

# How to Truncate a Table in PostgreSQL
Be very careful with this command. It will empty the contents of your PostgreSQL table. This is useful in development, but you'll rarely want to do this in production.

```sql
truncate my_table
```

If you have a serial ID column and you'd like to [restart its sequence](https://popsql.com/learn-sql/postgresql/how-to-alter-sequence-in-postgresql/) (ie restart IDs from `1`):

```sql
truncate my_table restart identity
```

How to Truncate a Table

# How to Rename a Table in PostgreSQL
```sql
alter table events rename to events_backup;
```

How to Rename a Table

# How to Drop a Table in PostgreSQL
```sql
drop table funky_users;
```

How to Drop a Table

# How to Create a Table in PostgreSQL
Here's an example of creating a `users` table in PostgreSQL:

```sql
create table users (
  id serial primary key, -- Auto incrementing IDs
  name character varying, -- String column without specifying a length
  preferences jsonb, -- JSON columns are great for storing unstructured data
  created_at timestamp without time zone -- Always store time in UTC
);
```

This is also a chance to specify [not null constraints](https://popsql.com/learn-sql/postgresql/how-to-add-a-not-null-constraint-in-postgresql/) and [default values](https://popsql.com/learn-sql/postgresql/how-to-add-a-default-value-to-a-column-in-postgresql/):

```sql
create table users (
  id serial primary key,
  name character varying not null,
  active boolean default true
);
```

You can also create temporary tables that will stick around for the duration of your session. This is helpful to break down your analysis into smaller pieces.

```sql
-- Create a temporary table called `scratch_users` with just an `id` column
create temporary table scratch_users (id integer);

-- Or create a temporary table based on the output of a select
create temp table active_users
as
select * from users where active is true;
```

How to Create a Table

# How to Update in PostgreSQL
```sql
-- All rows
update users set updated_at = now();

-- Some rows
update users set updated_at = now() where id = 1;
```

How to Update

# How to Insert Into PostgreSQL
Here's the shortest and easiest way to insert in PostgreSQL. You only have to specify the values, but you have to pass all values, and they have to be in order. So if you have 10 columns, you have to specify 10 values.

```sql
-- Assuming the users table has only three columns: first_name, last_name, and email, and in that order
insert into users values ('John', 'Doe', 'john@doe.com');
```

If you have many columns, but only want to specify some:

```sql
insert into users (first_name) values ('John');
```

If you want to insert into a JSON column, just wrap the valid JSON in a single quoted string:

```sql
insert into users (preferences) values ('{ "beta": true }');
```

If inserting a row would violate a unique constraint, you can use Postgres' `on conflict` clause to specify what to do when that happens. For example, imagine you have a webhook system and you want to gracefully handle duplicate webhooks:

```sql
-- If we already recorded this webhook, do nothing
insert into stripe_webhooks (event_id)
values ('evt_123')
on conflict do nothing;
```

You can also do "upserts" (update or insert) in Postgres:

```sql
-- Assuming you have a unique index on email
insert into users (email, name)
values ('john@doe.com', 'Jane Doe')
on conflict (email) do update set name = excluded.name; -- excluded.name refers to the 'Jane Doe' value
```

How to Insert

# How to Use PostgreSQL substring() with RegEx to Extract a String
You can use regular expressions in the `substring()` function to extract a string that matches a specified pattern:

**Syntax:**

```sql
substring(string from pattern) -- using POSIX regular expressions
substring(string from pattern for escape_char)  -- using SQL regular expressions
```

Here is one example that uses POSIX regular expressions to extract any word that has 'ss' among its letters:

```sql
# select substring('Learning SQL is essential.' from '\w*ss\w*');
 substring
-----------
 essential
```

`substring()` with SQL regular expressions involves three parameters: the string to search, the pattern to match, and a delimiter defined after the `for` keyword. In the following example we look for a three and then seven letter words that starts with an 'S' and ends with an 'L':

```sql
# select substring('Learning SQL is essential.' from '%#"S_L#"%' for '#');
 substring
-----------
 SQL

# select substring('Do you pronounce it as SQL or SEQUEL?' from '%#"S____L#"%' for '#');
 substring
-----------
 SEQUEL
```

You may refer to the [PostgreSQL documentation](https://www.postgresql.org/docs/12/functions-matching.html) for more information on regular expression pattern matching.

How to Use substring() with RegEx to Extract a String

# How to Use substring() in PostgreSQL
In PostgreSQL, the function `substring()` has many uses. The simplest one extracts a number of characters from the supplied string. Let's look at its syntax and parameters:

**Syntax:**

```sql
substring(original_string [from <starting_position>] [for <number_of_characters>])
```

**Parameters:**

* original\_string (required) - the string or column name to extract from
* `from` starting\_position (optional) - the position of the first character to be extracted. If not specified, the extraction will be from the first character.
* `for` number\_of\_characters - the number of characters to be extracted. If not specified, the extraction will be up to the last character of the string.

Note that while the last two parameters are optional, you must use one of them. Here are some examples:

```sql
# select substring('Learning SQL is essential.' from 10);
 substring 
-------------------
 SQL is essential.
```

```sql
# select substring('Learning SQL is essential.' from 10 for 3);
 substring
-----------
 SQL
```

```sql
# select substring('Learning SQL is essential.' for 13);
 substring 
---------------
 Learning SQL
```

`starting_position` can also be a negative number. On its own it is ignored but when used with `number_of_characters` it causes the function to count "invisible" characters to the left of the string.

```sql
# select substring('Learning SQL is essential.', -4, 10);
 substring
-----------
 Learn
```

There is also a version of this function that uses commas to separate the parameters instead of using the keywords `from` and `for`. In this case only the number of characters parameter is optional.

```sql
# select substring('Learning SQL is essential.', 10, 3);
 substring
-----------
 SQL

# select substring('Learning SQL is essential.', 10);
 substring 
-------------------
 SQL is essential.
```

How to Use substring()

# How to Use string_agg() in PostgreSQL
`string_agg()` combines non-null values into one string, separated by the delimiter character that you specify in the second parameter. For example, in the Sakila database there's a city and a country table. If you want to show the available cities per country in one line, separated by commas:

```sql
select
  country,
  string_agg(city, ',') as cities
from country
join city using (country_id)
group by country
limit 4;
```

```sql
       country        |             cities             
----------------------+--------------------------------
 Thailand             | Songkhla,Nakhon Sawan,Pak Kret
 Faroe Islands        | Trshavn
 Bangladesh           | Jamalpur,Tangail,Dhaka
 United States        | Springfield, Springfield
```

Note that PostgreSQL does not assume a default delimiter. You need to specify it as the second parameter. Also note that like other aggregate functions, you must use the `group by` clause with `string_agg()`.

## Removing duplicates in our output string

Notice that in our results, there are multiple cities in the United States named "Springfield". If you want to omit any duplicates, simply add `distinct` in the first parameter:

```sql
select
  country,
  string_agg(distinct city, ',') as cities
from country
join city using (country_id)
group by country
limit 4;
```

```sql
       country        |             cities             
----------------------+--------------------------------
 Thailand             | Songkhla,Nakhon Sawan,Pak Kret
 Faroe Islands        | Trshavn
 Bangladesh           | Jamalpur,Tangail,Dhaka
 United States        | Springfield
```

## Ordering the contents within the output string

Perhaps you also want the output of the `string_agg()` function to be ordered alphabetically. You can specify the order using `order by` after the second parameter:

```sql
select
  country,
  string_agg(distinct city, ',' order by city asc) as cities
from country
join city using (country_id)
group by country
limit 4;
```

```sql
       country        |             cities             
----------------------+--------------------------------
 Thailand             | Nakhon Sawan,Pak Kret,Songkhla
 Faroe Islands        | Trshavn
 Bangladesh           | Dhaka,Jamalpur,Tangail
 United States        | Springfield
```

This can be helpful for particular long outputs, but the `order by` clause is optional.

How to Use string_agg()

# How to Trim Strings in PostgreSQL
The `trim()` function removes specified characters or spaces from a string. You can specify to trim from only the start or end of the string, or trim from both. This is best explained using examples.

You can trim "1" from start of the string:

```sql
select trim(leading '1' from '111hello111');

 ltrim   
----------
 hello111
```

You can trim "1" from the end of the string:

```sql
select trim(trailing '1' from '111hello111211');

 rtrim     
--------------
 111hello1112
```

_Note: the 1's before the 2 were not trimmed, as they are not the end of the string._

You can specify multiple characters to trim. PostgreSQL will remove any combination of those characters in succession:

```sql
select trim(both 'abc' from 'abcbabccchellocbaabc');

 btrim
-------
 hello
```

PostgreSQL also supports the non-standard syntax of specifying which characters to remove as the last parameter, separated by a comma:

```sql
select trim(both 'abcbabccchellocbaabc','abc');

 btrim
-------
 hello
```

Not specifying where to trim from has the same result as "both":

```sql
 select trim('1' from '111hello111');

 btrim
-------
 hello
```

Not specifying what character to trim will result in trimming spaces:

```sql
select trim('    remove spaces from both sides    ');

 btrim             
-------------------------------
 remove spaces from both sides
```

`rtrim()` and `ltrim()` are special versions that only remove trailing and leading characters, respectively. These functions only accept parameters separated by commas:

```sql
select ltrim('zzzyclean this up', 'xyz');

 ltrim     
---------------
 clean this up
```

```sql
select rtrim('again not specifying what to trim removes spaces   ');

 rtrim                       
--------------------------------------------------
 again not specifying what to trim removes spaces
```

How to Trim Strings

# How to Replace Substrings in PostgreSQL
The `replace()` function is used to change all occurrences of a certain substring to a new string. It accepts three parameters - the main string, the substring to be replaced, and the new string to be used.

```sql
select replace('This is old, really old', 'old', 'new');
```

```sql
 replace         
-------------------------
 This is new, really new
```

How to Replace Substrings

# How to Query Arrays in PostgreSQL
You can retrieve the contents of an array by specifying it in the `select` clause like any other column:

```sql
select
 first_name,
 last_name,
 phone_numbers
from contacts;
```

```sql
 first_name | last_name | phone_numbers 
------------+-----------+-----------------------------
 John | Doe | {999-876-5432,999-123-4567}
 Bob | Parr | {555-INC-RDBL}
```

You can also specify which element of an array to retrieve by specifying its position inside the square brackets. By default PostgreSQL uses 1 as the first position, though this can be overridden as shown in the `array_fill()` example. The example below shows the first round scores of a player:

```sql
select
 player_number,
 round_scores[1]
from player_scores;
```

```sql
 player_number | round_scores
---------------+--------------
 10001 | 95
 10002 | 91
```

You can also check against the value of a specific element in the `where` clause:

```sql
select *
from player_scores
where round_scores[1] >= 95;
```

```sql
 player_number | round_scores 
---------------+------------------
 10001 | {95,92,96,97,98}
```

To compare all elements of an array to a value, you can use `ANY/SOME` and `ALL` . `ANY` and its synonym `SOME` will return a row if at least one element satisfies the condition. `ALL` requires all elements to satisfy the condition for a row to be returned. See the examples below:

_Show records where there's at least one score above 95. This is best read as "where 95 is lower than ANY of the scores":_

```sql
select *
from player_scores
where 95 < any (round_scores);
```

```sql
 player_number | round_scores 
---------------+------------------
 10001 | {95,92,96,97,98}
 10002 | {91,92,93,95,99}
```

_Only show records where 92 is lower or equal to ALL the scores:_

```sql
select *
from player_scores
where 92 <= all (round_scores);
```

```sql
 player_number | round_scores 
---------------+------------------
 10001 | {95,92,96,97,98} 
```

Using `unnest()` expands an array to multiple rows. The non-array columns get repeated for each row.

```sql
select
 first_name,
 last_name,
 unnest(phone_numbers)
from contacts;
```

```sql
 first_name | last_name | unnest 
------------+-----------+--------------
 John | Doe | 999-876-5432
 John | Doe | 999-123-4567
 Bob | Parr | 555-INC-RDBL
```

How to Query Arrays

# How to Modify Arrays in PostgreSQL
## Overwriting an Array

The most basic way to modify an array column are to overwrite all values by assigning it a new array, or to specify an element to change.

Say you start off with these data:

```sql
player_number  |  round_scores   
---------------+------------------
         10002 | {91,92,93,95,99}
         10001 | {95,92,96,97,98}
```

```sql
-- overwrite all scores for a player
update player_scores set round_scores='{92,93,94,96,98}' where player_number=10002;

-- change only the score for the second round for player 10001
update player_scores set round_scores[2]=94 where player_number=10001;
```

After performing these commands, the updated data are:

```sql
 player_number |   round_scores   
---------------+------------------
         10002 | {92,93,94,96,98}
         10001 | {95,94,96,97,98}
```

## Prepend and Append to an Array

PostgreSQL has functions that offer more ways to modify arrays. First, use `array_prepend()` and `array_append()` to add one element to the start and to the end of an array, respectively:

```sql
update player_scores set round_scores = array_prepend(0, round_scores);

update player_scores set round_scores = array_append(round_scores, 100);
```

```sql
 player_number |      round_scores      
---------------+------------------------
         10002 | {0,92,93,94,96,98,100}
         10001 | {0,95,94,96,97,98,100}
```

## Concatenate Multiple Arrays

To add an array to another array, use `array_cat()`.

```sql
select array_cat('{1, 2}', ARRAY[3, 4]) as concatenated_arrays;
```

```sql
 concatenated_arrays
---------------------
 {1,2,3,4}
```

The `||` operator can be used as a much simpler alternative to `array_prepend()`, `array_append()` and `array_cat()`:

```sql
select 1 || array[2, 3, 4] as element_prepend;
```

```sql
 element_prepend
-----------------
 {1,2,3,4}
```

```sql
select array[1, 2, 3] || 4 as element_append;
```

```sql
 element_append
----------------
 {1,2,3,4}
```

```sql
select array['a', 'b', 'c'] || array['d', 'e', 'f'] as concat_array;
```

```sql
 concat_array  
---------------
 {a,b,c,d,e,f}
```

You can even add an array to a 2-dimensional array:

```sql
select array[1, 2] || array[[4, 5],[6, 7]] as concat_2d_array;
```

```sql
   concat_2d_array   
---------------------
 {{1,2},{4,5},{6,7}}
```

## Removal from an Array

`array_remove()` removes all elements that matches the second parameter.

```sql
select array_remove(round_scores,94) as removed_94 from player_scores;
```

```sql
     removed_94      
---------------------
 {0,92,93,96,98,100}
 {0,95,96,97,98,100}
```

Note: `array_remove()` removes ALL occurences of the matching values

```sql
select array_remove(ARRAY[1,2,3,2,5], 2) as removed_2s;
```

```sql
 removed_2s
------------
 {1,3,5}
```

## Replace Elements in an Array

`array_replace()` replaces all elements that matches the second parameter with the third parameter.

```sql
select array_replace(ARRAY[1,2,3,2,5], 2, 10) as two_becomes_ten;
```

```sql
 two_becomes_ten
-----------------
 {1,10,3,10,5}
```

## Fill an Array

`array_fill()` takes three parametes. `array_fill()` returns an array pre-filled by the value of the first parameter. The second parameter defines how many elements to initialize with the given value. The optional third parameter defines the starting position of the array (defaults to 1).

```sql
insert into player_scores (player_number, round_scores) values
	(10003, array_fill(95,array[5]));
```

_In other words, update the_ `_player_scores_` _table with a new record for player\_number 10003. All 5 of her scores will be 95._

```sql
insert into player_scores (player_number, round_scores) values
	(10004, array_fill(90,array[5],array[3]));
```

_Similarly, you update the_ `_player_scores_` _table with a new record for player\_number 10004. However, his 5 scores of 90, will begin in position 3 in the array._

```sql
 player_number |      round_scores      
---------------+------------------------
         10003 | {95,95,95,95,95}
         10004 | [3:7]={90,90,90,90,90}
```

To shows that the scores array for player 10004 started with element position 3, simply query:

```sql
select
  round_scores[1],
  round_scores[2],
  round_scores[3]
from player_scores
where player_number in (10003, 10004);
```

```sql
 player_number | round_scores | round_scores | round_scores
---------------+--------------+--------------+--------------
         10003 |           95 |           95 |           95
         10004 |              |              |           90
```

How to Modify Arrays

# How to Insert Data Into an Array in PostgreSQL
There are two accepted syntaxes for inserting data to an array column. The first uses `ARRAY [value1, value2, etc]`:

```sql
insert into contacts (first_name, last_name, phone_numbers)
values ('John', 'Doe', ARRAY ['999-876-5432','999-123-4567']);

insert into player_scores (player_number, round_scores)
values (10001, ARRAY [95, 92, 96, 97, 98] );

-- multi-dimension arrays must have same array lengths for the inner dimensions
insert into student_scores (student_number, test_scores)
values (20001, ARRAY [[1, 95], [2, 94], [3, 98]]);
```

The second one uses single quotes and curly braces.

```sql
insert into contacts (first_name, last_name, phone_numbers)
values ('Bob', 'Parr', '{"555-INC-RDBL"}');

insert into player_scores (player_number, round_scores)
values (10002, '{91, 92, 93, 95, 99}' );

insert into student_scores (student_number, test_scores)
values (20002, '{{1, 96}, {2, 93}, {4, 97}}');
```

Note above that Bob Parr's phone number is now enclosed in double quotations, since we are using the single quotes to wrap the array. Also note that even if there is only one phone number, it still has to be inserted in array form.

How to Insert Data Into an Array

# How to Create an Array in PostgreSQL
An array is a single data object that holds multiple values.

In PostgreSQL, you can create an array for any built-in or user-defined data type. However, an array can only contain one data type. This means you can have an array of strings, an array of integers, and the like, but you cannot have an array that has both integer and string types.

To create a column of an array type, the `[]` symbol is used. The following examples illustrate this:

```sql
create table contacts (
	first_name varchar,
	last_name varchar,
	phone_numbers varchar[]
);

create table player_scores (
	player_number integer,
	round_scores integer[]
);
```

PostgreSQL also allows multi-dimensional arrays by using multiple pairs of square brackets in the column definition. One requirement here, as you will see later, is that the inner dimensions must have the same array lengths. Here we create a two-dimensional array for the student scores:

```sql
create table student_scores (
	student_number integer,
	test_scores decimal[][]
);
```

To conform to the SQL standard, PostgreSQL also accepts the `ARRAY` keyword for declaring one-dimensional arrays. Shown below is an alternate way to create the contacts and player\_scores tables:

```sql
create table contacts (
	first_name varchar,
	last_name varchar,
	phone_numbers varchar array
);

create table player_scores (
	player_number integer,
	round_scores integer array[10]
);
```

Note the `array[10]` in player\_scores above. PostgreSQL allows you to specify an array size limit, whether you use the `datatype[]` or `datatype array[]` pattern. However, this is just to conform to the SQL standard. As of PostgreSQL 12.2, this is quietly ignored and is not enforced.

Arrays are typically used when a field can have multiple values for an entity, and the values are on their own "complete" and do not merit that they be put to another table for a one-to-many relationship.

How to Create an Array

# How to Convert the Case of a String in PostgreSQL
The most basic case conversion functions are `lower()` and `upper()`. Usage is pretty straightforward:

```sql
select lower('Turn this into lOweRCAse');
```

```sql
 lower           
--------------------------
 turn this into lowercase
```

```sql
select upper('capiTalize THis');
```

```sql
 upper      
-----------------
 CAPITALIZE THIS
```

Another useful case conversion function is `initcap()`, which capitalizes the first character of each word and lowers the case of everything else. This is very useful when printing proper nouns and titles and making names stored in uppercase more pleasing to read:

```sql
select
  first_name,
  last_name,
  initcap(concat(first_name, ' ', last_name)) as name
from customer
limit 5;
```

```sql
 first_name | last_name |       name       
------------+-----------+------------------
 MARY       | SMITH     | Mary Smith
 PATRICIA   | JOHNSON   | Patricia Johnson
 LINDA      | WILLIAMS  | Linda Williams
 BARBARA    | JONES     | Barbara Jones
 ELIZABETH  | BROWN     | Elizabeth Brown
```

How to Convert the Case of a String

# How to Concatenate Strings in PostgreSQL
PostgreSQL offers two ways to concatenate strings. The first uses the `||` operator:

```sql
select 'Join these ' || 'strings with a number ' || 23;
```

```sql
 result               
-------------------------------------
 Join these strings with a number 23
```

You can see from above that PostgreSQL took care of transforming the number to a string to attach it to the rest. Note that you also need to consciously add spaces to make the string readable. For example, in the **customer** table of the Sakila database, to join the first and last names of the customers you have to add a single space in the concatenation:

```sql
select first_name||' '||last_name as customer_name from customer limit 5;
```

```sql
 customer_name   
------------------
 MARY SMITH
 PATRICIA JOHNSON
 LINDA WILLIAMS
 BARBARA JONES
 ELIZABETH BROWN
```

One disadvantage of using the `||` operator is a null value in any of the columns being joined together will result in a null value.

```sql
select 'Null with ||' || 'will make ' || 'everything disappear' || null;
```

```sql
 result
----------
```

Using `concat()` will transform the nulls into empty strings when concatenating:

```sql
select concat('Concat() handles', null, ' nulls better', null);
```

```sql
 concat            
-------------------------------
 Concat() handles nulls better
```

`concat()`accepts multiple parameters, separated by commas.

How to Concatenate Strings

# How to Compare Arrays in PostgreSQL
The **equality operators** (`=`, `<>`) do an exact element-by-element comparison.

```sql
select
array[1,2,3] = array[1,2,4] as compare1, -- arrays are equal
array[1,2,3] <> array[1,2,4] as compare2; -- arrays are not equal
```

```sql
 compare1 | compare2
----------+----------
 f | t
```

The **ordering operators** (`>`, `<`, `>=`, `<=`) also compare each element in an array in order. Results are based on the first different pair of elements, not the sizes of the arrays.

```sql
select
array[1,2,5] >= array[1,2,4] as compare1,
array[1,2,5] <= array[1,2,4,5] as compare2;
```

```sql
 compare1 | compare2
----------+----------
 t | f
```

Then there are the **containment operators** (`@>`, `<@`). They are casually called "bird operators", well, because `@>` looks like a bird. An array is said to be contained in another array if each of its unique elements is also present in the other array.

```sql
-- This reads as array['a', 'b', 'c'] contains array['a', 'b', 'b', 'a']
select array['a', 'b', 'c'] @> array['a', 'b', 'b', 'a'] as contains;
```

```sql
 contains
----------
 t
```

```sql
-- this reads as array[1, 1, 4] is contained by array[4, 3, 2, 1]
select array[1, 1, 4] <@ array[4, 3, 2, 1] as is_contained_by;
```

```sql
 is_contained_by
-----------------
 t
```

Lastly, there is the **overlap operator** (`&&`). Arrays that have elements in common are called overlapping arrays. To check if two arrays overlap, use the `&&` operator:

```sql
select
array[1, 2] && array[2, 3] as overlap1,
array[1, 2] && array[3, 4] as overlap2;
```

```sql
 overlap1 | overlap2
----------+----------
 t | f
```

PostgresSQL

Ready for a modern SQL editor?