Other articles

  1. SQL : CAST function

    In PostgreSQL, the CAST function is used to explicitly convert a value from one data type to another. It is an alternative to using the shorthand :: operator for type conversion.

    Syntax:

    CAST (value AS target_type)
    • value: The value or column you want to convert.
    • target_type: The target data type you want to convert the value to.

    Examples:

    1. Converting a String to an Integer

    If you have a string representation of a number and you want to convert it to an integer, you can use CAST.

    SELECT CAST('123' AS INTEGER) AS converted_value;

    Output:

     converted_value
-----------------
             123

    2. Converting a Number to a String

    You can convert a number to a string using CAST.

    SELECT CAST(456 AS TEXT) AS converted_value;

    Output:

     converted_value
-----------------
 456

    3. Converting a String to a Numeric

    If you have a string with a floating-point number, you can convert it to a numeric type.

    SELECT CAST('123 …
    read more

  2. SQL : DATE / Time in SQL

    How to apply datetime filters

    SELECT invoice_date FROM invoice WHERE invoice_date::DATE < '2021-03-06' AND invoice_date::DATE > '2021-03-01';

    NOW() and INTERVAL

    SELECT invoice_date, NOW()::DATE as today FROM invoice WHERE invoice_date::DATE < '2021-03-06' AND invoice_date::DATE > '2021-03-01';

SELECT invoice_date, NOW()::DATE - invoice_date::DATE AS no_of_days FROM invoice


SELECT invoice_date, invoice_date::DATE + INTERVAL '365 days' AS renewal_date FROM invoice
    read more

  3. SQL : JOINS, Union, Subqueries, CTE

    In PostgreSQL, joins are used to retrieve data from multiple tables based on a related column. There are several types of joins, including INNER, LEFT, RIGHT, and FULL (OUTER). Here's a breakdown of each with an example.

    Example Setup:

    Suppose we have two tables: students and courses.

    students table: | id | name | course_id | | --- | ------- | --------- | | 1 | Alice | 101 | | 2 | Bob | NULL | | 3 | Charlie | 102 | | 4 | David | 103 |

    courses table: | id | course_name | | --- | --------------- | | 101 | Math | | 102 | Science | | 104 | History |

    1. INNER JOIN:

    An INNER JOIN returns rows when there is a match in both tables.

    SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.course_id = courses.id;

    Result: | name | course_name | | ------- | ----------- | | Alice | Math | | Charlie | Science |

    • Explanation: Only students with a matching course_id in the courses table are included in the result.

    2. LEFT JOIN (or LEFT OUTER JOIN):

    A LEFT JOIN returns all rows from the left table (students), and the matched rows …

    read more

  4. SQL : Writing comments in SQL

    In PostgreSQL, you can write comments in SQL queries using two different styles:

    1. Single-line comments: Use -- to comment out a single line or part of a line. sql SELECT * FROM users; -- This selects all users

    2. Multi-line comments: Use /* */ to comment out multiple lines or a section of your query. sql /* This is a multi-line comment. You can use it for longer explanations. */ SELECT * FROM users;

    Both types of comments will be ignored by PostgreSQL when executing the query.

    read more

  7. SQL : String Manipulation

    In PostgreSQL, the TRIM function is used to remove both leading and trailing spaces (or specified characters) from a string. It is essentially a combination of LTRIM (left trim) and RTRIM (right trim).

    Syntax:

    TRIM([LEADING | TRAILING | BOTH] [characters FROM] string)
    • LEADING: Removes only leading characters (default is spaces) from the string.
    • TRAILING: Removes only trailing characters (default is spaces) from the string.
    • BOTH (or no option specified): Removes both leading and trailing characters (default is spaces) from the string.

    Example 1: Trimming Spaces

    Sample Data:

    CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT
);

INSERT INTO users (username) VALUES
    ('  Alice  '),
    ('  Bob'),
    ('Charlie  '),
    ('  Dave   ');

    Query:

    SELECT id, username, TRIM(username) AS trimmed_username
FROM users;

    Output:

     id | username   | trimmed_username
----+------------+------------------
  1 |   Alice    | Alice
  2 |   Bob      | Bob
  3 | Charlie    | Charlie
  4 |   Dave     | Dave

    In this example, TRIM removes both leading and trailing spaces from the username values.

    Example 2: Trimming Specific Characters

    You …

    read more

links

social