postgresql

org.postgresql.util.PGobject cannot be cast to org.postgis.PGgeometry

When I try to load points from a PostGIS database, I get the following exception: Caused by: java.lang.ClassCastException: org.postgresql.util.PGobject at org.hibernatespatial.postgis.PGGeometryUserType.convert2JTS(PGGeometryUserType.java:75) at org.hibernatespatial.AbstractDBGeometryType.nullSafeGet(AbstractDBGeometryTy...

Find Parent Recursively using Query

Hello, I am using postgresql. I have the table as like below parent_id child_id ---------------------- 101 102 103 104 104 105 105 106 I want to write a sql query which will give the final parent of input. i.e suppose i pass 106 as input then , its output will be 103. (106 --> 105 --> 104 --> 103) ...

How to pick a default value to the column in the trigger?

We implement some triggers which perform some operations with insert data before committing them to the database and when some conditions fail the trigger should pick up the default value for corresponding column. How to perform it inside the trigger? Thank you. PS. we work with postgersql, but other implementation with othee dbs may g...

SQL database usage in commercial projects

I am planning to start a commercial web project (like e.g. facebook) which requires a database. I've read through all the licenses and FAQ's of MySQL, PostgreSQL and HSQLDB but I am still not certain if I need to purchase a license (commercial type) or have to publish the source code of my web project (open source license) since I am no...

Nested aggregate and subquery in postgresql/SQL with grouping

I need to do the same group by on a bunch of different aggregates that I'm getting with nested subqueries in Postgresql 8.3. If I do this: select f10 as report_id, (SELECT AVG(age) FROM (select f10 as report_id, f62 as age from reports where f55 in ('1')) ...

Select or boolean aggregate function in PostgreSQL

Hi, I would like to ask you how in PostgreSQL can you check if one of boolean values in table's column is true using select or aggregate function? Thanks. ...

Is it possible to use variables to access columns of records in postgresql trigger

I have a trigger to update my timestamps for each table. I use the following function: CREATE OR REPLACE FUNCTION update_timstamp_table0() RETURNS TRIGGER AS $$ BEGIN IF NEW IS DISTINCT FROM OLD THEN NEW.table0_timestamp_column = extract( 'epoch' from NOW() ) RETURN NEW; ELSE RETURN NULL; END IF; END; $$ LANGUAGE 'plpgsql'; Sinc...

Difference between two sql count and subquery count statements

Is there a big performance difference between those two sql count statements, when performing large counts (large here means 100k + records) first: SELECT count(*) FROM table1 WHERE <some very complex conditions> second: SELECT count(*) FROM (SELECT * FROM table1 WHERE <some very complex conditions>) subquery_alias I know that fir...

Select rows from table using tree order

I have table witch contains fields: id, parent_id, name (etc.) i want to order this table in "tree travel order" ie. id parent_id 1, 0 3, 1 5, 1 2, 0 8, 2 4, 0 9, 4 (...) in short describe: take root node, append all children, take next root node append children etc. ...

Database connection error with Postgres & Movable Type

I'm trying to set up MovableType 4.34 using a Postgres database, and I'm getting the following (unhelpful) error message: Connection error: authentication method 7 not supported The database server doesn't support SSL connections, so I suspect that's to blame -- but I can't be sure (what is "authentication method 7" ?), and at any rat...

Migrating from Postgres to SQL Server 2008

I need to migrate a database from Postgres 7 to SQL Server 2008. I am familiar with the SSIS Import and Export wizard but I am stumped about how to define the data source or define the data provider. What is the best way to migrate Postgres to SQL Server, and how do I define data sources/drivers for postgres? ...

How does one write a DELETE CASCADE for postgres?

I'm manually constructing a DELETE CASCADE statement for postgres. I have a 'transaction' and a 'slice' table, related as shown below: Table "public.slice" Column | Type | Modifiers ----------+------+----------- id | text | not null name | text | Referenced by: TABLE "transaction" CONSTRAINT "transaction_slice_...

Compiling extension for PostgreSQL with Mingw for Windows

Hi!\ I am trying to do a C function for PostgreSQL in Windows using MingW to compile. I did the function compile so fine and link but when I add the function to PostgreSQL my server goes down and I dont know why. I use this to compile and link my code C:\mingw\bin\gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith ...

PostgreSQL - Slony > FATAL ERROR: duplicate key value violates unique constraint "sl_nodelock-pkey"

Just posting this here and might help the others: I got this error when we had an outage a while ago, and tried to restart the slon replication service as the replication is NOT really replicating the data from MASTER to SLAVE: FATAL localListenThread: "select "_MY_DATABASE_copy".cleanupNodelock(); insert into "_MY_DATABASE_copy".sl_n...

Ununderstandable Rails 3.0 log

Hi, since i migrated to rails 3.0, logs are now filled with queries like this: SQL (8.5ms) SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = '"checkins"'::regclass AND a.attnum > 0 AND NOT a.a...

Is there a limitation on the number of tables a PostgreSQL database can have?

I have created a database in PostgreSQL, let's call it testdb. I have a generic set of tables inside this database, xxx_table_one, xxx_table_two and xxx_table_three. Now, I have Python code where I want to dynamically create and remove "sets" of these 3 tables to my database with a unique identifier in the table name distinguishing dif...

Why does postgreSQL check foreign key constraints twice?

I have a postgresql database with two tables, one which has a foreign key constraint which references the other. The constraint is set to cascade on update and delete, so an update on the referenced table should cascade to the referencing table. When I analyzed an update on the referenced table, using explain analyze the I got the follow...

setting up elasticSearch with Postgresql

where do I find a howto to set up elasticSearch using Postgres? My field sizes will be about 350mb, yes, MB, each in size. I have a text output of all of the US Code and all decisions from all the courts, the Statutes at Large, pretty much everything you would find in a library, and I need to be able to do full text searches and return...

psycopg2.InternalError: how can I get more useful information?

I'm running this command in a Python script: try: print sql_string cursor.execute(sql_string) except: print sys.exc_info() and getting: (<class 'psycopg2.InternalError'>, InternalError('current transaction is aborted, commands ignored until end of transaction block\n',), <traceback object at 0x1010054d0>) However if I ...

Postgres statistics - pg_stat_get_db_xact_commit

Do any of you understand what "pg_stat_get_db_xact_commit" internally does? Even when the application is idle and no requests to the db are sent, the count keeps increasing continuously. Any idea what could be happening in the background? ...