### Example of Starting a Transaction

Source: https://docs.aws.amazon.com/redshift/latest/dg/c_serial_isolation-serializable-isolation-troubleshooting.md

This snippet shows a session beginning a transaction. The timing of this transaction start relative to other operations is crucial for avoiding isolation errors.

```sql
session2 = # BEGIN;
```

--------------------------------

### Example SHOW TEMPLATE Output

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_TEMPLATE.md

This example shows the output of the SHOW TEMPLATE command for a template named 'test_template'. The output is a CREATE TEMPLATE statement.

```sql
CREATE TEMPLATE test_template FOR COPY AS NOLOAD DELIMITER ',' ENCODING UTF16 ENCRYPTED;
```

--------------------------------

### Create User Statement Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_SYSTEM.md

Example of a CREATE USER statement after setting a default identity namespace.

```sql
CREATE USER bob password 'md50c983d1a624280812631c5389e60d48c';
```

--------------------------------

### Example SHOW MODEL Output

Source: https://docs.aws.amazon.com/redshift/latest/dg/tutorial_k-means_clustering.md

This is an example output of the SHOW MODEL operation, indicating a 'READY' model state and providing training details.

```text
+--------------------------+------------------------------------------------------------------------------------------------------+
|        Model Name        |                                          news_data_clusters                                          |
+--------------------------+------------------------------------------------------------------------------------------------------+
|       Schema Name        |                                                public                                                |
|          Owner           |                                               awsuser                                                |
|      Creation Time       |                                       Fri, 17.06.2022 16:32:19                                       |
|       Model State        |                                                READY                                                 |
|        train:msd         |                                             2973.822754                                              |
|      train:progress      |                                              100.000000                                              |
|     train:throughput     |                                            237114.875000                                             |
|      Estimated Cost      |                                               0.004983                                               |
|                          |                                                                                                      |
|      TRAINING DATA:      |                                                                                                      |
|          Query           | SELECT AVGTONE, EVENTCODE, NUMARTICLES, ACTOR1GEO_LAT, ACTOR1GEO_LONG, ACTOR2GEO_LAT, ACTOR2GEO_LONG |
|                          |                                           FROM GDELT_DATA                                            |
|                          |                                                                                                      |
|       PARAMETERS:        |                                                                                                      |
|        Model Type        |                                                kmeans                                                |
|    Training Job Name     |                                       redshiftml-20220617163219978978-kmeans                                |
|      Function Name       |                                       news_monitoring_cluster                                        |
|   Function Parameters    |       avgtone eventcode numarticles actor1geo_lat actor1geo_long actor2geo_lat actor2geo_long        |
| Function Parameter Types |                             float8 int8 int8 float8 float8 float8 float8                             |
|         IAM Role         |                                         default-aws-iam-role                                         |
|        S3 Bucket         |                                            amzn-s3-demo-bucket                                       |
|       Max Runtime        |                                                 5400                                                 |
|                          |                                                                                                      |
|     HYPERPARAMETERS:     |                                                                                                      |
|       feature_dim        |                                                  7                                                   |
|            k             |                                                  7                                                   |
+--------------------------+------------------------------------------------------------------------------------------------------+
```

--------------------------------

### Create Example Table

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_ABORT.md

This SQL command creates a table named MOVIE_GROSS to be used in the ABORT transaction example.

```sql
create table movie_gross( name varchar(30), gross bigint );
```

--------------------------------

### Example Query Plan Output

Source: https://docs.aws.amazon.com/redshift/latest/dg/c_data_redistribution.md

This is an example of the output from an EXPLAIN command in Amazon Redshift, showing a merge join between 'listing' and 'sales' tables.

```text
->  XN Merge Join DS_DIST_NONE  (cost=0.00..6286.47 rows=172456 width=30)
      Merge Cond: ("outer".listid = "inner".listid)
      ->  XN Seq Scan on listing  (cost=0.00..1924.97 rows=192497 width=14)
      ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=24)
```

--------------------------------

### Display All Parameters

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW.md

This example shows how to display a list of all available parameters and their current values. The output is formatted as a table with 'name' and 'setting' columns.

```sql
show all;
name        |   setting
--------------------+--------------
datestyle          | ISO, MDY
extra_float_digits | 0
query_group        | unset
search_path        | $user,public
statement_timeout  | 0
```

--------------------------------

### Query SVV_EXTERNAL_COLUMNS

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_EXTERNAL_TABLE_examples.md

Query the SVV_EXTERNAL_COLUMNS system view to get information about columns in external tables. This example filters for columns within schemas starting with 'spectrum' and a table named 'sales'.

```sql
select * from svv_external_columns where schemaname like 'spectrum%' and tablename ='sales';
```

--------------------------------

### Example WLM Query State After Reset

Source: https://docs.aws.amazon.com/redshift/latest/dg/tutorial-configuring-workload-management.md

Example output from WLM_QUERY_STATE_VW after resetting the slot count, showing updated query slot usage.

```text
query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 2260 |     2 |          1 | 2014-09-25 00:12:11 | Executing | 0          | 4042618
 2262 |     3 |          1 | 2014-09-25 00:12:15 | Executing | 0          | 680
```

--------------------------------

### SHA2 Function Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/SHA2.md

Example of using the SHA2 function to get the 256-bit hash of 'Amazon Redshift'.

```sql
select sha2('Amazon Redshift', 256);
```

--------------------------------

### Create and Populate Sample Bookings Table

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause-pivot-unpivot-examples.md

