MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘Postgres’ Category

Java and Postgres

without comments

I wanted to get Java working with PostgreSQL to test some GUI interfaces on Linux. Figuring out the necessary JAR file for the JDBC was my first hurdle. I found it was postgreSQL-42-2.5.jar file.

You can download it with the following command line:

wget https://jdbc.postgresql.org/download/postgresql-42.2.5.jar

I downloaded it to a Java directory off the home/student directory. Then, I added the following CLASSPATH to local java.env environment file.

#!/usr/bin/bash
 
# Set the Java CLASSPATH environment variable to include the JDBC jar file.
export set CLASSPATH=/home/student/Java/postgresql-42.2.5.jar:.

I sourced the postgresql-42.2.5.jar file and I wrote the following JavaTest.java program:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

/* Import classes. */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
 
public class JavaTest { 
  public static void main(String[] args) {
    /* Set default strings for the connection. */
    String url = "jdbc:postgresql://localhost:5432/videodb";
    String user = "student";
    String password = "student";
 
    /* Try the connection and statement. */
    try {
      Connection conn = DriverManager.getConnection(url, user, password);
      Statement st = conn.createStatement();
      ResultSet rs = st.executeQuery("SELECT VERSION()");
 
      if (rs.next()) {
        System.out.println(rs.getString(1));
      }
 
      /* Close the result set and statement. */
      rs.close();
      st.close();
    }
    catch (SQLException e) {
      Logger logger = Logger.getLogger(JavaTest.class.getName());
      logger.log(Level.SEVERE, e.getMessage(), e);
    }
  }
}

I compiled the JavaTest.java program and tested it. It failed with the following error:

FATAL: Ident authentication failed for user - Unable to connect to PostgreSQL

The failure occurred because I hadn’t allowed the connection in PostgreSQL’s pg_hba.conf file. I changed the following line in my pg_hba.conf file:

host    all             all             127.0.0.1/32            ident

to

host    all             all             127.0.0.1/32            trust

Then, I restarted the postgresql-11 service, like this:

systemctl restart postgresql-11.service

The JavaTest program ran successfully and returned:

PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.1.1 20190503 (Red Hat 9.1.1-1), 64-bit

You can extend the logic to output a comma-separated value file by leveraging the JDBC help page, like this:

/* Import classes. */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
 
/* Create class withonly a static method for runtime testing. */
public class JavaReturnSet { 
  public static void main(String[] args) {
    /* Set default strings for the connection. */
    String url = "jdbc:postgresql://localhost:5432/videodb";
    String user = "student";
    String password = "student";
 
    /* Try the connection and statement. */
    try {
      /* Set connection, statement, and result set. */
      Connection conn = DriverManager.getConnection(url, user, password);
      Statement st = conn.createStatement();
 
      /* Use the + to concatenate lines for query clarity. */
      ResultSet rs = st.executeQuery("SELECT m.account_number\n" +
                                     ",      CONCAT(c.last_name,', ',c.first_name)\n" +
                                     "FROM   member m JOIN contact c\n" +
                                     "ON     m.member_id = c.member_id");
 
      /* Get query metadata for subsequent management of results. */
      ResultSetMetaData rsmd = rs.getMetaData();
      String line;
 
      while (rs.next()) {
        /* Initialize the line output for each row. */
        line = "";
 
        /* Process the columns. */
        for (int i = 0; i < rsmd.getColumnCount(); i++) {
          if (rsmd.getColumnType(i + 1) == 12) {
            line = line + "\"" + rs.getString(i + 1) + "\"";
          }
          else {
            line = line + rs.getInt(i + 1);
          }
 
          /* Put a comma between output columns. */
          if (i < rsmd.getColumnCount() - 1) {
            line = line + ",";
          }
        }
        System.out.println(line);
      }
 
      /* Close the result set and statement. */
      rs.close();
      st.close();
    }
    catch (SQLException e) {
      Logger logger = Logger.getLogger(JavaReturnSet.class.getName());
      logger.log(Level.SEVERE, e.getMessage(), e);
    }
  }
}

