Create table IF NOT EXIST

it should go like this...

if select count(*) from pg_class where relname = 'mytable' is NULL THEN CREATE table table

if not check this link

http://www.experts-exchange.com/Database/PostgreSQL/Q_22889365.html

Thanlks for the quick response....
Hmm... Object_id not recognized as a function..  Do you know of anything that exists from the ODBC API?  (anything from odbc32.lib)?  Wouldn't this function need to be in the context of some given database?  Where are you calling Object_id from?

shasunder, that solution looks like the right one, works from the shell when I execute it by hand, but programmatically from ODBC, when I bind a variable to relname and call SQLFetch, I get SQL_NO_DATA back (when in fact, the table exists).  I'm using psqlodbc version 8.3.1.  Any ideas?

Thanks!

why don't they implement a CREATE TABLE IF NOT EXIST in postgres like in MySQL?  It would make things easier for me.....   :-)  

Hi,

Just a stupid solution:
Run a select from odbc:
select count(*) from are_you_sure_is_there_table;
then catch the error, and add the table.
If there is no error, it means the table exists.

Cheers,
za-k/

I agree adrpo, not the best solution but you may end up winning the points since this would be the only thing that would work so far out of all the solutions.....  ;-)

SELECT count(*) from information_schema.tables WHERE table_name = 'my_table';

That will give you the count of matching tables.  You can also optionally confirm the table_schema if you have tables of the same name in different schemas.

The good thing about using information_schema is that it's part of the SQL standard.  Here's a link to the documentation on the information_schema:

http://www.postgresql.org/docs/8.2/static/information-schema.html

There are several other useful views in that schema.

Hi wide_awake,
This also works in the shell, but not when I call it from a program using ODBC.  Something is wrong somewhere - I'm wondering if it could be the driver?  I'm connecting to the same database as the same user...  
Thanks,
Mike

Hi again...

Here is how you do it in ODBC ....
I just try it, so it works.

    Dim rsTables As ADODB.Recordset
    Set rsTables = cn.OpenSchema(adSchemaTables)
    
    Do Until rsTables.EOF
        If rsTables!TABLE_NAME = "YOUR_TABLE_NAME" Then
           Debug.Print "Table:" & rsTables!TABLE_NAME & " exists!" & vbCrLf
        End If
        rsTables.MoveNext
    Loop
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Hi adrpo,

I'm using the psqlODBC driver for Visual C++ - the one where your calls would have to use SQLPrepare, SQLBincCol, SQLFetch, etc...  That's maybe where the problem is - if this psqlODBC driver....  Not sure what's going wrong...

Mike

BOOL CMyClass::TableExists(CString strTableName, CDatabase * pDatabase, HSTMT hStatement)
{
	CString strStatement;
	strStatement.Format("SELECT count(*) from information_schema.tables WHERE table_name = '%s'), strTableName);
	if(!ExecuteStatement(strStatement))
	{
		CString S;
		S.Format("Error:  Could not run query from DoesTableExist:\r\n\r\n%s", strStatement);
		::MessageBox(NULL, S, "Error", MB_ICONWARNING);
		return FALSE;
	}
 
	SQLINTEGER nCount, cbCount;
	SQLBindCol(hStatement, 1, SQL_C_ULONG, &nCount, 0, &cbCount);
	RETCODE rc = SQLFetch(hStatement);
	if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
		return nCount > 0;
	else
	{
		CString S;
		S.Format("Error:  Could not fetch results in DoesTableExist: \r\n\r\n%s", strStatement);
		::MessageBox(NULL, S, "Error", MB_ICONWARNING);
		return FALSE;
	}
}
 
BOOL ExecuteStatement(CString strStatement, BOOL bReportErrors)
{
	unsigned char * QUERY = (unsigned char *) strStatement.GetBuffer(MAX_PATH);
	strStatement.ReleaseBuffer();
	RETCODE rc = SQLPrepare(m_hStatement, QUERY, SQL_NTS);
	if(!MYSQLSUCCESS(rc))
	{
		if(bReportErrors)
			DisplayErrorMessageStatementHandle(m_hStatement);
		return FALSE;
	}
	else
	{
		rc = SQLExecute(m_hStatement);
		if(!MYSQLSUCCESS(rc))
		{
			DisplayErrorMessageStatementHandle(m_hStatement);
			return FALSE;
		}
	}
	
	return TRUE;
}
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:

Your string on line 4 (the SQL string) is not closed.  Maybe that's your problem.

If that's not it, what is the error you're getting?

More info about SQLTables:
http://msdn2.microsoft.com/en-us/library/ms131341.aspx

Cheers,
za-k/

Thank you adrpo!  It was the SQLTables() call that I did not know about.  Thanks for the nice long answer - made it very easy for me - very appreciated!  Cheers!