SQL query to get all values a enum can have
Postgresql got enum support some time ago. CREATE TYPE myenum AS ENUM ( 'value1', 'value2', ); How do I get all values specified in the enum with a query? ...
postgresql
IN clause with integers in sqlalchemy/psycopg2
Just a beginner with the python/postgres combo so forgive me if this is trivial. I'm executing a raw SQL query with sqlalchemy along the lines of: SELECT * FROM table WHERE pk_table_id IN () For the example below I tried self.ids as a tuple containing string or integers as well as an array containing string or integers. Either way i...
postgreSQL inner join
i have sql query select * from "Roles" Join "Users" On "Roles".Role="Users".RoleId it return error column Roles.role does not exist query select * from "Roles" Join "Users" On Roles.Role=Users.RoleId return error missing FROM-clause entry for table "roles" how can i solve this problem? i aways work with ms sql ...
mySQL to postgreSQL
We are trying to switch our application from talking to mySQL to postgres. I wished we would have some kind of a subroutine (in VB6) were I can input the mySQl SQL query and get a string out containing the appropriate postgres query. Does anybody have a solution or is interested working with us on that? Thank you! Thomas ...
How best to work around PostgreSQL's stricter grouping
I am trying to convert from using MySQL to using PostgreSQL. I have this type of structure: User(entity) -> Follow -> Business(entity) -> Story The user needs to see all the news updates put out by the businesses they follow. The following query works great with MySQL because it simply shows all associated stories and groups by the sto...
Implementing a Type-2 Slowly Changing Dimension in PostgreSQL
I am planning to implement a Type-2 SCD in PostgreSQL. The downside of SCDs is that you cannot have foreign keys referencing thos tables if used as is often seen. In other words, I often see referential integrity being dealt with within the application code. This strikes me as bad practice as it could be done directly within the database...
SQL Server to PostgreSQL - Migration and design concerns
Currently migrating from SQL Server to PostgreSQL and attempting to improve a couple of key areas on the way: I have an Articles table: CREATE TABLE [dbo].[Articles]( [server_ref] [int] NOT NULL, [article_ref] [int] NOT NULL, [article_title] [varchar](400) NOT NULL, [category_ref] [int] NOT NULL, [size] [bigint] NOT...
Can PostgreSQL be used with an on-disk database?
Currently, I have an application that uses Firebird in embedded mode to connect to a relatively simple database stored as a file on my hard drive. I want to switch to using PostgreSQL to do the same thing (Yes, I know it's overkill). I know that PostgreSQL cannot operate in embedded mode and that is fine - I can leave the server process ...
MySQL: are the consistency/data loss/query optimization issues I read about "that bad"?
As I've been looking into the differences between Postgres and MySQL, it has struck me that, if what I read is to be believed, MySQL should be (disclaimer: by reading the rest of this sentence, you agree to read the next paragraph as well) the laughingstock of the RMDB world: it doesn't enforce ACID by default, the net is rife with stori...
How do i read from database with J2ME?
I am busy with a project that requires me to read from a database and display certain results on mobile(J2ME). For example: I must list all customers in the database (SELECT * FROM CUSTOMERS) and i must display that results on mobile(J2ME). So some how i need to connect the mobile to the database. How do i go about doing this? I really ...
pg_relation_size tells me column doesn't exist
http://www.postgresql.org/docs/8.4/static/functions-admin.html says: pg_relation_size accepts the OID or name of a table, index or toast table, and returns the size in bytes however when i use it with a valid table name, i get the error: "column [table] does not exist..." i know my table exists, because doing SELECT count(*) FROM ...
Using GWT with a database
How can I configure the embedded Jetty that comes with GWT so that when I click the run button in Eclipse, my server application can access a postgresql database? ...
Postgres vs Firebird
I'm looking to use either Firebird or Postgres in my next development project ... largely because both are available under a BSD-like license. I found a great comparison of the two database at http://www.amsoftwaredesign.com/pg_vs_fb But this comparison is a good 2+ years old and both databases have come a long ways since. Does anyone...
Display rows based on $array
I have a table in postgres called workorders. In it are various headings. The ones I am interested in are labor, date_out and ident. This table ties up with wo_parts (workorder parts). In this table are the headings I am interested in, part and workorder. Both are integers. (part auto number) The final table is part2vendor and the headin...
Rails: mysql & postgres at the same time in the same app?
Why you may ask? Because i have built the app on mysql and need to start using postgres for GIS component of my app only. Eventually i will migrate to postgres completely but in the mean time would like to know if this is possible ...
SOLR - how to index database partly?
I have a postgresql database. In table, which i need to index, i have about 20 million rows. When i want to index them all in one attempt(smth like "select * from table_name"), i have Java OutOfMemory error, even, if i`ll give to JVM more memory. Is there any option in SOLR to index a table part by part(e.g. execute sql for first 10000...
Storing SHA1 Signature as Primary Key in Postgres SQL
Hi, I'm writing a simple content management system. I need to store SHA1 hash values that are computed externally as the primary key for my biggest table. I can obviously use a sequence as a primary key and index the SHA1 hex-string for look-up... However, I'm looking for a more elegant solution, where I will simply use the 20-byte SHA1...
Postgres: making a schema restricted/unchangable?
We like our production environment with a restricted/unchangable schema -- the development side can be owned by the developers and changed as they like -- and we like to vet changes as they are promoted. I'm wondering if this may be a solution to making that happen: postgres% create proddb with owner=postgres; unixside% pg_restore --d...
How do I move data from Postgres to MySQL running on Amazon's RDS?
I need to move a database with a Django schema from Postgres to MySQL, running on Amazon's RDF. I can re-generate the tables using manage.py, but I'm still looking for a way to migrate over all of the row data. Does anyone know a clean way of moving it over? Are there any gotchas to watch out for with Amazon's RDF? ...
Checking timestamps in PL/pgSQL
If I have a function in PL/pgSQL that takes in a timestamp, what is the best way to identify whether that date is less than 12 months in the past? e.g. CREATE FUNCTION do_something(foo timestamp) .... -- IF foo is less than 12 months in the past THEN -- do something -- END IF; END; ...