As always, I hope this helps those looking for a clean solution.

Written by maclochlainn

November 2nd, 2019 at 11:46 pm

Posted in DBA,DevOps,Fedora,Java,Java 8,Linux,Postgres,PostgreSQL,sql

Tagged with ,

Postgres Drop Structures

with one comment

While building my PostgreSQL environment for the class, I had to write a couple utilities. They do the following:

  1. Drops all the tables from a schema.
  2. Drops all the sequences from a schema that aren’t tied to an _id column with a SERIAL data type.
  3. Drops all the functions and procedures (qualified as routines) from a schema.
  4. Drops all the triggers from a schema.

The following gives you the code for all four files: drop_tables.sql, drop_sequences.sql, drop_routines.sql, and drop_triggers.sql.

  • The drop_tables.sql Script:

    • /* Verify all tables present. */
SELECT table_name
FROM   information_schema.tables
WHERE  table_catalog = current_setting('videodb.catalog_name')
AND    table_schema = 'public';
 
DO $$
DECLARE
  /* Declare an indefinite length string and record variable. */
  sql  VARCHAR;
  row  RECORD;
 
  /* Declare a cursor. */
  table_cursor CURSOR FOR
    SELECT table_name
    FROM   information_schema.tables
    WHERE  table_catalog = current_setting('videodb.catalog_name')
    AND    table_schema = 'public';
BEGIN
  /* Open the cursor. */
  OPEN table_cursor;
  LOOP
    /* Fetch table names. */
    FETCH table_cursor INTO row;
 
    /* Exit when no more records are found. */
    EXIT WHEN NOT FOUND;
 
    /* Concatenate together a DDL to drop the table with prejudice. */
    sql := 'DROP TABLE IF EXISTS '||row.table_name||' CASCADE';
 
    /* Execute the DDL statement. */
    EXECUTE sql;
  END LOOP;
 
  /* Close the cursor. */
  CLOSE table_cursor;
END;
$$;
 
/* Verify all tables are dropped. */
SELECT table_name
FROM   information_schema.tables
WHERE  table_catalog = current_setting('videodb.catalog_name')
AND    table_schema = 'public';

  • The drop_sequences.sql script:

    • /* Verify all tables present. */
SELECT sequence_name
FROM   information_schema.sequences
WHERE  sequence_catalog = current_setting('videodb.catalog_name')
AND    sequence_schema = 'public';
 
DO $$
DECLARE
  /* Declare an indefinite length string and record variable. */
  sql  VARCHAR;
  row  RECORD;
 
  /* Declare a cursor. */
  sequence_cursor CURSOR FOR
    SELECT sequence_name
    FROM   information_schema.sequences
    WHERE  sequence_catalog = current_setting('videodb.catalog_name')
    AND    sequence_schema = 'public';
BEGIN
  /* Open the cursor. */
  OPEN sequence_cursor;
  LOOP
    /* Fetch table names. */
    FETCH sequence_cursor INTO row;
 
    /* Exit when no more records are found. */
    EXIT WHEN NOT FOUND;
 
    /* Concatenate together a DDL to drop the table with prejudice. */
    sql := 'DROP SEQUENCE IF EXISTS '||row.sequence_name;
 
    /* Execute the DDL statement. */
    EXECUTE sql;
  END LOOP;
 
  /* Close the cursor. */
  CLOSE sequence_cursor;
END;
$$;
 
/* Verify all tables are dropped. */
SELECT sequence_name
FROM   information_schema.sequences
WHERE  sequence_catalog = current_setting('videodb.catalog_name')
AND    sequence_schema = 'public';

  • The drop_routines.sql script:

    • /* Verify all tables present. */
