Hey everyone I am fairly new to doing a pg_dump in Postgre sql. I have logged into the server and am running as postgres user. I try to run the pg_dump in order to do a database migration but I keep getting a “Permission Denied” prompt. I believe that I have the highest permissions and should be a…
Tag: database-migration
Laravel Migration: Combine multiple values in a new colum
i’m trying to make a migration using Laravel. The idea is to get the values of columns ‘code’ and ‘id’ in different tables, and merge them into a column ‘name’ with ‘-‘ separators. So in part it is a SQL problem. Currently i’m trying something like t…
Convert foreign key constraint from SQL Server to Oracle
I want to convert the following script(SQL SERVER) to Oracle: I have tried to run the same script in Oracle: and I got this error: I believe that the “WITH CHECK ADD” is the problem! Answer See if this helps. A master table – its primary key column will be referenced from the tdistribucion t…
NZ function in T-SQL where ValueIfNull is NOT specified
I am working on an MS Access to SQL Server Migration project and am currently in the process of converting a complex query into T-SQL from MS Access. Now I’m quite familiar with how Nz works in Access …
Postgres Import from different table
I’m still fairly new to postgres. I have a table named: university_table with fields: name, nationality, abbreviation, adjective, person. I found this sql query to insert data from: https://stackoverflow.com/a/21759321/9469766 Snippet of query below. How can alter the query to insert these values into m…
How to add a comment to flyway migration
I am trying to make my big migration more self explaining and that’s why I need to add some comments to it. However, I tried a few approaches and haven’t succeeded with them. I tried: // # <!– TEXT –> Error: Is there a way I can add a comment to flyway SQL migration? Answer The f…
Do a dry-run of an Alembic upgrade
Sometimes alembic upgrade head may fail at runtime against my production database even though it worked fine against my testing databases. For instance, a migration might add a NOT NULL constraint to a column that didn’t previously contain NULLs in my testing environment, but did contain NULLs in produc…
Can we define a GROUP_CONCAT function in PostgreSQL?
I have several lines of SQL code for my legacy database with GROUP_CONCAT statements, as in: In PostgreSQL, I can do the same with: I would like to reuse the old SQL as much as possible. So I need to define a GROUP_CONCAT function that internally calls string_agg. Is this possible? EDIT: The linked question i…
pgloader not importing data from MySQL to Postgres
I tried the following command and it returns no errors but the data is not imported in my postgres database. Database is already created in Postgres. This is the result: When I login to psql to look for the data, its not there. For example, for the table users 2 records were supposed to be imported as mention…
Default row order in SELECT query – SQL Server 2008 vs SQL 2012
Our team recently upgraded our databases from SQL Server 2008 to SQL Server 2012. One breaking change we noticed was in the default order of rows returned by the SELECT statement, i.e. when an explicit ORDER BY clause is not specified. As per MSDN, SQL Server 2012 does not gaurantee the order of the rows retu…