Is there anyway to tell the datatype in a postgres array?

Question

I am using a Postgres 9.4 database and have PHP as my front end.

A general query I may run would look like this:

PHP :

$query = "select * from some_table";
pg_prepare($connection,"some_query",$query);
$result = pg_execute($connection,"some_query",array());

while ($row = pg_fetch_array($result,null,PGSQL_ASSOC)) {
    echo $row['some_field'];
    echo $row['some_field_1'];
    echo $row['some_field_2'];
}

I am running into a front-end that requires to know the datatype of the column that spits out - specifically I need to know when the echo'd database field is a timestamp column.

Obviously I can tell integers and string, however timestamp is a bit of a different thing.

I suppose I could see if strtotime() returns false, however that seems a little dirty to me.

So my question is:

Is there a PHP built-in function that can return a multi-dimensional array of the database row with not only $key=>$value pair but also the datatype?

Any help on this would be appreciated - thank you!

There are queries to get this and there is also php.net/manual/en/function.pg-field-type.php — AbraCadaver
– AbraCadaver, Commented Aug 10, 2015 at 17:15
@AbraCadaver Please post that with a little elaboration as an answer. — Craig Ringer
– Craig Ringer, Commented Aug 11, 2015 at 3:08

AbraCadaver · Accepted Answer · 2015-08-11 03:19:22Z

1

You can query from information_schema.columns and fetch just like any other query:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name='some_table'

Or after your query use pg_field_type():

$type = pg_field_type($result, 0);

But you need to know the position of the column in the result so you should (best practice anyway) list the columns. For the above case using 0 would give the type of col1 in the query below::

SELECT col1, col2, col3 FROM some_table

answered Aug 11, 2015 at 3:19

AbraCadaver

79.2k7 gold badges75 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is there anyway to tell the datatype in a postgres array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related