Closer examination of the documentation shows that array_length() takes two parameters. I just had to change SELECT question_text, array_length(sort_order) AS level, … to SELECT question_text, array_length(sort_order, 1) AS level, …
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
To get a table OID, cast to the object identifier type regclass (while connected to the same DB!). This finds the first table (or view etc.) with the given (unqualified) name along the search_path or raises an exception if not found: SELECT ‘mytbl’::regclass::oid; Schema-qualify the table name to remove the dependency on the search path: … Read more
Because the number can be up to 15 digits, you’ll need to cast to an 64 bit (8-byte) integer. Try this: SELECT * FROM table WHERE myint = mytext::int8 The :: cast operator is historical but convenient. Postgres also conforms to the SQL standard syntax myint = cast ( mytext as int8) If you have … Read more
A year is an integer and even supports integer arithmetic in a meaningful way so text makes no sense. You don’t have a month or day so date is right out the window. The fine manual has this to say about smallint: The smallint type is generally only used if disk space is at a … Read more
You need to supply a format mask. In PostgreSQL there is no default: select to_char(1234, ‘FM9999′); If you don’t know how many digits there are, just estimate the maximum: select to_char(1234, ‘FM999999999999999999′); If the number has less digits, this won’t have any side effects. If you don’t need any formatting (like decimal point, thousands separator) … Read more
Nope. Only one auth method is supported for any given configuration. I’d love it if Pg could support fall-back authentication, where if an ident check fails it allows md5 auth instead. It doesn’t support this at the moment, though, and I suspect (I haven’t verified) that a protocol change would be required to support it. … Read more
SQL Fiddle select “date”, shop_id, amount, extract(dow from date), case when row_number() over (order by date) > 3 then avg(amount) OVER ( ORDER BY date DESC ROWS BETWEEN 1 following AND 3 FOLLOWING ) else null end from ( select * from ro where extract(dow from date) = 4 ) s What is wrong with … Read more
Instead of using window functions and partitioning, use a query-level GROUP BY and aggregate with a DISTINCT clause: SELECT rnp.grp_id, array_to_string(array_agg(distinct rnp.cabinets),’,’) AS cabinets, array_to_string(array_agg(distinct ips.address),’,’) AS addresses FROM rnp JOIN ips ON rnp.grp_id=ips.grp_id GROUP BY rnp.grp_id, ips.grp_id; Result: grp_id | cabinets | addresses ——–+————————-+———– 11 | cabs1,cabs2,cabs3,cabs4 | CA,NY 22 | c1,c2 | DC,LA … Read more
In Postgres 9.3 or later use a LATERAL join: SELECT v.col_a, v.col_b, f.* — no parentheses, f is a table alias FROM v_citizenversions v LEFT JOIN LATERAL f_citizen_rec_modified(v.col1, v.col2) f ON true WHERE f.col_c = _col_c; Why LEFT JOIN LATERAL … ON true? Record returned from function has columns concatenated For older versions, there is … Read more