Sets up the 'bookings' table and inserts sample data for further examples.

```sql
CREATE TABLE bookings (
    booking_id int,
    hotel_code char(8),
    booking_date date,
    price decimal(12, 2)
);

INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12);
INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54);

INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00);
INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00);
INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00);

INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00);
```

--------------------------------

### PL/pgSQL Example Variable Declarations

Source: https://docs.aws.amazon.com/redshift/latest/dg/c_PLpgSQL-structure.md

Provides concrete examples of variable declarations in PL/pgSQL, showcasing different data types and initialization methods.

```plpgsql
customerID integer;
numberofitems numeric(6);
link varchar;
onerow RECORD;
```

--------------------------------

### Create and Populate Sample Part Table

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause-pivot-unpivot-examples.md

Sets up the 'part' table and inserts sample data for PIVOT and UNPIVOT examples.

```sql
CREATE TABLE part (
    partname varchar,
    manufacturer varchar,
    quality int,
    price decimal(12, 2)
);

INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00);
INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00);
INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00);

INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50);
INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75);
INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90);

INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50);
INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20);
INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80);
```

--------------------------------

### Example WLM Queue State After Reset

Source: https://docs.aws.amazon.com/redshift/latest/dg/tutorial-configuring-workload-management.md

Example output from WLM_QUEUE_STATE_VW after resetting the slot count, showing updated queue usage.

```text
query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        2
    2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         1 |        2
    3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |       14
```

--------------------------------

### Create and show a table with a primary key

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_TABLE.md

This example demonstrates creating a table 'foo' with a primary key on column 'a', and then using SHOW TABLE to view its definition. The output includes the primary key constraint.

```sql
create table foo(a int PRIMARY KEY, b int);
```

```sql
show table foo;
```

```sql
CREATE TABLE public.foo (
a integer NOT NULL ENCODE az64,
b integer ENCODE az64, PRIMARY KEY (a) 
) 
DISTSTYLE AUTO;
```

--------------------------------

### Get Start Point of a Linestring

Source: https://docs.aws.amazon.com/redshift/latest/dg/ST_StartPoint-function.md

Use this snippet to retrieve the starting point of a LINESTRING geometry. The input geometry must be a LINESTRING and have a valid SRID.

```sql
SELECT ST_AsEWKT(ST_StartPoint(ST_GeomFromText('LINESTRING(0 0,10 0,10 10,5 5,0 5)',4326)));
```

--------------------------------

### Show Template and Create Template Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_TEMPLATE.md

This snippet demonstrates showing a template and its corresponding CREATE OR REPLACE TEMPLATE statement. It includes schema and database qualifiers.

```sql
SHOW TEMPLATE test_template;

CREATE OR REPLACE TEMPLATE dev.public.test_template FOR COPY AS ENCRYPTED NOLOAD ENCODING UTF16 DELIMITER ',';
```

--------------------------------

### Get Parent H3 Cell ID (VARCHAR Input)

Source: https://docs.aws.amazon.com/redshift/latest/dg/H3_ToParent-function.md

This example demonstrates how to use H3_ToParent with a VARCHAR input for the H3 index and an INTEGER for the resolution to get the parent cell ID at resolution 0.

```sql
SELECT H3_ToParent('85283473fffffff', 0);
```

```text
 h3_toparent
--------------------
 577199624117288959
```

--------------------------------

### Create Template with Options

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_TEMPLATE.md

This example shows the creation of a template named 'demo_template' in the 'demo_schema' with various options like date formats, null handling, and encryption.

```sql
CREATE OR REPLACE TEMPLATE demo_schema.demo_template FOR COPY AS
ACCEPTANYDATE ACCEPTINVCHARS DATEFORMAT 'DD-MM-YYYY' EXPLICIT_IDS ROUNDEC
TIMEFORMAT  AS 'DD.MM.YYYY HH:MI:SS' TRUNCATECOLUMNS NULL  AS 'null_string';
```

--------------------------------

### Filter Event Names Starting with 'A'

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CHR.md

Retrieves distinct event names that start with the character 'A' (ASCII 65) using the CHR function. This example assumes the TICKIT sample database and limits results to 5.

```sql
SELECT DISTINCT eventname FROM event
WHERE SUBSTRING(eventname, 1, 1)=CHR(65) LIMIT 5;

+-----------------------+
|       eventname       |
+-----------------------+
| A Catered Affair      |
| As You Like It        |
| A Man For All Seasons |
| Alan Jackson          |
| Armando Manzanero     |
+-----------------------+
```

--------------------------------

### Invalid BETWEEN Clause Examples

Source: https://docs.aws.amazon.com/redshift/latest/dg/c_Window_functions.md

Illustrates invalid frame specifications where the starting boundary is greater than the ending boundary.

```sql
between 5 following and 5 preceding
```

```sql
between current row and 2 preceding
```

```sql
between 3 following and current row
```

--------------------------------

### Create Dependent Views for Demonstration

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_DROP_VIEW.md

These examples demonstrate creating a view and then a second view dependent on the first, setting up a scenario for using CASCADE.

```sql
create view eventview as
select dateid, eventname, catid
from event where catid = 1;
```

```sql
create view myeventview as
select eventname, catid
from eventview where eventname <> ' ';
```

--------------------------------

### Get Last Day of Current Month

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.md

Returns the date of the last day in the current month. Requires no special setup.

```sql
select last_day(sysdate);

  last_day
------------
 2014-01-31
```

--------------------------------

