I have a query like this: SELECT jobs.*, (CASE WHEN lead_informations.state IS NOT NULL THEN lead_informations.state ELSE 'NEW' END) as lead_state FROM "jobs" LEFT JOIN lead_informations ON lead_informations.job_id = jobs.id AND lead_informations.mechanic_id = 3 WHERE (lead_state = 'NEW') Which gives the following error: PGEr...
I also have a very large table in SQL Server (2008 R2 Developer Edition) that is having some performance problems. I was wondering if another DBMS would be better for handling large tables. I'm mainly only considering the following systems: SQL Server 2008, MySQL, and PostgreSQL 9.0. Or, as the referenced question above eludes to, is ...
Hi all, I try to format numeric field select to_char(12315.83453, 'FM999999999999D9999') in this case all its OK. Result is 12315.8345 But if value is between 0 and 1 select to_char(0.83453, 'FM999999999999D9999') result is .8345 without 0 (zero) but I need 0.8345. What kind of format should be indicate to to_char function to ob...
I'm using codeigniter 1.7.2 on WAMP with php_pgsql and php_pdo_pgsql extensions enabled here is my configuration : $active_group = "default"; $active_record = TRUE; $db['default']['hostname'] = "localhost"; $db['default']['username'] = "postgres"; $db['default']['password'] = "mypassword"; $db['default']['database'] = "mydatabasename"; ...
Hi, I have one problem with the ALTER TABLE in postgre. I want to change size of the varchar column. When I try to do this, It says that the view is dependent on that column. I can't drop the view because comething else is dependent on it. Is there any other way than to drop everything and recreate it again? I just found one option, whi...
greetings all i want to know what's equivalent to Long data type in postgresql which i can retrieve in a long variable. ...
I'm creating a web app using PHP and PostgreSQL. Database are too big and some kinds of search takes a lot of time to process. User can wait into first search, but it sucks when paginates. Can I store the resultset into sessions vars? It would be great to deal with pagination. ...
I'm using postgresql COPY FROM/TO command to move data from my production environment table to my development environment table (they have the same structure, constraints etc). I export my data using COPY TO command and that's OK, but problem occurs where i try to import that data using COPY FROM. I get an ERROR: missing data for column...
How do I find all the paths from a specific node (node 36 in the example)? Let's say we have two tables: CATEGORIES CATEG_PARENTS id idc | idparent -- ---------------- 1 2 1 2 2 20 5 5 2 8 8 5 20 20 1 22 ...
I am trying to use UUID in Hibernate. Have the following Entity base-class description (with @MappedSuperclass annotation): @Id @Column(name="id") private UUID id; public UUID getId() { return id; } For test, I am trying to read all entities of my class from database (database exists, records exist). My database is PostgreSQL 8...
Is there any an easy (built-in, add-on, open-source or commercial) to do replication on Postgresql (Master-slave) to have the data inside the slave be scrubbed for PCI compliance while being replicated across? How about ETL tools? It does not have to be instantaneous ... up to an hour lag is acceptable but the faster the better of cour...
Hi. I am writing a CMS using PHP and PostgreSQL. The CMS is capable of running multiple sites from a single codebase, using a unique set of tables per site / domain, differeing depending on which features are enabled. I understand that PostgreSQL supports multiple schemas within a single database, and that access permissions are then se...
I write PSQL script and using variables (for psql --variable key=value commandline syntax). This works perfectly for top level scope like select * from :key, but I create functions with the script and need variable value inside them. So, the syntax like create function foo() returns void as $$ declare begin grant select on my_tabl...
Can someone recommend a great IDE/Database Developer Tool for Postgres 8.4. We are currently using EMS SQL Manager but it has so many bugs and shortcomings that its getting on my nerves. I reported the bugs to EMS but they have not been fixed yet. I wonder what do people use for PostgresSQL development? Windows/Linux, commercial/free, ...
hi i try to create a plpgsql trigger for postgresql 8.3 which automatically partitions a table on before insert by the id column if the destination table doesnt exist it will be created, and the insert goes there so i created the insert statement with the new table name like this exec_insert := 'INSERT INTO '||TG_TABLE_SCHEMA||'.'||T...
We're building our DB design (using PostgreSQL) and for (almost) every table , I have the following columns CREATE_TIMESTAMP TIMESTAMP, CREATED_BY VARCHAR(25), modified_TIMESTAMP TIMESTAMP, modified_BY VARCHAR(25), I am also using Audit tables for some of the Entity Tables. The DB about 15 tables as of now (very soon will ...
If I built a btree index on columns (one, two, three, four, five) will it be used for a query with where block: (1) ... WHERE one = 1 AND two = 2 AND three = 3 AND four = 4 -- no five or (2) ... WHERE two = 2 AND one = 1 -- wrong sequence, no five or (3) ... WHERE one = 1 AND three = 3 AND two = 2 AND five = 5 AND four = 4 -- wron...
I have a large database, that I want to do some logic to update new fields. The primary key is "id" for the table harvard_assignees The LOGIC GOES LIKE THIS Select all of the records based on "id" For each record (WHILE), if (state is NOT NULL && country is NULL), update country_out = "US" ELSE update country_out=country I see step...
How I should enter my multicolum indexes which contain functions into schema.rb ? for example this DOESN'T work: add_index "temporary_events", ["templateinfoid", "campaign", "date(gw_out_time)", "messagetype"], :name => "temporary_events_campaign_tinfoid_date_messagetype" rake db:test:load rake aborted! PGError: ERROR: ...
Hi, I'm writing an application using Wicket/Hibernate and I need to support many languages. How to make good internationalization of user data in a PostgreSQL database? Thanks for any help/suggestions. ...