How to know which columns have not null constraint in postgres via php?

Question

Im trying to build some html input elements via php in order to fill up rows in a postgres table.

I want to add the attribute "required" to some of the html input elements (those that correspond to the columns that have the NOT NULL constraint).

How can I know which columns have that constraint?

Depends on the DB engine you use (PDO, pg_* functions, etc. etc.), but I wonder why would you want to do that during runtime, unless you're building some sort of a DB web editor. — Sergey Vidusov, Feb 17 '16 at 03:31
Im trying to make a web page that gives the user the possibility to add rows to a table.. I expect more columns to be added to the table (with and without the not null contraint, that's why I want to do it programmatically). — randomDude, Feb 17 '16 at 03:35

score 0 · Accepted Answer · edited May 23 '17 at 12:15

0

Using PDO (and suggestion here), you should be able to pull it off as follows:

$q = $dbh->prepare("\d tablename");
$q->execute();
$table_fields = $q->fetchAll();
foreach ($table_fields as $field) {
    if (strpos($field['Modifiers'], 'not null') !== FALSE) {
         // this column ($field['Column']) has a "not null" constraint.
    }
}

Edit: same if you're dead set on using PostgreSQL PHP extension:

$q = pg_query("\d tablename");
while ($row = pg_fetch_array($result)) {
    if (strpos($row['Modifiers'], 'not null') !== FALSE) {
         // this column ($row['Column']) has a "not null" constraint.
    }
}

edited May 23 '17 at 12:15

Community

1
1

answered Feb 17 '16 at 03:43

Sergey Vidusov

1,342
1
7
10

Thanks, but isn't there a way to accomplish it with pg_* functions? or even with SQL commands in psql? Sorry for not being specific... – randomDude Feb 17 '16 at 04:01
I've edited my answer to include the pg_* functions. As for the functions specific to this particular purpose, they don't exist as far as I'm aware. – Sergey Vidusov Feb 17 '16 at 04:15

score 0 · Answer 2 · answered Feb 17 '16 at 15:21

Use Pomm and it will perform those queries for you to inspect your database with the CLI:

$ php vendor/bin/pomm.php pomm:inspect:relation test pika_chu
Relation public.pika_chu
+----+---------------+--------+-----------------------------------------------+---------+---------+
| pk | name          | type   | default                                       | notnull | comment |
+----+---------------+--------+-----------------------------------------------+---------+---------+
| *  | pika_chu_id   | int4   | nextval('pika_chu_pika_chu_id_seq'::regclass) | yes     |         |
|    | some_data     | int4   |                                               | yes     |         |
|    | nullable_data | bpchar |                                               | no      |         |
+----+---------------+--------+-----------------------------------------------+---------+---------+

How to know which columns have not null constraint in postgres via php?

2 Answers2