Access a specific schema in a PostgreSQL database via PHP

Question

I have a PostgreSQL database userdb with 5 schemas.

Schema 1- Persons
Schema 2- Project
Schema 3- Shop
Schema 4- Test

I was able to connect to the database using pg_connect. How do I access a specific schema in that database?

I was able to connect to a schema when there was only one in the database. But now since I have multiple schemas I am having difficulty in accessing any specific one.

 <?php
 // attempt a connection
 $dbh = pg_connect("host=**.****.*******.*** dbname=test user=merlin port=5433 password=passw123");
 if (!$dbh) {
     die("Error in connection test: " . pg_last_error());
 } 
// execute query
 $sql = "SELECT * FROM test.country";
 $result = pg_query($dbh, $sql);
 if (!$result) {
     die("Error in SQL query: " . pg_last_error());
 }       

 // iterate over result set
 // print each row
 while ($row = pg_fetch_array($result)) {
     echo "Country code: " . $row[0] . "<br />";
     echo "Country name: " . $row[1] . "<p />";
 }       

 // free memory
 pg_free_result($result);       

 // close connection
 pg_close($dbh);      
?>

it gives me this error: Error in SQL query: ERROR: relation "aircraft" does not exist at character 15. Aircraft is a table in one of my schemas. — begin.py
– begin.py, Commented Apr 19, 2013 at 16:45
Is the name of the table aircraft or "Aircraft"? More about mixed case identifiers here: stackoverflow.com/questions/8792912/… — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 20, 2013 at 17:10

Clodoaldo Neto · Accepted Answer · 2013-04-19 17:22:22Z

6

Qualify the table with the schema name

select *
from my_schema.aircraft

answered Apr 19, 2013 at 17:22

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

begin.py Over a year ago

I am getting this error - Error in SQL query: ERROR: schema "aircraft" does not exist at character 15

sdfor · Accepted Answer · 2016-06-30 18:36:51Z

3

use:
SET search_path TO myschema; or

SET search_path TO myschema, myschemab;

https://www.postgresql.org/docs/9.4/static/ddl-schemas.html

answered Jun 30, 2016 at 18:36

sdfor

6,51816 gold badges54 silver badges62 bronze badges

Comments

Erwin Brandstetter · Accepted Answer · 2022-01-19 09:05:53Z

3

Schema-qualify the table name as Clodoaldo already advised. Or set the search_path for a permanent effect. It works much like a search path in the file system.

How permanent depends on how you set it. See:

How does the search_path influence identifier resolution and the "current schema"

edited Jan 19, 2022 at 9:05

answered Apr 19, 2013 at 19:03

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

1 Comment

begin.py Over a year ago

@Clodoaldo 's method doesnt seem to work for some reason. my php code. And ive only just begun SQL n PHP so I dint understand you procedure. I am editing my question to incorporate my code now.

Collectives™ on Stack Overflow

Access a specific schema in a PostgreSQL database via PHP

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related