I have a function written in plpythonu. CREATE OR REPLACE FUNCTION temp(t_x integer[]) RETURNS void AS $BODY$ . . . x=plpy.execute(“””select array_to_string(select id from A where id=any(array%s) ),…
Tag: postgresql
Postgresql delete multiple rows from multiple tables
Consider 2 or more tables: I wish to delete all users and their orders that match first name ‘Sam’. In mysql, I usually do left join. In this example userid is unknown to us. What is the correct format of the query? Answer http://www.postgresql.org/docs/current/static/sql-delete.html You can also …
null value in column “last_login” violates not-null constraint
Having issues getting rid of this error. I’m trying to use Digital Ocean to deploy my Django app. I configured Postgres, but when I’m trying to register a new user in my app, I get this problem. I’…
Select timestamp with time zone, but ignore seconds
I’m selecting a timestamptz from a PosgreSQL database. I want my SELECT to return only the date, hours and minutes. No seconds. Can I set the date format in my psql SELECT to accommodate this? Answer You can use date_trunc() to truncate seconds and still return a timestamptz: Or you can use to_char() to…
Postgresql ON CONFLICT in sqlalchemy
I’ve read quite a few resources (ao. 1, 2) but I’m unable to get Postgresql’s ON CONFLICT IGNORE behaviour working in sqlalchemy. I’ve used this accepted answer as a basis, but it gives I’ve tried adding the postgresql dialect to the @compile clause, renaming my object, but it do…
How to iterate over results of query
I am creating a function in pgsql script language, and what I want to do in this point is iterate over the results of a query and for each row do something specific. My current try is the following, where temprow is declared as temprow user_data.users%rowtype. The code in question is the following: However I …
The inverse of a between select
I want to select the inverse of a data range in a Postgres database: the query i am trying to use is SELECT * FROM projects WHERE lastactivity BETWEEN 2015-08-3 AND 2015-09-11
RETURNING rows using unnest()?
I’m trying to return a set of rows after doing UPDATE. Something like this. but postgres complains, rightly so: set-valued function called in context that cannot accept a set How am I supposed to go about implementing this? That is, RETURNING a set of rows from SELECTed array after UPDATE? I’m awa…
Count rows after joining three tables in PostgreSQL
Suppose I have three tables in PostgreSQL: Suppose I am using the using the following query: I get 50 as count. Whereas with: I get only 25 as count. What is my mistake in the second query? What can I do to get the same count? My requirement is that there is a single user table, referenced by multiple tables.
How to calculate power consumption from power records?
I have table which contains power values (kW) for devices. Values are read from each device once a minute and inserted into table with timestamp. What I need to do is calculate power consumption (kWh) for given time span and return 10 most power consuming devices. Right now I query results for given time span…