I believe I my understanding is correct that there is a standard SQL. I'm assuming that MySQL, SQLite, and PostgreSQL all have variable support for the standard. Can anyone point me to a comprehensive comparison table that shows the differences? ...
How is the Postgres connection url formed, when the host is some other computer than the localhost? I have allowed Postgres to accept requests from outside. ...
Hello, I'm trying to use the 8 threads from my new processor to handle transactions on the PostgreSQL database. It have to process geographic data in PostGIS, what I already do with just 1 processor core (one thread). I'm using Java (JDBC4) to create one Connection for each thread. Each connection receives the job to process groups of g...
When saving an object in the Django admin I get this error, UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 63: ordinal not in range(128) my database is unicode (postgres) The object being saved has some french characters. I have never had issues before saving objects when I was using MySQL. ...
I'm trying to alter a table column in Postgres, but the table has several AccessShare and AccessExclusive locks. How do I over come the locks? ...
Hello, I need to determine if user has already visited a page, for tracking unique page views. I have already implemented some HTTP header cache, but now I need to optimize the SQL queries. The visit is unique, when: pair: page_id + user_id is found in the visit table or pair: page_id + session_id is found or: page_id + [ip + userag...
I want to create my database tables automatically with Hibernate and Postgresql, but I get errors about sequences. Is it possible to auto create sequences too with Hibernate, or do I have generate sequences manually? Example of my entity: import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence...
Is there a simple (ie. non-hacky) and race-condition free way to create a partitioned sequence in PostgreSQL. Example: Using a normal sequence in Issue: | Project_ID | Issue | | 1 | 1 | | 1 | 2 | | 2 | 3 | | 2 | 4 | Using a partitioned sequence in Issue: | Project_ID | Issue | | 1 ...
Here's my problem. Suppose I have a table called persons containing, among other things, fields for the person's name and national identification number, with the latter being optional. There can be multiple rows for each actual person. Now suppose I want to select exactly one row for each actual person. For the purposes of the applicat...
Folks, PostgreSQL does not allow altering a view (i.e. adding column, changing column orders, adding criterie etc.) if it has dependent objects. This is really getting annoying since you have to write a script to; Drop all the dependent objects Alter the view Recreate all the dependent objects back again I understand that postgreS...
why connect to postgres over internet or VPN slower than sql server ? I have test DateTime now0 = System.DateTime.Now; string sqlconnectsrting = "server=xxx.xxx.xxx.xxx;database=pubs;uid=sa;pwd="; for (int i = 0; i < 1000; i++) { SqlConnection connect = new SqlConnection(sqlconnectsrting); connect.Open(...
I'm wondering if I can do something like CREATE INDEX firstelement ON mytable (myarray[1]); this particular syntax creates a syntax error. ...
I have the following database tables: party, with a pk "pty_id" connected to a sequence for generating pk values. person with a fpk "prs_pty_id" in an identifying relation to party.pty_id. company ... which is not involved at the moment, but obviously this is kindof sub-superclass setup, and it could probably have been implemented with...
I'm using postgresql 9.0 beta 4. After inserting a lot of data into a partitioned table, i found a weird thing. When I query the table, i can see an empty row with null-like values in 'not-null' fields. That weird query result is like below. 689th row is empty. The first 3 fields, (stid, d, ticker), are composing primary key. So th...
Hey guys, when I first begin a rails project, the model user was designed and created. After all the migration part, it successful created the table "users" at postgres. Well, then after doing some changes during the project, I realized that was missing an attribute/new column at the table. So what I did was delete the table users from ...
Can we classify/say that TRUNCATE belongs to/falls under DML statement? Check here for PostgreSQL TRUNCATE compatibility. NOTE: TRUNCATE is part of SQL standard ANSI SQL 2008 - F200 ...
Given a table: | id | price | item | total | | 0 | 1.0 | A | | | 1 | 1.0 | A | | | 2 | 0.1 | B | | | 3 | 1.0 | B | | | 4 | 2.1 | B | | | 5 | 1.0 | A | | | 6 | 2.0 | C | | is there an SQL statement that will lead to this ?. | id | price | item | tota...
I'm starting some new projects and want to know if pg8000 is considered a good choice for a production project? Obviously Python and PostgreSQL are mature products, but I'm concerned about pg8000 both when it comes to maturity and performance. Will my DB access suffer or will it be acceptable? So, please take some latitude in respondin...
I would like to convert a given date and integer to a timestamp in a pl/pgsql function. i've never done anything with pl/pgsql before, so i'm somewhat at a loss. Thanks to the answer by Pablo Santa Cruz, it got this far: CREATE OR REPLACE FUNCTION to_my_timestamp(mydate date, timeint integer) RETURNS timestamp AS $$ DECLARE myhours...
In my tests I do not only test for the perfect case, but especially for edge cases and error conditions. So I wanted to ensure some uniqueness constraints work. While my test and test fixtures are pretty complicated I was able to track the problem down to the following example, which does not use any custom models. To reproduce the beha...