SELECT routine_name
,      routine_type
FROM   information_schema.routines
WHERE  specific_catalog = current_setting('videodb.catalog_name')
AND    specific_schema = 'public';
 
DO $$
DECLARE
  /* Declare an indefinite length string and record variable. */
  sql  VARCHAR;
  row  RECORD;
 
  /* Declare a cursor. */
  routine_cursor CURSOR FOR
    SELECT routine_name
    ,      routine_type
    FROM   information_schema.routines
    WHERE  specific_catalog = current_setting('videodb.catalog_name')
    AND    routine_schema = 'public';
BEGIN
  /* Open the cursor. */
  OPEN routine_cursor;
  LOOP
    /* Fetch table names. */
    FETCH routine_cursor INTO row;
 
    /* Exit when no more records are found. */
    EXIT WHEN NOT FOUND;
 
    /* Concatenate together a DDL to drop the table with prejudice. */
    sql := 'DROP '||row.routine_type||' IF EXISTS '||row.routine_name;
 
    /* Execute the DDL statement. */
    EXECUTE sql;
  END LOOP;
 
  /* Close the cursor. */
  CLOSE routine_cursor;
END;
$$;
 
/* Verify all tables are dropped. */
SELECT routine_name
,      routine_type
FROM   information_schema.routines
WHERE  specific_catalog = 'videodb'
AND    specific_schema = 'public';

  • The drop_triggers.sql script:

    • /* Verify all tables present. */
SELECT trigger_name
FROM   information_schema.triggers
WHERE  trigger_catalog = current_setting('videodb.catalog_name')
AND    trigger_schema = 'public';
 
DO $$
DECLARE
  /* Declare an indefinite length string and record variable. */
  sql  VARCHAR;
  row  RECORD;
 
  /* Declare a cursor. */
  trigger_cursor CURSOR FOR
    SELECT trigger_name
    FROM   information_schema.triggers
    WHERE  trigger_catalog = current_setting('videodb.catalog_name')
    AND    trigger_schema = 'public';
BEGIN
  /* Open the cursor. */
  OPEN trigger_cursor;
  LOOP
    /* Fetch table names. */
    FETCH trigger_cursor INTO row;
 
    /* Exit when no more records are found. */
    EXIT WHEN NOT FOUND;
 
    /* Concatenate together a DDL to drop the table with prejudice. */
    sql := 'DROP TRIGGER IF EXISTS '||row.trigger_name;
 
    /* Execute the DDL statement. */
    EXECUTE sql;
  END LOOP;
 
  /* Close the cursor. */
  CLOSE trigger_cursor;
END;
$$;
 
/* Verify all tables are dropped. */
SELECT trigger_name
FROM   information_schema.triggers
WHERE  trigger_catalog = current_setting('videodb.catalog_name')
AND    trigger_schema = 'public';

You can create a cleanup_catalog.sql script to call all four in sequence, like the following: 

\i /home/student/Data/cit225/postgres/lib/utility/drop_tables.sql
\i /home/student/Data/cit225/postgres/lib/utility/drop_sequences.sql
\i /home/student/Data/cit225/postgres/lib/utility/drop_routines.sql
\i /home/student/Data/cit225/postgres/lib/utility/drop_triggers.sql

The nice thing about this approach is that you won’t see any notices when tables, sequences, routines, or triggers aren’t found. It’s a clean approach to cleaning the schema for a testing environment.

Written by maclochlainn

October 27th, 2019 at 3:58 pm

Posted in Linux,Postgres,PostgreSQL,sql,Unix

Tagged with , , ,

Postgres Print Debug Notes

without comments

A student asked how you print output from PL/pgSQL blocks. The student wanted to know if there was something like the following in Oracle’s PL/SQL programming language:

dbms_output.put_line('some string');

or, in Java programming the:

System.out.println("some string");

The RAISE NOTICE is the equivalent to these in Postgres PL/pgSQL, as shown in the following anonymous block:

