MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Postgres Developer’ tag

Postgres Check Constraints

without comments

The Postgres 11 database documentation says that it supports naming constraints. While you can create a table with named constraints inside the CREATE TABLE statement, the names are not assigned to the not null check constraint.

Here’s a quick test case in three steps:

  1. Drop a demo table if it exists: 

    DROP TABLE IF EXISTS demo;

  2. Drop a demo table if it exists: 

    CREATE TABLE demo
( demo_id    SERIAL
, demo_text  VARCHAR(20) CONSTRAINT nn_demo_1 NOT NULL );

  3. Create a demo table if it exists: 

    SELECT substr(check_clause,1,strpos(check_clause,' ')-1) AS check_column
,      constraint_name
FROM   information_schema.check_constraints
WHERE  check_clause LIKE 'demo_text%';

    You should see the following output with a parsed check_column name and the system generated check constraint name rather than the nn_demo_1 constraint name:

     check_column |    constraint_name    
--------------+-----------------------
 demo_text    | 2200_18896_2_not_null
(1 row)

    4. On the bright side, you can name primary key and foreign key constraints.

Written by maclochlainn

October 7th, 2019 at 10:35 pm

Posted in Fedora,Linux,Postgres,PostgreSQL,sql

Tagged with ,

Session Variables

without comments

In MySQL and Oracle, you set a session variable quite differently. That means you should expect there differences between setting a session variable in Postgres. This blog post lets you see how to set them in all three databases. I’m always curious what people think but I’m willing to bet that MySQL is the simplest approach. Postgres is a bit more complex because you must use a function call, but Oracle is the most complex.

The difference between MySQL and Postgres is an “@” symbol versus a current_setting() function call. Oracle is more complex because it involves the mechanics in Oracle’s sqlplus shell, SQL dialect, and PL/SQL language (required to assign a value to a variable).

MySQL

MySQL lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable on a single line with the following command: 

    SET @my_variable_name := 'My Value';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list 

    SELECT @my_variable_name AS "The Value" FROM dual;

    or WHERE clause

    SELECT column_name
FROM   table_name
WHERE  column_name = @my_variable_name;

Postgres

Postgres lets you declare a session variable in one step and use it one way in a SQL statement or stored procedure.

  1. You set a session variable in a single line. It iss critical to note that you must use double quotes around the session variable name and single quotes for the value. You raise an error when you use a single quote instead a double quote around the session variable name. The syntax is: 

    SET SESSION "videodb.table_name" = 'new_hire';

  2. You can query a variable from the pseudo table dual or as a comparison value in the SELECT-list with the current_setting() function call. 

    SELECT current_setting('videodb.table_name') AS "The Value";

    or WHERE clause

    SELECT column_name
FROM   table_name
WHERE  column_name = current_setting('videodb.table_name');

Oracle

There are two steps required to declare a session variable in Oracle. First, you need to define the variable in the SQL*Plus session. Oracle lets you define a variable like you would define a variable in the C language, using extern before the variable’s type. Second, you assign a value to the session variable in an anonymous PL/SQL block. There is no single line statement to declare a variable with an initial value.

  1. You set a session variable by using the VARIABLE keyword, a variable name, and data type. The supported data types are: BLOB, BFILE, BINARY_DOUBLE, BINARY_FLOAT, CHAR, CLOB, NCHAR, NCLOB, NVARCHAR2, REFCURSOR, and VARCHAAR2. You define a variable with the following syntax: 

    VARIABLE bv_variable_name VARCHAR2(30)

  2. You assign a value to the bind variable inside an anonymous block by prefacing the variable name with a colon. You assign values inside PL/SQL with the walrus operator (:=) and a string enclosed by single quotes. Anonymous blocks start with a BEGIN and end with an END followed by a semicolon (;) and a forward slash (/) to dispatch the block for execution. The following example shows a full block: 

    BEGIN
  :bv_variable_name := 'Some Value';
END;
/

  3. You can query any declared variable from the pseudo table dual or as a comparison value in the SELECT-list 

    SELECT :bv_variable_name FROM dual;

    or WHERE clause

    SELECT column_name
FROM   table_name
WHERE  column_name = :bv_variable_name;

Written by maclochlainn

September 28th, 2019 at 9:01 pm

Posted in MySQL,MySQL 8,Oracle,Oracle 11g,Oracle 12c,Oracle 18c,Postgres,PostgreSQL,Uncategorized

Tagged with , ,

Newer Entries »