the sql statement in postgres, is the prefix "public" necessary?

Navigation
1

I am working with PHP and PDO connecting to a postgresql database, I have a doubt, since something that I do not expect is happening. 

$pdo = new PDO('pgsql:dbname=db_pdo_postgresql; 
                            host=localhost;', 'juan_postgres', 'root');
        foreach($pdo->query('SELECT * from user') as $fila) {
            if($fila==null){
                echo "Vacio";

            }else{
                echo "<pre>";
                print_r($fila);
            }
        }
        $pdo = null;

That's the code, and the result is this. 

Array
(
    [current_user] => juan_postgres
    [0] => juan_postgres
)

the user returns me practically. and if I change the query 

SELEC * from public.user

He returns me. 

Array
(
    [ID] => 1
    [0] => 1
    [name] => Laura
    [1] => Laura
)

    Array
    (
        [ID] => 2
        [0] => 2
        [name] => Juan
        [1] => Juan
    )

The case, is that I do not want you to be adding the public prefix, in each sql statement. since when changing BD, you would have to modify the SQL. and that would not be the case, for the use of PDO. thanks

    
asked by JuanL 18.02.2018 в 14:00
source

1 answer

1

Generally when you connect to postgresql you can access any table to which the user with whom you have logged in has access privileges.

Now, if two tables are called equal , as is the case here, it is logical that you should specify using schema , which one you want to connect to. What was happening in your case is that the query was bringing the data from the postgresql user table.

To avoid these confusions I would recommend that you do not call your tables with names that are reserved words from postgresql. Such a practice, apart from leading you to confusions like the current one, could put the data at risk, for example, exposing the access credentials of the users of the database itself, or allowing to modify the access code of the administrator of the database of data or a user with editing privileges. As you will understand, this could lead to very delicate situations due to a confusion between the table users of the same database and a possible table users that would be part of a specific application.

If anyway, for some reason two tables have to be called equal and are in two schema different, you can set the schema by default by search_path . That way, you will not be forced to write the schema in each query.

Suppose the following scenario:

▸ In Postgresql 

schema_app1
    tabla1
    tabla2

schema_app2
    tabla1
    tabla2

▸ In an application app1

If when you connect to PDO in the app1 you do this: 

$pdo->exec('SET search_path TO schema_app1');

And then you do: 

SELECT * FROM tabla1;

You will be referencing the tabla1 of schema_app1 . That is: schema_app1.tabla1 .

▸ In an application app2

If when you connect to PDO in the app2 you do this: 

$pdo->exec('SET search_path TO schema_app2');

And then you do: 

SELECT * FROM tabla1;

You will be referencing the tabla1 of schema_app2 . That is, it is as if you were doing: schema_app2.tabla1 .

    
answered by 19.02.2018 / 02:40
source
Find values from a list in a csv by column Python Systems of differential equations