### Create and Query Tables with Different Distribution Styles

Source: https://docs.aws.amazon.com/redshift/latest/dg/viewing-distribution-styles.md

This example demonstrates creating tables with KEY, EVEN, ALL, and AUTO distribution styles, then querying SVV_TABLE_INFO to view their distribution styles. Ensure tables are created before querying.

```SQL
create table public.dist_key (col1 int)
diststyle key distkey (col1);

insert into public.dist_key values (1);

create table public.dist_even (col1 int)
diststyle even;

insert into public.dist_even values (1);

create table public.dist_all (col1 int)
diststyle all;

insert into public.dist_all values (1);

create table public.dist_auto (col1 int);

insert into public.dist_auto values (1);

select "schema", "table", diststyle from SVV_TABLE_INFO
where "table" like 'dist%';

-- Expected Output:
--      schema   |    table        | diststyle
--   ------------+-----------------+------------
--    public     | dist_key        | KEY(col1)
--    public     | dist_even       | EVEN
--    public     | dist_all        | ALL
--    public     | dist_auto       | AUTO(ALL)
```

--------------------------------

### AddBBox Function Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/AddBBox-function.md

This SQL example demonstrates how to use the AddBBox function to get a copy of a polygon geometry that supports encoding with a bounding box. The ST_GeomFromText function is used to create the initial polygon, and ST_AsText is used to display the result.

```sql
SELECT ST_AsText(AddBBox(ST_GeomFromText('POLYGON((0 0,1 0,0 1,0 0))')));
```

```text
 st_astext
----------
 POLYGON((0 0,1 0,0 1,0 0))
```

--------------------------------

### Create a Sample View

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_VIEW.md

This snippet shows how to create a sample view named LA_Venues_v. This is a prerequisite for demonstrating the SHOW VIEW command.

```sql
create view LA_Venues_v as select * from venue where venuecity='Los Angeles';
```

--------------------------------

### Example Result of WLM Query State View

Source: https://docs.aws.amazon.com/redshift/latest/dg/tutorial-configuring-workload-management.md

This is an example output showing the columns and data format you can expect when querying the WLM_QUERY_STATE_VW. It includes query ID, queue, slot count, start time, state, queue time, and execution time.

```text
query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 1249 |     1 |          1 | 2014-09-24 22:19:16 | Executing | 0          | 516
```

--------------------------------

### Example File URL for CREATE LIBRARY

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_LIBRARY.md

Specifies a URL to download a library .zip file from a public website. The URL can include up to three redirects.

```sql
'https://www.example.com/pylib.zip'
```

--------------------------------

### Get Current Database User

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CURRENT_USER.md

This example demonstrates how to retrieve the name of the current database user. The output will vary based on the logged-in user.

```sql
select current_user;

current_user
--------------
dwuser
(1 row)
```

--------------------------------

### Create Library from Website URL

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_LIBRARY.md

Installs a UDF library from a zip file hosted on a website. Requires specifying the library name, language, and the URL of the zip file.

```sql
create library f_urlparse
language plpythonu
from 'https://example.com/packages/urlparse3-1.0.3.zip';
```

--------------------------------

### Get Dimension of a 3DZ Linestring

Source: https://docs.aws.amazon.com/redshift/latest/dg/ST_NDims-function.md

This example shows how to use ST_NDims to find the dimension of a 3DZ linestring. Ensure the input is a valid GEOMETRY.

```sql
SELECT ST_NDims(ST_GeomFromText('LINESTRING Z(0 0 3,1 1 3,2 2 3,0 0 3)'));
```

```text
st_ndims
-------------
 3
```

--------------------------------

### Show Template and Create Template with Options Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_TEMPLATE.md

This snippet shows how to display a template definition and its corresponding CREATE OR REPLACE TEMPLATE statement, including specific schema and options.

```sql
SHOW TEMPLATE demo_schema.demo_template;

CREATE OR REPLACE TEMPLATE dev.demo_schema.demo_template FOR COPY AS TRUNCATECOLUMNS NULL 'null_string' EXPLICIT_IDS TIMEFORMAT 'DD.MM.YYYY HH:MI:SS' ACCEPTANYDATE ROUNDEC ACCEPTINVCHARS DATEFORMAT 'DD-MM-YYYY';
```

--------------------------------

### Get Boundary of Polygon as Multilinestring

Source: https://docs.aws.amazon.com/redshift/latest/dg/ST_Boundary-function.md

This example demonstrates how to use the ST_Boundary function to retrieve the boundary of a polygon with an interior ring. The result is returned as a MULTILINESTRING.

```sql
SELECT ST_AsEWKT(ST_Boundary(ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(1 1,1 2,2 1,1 1))')));

```

--------------------------------

### Example COPY Command with Delimiter and IAM Role

Source: https://docs.aws.amazon.com/redshift/latest/dg/t_loading-tables-from-s3.md

This example demonstrates loading a VENUE table from pipe-delimited data files in an S3 bucket using a specified prefix and IAM role authentication.

```sql
COPY venue FROM 's3://amzn-s3-demo-bucket/venue'
IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
DELIMITER '|';
```

--------------------------------

### List Query Priority for Completed Queries

Source: https://docs.aws.amazon.com/redshift/latest/dg/query-priority.md

Query the `stl_wlm_query` system table to retrieve the `query_priority` for completed queries. This example selects the query ID, service class, start time, and priority, ordering by start time in descending order and limiting to the top 10 results.

