I am not a DBA, but recently I have to upgrade Postrges minor version with a security update. This is not either a tutorial or some best practice, but some small snippet of the command you will need, before doing…
I am not a DBA, but recently I have to upgrade Postrges minor version with a security update. This is not either a tutorial or some best practice, but some small snippet of the command you will need, before doing…
Assumptions My primary focus is not on Matlab, but I used it for a while in the Uni. I have to support connecting Matlab installed on Windows machine with Postgres 9.2 database. I used the Matlab command window, so this…
I noticed a column in Postgres which is varchar, but has geo information and should be geometry. Here are short steps to do in order to convert the data in geometry instead of varchar. 1. Rename the column to the_geom_1…
This snippet is particularly for Postgres (that database I am currently using and didn’t tested it in other, but should work. At most you have to change the syntax a little). I have to remove duplicated entries for a duplicated_column…
Copy this snippet, replace database_name with the name of your database AND utf8_general_ci to any other collation you preferred. This will generate the sql commands needed for changing collation of all tables. SELECT CONCAT(‘ALTER TABLE `’, t.`TABLE_SCHEMA`, ‘`.`’, t.`TABLE_NAME`, ‘`…
SQL code snippet to reset the primary key sequence in Postgres database table: SELECT setval(‘sequence_name’, 1); UPDATE table_name SET primary_key_id = DEFAULT;
1. When that happen This error is issued when you try to alter a table in a way that it will break a foreign key constraint. Consider the following example CREATE TABLE test1(id INT NOT NULL PRIMARY KEY)ENGINE=INNODB; CREATE TABLE…
1. copy/insert from one table to another INSERT INTO `db_name`.`table_name`( `title`, `intro`, `content`, `keywords`, `description`) SELECT `title`, `intro`, `content`, `keywords`, `description`FROM `db_name`.`other_table`; 2. update whole column with the values from other…
# iso_country_list.sql # This will create and then populate a MySQL table with a list of the names and # ISO 3166 codes for countries in existence as of the date below. # For more about ISO 3166, see http://www.iso.ch/iso/en/prods-services/iso3166ma/02iso-3166-code-lists/list-en1.html…
# usps_states_list.sql# # This will create and then populate a MySQL table with a list of the names and # USPS abbreviations for US states and possessions in existence as of the date # below.# # For more about USPS…