My database layout needs to create new schema for each new customer. Currently I use internal function I found on the net and modified a little bit. CREATE FUNCTION copy_schema( source_schema …
Tag: postgresql
cannot get simple PostgreSQL insert to work
I’m trying to do a simple insert into a postgres table, but am getting an error that the value I’m trying to insert is being interpreted as a column name Where id is set up to be the primary key, and auto increment, and not null. Those are the boxes I ticked when I set up the table in phpPgAdmin.
Rails ActiveRecord – how to fetch records between two dates
I have two date columns – from_date and to_date in a database table. Example: from_date: 2012-09-10 to_date: 2012-09-30 today: 2012-09-13 I need to fetch all records, if today’s date is between from_date and to_date. How do I do that with a SQL query? If I have loaded the respective record, I can easily decide, if today’s date is between from_date
Normalize array subscripts so they start with 1
PostgreSQL can work with array subscripts starting anywhere. Consider this example that creates an array with 3 elements with subscripts from 5 to 7: Returns: We get the first element at subscript 5: I want to normalize 1-dimensional arrays to start with array subscript 1. The best I could come up with: The same, easier the read: Do you know
Problems with Postgresql CASE syntax
Here is my SQL query: When I execute the above query, I get this error: ERROR: CASE types record and integer cannot be matched From the error message I understand that PostgreSQL take the second select, respectively elapsed_time_from_first_login as a row, even if it will always be a single value (because of the min() function). Question: do you have some
Combine multiple SELECT statements
I’ve used Excel to generate numerous SELECT statements from a list of the schema names from a database with a large number of identical schemas: select result from foo.table limit 1; select result …
GROUP BY and COUNT in PostgreSQL
The query: Returns n records in Postgresql: I just want to retrieve the number of records returned: 6. I used a subquery to achieve what I want, but this doesn’t seem optimum: How would I get the number of records in this context right in PostgreSQL? Answer I think you just need COUNT(DISTINCT post_id) FROM votes. See “4.2.7. Aggregate Expressions”
PL/pgSQL SELECT into an array
Here’s my function declaration and part of the body: I want team_ids to be an array of ints that I can then use in the UPDATE statement. This function give me errors like this: Answer Faster and simpler with a FROM clause in your UPDATE statement: That aside, while operating with an array, the WHERE clause would have to be:
SQL select elements where sum of field is less than N
Given that I’ve got a table with the following, very simple content: I would like to select N messages, which sum of verbosity is lower than Y (for testing purposes let’s say it should be 70, then correct results will be messages with id 1,2,3). It’s really important to me, that solution should be database independent (it should work at
Differences between PostgreSQL and MySQL for PHP developers
For a project where most queries on the database will include requirements like “within an polygon” I have come to think about choosing PostgreSQL with PostGIS instead of MySQL, which I have previously mainly used. Mainly because PostgreSQL has more GIS functions. However, I am uncertain how to evaluate this, and also can’t really see the difference from a PHP