```sql
select query, service_class as svclass, service_class_start_time as starttime, query_priority 
from stl_wlm_query order by 3 desc limit 10;
```

```text
  query  | svclass |         starttime          |    query_priority
---------+---------+----------------------------+----------------------
 2723254 |     100 | 2019-06-24 18:14:50.780094 | Normal
 2723251 |     102 | 2019-06-24 18:14:50.749961 | Highest  
 2723246 |     102 | 2019-06-24 18:14:50.725275 | Highest
 2723244 |     103 | 2019-06-24 18:14:50.719241 | High
 2723243 |     101 | 2019-06-24 18:14:50.699325 | Low
 2723242 |     102 | 2019-06-24 18:14:50.692573 | Highest
 2723239 |     101 | 2019-06-24 18:14:50.668535 | Low
 2723237 |     102 | 2019-06-24 18:14:50.661918 | Highest
 2723236 |     102 | 2019-06-24 18:14:50.643636 | Highest
```

--------------------------------

### Get Current Date

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CURRENT_DATE_function.md

This example demonstrates how to retrieve the current date using the CURRENT_DATE function. The output will reflect the date in the AWS Region where the function is executed.

```sql
select current_date;

   date
------------
2008-10-01
```

--------------------------------

### CREATE MODEL with Preprocessors

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_MODEL.md

Example demonstrating the use of the PREPROCESSORS parameter to apply specific transformations to column sets during model training.

```sql
CREATE MODEL customer_churn
FROM customer_data
TARGET 'Churn'
FUNCTION predict_churn
IAM_ROLE { default | 'arn:aws:iam::<account-id>:role/<role-name>' }
PROBLEM_TYPE BINARY_CLASSIFICATION
OBJECTIVE 'F1'
PREPROCESSORS '[
...
  {"ColumnSet": [
      "t1",
      "t2"
    ],
    "Transformers": [
      "OneHotEncoder",
      "Imputer"
    ]
  },
  {"ColumnSet": [
      "t3"
    ],
    "Transformers": [

```

--------------------------------

### Creating Table with Mixed Collations

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_DATABASE.md

This example demonstrates the creation of a table with columns having different collations (case-insensitive and case-sensitive), which is a setup that leads to errors in subsequent operations.

```sql
CREATE TABLE test
  (ci_col varchar(10) COLLATE case_insensitive, 
   cs_col varchar(10) COLLATE case_sensitive, 
   cint int,
   cbigint bigint);
```

--------------------------------

### Show All Configurable Server Parameters

Source: https://docs.aws.amazon.com/redshift/latest/dg/cm_chap_ConfigurationRef.md

Use the SHOW ALL command to view all server configuration parameters that can be modified using the SET command. This displays the current settings for various parameters.

```sql
SHOW ALL;
```

--------------------------------

### Get Column Metadata for a View in Table Format

Source: https://docs.aws.amazon.com/redshift/latest/dg/PG_GET_COLS.md

This example shows how to retrieve column metadata for a view and display it in a table format using aliased column names.

```sql
select * from pg_get_cols('sales_vw') 
cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int);
```

--------------------------------

### Compare EXPLAIN and STL_PLAN_INFO for a simple SELECT

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_STL_PLAN_INFO.md

This snippet shows how to use the EXPLAIN command to get a query plan and then query the STL_PLAN_INFO view for the same query to compare the results. Use this to understand the detailed plan of a simple query.

```sql
explain select * from category;
QUERY PLAN
-------------------------------------------------------------
XN Seq Scan on category (cost=0.00..0.11 rows=11 width=49)
(1 row)

select * from category;
catid | catgroup | catname | catdesc
-------
1 | Sports | MLB | Major League Baseball
3 | Sports | NFL | National Football League
5 | Sports | MLS | Major League Soccer
...

select * from stl_plan_info where query=256;

query | nodeid | segment | step | locus | plannode | startupcost | totalcost | rows | bytes
-------+--------+---------+------+-------+----------+-------------+-----------+------+-------
256 | 1 | 0 | 1 | 0 | 104 | 0 | 0.11 | 11 | 539
256 | 1 | 0 | 0 | 0 | 104 | 0 | 0.11 | 11 | 539
(2 rows)
```

--------------------------------

### Get Next Tuesday from Timestamp

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_NEXT_DAY.md

Retrieves the next Tuesday's date from a timestamp column. This example shows how to use NEXT_DAY with a timestamp and limits the output to one row.

```sql
select listtime, next_day(listtime, 'Tue') from listing limit 1;
```

--------------------------------

### Creating a Table with Schema and Database Qualification

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_AS.md

This example shows how to specify the schema and database names when creating a table using CREATE TABLE AS. The database and schema must exist, and the creator must have access.

```sql
create table tickit.public.test (c1) as select * from oldtable;
```

--------------------------------

### Get Centroid of a Linestring

Source: https://docs.aws.amazon.com/redshift/latest/dg/ST_Centroid-function.md

This example demonstrates how to use the ST_Centroid function to find the central point of a linestring geometry. The input linestring is defined using ST_GeomFromText with SRID 4326.

```sql
SELECT ST_AsEWKT(ST_Centroid(ST_GeomFromText('LINESTRING(110 40, 2 3, -10 80, -7 9, -22 -33)', 4326)))
```

--------------------------------

### Prepare Sample Merge Data Source

Source: https://docs.aws.amazon.com/redshift/latest/dg/merge-examples.md

Creates a sample table 'SALES_UPDATE' by copying the 'SALES' table, then applies updates to some rows and inserts new rows to simulate a data merge scenario.