do $$
BEGIN
  raise notice 'Hello World!';
END;
$$;

It prints:

NOTICE:  Hello World!

You can write a hello_world function as a named PL/pgSQL block:

CREATE FUNCTION hello_world()
RETURNS text AS
$$
DECLARE
  output  VARCHAR(20);
BEGIN
  /* Query the string into a local variable. */
  SELECT 'Hello World!' INTO output;
 
  /* Return the output text variable. */
  RETURN output;
END
$$ LANGUAGE plpgsql;

You can call it with the following:

SELECT hello_world();

It prints:

 hello_world  
--------------
 Hello World!
(1 row)

Here’s a full test case with stored procedure in PL/pgSQL:

-- Drop the msg table.
DROP TABLE msg;
 
-- Create the msg table.
CREATE TABLE msg
( comment  VARCHAR(400) );
 
-- Transaction Management Example.
DROP PROCEDURE IF EXISTS testing
( IN pv_one                 VARCHAR(30)
, IN pv_two                 VARCHAR(10));
 
-- Transaction Management Example.
CREATE OR REPLACE PROCEDURE testing
( IN pv_one                 VARCHAR(30)
, IN pv_two                 VARCHAR(10)) AS
$$
DECLARE
  /* Declare error handling variables. */
  err_num      TEXT;
  err_msg      INTEGER;
BEGIN
  /* Log actdual parameter values. */
  INSERT INTO msg VALUES (pv_one||'.'||pv_two);
 
EXCEPTION
  WHEN OTHERS THEN
    err_num := SQLSTATE;
    err_msg := SUBSTR(SQLERRM,1,100);
    RAISE NOTICE 'Trapped Error: %', err_msg;
END
$$ LANGUAGE plpgsql;
 
do $$
DECLARE
  lv_one VARCHAR(30) := 'INDIVIDUAL';
  lv_two VARCHAR(19) := 'R11-514-34';
BEGIN
  RAISE NOTICE '[%]', lv_one;
  RAISE NOTICE '[%]', lv_two;
  CALL testing( pv_one := lv_one, pv_two := lv_two );
END
$$;
 
-- Query any logged results.
SELECT * FROM msg;

It prints:

DROP TABLE
CREATE TABLE
DROP PROCEDURE
CREATE PROCEDURE
psql:fixed.sql:61: NOTICE:  [INDIVIDUAL]
psql:fixed.sql:61: NOTICE:  [R11-514-34]
DO
        comment        
-----------------------
 INDIVIDUAL.R11-514-34
(1 row)

I hope this helps those looking for a solution.

Written by maclochlainn

October 12th, 2019 at 5:03 pm

Posted in Java,PL/pgSQL,pl/sql,Postgres,PostgreSQL

Tagged with ,

Postgres SQL Nuance

without comments

I ran across an interesting nuance between Oracle and Postgres with the double-pipe operator. I found that the following query failed to cross port from Oracle to Postgres:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

COL account_number  FORMAT A10  HEADING "Account|Number"
COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
COL city            FORMAT A12  HEADING "City"
COL state_province  FORMAT A10  HEADING "State"
COL telephone       FORMAT A18  HEADING "Telephone"
SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN
         address a ON c.contact_id = a.contact_id INNER JOIN
         street_address sa ON a.address_id = sa.address_id INNER JOIN
         telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id
WHERE    c.last_name = 'Winn';

In Oracle, a CASE statement ignores the null of a missing ELSE clause between lines 4 and 5. Oracle assumes a null value is an empty string when concatenated to a string with the double-piped concatenation operator. Oracle’s implementation differs from the ANSI standard and is non-compliant.

It would display the following thanks to the SQL reporting features that don’t exist in other Command-Line Interface (CLI) implementations, like mysql, psql, sqlcmd, or cql:

