Is it good practice to delegate data validation entirely to the database engine constraints? Validating data from the application doesn't prevent invalid insertion from another software (possibly written in another language by another team). Using database constraints you reduce the points where you need to worry about invalid input dat...
Hi I have a migration that runs an SQL script to create a new Postgres schema. When creating a new database in Postgres by default it creates a schema called 'public', which is the main schema we use. The migration to create the new database schema seems to be working fine, however the problem occurs after the migration has run, when ra...
I've tried the tools listed here, some with more success than others, but none gave me valid postgres syntax I could use (tinyint errors etc.) ...
I'm building an installer for an application. The user gets to select a datasource they have configured and nominate what type of database it is. I want to confirm that the database type is indeed Postgres, and if possible, what version of Postgres they are running by sending a SQL statement to the datasource. ...
OK, so practically every database based application has to deal with "non-active" records. Either, soft-deletions or marking something as "to be ignored". I'm curious as to whether there are any radical alternatives thoughts on an `active' column (or a status column). For example, if I had a list of people CREATE TABLE people ( id ...
coming back to postgresql after several years of oracle ... what are the state-of-the art postgresql administrative/ddl generating/data inserting frontends on windows? it would be nice if it had integration for postgis as well. it should be standalone or alternatively a plugin for intellij idea thinking back, all the windows programs ...
I am setting up a project using Hibernate 3.3.1 GA and PostgreSQL 8.3. I've just created a database, the first table, added one row there and now configuring Hibernate. However, even the simplest query: Criteria criteria = session.createCriteria(Place.class); List result = criteria.list(); could not be executed (empty list is returne...
How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL (using the psql command)? ...
I've seen questions for doing the reverse, but I have an 800MB PostgreSQL database that needs to be converted to MySQL. I'm assuming this is possible (all things are possible!), and I'd like to know the most efficient way of going about this and any common mistakes there are to look out for. I have next to no experience with Postgre. Any...
We are currently working with JavaEE and MySQL 5 in our company, but we have some queries, especially delete queries that take > 10 min to execute. We consider to switch to PostgreSQL. What are the advantages of PostgreSQL over MySQL if there are any? Do you have experiences with both DBs and may give me a roundup whether this is a good...
I have a very large database table in PostgresQL and a column like "copied". Every new row starts uncopied and will later be replicated to another thing by a background programm. There is an partial index on that table "btree(ID) WHERE replicated=0". The background programm does a select for at most 2000 entries (LIMIT 2000), works on th...
Hello. I'm using NHibernate 2 and PostgreSQL in my project. SchemaExport class does a great job generating DDL scheme for database, but it's great until the first application. Is there any way to generate "migration" DLL (batch of "ALTER TABLE"'s instead of DROP/CREATE pair) using NHibernate mapping files? ...
MySQL's explain output is pretty straightforward. PostgreSQL's is a little more complicated. I haven't been able to find a good resource that explains it either. Can you describe what exactly explain is saying or at least point me in the direction of a good resource? ...
Ideally something that will work with Oracle, MS SQL Server, MySQL and Posgress. ...
Intro: I'm trying to migrate our Trac SQLite to a PostgreSQL backend, to do that I need psycopg2. After clicking past the embarrassing rant on www.initd.org I downloaded the latest version and tried running setup.py install. This didn't work, telling me I needed mingw. So I downloaded and installed mingw. Problem: I now get the followin...
Is it possible to change the natural order of columns in Postgres 8.1? I know that you shouldn't rely on column order - it's not essential to what I am doing - I only need it to make some auto-generated stuff come out in a way that is more pleasing, so that the field order matches all the way from pgadmin through the back end and out to...
The question is pretty self-explanatory. I'm looking for a PostgreSQL equivalent to the SQLite datetime function. ...
I have a Postgresql database on which I want to do a few cascading deletes. However, the tables aren't set up with the ON DELETE CASCADE rule. Is there any way I can perform a delete and tell Postgresql to cascade it just this once? Something equivalent to DELETE FROM some_table CASCADE; The answers to this older question make it s...
Hi, I'm new to postgreSQL and I have a simple question: I'm trying to create a simple script that creates a DB so I can later call it like this: psql -f createDB.sql I want the script to call other scripts (separate ones for creating tables, adding constraints, functions etc), like this: \i script1.sql \i script2.sql It works fine ...
Hi everyone! I'm currently planning the infrastructure for my future web project. I want to go the way Joel went with having one DB per client and now thinking which DB engine will be good for me. The best would be of course SQL Server, but I can't afford a full-blown version at this moment and I don't think SQL Server Express will be a...