How to store BOOL values

questions
1

Another clueless newbie question: What is the best data type to store boolean values? I want to create a soft delete flag (True if deleted, False if not) and was wondering what data type will work best with indexes and such. I will be using a PostgreSQL database, if that makes any difference

2

Postgres uses 1 byte to store Boolean, so BYTE type on Clarion side is good.

1 Like
3

If I import a PostgreSQL boolean field into the Clarion dictionary it shows up as a 6-character field, which is weird. It works, but it’s weird. And Clarion shows its value as a 0 or 1 which is hardly user-friendly. I’m thinking of using a STRING(1) or CHAR(1) instead, but I don’t know how it will behave if I edit it with a checkbox.

4

With Postgres, I usualy use a NUMERIC(1,0) NOT NULL DEFAULT(0), and a BYTE in the dictionary. It works very well.

3 Likes
5

Thanks. are there any other PostgreSQL tricks like that you’d like to share? I’m always willing to learn.

6

Some things that I do for avoiding problems:

  1. I never let Clarion create a table in a sql database. I always create tables in the database with a script and then import the table into the dictionary.
  2. When I need to change the structure of a table, I always add the new fields at the end of the structure and I add them manually in the dictionary. The tric is to be sure that the definition in the dictionary match exactly the structure of the table in the database. Easier when you use SELECT * in a prop:sql statement.
  3. When I need a large text field (aka memo), I don’t put it into the table itself but in a special text table with a link to his record.
2 Likes
7

My 2 cents:

a) Use a Clarion BYTE for a PG boolean
b) Avoid using SELECT * for queries to stay away from field ordering errors.
c) Define data integrity checks in PG not Clarion code.
d) Learn to use PROP:Alias w/ SQL strings for setting some Clarion browse filters
e) Plan to add critical PG views to your Clarion DCT
f) Learn to use PG Before/After/Instead triggers
g) Know the operational differences between NULL and Clarion zero, nil values
h) Learn when to use PG is distinct from rather than var1 = var2

3 Likes
8

For flags in postgres, well clarion in general now, I use a Character(1) for a Flag and store Y/N The bonus with this is the field can be used as is in a browse and it makes sense to the user.

Also in Postgres setup postgresql.conf you might want to check on transform_null_equals
See https://www.postgresql.org/docs/current/runtime-config-compatible.html#GUC-TRANSFORM-NULL-EQUALS

1 Like
9

I would go with Guy’s suggestion. My guess on the Boolean type is compatibility with dBase, where values in a Boolean could include True, False, T,F as well as 1 and 0. So allowing those values in a postgres variable could ease conversion (but complicate everything else).

1 Like
10

Just to confirm, a PostgreSQL boolean can indeed be queried using:
True / False
‘T’ / ‘F’
‘1’ / ‘0’

1 Like