sql数据类型的类型是否会对sql查询的组成产生影响？

Question

I have written some sql queries to generate data from a postgresql database. When I try to search for a word (for example work) It works but if I carry out the same for a bigint (for example 45), I got no result. I got the following error: Warning: pg_num_rows() expects parameter 1 to be resource, boolean given in require(). See fiddle for the database Could it have something to do with the fact it's a bigint? Has it to be defined differently.

$table = "test";
$field="\"number\"";
$text = "45"; # $text = 45;
$query = "SELECT DISTINCT \"number\", \"word\", \"sentence\", lower(\"sentence\") FROM \"" . $table . "\" WHERE ";
$query = $query . $field . " ~* '" . $text . "' ";
echo $query;

$recordset = pg_query($connection, $query);
if($recordset)
{
    $row = pg_numrows ($recordset);
    var_dump($row);
}

Definition of table test:

CREATE TABLE "test" (
  "id" SERIAL,
  "number" BIGINT NOT NULL,
  "word" VARCHAR,
  "sentence" VARCHAR
);
INSERT INTO test (id, number, word, sentence) VALUES
    (1, "451", 'make', 'I will make you great'),
    (2, "45", 'new', 'Is this new ?'),
    (3, "57", 'work', 'I always work very hard'),
    (4, "48", 'sell', 'Are you going to sell your house'),
    (5, "781", 'feel', 'Do you feel well?')

Answer 1

The SQL fiddle where you say it works uses MySQL, so it is irrelevant here.

This does not work and causes an error, because PostgreSQL does not have implicit type casts from numeric to string data types and vice versa. The reason is that with function and operator overloading, this would create ambiguities and surprising behavior in certain cases.

The solution for you is to use a type cast:

... WHERE CAST(number AS text) ~* '^45.*'