I have a PostgreSQL database whose tables are divided amongst a number of schemas. Each schema has a different set of access controls; for example, one schema might be read-only to regular users, while they are allowed to create tables on another. Schemas also act as namespaces, so users don't have to worry about duplicating existing t...
I often find myself in need of very complex SQL examples when testing abstraction concepts or just comparing database styles and structures while working with PostgreSQL, MySQL, and even SQLite. I assume that means there are others in need of insane queries to open our eyes to what is possible and insure our DB layers can handle anythin...
I have a procedure written in PLJava that sends out updates over JMS in my postgres database. What I would like to do is have that function called on an interval (every 15 seconds) internally in the database (preferably not from an outside process). Is this possible? Any ideas? ...
Hello, we are making an ASP.Net application. We would like to have our application to be at least sorta DB agnostic, most notable to be compatible with both SQL Server and PostgreSQL. What is the best way of doing this? What are some of the common pitfalls? Also is there a class or something that already abstracts away the difference bet...
I'm developing an app in Rails on OS X using PostgreSQL 8.4. I need to setup the database for the app so that standard text queries are case-insensitive. For example: SELECT * FROM documents WHERE title = 'incredible document' should return the same result as: SELECT * FROM documents WHERE title = 'Incredible Document' Just...
Using Toad for Oracle, I can generate full DDL files describing all tables, views, source code (procedures, functions, packages), sequences, and grants of an Oracle schema. A great feature is that it separates each DDL declaration into different files (a file for each object, be it a table, a procedure, a view, etc.) so I can write code...
hoping someone has experience with both sql server and postgresql. Between the two db's, which one is easier to scale? Is creating a read only db that mirrors the main db easier/harder than sql server? Seeing as sql server can get $$, I really want to look into postgresql. Also, are the db access libraries written well for an asp.net ...
Dear all, I have working in database Postgresql for the first time. I need your help to finding out a solution. One table contains 15 rows With a regn_srno as P.K., another table has the same regn_srno as F.K. I want to count the total number of rows which has the same regn_srno. But My problem is the second table has contain 2 or 3 fie...
Having used an Oracle DB for a while, I got used to using the Oracle DB Application Context to implement access restrictions. To simply put it, upon login a procedure is called that puts the user details into the Oracle Application context like so: DBMS_SESSION.SET_CONTEXT('context_name', 'user_id', user_id); Then access can be enforc...
I'm using this function... -- Function: dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying) -- DROP FUNCTION dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying); CR...
Hi all I have an application on Glassfish v2 ( on Mac OS 10.5.8 and Java 1.6 ) that uses JavaDB and Toplinks that comes with the Glassfish bundle. Everything works fine. I have installed PostgreSQL 8.4 and the JDBC v4 driver. Both Glassfish and Postgres server run on localhost. From Netbeans, I create a connection to a database on the P...
Hello, I'm migrating from SQL Server to PostgreSQL. I've seen from http://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query that there is no such thing as temporary variables in native sql queries. Well, I pretty badly need a few... How would I go about mixing in plpgsql? Must I create a function and th...
I'm trying to export some data from MATLAB to a database. I use a PostgreSQL database via ODBC as follows. First, I create the connection: dbConn = database('PostgreSQL30', username,password); If I try to execute some test insertion manually, everything looks fine: exec( dbConn, 'insert into test(std) values(2.2)') When I try to g...
Is there a way to show all tables, describe them \d and dump the result on php? Any ideas will be appreciated ...
I am trying to find some way to relate column types across the the most used Databases: MySQL, PostgreSQL, and SQLite. Here is what I have so far, but I'm afraid it's not done and I need some people with more experience to help me finish any missing types. MySQL PostgreSQL SQLite TINYINT SMA...
I have a database field of type time with time zone. How can I query this field in EST5EDT time zone with the output format hh:mm:ss? All I can find is using multiple calls to EXTRACT: SELECT EXTRACT(HOUR FROM TIME WITH TIME ZONE '20:38:40-07' AT TIME ZONE 'EST5EDT'); [Edit:] To be clear, this query: SELECT TIME WITH TIME ZONE '...
I'm having a first painful experience with postgresql, and the minute-challenge of the moment is : How to perform a concat_ws in postgresql, to join several fields value from a group by : select concat_ws(';',field_lambda) from table_lambda group by id; ...
I am running a Criteria.scroll() on PostgreSQL on a DB containing 2M records. The memory keeps increasing and finally it generates an OutOfMemoryException. Please can you advice how to fix this. Postgresql DB version: 8.4 Postgresql Driver Used: postgresql-8.4-701.jdbc4.jar Is there some known issue with Hibernate scroll() in Pos...
Hello, the problem is as follows: We're using hibernate with annotations as O/R Mapper. Some @Column annotations look like: @Column(columnDefinition = "longblob", name = "binaryData", nullable = true) or @Column(columnDefinition = "mediumtext", name = "remark", nullable = true) with the columnDefinition attributes being mysql speci...
I have a table in PostgreSQL that I need read into memory. It is a very small table, with only three columns and 200 rows, and I just do a select col1, col2, col3 from my_table on the whole thing. On the development machine this is very fast (less than 1ms), even though that machine is a VirtualBox inside of a Mac OS FileVault. But on ...