Account    Name
Number     (Last, First MI) City         State      Telephone
---------- ---------------- ------------ ---------- ------------------
B293-71445 Winn, Randi      San Jose     CA         001-(408) 111-1111
B293-71445 Winn, Brian      San Jose     CA         001-(408) 111-1111

However, it fails in Postgres without a notice, warning, or error. Postgres simply returns a null string for the missing ELSE clause and follows the rule that any string concatenated against a null is a null. That means it retunes a null value for the full_name column above. The Postgres behavior is the ANSI standard behavior. After years of working with Oracle it was interesting to have this pointed out while porting a query.

You can fix the statement in Postgres by adding an explicit ELSE clause on a new line 5 that appends an empty string, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT   m.account_number
,        c.last_name || ', ' || c.first_name
||       CASE
           WHEN c.middle_name IS NOT NULL THEN ' ' || c.middle_name
           ELSE ''
         END AS full_name
,        a.city
,        a.state_province
,        t.country_code || '-(' || t.area_code || ') ' || t.telephone_number AS telephone
FROM     member m INNER JOIN contact c ON m.member_id = c.member_id INNER JOIN
         address a ON c.contact_id = a.contact_id INNER JOIN
         street_address sa ON a.address_id = sa.address_id INNER JOIN
         telephone t ON c.contact_id = t.contact_id AND a.address_id = t.address_id
WHERE    c.last_name = 'Winn';

It would display:

 account_number |  full_name  |   city   | state_province |     telephone      
----------------+-------------+----------+----------------+--------------------
 B293-71445     | Winn, Randi | San Jose | CA             | 001-(408) 111-1111
 B293-71445     | Winn, Brian | San Jose | CA             | 001-(408) 111-1111
(2 rows)

As always, I hope this helps those looking to solve a problem.

Written by maclochlainn

October 12th, 2019 at 1:20 pm

Posted in Linux,Oracle,Postgres,PostgreSQL,sql

Tagged with , , ,

Postgres Foreign Constraints

without comments

You can’t disable a foreign key constraint in Postgres, like you can do in Oracle. However, you can remove the foreign key constraint from a column and then re-add it to the column.

Here’s a quick test case in five steps:

  1. Drop the big and little table if they exists. The first drop statement requires a cascade because there is a dependent little table that holds a foreign key constraint against the primary key column of the big table. The second drop statement does not require the cascade keyword because there is not a dependent foreign key constraint. 

    DROP TABLE IF EXISTS big CASCADE;
DROP TABLE IF EXISTS little;

  2. Create the big and little tables: 

    -- Create the big table.
CREATE TABLE big
( big_id     SERIAL
, big_text   VARCHAR(20) NOT NULL
, CONSTRAINT pk_little_1 PRIMARY KEY (big_id));
 
-- Display the big table.
\d big
 
-- Create little table.
CREATE TABLE little
( little_id     SERIAL
, big_id        INTEGER     NOT NULL
, little_text   VARCHAR(20) NOT NULL
, CONSTRAINT fk_little_1 FOREIGN KEY (big_id) REFERENCES big (big_id));
 
-- Display the little table.
\d little

    If you failed to designate the big_id column as a primary key constrained, Postgres will raise the following exception:

    ERROR:  there IS no UNIQUE CONSTRAINT matching given KEYS FOR referenced TABLE "big"

  3. Insert a non-compliant row in the little table. An insert statement into the little table with a value for the foreign key column that does not exist in the big_id column of the big table would fail with the following error: 

    ERROR:  INSERT OR UPDATE ON TABLE "little" violates FOREIGN KEY CONSTRAINT "fk_little_1"
DETAIL:  KEY (big_id)=(2) IS NOT present IN TABLE "big".

    Re-enabling the foreign key constraint, the insert statement succeeds after you first insert a new row into the big table with the foreign key value for the little table as its primary key. The following two insert statements add a row to both the big and little table:

    -- Insert into a big table.
