Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE foo (id int);

CREATE TABLE

-- use default search_path
SET search_path = "$user",public;

SELECT current_setting('search_path')               -- the bare setting
     , current_schemas(FALSE) AS effective_schemas  -- the effective setting
     , current_schemas(TRUE)  AS schemas_plus_implicit;

SET

current_setting
effective_schemas
schemas_plus_implicit

"$user", public
{public}
{pg_catalog,public}

SELECT 1

-- create schema named after session user, and try again
-- not allowed on dbfiddle.uk! try at home ...
DO
$do$
BEGIN
   EXECUTE 'CREATE SCHEMA ' || quote_ident(SESSION_USER);  -- create schema for current
END
$do$;

SELECT current_setting('search_path')
     , current_schemas(FALSE) AS effective_schemas
     , current_schemas(TRUE)  AS schemas_plus_implicit;

DO

current_setting
effective_schemas
schemas_plus_implicit

"$user", public
{u_1476741582,public}
{pg_catalog,u_1476741582,public}

SELECT 1

-- include non-existing schema and temporary schema
SET search_path = non_existent_schema,public,pg_temp;

SELECT current_setting('search_path')
     , current_schemas(FALSE) AS effective_schemas
     , current_schemas(TRUE)  AS schemas_plus_implicit;

SET

current_setting
effective_schemas
schemas_plus_implicit

non_existent_schema, public, pg_temp
{public}
{pg_catalog,public}

SELECT 1

-- trigger creation of temp schema
CREATE TEMP TABLE foo_tmp (id int);

SELECT current_setting('search_path')
     , current_schemas(FALSE) AS effective_schemas
     , current_schemas(TRUE)  AS schemas_plus_implicit;

CREATE TABLE

current_setting
effective_schemas
schemas_plus_implicit

non_existent_schema, public, pg_temp
{public,pg_temp_3}
{pg_catalog,public,pg_temp_3}

SELECT 1

-- quick and dirty method
SELECT 'foo'::regclass       -- exists
     , 'foo_tmp'::regclass;  -- exists too! but in temp schema, so misleading!

regclass
regclass

foo
foo_tmp

SELECT 1

-- slow and sure
SELECT 'foo' AS table_name, EXISTS (
   SELECT  -- can be empty
   FROM   pg_catalog.pg_class c
   JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
   WHERE  n.nspname = ANY(current_schemas(false))
   AND    n.nspname NOT LIKE 'pg_%'   -- exclude system schemas!
   AND    c.relname = 'foo'
   AND    c.relkind = 'r'             -- only regular tables
   );

table_name
exists

foo
t

SELECT 1

-- example with non-existing table - raises exception!
SELECT 'bar'::regclass;  -- does not exist

ERROR:  relation "bar" does not exist
LINE 2: SELECT 'bar'::regclass;  -- does not exist
               ^