```sql
create table tickit.sales_update as
select * from tickit.sales;

update tickit.sales_update
set qtysold = qtysold*2,
pricepaid = pricepaid*0.8,
commission = commission*1.1
where saletime > '2008-11-30'
and mod(sellerid, 5) = 0;

insert into tickit.sales_update
select (salesid + 172456) as salesid, listid, sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, getdate() as saletime
from tickit.sales_update
where saletime > '2008-11-30'
and mod(sellerid, 4) = 0;
```

--------------------------------

### Get Byte Size of a SUPER Expression

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_json_size.md

Use this example to return the length in bytes of a SUPER value when serialized to a string. Note that multi-byte characters contribute to the total byte count.

```sql
SELECT JSON_SIZE(JSON_PARSE('[10001,10002,"⬤"]'));

+-----------+
| json_size |
+-----------+
|        19 |
+-----------+
```

--------------------------------

### Create Databases and Users

Source: https://docs.aws.amazon.com/redshift/latest/dg/cross-database_example.md

Sets up two databases (db1, db2) and two users (user1, user2) required for cross-database query examples.

```sql
--As user1 on db1
CREATE DATABASE db1;

CREATE DATABASE db2;

CREATE USER user1 PASSWORD 'Redshift01';

CREATE USER user2 PASSWORD 'Redshift01';
```

--------------------------------

### Get Multiple Bits from a Binary Value

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_GETBIT.md

This example demonstrates how to retrieve multiple bits from a binary value by calling GETBIT for each desired index. The binary value '4d' is represented as 01001101.

```sql
SELECT GETBIT(FROM_HEX('4d'), 7), GETBIT(FROM_HEX('4d'), 6),
  GETBIT(FROM_HEX('4d'), 5), GETBIT(FROM_HEX('4d'), 4),
  GETBIT(FROM_HEX('4d'), 3), GETBIT(FROM_HEX('4d'), 2),
  GETBIT(FROM_HEX('4d'), 1), GETBIT(FROM_HEX('4d'), 0);
               
+
| getbit | getbit | getbit | getbit | getbit | getbit | getbit | getbit |
+
|      0 |      1 |      0 |      0 |      1 |      1 |      0 |      1 |
+

```

--------------------------------

### Create Table, Insert VARBYTE to Hex, and Select

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_TO_HEX.md

This example shows how to create a table, insert the hexadecimal representation of a VARBYTE value into it, and then select the data. It illustrates a common pattern for storing and retrieving transformed data.

```sql
CREATE TABLE t (vc VARCHAR);
INSERT INTO t SELECT TO_HEX('abc'::VARBYTE);
SELECT vc FROM t;
 
+
|   vc   |
+
| 616263 |
+

```

--------------------------------

### Multi-level Partitioning Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/iceberg-writes-sql-syntax.md

Demonstrates how to define multi-level partitioning for an Iceberg table using different transform functions.

```sql
CREATE TABLE ...
USING ICEBERG
LOCATION ...
PARTITIONED BY (bucket(16, id), year(ship_date));
```

--------------------------------

### Getting Array Length with GET_ARRAY_LENGTH

Source: https://docs.aws.amazon.com/redshift/latest/dg/operators-functions.md

Shows how to use GET_ARRAY_LENGTH to find the number of elements in a SUPER array. This example finds rows where the array length matches the maximum array length in the table.

```sql
SELECT c_name
FROM customer_orders_lineitem
WHERE GET_ARRAY_LENGTH(c_orders) = (
    SELECT MAX(GET_ARRAY_LENGTH(c_orders))
    FROM customer_orders_lineitem
);
```

--------------------------------

### Create User Group and Add Users

Source: https://docs.aws.amazon.com/redshift/latest/dg/cm-c-executing-queries.md

Example commands for creating a user group and adding users to it, which is used for assigning queries based on user groups.

```sql
create group admin_group with user admin246, admin135, sec555;
create user vp1234 in group ad_hoc_group password 'vpPass1234';
alter group admin_group add user analyst44, analyst45, analyst46;
```

--------------------------------

### Get Endpoint of a Linestring

Source: https://docs.aws.amazon.com/redshift/latest/dg/ST_EndPoint-function.md

This example demonstrates how to use ST_EndPoint to retrieve the last point of a linestring geometry. It converts a string representation to a GEOMETRY object and then extracts the endpoint, returning it in EWKT format.

```SQL
SELECT ST_AsEWKT(ST_EndPoint(ST_GeomFromText('LINESTRING(0 0,10 0,10 10,5 5,0 5)',4326)));
```

```text
st_asewkt
-------------
 SRID=4326;POINT(0 5)
```

--------------------------------

### Nested Stored Procedures with TRUNCATE

Source: https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-transaction-management.md

This example illustrates transaction management in nested stored procedures. A TRUNCATE in an inner procedure commits transactions from both outer and inner procedures and starts a new one.

```SQL
CREATE OR REPLACE PROCEDURE sp_inner(c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO inner_table values (c);
  TRUNCATE outer_table;
  INSERT INTO inner_table values (d);
END;
$$;

CREATE OR REPLACE PROCEDURE sp_outer(a int, b int, c int, d int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO outer_table values (a);
  Call sp_inner(c, d);
  INSERT INTO outer_table values (b);
END;
$$;

Call sp_outer(1, 2, 3, 4);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;
```

--------------------------------

### Compare EXPLAIN and STL_PLAN_INFO for DISTINCT query

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_STL_PLAN_INFO.md

