Some items with Postgres and SQL that I’ve run across:
INITCAP:
INITCAP converts a string to ‘Title Case’, where the first letter of each word is capitalized:
SELECT INITCAP('MS DOE');
Returns “Ms Doe”
OVERLAPS:
OVERLAPS returns true or false, evaluating if two date/time spans overlap or not. This select returns true:
SELECT (CAST('2023-02-15' as date),
CAST('2023-03-15' as date))
OVERLAPS (CAST('2023-03-01' as date),
CAST('2023-03-01' as date));
We can also specify an interval for the second parameter, the amount of time from the first datetime.
SELECT (CAST('2023-02-15' as date), INTERVAL '30' DAY)
OVERLAPS (CAST('2023-03-01' as date),
INTERVAL '30' DAY);
FILTER:
FILTER can be used with aggregates when we want to function to aggregate a subset of the data set:
SELECT
COUNT(table_type) as table_count,
COUNT(table_type)
FILTER (WHERE table_type = 'VIEW') as view_count,
COUNT(table_type)
FILTER (WHERE table_type = 'BASE TABLE')
as base_table_count
FROM information_schema.tables;
Instead of using case statements to determine the records to count.
Pseudo Types:
Postgres data types that can be used for function inputs or outputs. Types include Any, AnyArray, Record, Void. These types can’t be used for a column data type.
ENUM:
Postgres allows us to creates Enums, we can declare a Type as an Enum.
CREATE TYPE team as ENUM ('FALCONS', 'PANTHERS',
'SAINTS', 'BUCS');
We can use the Enum when we create a table column, we can also use the enum as the input and/or return parameters for a function. As an example:
CREATE OR REPLACE FUNCTION get_team_location
(team_name team)
RETURNS text
LANGUAGE plpgsql
AS
$$
DECLARE return_value text;
BEGIN
SELECT
CASE WHEN team_name = 'FALCONS' THEN 'ATLANTA'
WHEN team_name = 'PANTHERS' THEN 'CAROLINA'
WHEN team_name = 'SAINTS' THEN 'NEW ORLEANS'
WHEN team_name = 'BUCS' THEN 'TAMPA'
ELSE NULL END
INTO return_value;
RETURN return_value;
END;
$$;
When we call the function with a valid input value, we’ll get ‘ATLANTA’ as a result.
SELECT get_team_location('FALCONS');
When we call with an invalid value, we get this message:
— ERROR: invalid input value for enum team: “JAGUARS”
SELECT get_team_location('JAGUARS');
postgresql.org – Enumerated Types
DOMAIN:
Postgres allows us to declare a Domain, which we can then use in creating a table column. We can define the data type, nullability, and a check constraint.
CREATE DOMAIN string_name as text NOT NULL
CHECK(LENGTH(value) > 0);
CREATE TABLE domain_test (
id int NOT NULL,
display_name string_name
);
So this insert will succeed.
INSERT INTO domain_test VALUES (1, 'TEST');
This insert will fail:
— ERROR: domain string_name does not allow null values
INSERT INTO domain_test VALUES (2, NULL);
This insert will fail as well:
— ERROR: value for domain string_name violates check constraint “string_name_check”
INSERT INTO domain_test VALUES (3, '');
ALTER TABLE USING:
Say we have a table with a string column, and all of the values are actually numeric. We decide to change the data type to an integer.
DROP TABLE IF EXISTS using_test;
CREATE TABLE using_test (
value_name text NOT NULL
);
INSERT INTO using_test(value_name) VALUES ('1');
ALTER TABLE using_test
ALTER COLUMN value_name TYPE integer;
This ALTER will fail, because we can’t implicitly change a string into a numeric value, even when the values are all numbers.
ERROR: column “value_name” cannot be cast automatically to type integer
Hint: You might need to specify “USING value_name::integer
We can add a USING to the ALTER and specify how to cast the existing data.
ALTER TABLE using_test ALTER COLUMN value_name TYPE integer USING CAST(value_name as integer);
SQL Rob 