postgresql

Removing a set of letters from a string

by

translate() replaces a set of single characters (passed as a string) with another set of characters (also passed as a string), for example: translate(‘abcdef’, ‘ace’, ‘XYZ’) –> ‘XbYdZf’ replace() replaces occurrences of a string of arbitrary length with another string: replace(‘abcdef’, ‘bc’, ‘FOO’) –> ‘aFOOdef’

move data from one table to another, postgresql edition

by

[Expanding on dvv’s answer] You can move to an existing table as follows. For unmatched schema, you should specify columns. WITH moved_rows AS ( DELETE FROM <original_table> a USING <other_table> b WHERE <condition> RETURNING a.* — or specify columns ) INSERT INTO <existing_table> –specify columns if necessary SELECT [DISTINCT] * FROM moved_rows; But you want … Read more

For a beginner, is there much difference between MySQL and PostgreSQL [closed]

by

The reason why I usually suggest PostgreSQL before MySQL is because MySQL is far from the standards (SQL-wise). It does not support the use of window functions (8.4 version), common table expressions (8.4), CHECK constraints, EXCEPT/MINUS operator, even FULL OUTER JOINs… Even though you may have never heard of these words, you’ll have to use … Read more

GeoDjango on Windows: “Could not find the GDAL library” / “OSError: [WinError 126] The specified module could not be found”

by

I have found the following to work for windows: Run python to check if your python is 32 or 64 bit. Install corresponding OSGeo4W (32 or 64 bit) into C:\OSGeo4W or C:\OSGeo4W64: Note: Select Express Web-GIS Install and click next. In the ‘Select Packages’ list, ensure that GDAL is selected; MapServer and Apache are also … Read more

Division of integers returns 0

by

You should cast before you divide, but also you were missing a subquery to get the total count from the table. Here’s the sample. select random_int, count(random_int) as Count, cast(count(random_int) as decimal(7,2)) / cast((select count(random_int) from test) as decimal(7,2)) as Percent from test group by random_int order by random_int;

What are the conditions for encountering a serialization failure in PostgreSQL?

by

There are many possible causes for serialization failures. Technically, a deadlock between two transactions is one form of serialization failure, and can potentially happen at any isolation level if there are concurrent modifications to the schema (database structure). Since you’re asking about PostgreSQL, you should be aware that in PostgreSQL this type of serialization failure … Read more

PG::UndefinedTable: ERROR: relation “active_storage_blobs” does not exist

by

The active_storage_blobs table does not exist yet. You need to first run: rails active_storage:install This command will add 2 migrations for 2 tables: active_storage_attachments and active_storage_blobs. These new migrations need to be run before the migrations you have above. There is a trick for this, you can consider manually changing the timestamp in the filenames … Read more