This example compares the query plan for a DISTINCT query using EXPLAIN and STL_PLAN_INFO. It helps in analyzing plans involving sorting and unique operations.

```sql
select distinct eventname from event order by 1;

eventname
------------------------------------------------------------------------
.38 Special
3 Doors Down
70s Soul Jam
A Bronx Tale
...

explain select distinct eventname from event order by 1;

QUERY PLAN
-------------------------------------------------------------------------------------
XN Merge (cost=1000000000136.38..1000000000137.82 rows=576 width=17)
Merge Key: eventname
-> XN Network (cost=1000000000136.38..1000000000137.82 rows=576
width=17)
Send to leader
-> XN Sort (cost=1000000000136.38..1000000000137.82 rows=576
width=17)
Sort Key: eventname
-> XN Unique (cost=0.00..109.98 rows=576 width=17)
-> XN Seq Scan on event (cost=0.00..87.98 rows=8798
width=17)
(8 rows)

select * from stl_plan_info where query=240 order by nodeid desc;

query | nodeid | segment | step | locus | plannode | startupcost | 
totalcost | rows | bytes
-------+--------+---------+------+-------+----------+------------------+------------------+------+--------
240 | 5 | 0 | 0 | 0 | 104 | 0 | 
87.98 | 8798 | 149566 
240 | 5 | 0 | 1 | 0 | 104 | 0 | 
87.98 | 8798 | 149566
240 | 4 | 0 | 2 | 0 | 117 | 0 | 
109.975 | 576 | 9792
240 | 4 | 0 | 3 | 0 | 117 | 0 | 
109.975 | 576 | 9792
240 | 4 | 1 | 0 | 0 | 117 | 0 | 
109.975 | 576 | 9792
240 | 4 | 1 | 1 | 0 | 117 | 0 | 
109.975 | 576 | 9792
240 | 3 | 1 | 2 | 0 | 114 | 1000000000136.38 | 
1000000000137.82 | 576 | 9792
240 | 3 | 2 | 0 | 0 | 114 | 1000000000136.38 | 
1000000000137.82 | 576 | 9792
240 | 2 | 2 | 1 | 0 | 123 | 1000000000136.38 | 
1000000000137.82 | 576 | 9792
240 | 1 | 3 | 0 | 0 | 122 | 1000000000136.38 | 
1000000000137.82 | 576 | 9792
(10 rows)
```

--------------------------------

### CREATE LIBRARY Syntax

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_LIBRARY.md

This is the basic syntax for the CREATE LIBRARY command. It specifies the library name, language, and the source of the library (URL or S3 bucket) along with authorization details.

```sql
CREATE [ OR REPLACE ] LIBRARY library_name LANGUAGE plpythonu
FROM
{
  'https://file_url'
| 's3://bucketname/file_name'
authorization
  [ REGION [AS] 'aws_region']
  IAM_ROLE { default | ‘arn:aws:iam::{{<AWS account-id>}}:role/{{<role-name>}}’ }
}
```

--------------------------------

### Get Byte Length of VARBYTE String

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_OCTET_LENGTH.md

Use OCTET_LENGTH with CAST to VARBYTE to determine the byte length of a string when treated as a variable-byte type. This example shows the direct byte count for the given string.

```sql
SELECT OCTET_LENGTH(CAST('français' AS VARBYTE));

+--------------+
| octet_length |
+--------------+
|            9 |
+--------------+

```

--------------------------------

### Get Minimum Z Coordinate of a 4D Linestring

Source: https://docs.aws.amazon.com/redshift/latest/dg/ST_ZMin-function.md

This example demonstrates retrieving the minimum z coordinate from a 4D linestring geometry using the ST_ZMin function. Ensure the input is a valid GEOMETRY type.

```sql
SELECT ST_ZMin(ST_GeomFromEWKT('LINESTRING ZM (0 1 2 3, 4 5 6 7, 8 9 10 11)'));
```

```text
st_zmin
-----------
  2
```

--------------------------------

### Example Manifest File with Multiple Connections

Source: https://docs.aws.amazon.com/redshift/latest/dg/loading-data-from-remote-hosts.md

Illustrates a complete manifest file configuration for establishing four distinct SSH connections to the same host, each executing a different 'cat' command to load data.

```json
{
  "entries": [
       {"endpoint":"ec2-184-72-204-112.compute-1.amazonaws.com", 
          "command": "cat loaddata1.txt", 
          "mandatory":true, 
          "publickey": "ec2publickeyportionoftheec2keypair", 
          "username": "ec2-user"}, 
       {"endpoint":"ec2-184-72-204-112.compute-1.amazonaws.com", 
          "command": "cat loaddata2.txt", 
          "mandatory":true, 
          "publickey": "ec2publickeyportionoftheec2keypair", 
          "username": "ec2-user"},
       {"endpoint":"ec2-184-72-204-112.compute-1.amazonaws.com", 
          "command": "cat loaddata3.txt", 
          "mandatory":true, 
          "publickey": "ec2publickeyportionoftheec2keypair", 
          "username": "ec2-user"},
       {"endpoint":"ec2-184-72-204-112.compute-1.amazonaws.com", 
          "command": "cat loaddata4.txt", 
          "mandatory":true, 
          "publickey": "ec2publickeyportionoftheec2keypair", 
          "username": "ec2-user"}
     ]
}
```

--------------------------------

### Create and Use a Lambda UDF, then Query SYS_LUDF_DETAIL