INSERT INTO big
(big_text)
VALUES
('Cat in the Hat 2');
 
-- Insert into a little table.
INSERT INTO little
(big_id
,little_text)
VALUES
( 2
,'Thing 3');

    Then, you can query it like this:

    SELECT *
FROM   big b JOIN little l ON b.big_id = l.big_id;

     big_id |     big_text     | little_id | big_id | little_text 
--------+------------------+-----------+--------+-------------
      1 | Cat IN the Hat 1 |         1 |      1 | Thing 1
      1 | Cat IN the Hat 1 |         2 |      1 | Thing 2
      2 | Cat IN the Hat 2 |         3 |      2 | Thing 3
(3 ROWS)

  4. You can drop a foreign key constraint with the following syntax: 

    ALTER TABLE little DROP CONSTRAINT fk_little_1;

  5. You can add a foreign key constraint with the following syntax: 

    ALTER TABLE little ADD CONSTRAINT fk_little_1 FOREIGN KEY (big_id) REFERENCES big (big_id);

As always, I hope this helps you solve problems.

Written by maclochlainn

October 8th, 2019 at 8:49 pm

Posted in Fedora,Linux,Postgres,PostgreSQL,sql

Tagged with ,

Postgres Remove Constraints

without comments

You can’t disable a not null constraint in Postgres, like you can do in Oracle. However, you can remove the not null constraint from a column and then re-add it to the column.

Here’s a quick test case in four steps:

  1. Drop a demo table if it exists: 

    DROP TABLE IF EXISTS demo;

  2. Create a demo table if it exists: 

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

  3. Insert a compliant row in the demo table if it exists: 

    INSERT INTO demo
(demo_text)
VALUES
('Thing 1');

    Attempt to insert another row with a null value in the demo_text column:

    INSERT INTO demo
(demo_text)
VALUES
(NULL);

    It raises the following error:

    INSERT 0 1
psql:remove_not_null.sql:22: ERROR:  NULL VALUE IN COLUMN "demo_text" violates not-NULL CONSTRAINT
DETAIL:  Failing ROW contains (2, NULL).

  4. You can drop the not null constraint from the demo_text column: 

    ALTER TABLE demo ALTER COLUMN demo_text DROP NOT NULL;

    You can now successfully insert a row with a demo_text column value of null. After you have performed your table maintenance you can add the not null constraint back on to the demo_text column.

    You need to update the row with a null value in the demo_text column with a valid value before you re-add the not null constraint. The following shows an update statement that replaces the null value with a text string:

    UPDATE demo
SET    demo_text = 'Thing 2'
WHERE  demo_text IS NULL;

    Now, you can change the demo_text column back to a not null constrained column with the following syntax.

    ALTER TABLE demo ALTER COLUMN demo_text SET NOT NULL;

    5. While you can not defer the constraint, removing it and adding it back works well.

Written by maclochlainn

October 8th, 2019 at 12:26 am

Posted in Fedora,Linux,Postgres,sql

Tagged with ,

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 , ,

Python-Postgres Query

without comments

As I committed to a student, here are sample programs for writing a Python query against the Postgres 11 database. The first one returns rows or tuples. The latter formats the text returned as columns.

If you’re one of many looking for the key psycopg2 driver library, you can find it in most distro repositories as: python3-psycopg2. You can use dnf or yum to install it separately or you can install pgadmin4, which includes the psycopg2 library.

The first example returns the entire row from a new_hire table with two rows:

import psycopg2
 
try:
  # Open a connection to the database.
  connection = psycopg2.connect( user="student"
                               , password="student"
                               , port="5432"
                               , dbname="videodb")
 
  # Open a cursor.
  cursor = connection.cursor()
 
  # Assign a static query.
  query = "SELECT * FROM new_hire"
 
  # Parse and execute the query.
  cursor.execute(query)
 
  # Fetch all rows from a table.
  records = cursor.fetchall()
 
  # Read through and print the rows as tuples.
  for row in range(0, len(records)):
    print(records[row]) 
 
