Why not use the RETURNING clause and process both things in one single statement: UPDATE jobs SET status=”RUNNING” WHERE status=”PENDING” RETURNING * That way you will get all rows that were changed by the UPDATE with a single atomic operation.
No, as the error message states, DISTINCT is not implemented with windows functions. Aplying info from this link into your case you could use something like: WITH uniques AS ( SELECT congestion.id_element, COUNT(DISTINCT congestion.week_nb) AS unique_references FROM congestion WHERE congestion.date >= ‘2014.01.01’ AND congestion.date <= ‘2014.12.31’ GROUP BY congestion.id_element ) SELECT congestion.date, congestion.week_nb, congestion.id_congestion, congestion.id_element, … Read more
I’m not aware of any tool for this, but this one-liner extracts precious_table from my_backup.sql file: sed -n ‘/^COPY precious_table /,/^\\\.$/p’ my_backup.sql
translate() replaces a set of single characters (passed as a string) with another set of characters (also passed as a string), for example: translate(‘abcdef’, ‘ace’, ‘XYZ’) –> ‘XbYdZf’ replace() replaces occurrences of a string of arbitrary length with another string: replace(‘abcdef’, ‘bc’, ‘FOO’) –> ‘aFOOdef’
PostgreSQL doesn’t know how to automatically cast input of type text to input of type equipment. You have to explicitly declare your strings as being of type equipment: ARRAY[‘projector’,’PAsystem’,’safe’]::equipment[] I confirmed this with SQL Fiddle.
Is this what you mean? \i e:/myFolder/index.sql;
Yes, you can be more specific. For example: CREATE DATABASE “scratch” WITH OWNER “postgres” ENCODING ‘UTF8’ LC_COLLATE = ‘en_US.UTF-8’ LC_CTYPE = ‘en_US.UTF-8’; Also I recommend to read the following pages about locales and collations in PostgreSQL: http://www.postgresql.org/docs/current/interactive/locale.html http://www.postgresql.org/docs/current/interactive/collation.html
You almost had it, but better start here plpgsql Some will point that PL/pgSQL is not the only way to write Stored Procedures for PostgreSQL and they will be right (that’s one of the advantages of Postgres), but on the other hand PL/pgSQL is nice and relatively easy to start with.