I’ve constructed a test which compares OFFSET, cursors, and ROW_NUMBER(). My impression of ROW_NUMBER(), that it would be consistent in speed regardless of where you are in the result set, is correct. However, that speed is dramatically slower than either OFFSET or CURSOR, which, as was also my impression, are pretty much the same in … 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
The other answer indeed points to the root cause of the problem, however the help page it points to does not contain a solution. Here is what I came up with to make this work for me: start the container using your normal docker-compose file, this creates the directory with the hardcoded uid:gid (999:999) version: … Read more
Use some conditional functions, e.g. ORDER BY NULLIF(first_name, ”) DESC NULLS LAST
Taken from the PostgreSQL documentation on ALTER DEFAULT PRIVILEGES, Notes section: If you wish to drop a role for which the default privileges have been altered, it is necessary to reverse the changes in its default privileges or use DROP OWNED BY to get rid of the default privileges entry for the role. Another worthy … Read more
Views in Postgresql can’t have primary keys. you are basically on wrong way creating constraint on a View, constraints should be created on tables, but some DBMSes do support adding constraints on Views like oracle with this syntax: ALTER VIEW VIEW_NAME ADD PRIMARY KEY PK_VIEW_NAME DISABLE NOVALIDATE; Oracle Doc For Constraints You can specify only … Read more
When you display the psql online help by entering \? you can see: Connection \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo} connect to new database (currently “postgres”) So you need to use: \c shorturl shorturl
The length limit imposed by varchar(N) types and calculated by the length function is in characters, not bytes. So ‘abcdef’::char(3) is truncated to ‘abc’ but ‘a€cdef’::char(3) is truncated to ‘a€c’, even in the context of a database encoded as UTF-8, where ‘a€c’ is encoded using 5 bytes. If restoring a dump file complained that ‘Mér’ … Read more
Passwords are stored in pg_shadow In documentation: Password (possibly encrypted); null if none. See pg_authid for details of how encrypted passwords are stored. So you should select * from pg_shadow; You should also check pg_authid table.