Source: https://docs.aws.amazon.com/redshift/latest/dg/SYS_LUDF_DETAIL.md

This example demonstrates creating an external Lambda UDF, using it in a query, and then inspecting the SYS_LUDF_DETAIL view as a superuser to retrieve function execution metrics. Ensure you have the necessary IAM role and Lambda function configured.

```sql
SET SESSION AUTHORIZATION regular_user;

CREATE EXTERNAL FUNCTION exfunc_sum(INT,INT) RETURNS INT STABLE
LAMBDA 'lambda_sum'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-Exfunc-Test';

CREATE TABLE t_sum(c1 int, c2 int);
INSERT INTO t_sum VALUES (4,5), (6,7);
SELECT exfunc_sum(c1,c2) FROM t_sum;

-- Switch to super user in order to inspect records in the LUDF SYS view.
SET SESSION AUTHORIZATION super_user;
select * from sys_ludf_detail;
```

--------------------------------

### Get Minimum M Coordinate from 4D Linestring

Source: https://docs.aws.amazon.com/redshift/latest/dg/ST_MMin-function.md

This example shows how to retrieve the minimum 'm' coordinate from a 4D linestring (LINESTRING ZM) using the ST_MMin function. The function handles geometries with both Z and M dimensions.

```sql
SELECT ST_MMin(ST_GeomFromEWKT('LINESTRING ZM (0 1 2 3, 4 5 6 7, 8 9 10 11)'));
```

```text
st_mmin
-----------
  3
```

--------------------------------

### Unsupported Partitioning Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/iceberg-writes-sql-syntax.md

Illustrates an unsupported partitioning configuration where a single column is used in multiple transform functions.

```sql
CREATE TABLE ...
USING ICEBERG
LOCATION ...
PARTITIONED BY (bucket(16, ship_date), year(ship_date));
```

--------------------------------

### Get Parent H3 Cell ID (BIGINT Input)

Source: https://docs.aws.amazon.com/redshift/latest/dg/H3_ToParent-function.md

This example shows how to use H3_ToParent with a BIGINT input for the H3 index and an INTEGER for the resolution to retrieve the parent cell ID at resolution 8.

```sql
SELECT H3_ToParent(646078419604526808, 8);
```

```text
 h3_toparent
--------------------
 614553222213795839
```

--------------------------------

### Create Table and Insert Data for SIGN Function Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SIGN.md

This snippet demonstrates creating a table with DOUBLE PRECISION and NUMERIC types, inserting sample data, and then using the SIGN function to populate a new table. It also queries column metadata to show the resulting data types.

```sql
CREATE TABLE t1(d DOUBLE PRECISION, n NUMERIC(12, 2));
INSERT INTO t1 VALUES (4.25, 4.25), (-4.25, -4.25);
CREATE TABLE t2 AS SELECT SIGN(d) AS d, SIGN(n) AS n FROM t1;
SELECT table_name, column_name, data_type FROM SVV_REDSHIFT_COLUMNS WHERE table_name='t1' OR table_name='t2';
```

--------------------------------

### Get Point at Index 5 from a Linestring

Source: https://docs.aws.amazon.com/redshift/latest/dg/ST_PointN-function.md

This example demonstrates how to use ST_PointN to retrieve the point at index 5 from a LINESTRING. The input linestring is created using ST_GeomFromText, and the output point is returned in EWKT format.

```sql
SELECT ST_AsEWKT(ST_PointN(ST_GeomFromText('LINESTRING(0 0,10 0,10 10,5 5,0 5,0 0)',4326), 5));
```

```text
st_asewkt
-------------
 SRID=4326;POINT(0 5)
```

--------------------------------

### Stored Procedure with TRUNCATE and Implicit Commit

Source: https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-transaction-management.md

This example defines a stored procedure that inserts data, then truncates a table. The TRUNCATE statement causes an implicit commit, and a new transaction is started for subsequent operations within the procedure.

```SQL
CREATE OR REPLACE PROCEDURE sp_truncate_proc(a int, b int) LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test_table_a values (a);
  TRUNCATE test_table_b;
  INSERT INTO test_table_b values (b);
END;
$$;

Call sp_truncate_proc(1,2);

select userid, xid, pid, type, trim(text) as stmt_text 
from svl_statementtext where pid = pg_backend_pid() order by xid , starttime , sequence;
```

--------------------------------

### Get Partition Details for Last Query

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SVL_S3PARTITION.md

Retrieves partition pruning details for the most recently completed query. It aggregates start and end times, calculates duration, and shows partition counts and assignment types per segment.

```sql
SELECT query, segment,
       MIN(starttime) AS starttime,
       MAX(endtime) AS endtime,
       datediff(ms,MIN(starttime),MAX(endtime)) AS dur_ms,
       MAX(total_partitions) AS total_partitions,
       MAX(qualified_partitions) AS qualified_partitions,
       MAX(assignment) as assignment_type
FROM svl_s3partition
WHERE query=pg_last_query_id()
GROUP BY query, segment
```

--------------------------------

### Create Users, Roles, and Grant Permissions

Source: https://docs.aws.amazon.com/redshift/latest/dg/t_rls-example.md

This snippet sets up the necessary users, roles, and grants permissions on tables required for the RLS example. It ensures that the environment is prepared for policy creation and application.

