Npgsql Exception- syntax error at or near "\"

Question

I am trying to execute postgresql query (\list) to get the list of databases using npsql.

string postgresQuery = @"\list"; 
            ICollection<Schema> schemas = new List<Schema>();
            string ConnectionString =
"Host=xxx;Username=postgres;Password=admin;Database=test;";
            NpgsqlConnection conn = new NpgsqlConnection(ConnectionString);
            NpgsqlCommand cmd = new NpgsqlCommand(postgresQuery, conn);
            conn.Open();
   NpgsqlDataReader dataReader = cmd.ExecuteReader();

While executing the query facing an issue that "syntax error at or near "\" ". The string contains double-slash as follow (\list) instead of single-slash (\list). Please let me know if there is any solution for this. Thanks in advance.

`\list` isn't a valid SQL query. It's a *command* of the `psql-tool` — Panagiotis Kanavos, Jul 06 '17 at 12:44
Possible duplicate of [Show tables in PostgreSQL](https://stackoverflow.com/questions/769683/show-tables-in-postgresql) — Panagiotis Kanavos, Jul 06 '17 at 12:47

Panagiotis Kanavos · Answer 1 · 2017-07-07T07:38:04.280

1

\list isn't a SQL query. It's is a command of the psql-tool.

To get a list of all database with SQL, use the following query:

SELECT datname FROM pg_database
WHERE datistemplate = false;

Check How do I list all databases and tables using psql?

The fields of the pg_database table are described here

The database's owner is stored in the datdba field which references the pg_authid table. It's possible to find all databases owned by a user, as shown in How to list databases owned by rolename in postgresql with

SELECT datname 
FROM pg_database 
JOIN pg_authid ON pg_database.datdba = pg_authid.oid 
WHERE rolname = 'username'

Of course, the query should be parameterized:

SELECT datname 
FROM pg_database 
JOIN pg_authid ON pg_database.datdba = pg_authid.oid 
WHERE rolname = :username

pg_authid is described here

edited Jul 07 '17 at 07:38

answered Jul 06 '17 at 12:51

Panagiotis Kanavos

120,703
13
188
236

Thanks for the update. This lists all the databases. But I need only the databases which are all created by the user not all the databases in the PostgreSQL.Please let me know is it possible to get the only the databases created by the user. – mRhNs13 Jul 07 '17 at 07:24
That's the `datdba` field. You can use this as a filter in the query, or join with the `pg_authid` table. . Check the [pg_database](https://www.postgresql.org/docs/9.4/static/catalog-pg-database.html) and [pg_authid](https://www.postgresql.org/docs/9.4/static/catalog-pg-authid.html) documentation pages – Panagiotis Kanavos Jul 07 '17 at 07:27

Npgsql Exception- syntax error at or near "\"

1 Answers1