Archive for the ‘PostgreSQL’ Category
DBeaver for PostgreSQL
I’m migrating my database classes from the Oracle database to the PostgreSQL database. Using the Oracle Express Edition has always required a virtualized image because students use Windows and Mac OS. Also, the university doesn’t like my use of a virtualized image. Virtualization imposes incremental cost on students to have high end laptops.
The available Docker images don’t typically support the Oracle Express Edition. That means there are licensing implications tied to Oracle.
As a committee, we decided to use PostgreSQL as our new database platform. We opted to use PostgreSQL over MySQL because it supports arrays and stored procedures. PostgreSQL PL/pgSQL are a solid implementation of stored procedures. While MySQL supports a stored procedure language, it effectively discourages using it.
We use sqlplus, mysql, or psql Command-Line Interface (CLI) as the primary interface. That’s because CLI supports production-oriented deployment and DevOps practices. The Open Source DBeaver project provides a clean native GUI management console for PostgreSQL. DBeaver also supports other databases, which pgAdmin doesn’t.
You click the Download button from the DBeaver home page. It support Windows, Mac OS, and Linux distributions. I downloaded the Linux RPM package to my Downloads directory. You can run the RPM with the following syntax:
rpm -ivh dbeaver-ce-6.2.3-stable.x86_64.rpm |
It doesn’t check for dependencies. The installation is quick. You can verify that it’s installed with the following command:
which dbeaver |
It installs here:
/usr/bin/dbeaver |
If you failed to install the PostgreSQL JAR file, you’ll see the following error message:
It will look like the following on Linux:
You should have a user in the pg_hba.conf file, like the following:
host all all 127.0.0.1/32 trust |
As always, I hope this helps those solving problems.
Java and Postgres
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.
Postgres Drop Structures
While building my PostgreSQL environment for the class, I had to write a couple utilities. They do the following:
- Drops all the tables from a schema.
- Drops all the sequences from a schema that aren’t tied to an
_idcolumn with aSERIALdata type. - Drops all the functions and procedures (qualified as routines) from a schema.
- 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:
- The drop_sequences.sql script:
- The drop_routines.sql script:
- The drop_triggers.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'; |
/* 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'; |
/* 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'; |
/* 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.
Postgres Print Debug Notes
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.
Postgres SQL Nuance
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.
Postgres Foreign Constraints
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:
- Drop the
bigandlittletable if they exists. The firstdropstatement requires a cascade because there is a dependentlittletable that holds a foreign key constraint against the primary key column of thebigtable. 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;
- Create the
bigandlittletables:-- 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_idcolumn as a primary key constrained, Postgres will raise the following exception:ERROR: there IS no UNIQUE CONSTRAINT matching given KEYS FOR referenced TABLE "big"
- Insert a non-compliant row in the
littletable. An insert statement into thelittletable with a value for the foreign key column that does not exist in thebig_idcolumn of thebigtable 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
bigtable with the foreign key value for thelittletable as its primary key. The following two insert statements add a row to both thebigandlittletable:-- 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)
- You can drop a foreign key constraint with the following syntax:
ALTER TABLE little DROP CONSTRAINT fk_little_1;
- 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.
Postgres Check Constraints
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:
- Drop a demo table if it exists:
DROP TABLE IF EXISTS demo;
- Drop a demo table if it exists:
CREATE TABLE demo ( demo_id SERIAL , demo_text VARCHAR(20) CONSTRAINT nn_demo_1 NOT NULL );
- Create a
demotable 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_columnname and the system generatedcheckconstraint name rather than thenn_demo_1constraint name:check_column | constraint_name --------------+----------------------- demo_text | 2200_18896_2_not_null (1 row)
On the bright side, you can name primary key and foreign key constraints.
Session Variables
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.
- You set a session variable on a single line with the following command:
SET @my_variable_name := 'My Value';
- You can query a variable from the pseudo table
dualor as a comparison value in theSELECT-listSELECT @my_variable_name AS "The Value" FROM dual;
or
WHEREclauseSELECT 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.
- 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';
- You can query a variable from the pseudo table
dualor as a comparison value in theSELECT-list with thecurrent_setting()function call.SELECT current_setting('videodb.table_name') AS "The Value";
or
WHEREclauseSELECT 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.
- You set a session variable by using the
VARIABLEkeyword, a variable name, and data type. The supported data types are:BLOB,BFILE,BINARY_DOUBLE,BINARY_FLOAT,CHAR,CLOB,NCHAR,NCLOB,NVARCHAR2,REFCURSOR, andVARCHAAR2. You define a variable with the following syntax:VARIABLE bv_variable_name VARCHAR2(30)
- 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 aBEGINand end with anENDfollowed 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; /
- You can query any declared variable from the pseudo table
dualor as a comparison value in theSELECT-listSELECT :bv_variable_name FROM dual;
or
WHEREclauseSELECT column_name FROM table_name WHERE column_name = :bv_variable_name;
Python-Postgres Query
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.
Postgres & Sequences
Many developers already know about the change from int to serial in Postgres. Serial is simpler because it automatically makes the column not null and links it to a sequence. The sequence is created with the following naming convention:
table_name_column_name_seq |
The Postgres serial type always has a start with value of 1. Though, you can alter it immediately after creating the table.
You can create a serial surrogate key column like this:
CREATE TABLE new_hire ( new_hire_id SERIAL CONSTRAINT new_hire_pk PRIMARY KEY , first_name VARCHAR(20) NOT NULL , middle_name VARCHAR(20) , last_name VARCHAR(20) NOT NULL , hire_date DATE NOT NULL , UNIQUE(first_name, middle_name, hire_date)); |
If you want the sequence to start with 1001, you can ALTER the sequence after creating the table. Before you change the sequence staring value, you may want to confirm the automatic name assigned to the sequence. You can do that with the following query:
SELECT pg_get_serial_sequence('new_hire','new_hire_id'); |
It should return:
pg_get_serial_sequence --------------------------------- public.new_hire_new_hire_id_seq (1 row) |
Here’s the syntax for changing the current starting value of a Postgres sequence:
ALTER SEQUENCE new_hire_new_hire_id_seq RESTART WITH 1001; |
If you want more control, you still have it with the older syntax. The older way requires that you create the sequence before you create the table, like this:
CREATE SEQUENCE new_hire_s START WITH 1001; |
Then, you create the new_hire table like this:
CREATE TABLE new_hire ( new_hire_id INT NOT NULL DEFAULT NEXTVAL('new_hire_s') , first_name VARCHAR(20) NOT NULL , middle_name VARCHAR(20) , last_name VARCHAR(20) NOT NULL , hire_date DATE NOT NULL , PRIMARY KEY(new_hire_id) , UNIQUE(first_name, middle_name, hire_date)); |
You probably will test this from a file because tables are tedious type and usually much larger than the sample new_hire table. If you wonder how to do that, typing help from the psql> prompt will not reveal it. The key is the \i command, like this:
\i new_hire.sql |
Unfortunately, the psql> command-line shell does not allow for embedding a means to log the activity of your script file. You must do that from the Linux command-line through redirecting the console to a log file.
The rules are pretty simple for logging file results:
-a, --echo-all echo all input from script -b, --echo-errors echo failed commands -e, --echo-queries echo commands sent to the server |
Here’s an example connecting to a videodb database with a student user:
psql -d videodb -U student -W -f postgres_tables.sql -a &> postgres_tables.txt |
As always, I hope this helps those looking for how to solve the puzzle of a new environment.
Subscribe via RSS