```sql
-- Create users and roles referenced in the policy statements.
CREATE ROLE analyst;
CREATE ROLE consumer;
CREATE ROLE dbadmin;
CREATE ROLE auditor;
CREATE USER bob WITH PASSWORD 'Name_is_bob_1';
CREATE USER alice WITH PASSWORD 'Name_is_alice_1';
CREATE USER joe WITH PASSWORD 'Name_is_joe_1';
CREATE USER molly WITH PASSWORD 'Name_is_molly_1';
CREATE USER bruce WITH PASSWORD 'Name_is_bruce_1';
GRANT ROLE sys:secadmin TO bob;
GRANT ROLE analyst TO alice;
GRANT ROLE consumer TO joe;
GRANT ROLE dbadmin TO molly;
GRANT ROLE auditor TO bruce;
GRANT ALL ON TABLE tickit_category_redshift TO PUBLIC;
GRANT ALL ON TABLE tickit_sales_redshift TO PUBLIC;
GRANT ALL ON TABLE tickit_event_redshift TO PUBLIC;
```

--------------------------------

### Comparing SIMILAR TO and LIKE Performance

Source: https://docs.aws.amazon.com/redshift/latest/dg/pattern-matching-conditions.md

This example demonstrates two functionally identical queries, one using SIMILAR TO and the other using LIKE, to highlight the performance difference. The LIKE version is expected to run significantly faster.

```sql
select count(*) from event where eventname SIMILAR TO '%(Ring|Die)%';
```

```sql
select count(*) from event where eventname LIKE '%Ring%' OR eventname LIKE '%Die%';
```

--------------------------------

### Get H3 Cell ID from Coordinates and Resolution

Source: https://docs.aws.amazon.com/redshift/latest/dg/H3_FromLongLat-function.md

Use this SQL query to retrieve the H3 cell ID for specific longitude, latitude, and resolution values. The example uses longitude 0, latitude 0, and resolution 10.

```sql
SELECT H3_FromLongLat(0, 0, 10);
```

```text
 h3_fromlonglat
-------------------
 623560421467684863
```

--------------------------------

### DropBBox Function Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/DropBBox-function.md

This SQL query demonstrates how to use the DropBBox function to get a polygon geometry without a precomputed bounding box. The input geometry is created using ST_GeomFromText and the output is returned in Well-Known Text format using ST_AsText.

```sql
SELECT ST_AsText(DropBBox(ST_GeomFromText('POLYGON((0 0,1 0,0 1,0 0))')));
```

```text
 st_astext
----------
 POLYGON((0 0,1 0,0 1,0 0))
```

--------------------------------

### Creating and Populating a Date Table

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_NVL_function.md

Sets up a table named 'datetable' with date columns and inserts sample data, including null values, for demonstrating NVL.

```sql
create table datetable (start_date date, end_date date);           
insert into datetable values ('2008-06-01','2008-12-31');
insert into datetable values (null,'2008-12-31');
insert into datetable values ('2008-12-31',null);
```

--------------------------------

### Using NVL2 for Contact Information

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_NVL2.md

This example demonstrates using the NVL2 function to retrieve either an email address or a phone number as contact information, based on whether the email field is null. It also includes sample data setup and query ordering.

```sql
update users set email = null where firstname = 'Aphrodite' and lastname = 'Acevedo';
```

```sql
select (firstname + ' ' + lastname) as name, 
nvl2(email, email, phone) AS contact_info
from users 
where state = 'WA'
and lastname  like 'A%'
order by lastname, firstname;
```

--------------------------------

### Set and Show Search Path

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_USE_command.md

Demonstrates setting the search path to prioritize schema 's1' over 'public' and then querying a table, followed by changing the order and querying again.

```sql
dev=# set search_path to public, s1;
SET
dev=# select * from t;
 id
----
  2
(1 row)

dev=# set search_path to s1, public;
SET
dev=# show search_path;
 search_path
-------------
 s1, public
(1 row)
dev=# select * from t;
 id
----
  3
(1 row)
```

--------------------------------

### Create Data Catalog View Example

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_EXTERNAL_VIEW.md

This example demonstrates how to create a Data Catalog view named 'glue_data_catalog_view' within the 'sample_schema'. The view is created only if it does not already exist and is defined by a query selecting all columns from a remote table.

```sql
CREATE EXTERNAL PROTECTED VIEW sample_schema.glue_data_catalog_view IF NOT EXISTS
AS SELECT * FROM sample_database.remote_table "remote-table-name";
```

--------------------------------

### Get Partition Scan Details for Last Query

Source: https://docs.aws.amazon.com/redshift/latest/dg/r_SVCS_S3PARTITION_SUMMARY.md

This query retrieves partition scan details for the most recent query executed. It selects query ID, segment, assignment type, start and end times, and duration metrics from the svcs_s3partition_summary view, filtering by the last query ID.

```sql
select query, segment, assignment, min_starttime, max_endtime, min_duration, avg_duration 
from svcs_s3partition_summary 
where query = pg_last_query_id() 
order by query,segment;
```

--------------------------------

### PCRE Pattern: Find Second Word with Letter and Number (Case-Sensitive)

Source: https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_INSTR.md

Uses a PCRE pattern with look-ahead assertions to locate words containing at least one lowercase letter and one number. This example finds the starting position of the second such word, using case-sensitive matching.

```SQL
SELECT REGEXP_INSTR('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 2, 0, 'p');
```

--------------------------------

### Create and Call a Stored Procedure with No Output Arguments

Source: https://docs.aws.amazon.com/redshift/latest/dg/stored-procedure-create.md

This example shows how to create a simple stored procedure that accepts input arguments and logs them. It then demonstrates how to call this procedure.

```SQL
CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
```