except (Exception, psycopg2.Error) as error :
  print("Error while fetching data from PostgreSQL", error)
 
finally:
  # Close the database connection.
  if (connection):
    cursor.close()
    connection.close()

The first example returns the rows as tuples, which is probably desired if you want to consume the result in another Python program. Here’s the output retrieved:

(1001, 'Malcolm', 'Jacob', 'Lewis', datetime.date(2018, 2, 14))
(1002, 'Henry', None, 'Chabot', datetime.date(1990, 7, 31))

The second one returns the rows and formats the columns into output for a csv style file:

import psycopg2
 
try:
  # Open a connection to the database.
  connection = psycopg2.connect( user="student"
                               , password="student"
                               , port="5432"
                               , dbname="videodb")
 
  # Open a cursor.
  cursor = connection.cursor()
 
  # Assign a static query.
  query = "SELECT * FROM new_hire"
 
  # Parse and execute the query.
  cursor.execute(query)
 
  # Read through and print the formatted columns of each row.
  for (new_hire_id, first_name, middle_name, last_name, hire_date) in cursor:
    if (isinstance(middle_name,type(None))):
      print("{},'{} {}','{:%d-%b-%Y}'".format(new_hire_id, first_name, last_name, hire_date))
    else:
      print("{},'{} {} {}','{:%d-%b-%Y}'".format(new_hire_id, first_name, middle_name, last_name, hire_date))
 
except (Exception, psycopg2.Error) as error :
  print("Error while fetching data from PostgreSQL", error)
 
finally:
  # Close the database connection.
  if (connection):
    cursor.close()
    connection.close()

The second one returns the rows and formatted columns for a csv style file:

1001,'Malcolm Jacob Lewis','14-Feb-2018'
1002,'Henry Chabot','31-Jul-1990'

As always, I hope these help those looking for a starting place with Python and Postgres.

Written by maclochlainn

September 23rd, 2019 at 1:23 am

Posted in Fedora,Linux,Postgres,PostgreSQL,Python,Python 3.x

Tagged with

pgAdmin4 on Fedora 30

without comments

While attempting an install of pgAdmin and updating a Fedora 30 environment, I encountered a conflict on the upgrade of MySQL 8.0.17-1 to 8.0.17.2. The community-mysql-8.0.17-2.fc30.x86_64 had conflicts with:

  • mysql-community-client-8.0.17-1.fc30.x86_64 package
  • mysql-community-server-8.0.17-1.fc30.x86_64 package

I tried to update the system before install pgadmin4 with the following syntax:

dnf -y update && dnf -y install pgadmin4

The dnf utility raise the following MySQL package errors during transaction checking:

Display detailed console log →

Error: Transaction check error:
  file /usr/bin/mysql conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_config_editor conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqladmin conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlbinlog conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlcheck conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqldump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlimport conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlpump conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlshow conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqlslap conflicts between attempted installs of community-mysql-8.0.17-2.fc30.x86_64 and mysql-community-client-8.0.17-1.fc30.x86_64
  file /usr/bin/ibd2sdi conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/innochecksum conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/my_print_defaults conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisam_ftdump conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisamchk conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisamlog conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/myisampack conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_secure_installation conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_ssl_rsa_setup conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_tzinfo_to_sql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysql_upgrade conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/mysqld_pre_systemd conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/bin/perror conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib/systemd/system/mysqld.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib/systemd/system/mysqld@.service conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/adt_null.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/auth_socket.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_audit_api_message_emit.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_log_filter_dragnet.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_log_sink_json.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_log_sink_syseventlog.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/component_validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/connection_control.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/ddl_rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/group_replication.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/ha_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/ha_mock.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/innodb_engine.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/keyring_file.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/keyring_udf.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/libmemcached.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/locking_service.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/mypluglib.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/mysql_clone.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/mysql_no_login.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/rewrite_example.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/rewriter.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/semisync_master.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/semisync_slave.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/validate_password.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/plugin/version_token.so conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/sbin/mysqld conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /var/lib/mysql conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /var/lib/mysql-keyring conflicts between attempted installs of community-mysql-server-8.0.17-2.fc30.x86_64 and mysql-community-server-8.0.17-1.fc30.x86_64
  file /usr/lib64/mysql/libmysqlclient.so.21.1.17 conflicts between attempted installs of community-mysql-libs-8.0.17-2.fc30.x86_64 and mysql-community-libs-8.0.17-1.fc30.x86_64

