# Error with None/NULL Parameters While Converting the Document Management Application to PostgreSQL

**URL:** <https://forum.inductiveautomation.com/t/error-with-none-null-parameters-while-converting-the-document-management-application-to-postgresql/100758>\
**Category:** Ignition\
**Tags:** database, perspective, scripting\
**Created:** [January 11, 2025, 7:49am UTC](https://forum.inductiveautomation.com/t/error-with-none-null-parameters-while-converting-the-document-management-application-to-postgresql/100758 "2025-01-11T07:49:53Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![MESConsultant](https://avatars.discourse-cdn.com/v4/letter/m/35a633/32.png) [@MESConsultant](https://forum.inductiveautomation.com/u/MESConsultant)\
**Post date:** [January 11, 2025, 7:49am UTC](https://forum.inductiveautomation.com/t/error-with-none-null-parameters-while-converting-the-document-management-application-to-postgresql/100758/1 "2025-01-11T07:49:53Z")

</div>

Hello,

I’m trying to adapt a “Document Management” solution from Ignition Exchange to PostgreSQL. In the current setup, some queries can pass a “NULL” value to the parentId column of type INT. When folder.id in Python is None, I send it to the Named Query, but the PostgreSQL driver throws this error:

"java.lang.Exception: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1"

```python
def createFolder(self, name):
	import re
	folder = self.session.custom.filters.folder
	system.perspective.print(folder.id)
	if name == "":
		message("Please provide a folder name")
	elif not re.match("^[\s\w\d_-]*$", name):
		message("Folder contains invalid characters, only letters, numbers, spaces, underscores, and dashes allowed")
	elif system.db.runNamedQuery(path="Document Management/Folders/Exists", parameters={"parentId":folder.id, "name":name}):
		message("Folder '%s' already exists. Please try another." % name)
	else:
		system.db.runNamedQuery(path="Document Management/Folders/Add", parameters={"parentId":folder.id, "name":name})
		system.perspective.closePopup(id="folder-create")
		refreshFolders()
		refreshDocuments()

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cbb5cc5333b104705438bc8ecf0907b378259de3.png)

Am I using it incorrectly, or is there no way to send a null value to an integer field in PostgreSQL? Should I consider alternative solutions?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 11, 2025, 10:04am UTC](https://forum.inductiveautomation.com/t/error-with-none-null-parameters-while-converting-the-document-management-application-to-postgresql/100758/2 "2025-01-11T10:04:40Z")

</div>

I've never used PostgreSQL, but I'd check that the table's parentId column is nullable (can accept NULL values).

The tutorial on [PostgreSQL DESCRIBE TABLE](https://neon.tech/postgresql/postgresql-administration/postgresql-describe-table) says that you can query the information\_schema:

```python
SELECT
  column_name,
  data_type,
  character_maximum_length,
  is_nullable,
  column_default
FROM
  information_schema.columns
WHERE
  table_name = 'myTable';

```

According to the StackOverflow question, [sql - How to change a PG column to NULLABLE TRUE? - Stack Overflow](https://stackoverflow.com/questions/4812933/how-to-change-a-pg-column-to-nullable-true), you can modify the table as follows:

```python
ALTER TABLE mytable 
    ALTER COLUMN mycolumn 
    DROP NOT NULL;

```

I would expect that both of those queries could be run from Designer's Named Query editor if you have permissions.

---

<div class="post-metadata">

**Author:** ![MESConsultant](https://avatars.discourse-cdn.com/v4/letter/m/35a633/32.png) [@MESConsultant](https://forum.inductiveautomation.com/u/MESConsultant)\
**Post date:** [January 11, 2025, 10:50am UTC](https://forum.inductiveautomation.com/t/error-with-none-null-parameters-while-converting-the-document-management-application-to-postgresql/100758/3 "2025-01-11T10:50:41Z")

</div>

```python
CREATE TABLE document_folders (
	id serial4 NOT NULL,
	"name" varchar(200) NOT NULL,
	parentid int4 NULL,
	CONSTRAINT document_folders_pkey PRIMARY KEY (id),
	CONSTRAINT fk_document_folders_1 FOREIGN KEY (parentid) REFERENCES mes.document_folders(id)
);
CREATE INDEX fk_document_folders_1_idx ON mes.document_folders USING btree (parentid);
CREATE INDEX idx_document_folders_1 ON mes.document_folders USING btree (name);

```

I checked table and parentid column is nullable.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/9/c963ce1152e99f7f6634cccf2bb1b169b38865d8.png)d

And also entered same value on that table. In designer it is not accept null value or None value on integer field same as MSSQL. But MSSQL accept None value in the script side.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 11, 2025, 10:59am UTC](https://forum.inductiveautomation.com/t/error-with-none-null-parameters-while-converting-the-document-management-application-to-postgresql/100758/4 "2025-01-11T10:59:50Z")

</div>

That all looks OK then.  
Can you show us your named query SQL?

---

<div class="post-metadata">

**Author:** ![MESConsultant](https://avatars.discourse-cdn.com/v4/letter/m/35a633/32.png) [@MESConsultant](https://forum.inductiveautomation.com/u/MESConsultant)\
**Post date:** [January 11, 2025, 11:33am UTC](https://forum.inductiveautomation.com/t/error-with-none-null-parameters-while-converting-the-document-management-application-to-postgresql/100758/5 "2025-01-11T11:33:18Z")

</div>

Of course I'm adding it.

```python
SELECT 
	COUNT(*)
FROM 
	document_folders f
WHERE 
	((:parentId IS NULL AND f.parentid IS NULL) OR (:parentId IS NOT NULL AND f.parentid = :parentId)) AND 
	f.name = :name

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/b/fbfb1b39b6694ad37f65802f7293462a417e6fa4.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 11, 2025, 12:46pm UTC](https://forum.inductiveautomation.com/t/error-with-none-null-parameters-while-converting-the-document-management-application-to-postgresql/100758/6 "2025-01-11T12:46:13Z")

</div>

Based on [postgresql - Postgres Sql `could not determine data type of parameter` by Hibernate - Stack Overflow](https://stackoverflow.com/a/64223435/3934992), try changing the first OR branch of your query from

```python
WHERE
  (
    (:parentId IS NULL AND f.parentid IS NULL) 
    OR 
    (:parentId IS NOT NULL AND f.parentid = :parentId)) 
    AND 
    f.name = :name
  )

```

to

```python
WHERE
  (
    (cast(:parentId AS INT) IS NULL AND f.parentid IS NULL) 
    OR 
    (:parentId IS NOT NULL AND f.parentid = :parentId)) 
    AND 
    f.name = :name
  )

```

(Again, I don't know PostgreSQL!)

---

<div class="post-metadata">

**Author:** ![MESConsultant](https://avatars.discourse-cdn.com/v4/letter/m/35a633/32.png) [@MESConsultant](https://forum.inductiveautomation.com/u/MESConsultant)\
**Post date:** [January 11, 2025, 4:04pm UTC](https://forum.inductiveautomation.com/t/error-with-none-null-parameters-while-converting-the-document-management-application-to-postgresql/100758/7 "2025-01-11T16:04:19Z")

</div>

Thank you for your help. I don't understand what the exatctly issue is but this work.

```python
SELECT COUNT(*)
FROM document_folders f
WHERE 
    f.parentid IS NOT DISTINCT FROM :parentId::INTEGER
    AND f.name = :name

```