Importing Data Dump into Postgres

I needed to take four steps to import an Oracle binary dump into PostgreSQL Open Oracle dump with something like notepad++ or grap and do a find/replace for all the binary characters (these can be highlighted in notepad++) and replace them with something like a pipe character (“|”) If you know the special characters that…

there are so many options with pear’s db library for php, I sometimes forget the optimal code for getting a sql result as an array that I can easily use within php. The code below fully demonstrates connecting to and getting an associative array with an ‘order by’ field as the array key. This code…

To do a non-boolean conditional insert in SQL Server (the “where exists” clause is best for boolean), use the “where in” clause along with an inner join, in the following form: INSERT INTO [Destination Table]([Destination Field1],[Destination Field2],[…])SELECT [Origin Field1],[Origin Field2],[…]FROM [Origin Table]WHERE ([Test Field A, could be from origin] Not In (select [Test Field B]…

this is a nice pear db query which returns an associative array $data = $sqlconnectionobject ->getAssoc(‘sql statement here’,true, array(), DB_FETCHMODE_ASSOC, true); if (PEAR::isError($data)) { die($data->getMessage()); }

postgres to mysql workflow

postgres to mysql workflow 1. Export database from postgres to plain text sql. You should do this with the pgAdmin III tool by right clicking on the db you want to export and choosing “Backup …” this will take you to the following screen, where you should select options as displayed below (notice the “Plain”…

a common task I come across is the need to create a table of “distinct” records based upon “uniqueness” of values in a given field. SELECT * INTO new_distinct_table FROM [old_non_distinct_table] WHERE [some_unique_field] In (SELECT Max([some_unique_field]) FROM [old_non_distinct_table] GROUP BY [non_unique_field_of_interest]);

I had earlier shown how to do an update query based on a field from another table in MS Access. Here is the syntax for postgres: UPDATE to_table SET to_column = from_column from from_table where to_table.primary_key= from_table.foreign_key; handy notes for this query: append in postgres is || (double pipe) cast in postgres is like column_to_cast::type_to_cast

I often get tripped up doing an update of one table from fields in other table in access sql. i’m not sure if its because its different in other sql’s, but in access it is required to do an inner join to the table which is being updated within the foreign update query. Here is…