Since I’m not sure what’s wrong or how to fix it, I’ve put it in my queue of things to get to later. However, when I figure it out I’ll update this blog page with the solution or work around. If anybody knows the fix and would like to share, please let me know.

I removed the pending update packages with the following command:

dnf clean packages

Then, I simply installed pgadmin4 with the following command:

dnf -y install pgadmin4

Display detailed console log →

 

The pgadmin4 configuration instructions can be found for several Linux versions at Josphat Mutai’s Computing for Geeks web page. On Fedora 30, you need to do the following:

  • Install, start, and enable Apache as the httpd service unless you already have done that.
  • Copy the /etc/httpd/conf.d/pgadmin4.conf.sample file to /etc/httpd/conf.d/pgadmin4.conf, which is a new file.
  • Restart the httpd service to incorporate the pgadmin4 configuration file.

After that, you create the following new directories as the root or sudo user:

  • /var/lib/pgadmin4
  • /var/log/pgadmin4

You can make both directories with a single mkdir command, like:

mkdir -p /var/lib/pgadmin4 /var/log/pgadmin4

As the root or sudo user, change the ownership of these two directories to the apache user with the following syntax:

chown -R apache:apache /var/lib/pgadmin4 /var/log/pgadmin4

You add the following four statements to the config_distro.py file in the /usr/lib/python3.7/site-packages/pgadmin4-web directory as the root or sudo user:

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'

You need to setup the pgadmin user with the following python3 command:

python3 /usr/lib/python3.7/site-packages/pgadmin4-web/setup.py

Enter the following values, a real email address and a password twice:

NOTE: Configuring authentication for SERVER mode.
 
Enter the email address and password to use for the initial pgAdmin user account:
 
Email address: admin@example.com   
Password: your_password
Retype password: your_password
pgAdmin 4 - Application Initialisation
======================================

Assuming you have an enabled firewall, you need to issue the following two commands as the root or sudo user:

rirewall-cmd --permanent --add-service=http
firewall-cmd --reload

You invoke pgAdmin4 from within a browser window with the following URL for a stand alone workstation (for a workstation on a DNS network you would enter pgadmin.domain.domain_type in lieu of localhost):

pgadmin/localhost/pgadmin4

You most likely will encounter an Internal Server Error, the recommended fix is reputed to be:

ausearch -c 'httpd' --raw | audit2allow -M my-httpd
semodule -X 300 -i my-httpd.pp

It didn’t work for me. At the end of the process, I have an Internal Server Error. It is something that I’ll try to fix next. The actual error message:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
 
Please contact the server administrator at root@localhost to inform them of the time this error occurred, and the actions you performed just before this error.
 
More information about this error may be available in the server error log.

If somebody figures out the last step before I do, that’s great. Let me and everybody else know the mystery.

On a positive note, the pgadmin4 package provided the psycopg2 library. I had looked for it as a psycopg2 package but it is in python3-psycopg2 package.

Written by maclochlainn

September 21st, 2019 at 5:29 pm

Posted in Linux,MySQL,MySQL 8,pgAdmin4,Postgres,Python,Python 2.7,Python 3.x

Tagged with , , ,

« Older Entries
Newer Entries »