postgresql

ERROR: CASE types character varying and numeric cannot be matched

by

All the branches of a case expression should return the same datatype. One way to achieve that is to explicitly cast where needed: ,(case when all_loc.country = ‘DE’ then msc_si.buyer_id::varchar else msc_si.buyer_name end) as “purchasing_group_name_buyer_name” — Here ———————————————–^ ,(case when all_loc.country = ‘DE’ then msc_si.planner_code::varchar else mscp.description end) as “mrp_controller_name” — And here ———————————————–^

group by date aggregate function in postgresql

by

At the moment it is unclear what you want Postgres to return. You say it should order by persons.updated_at but you do not retrieve that field from the database. I think, what you want to do is: SELECT date(updated_at), count(updated_at) as total_count FROM “persons” WHERE (“persons”.”updated_at” BETWEEN ‘2012-10-17 00:00:00.000000’ AND ‘2012-11-07 12:25:04.082224’) GROUP BY date(updated_at) … Read more

Postregsql Date Difference on basis on seconds

by

First, the dates need to be values of timestamp type (so append ::timestamp if you’re just specifying them as string literals). If you subtract two timestamps, the result is of interval type, which describes a duration of time (in hours, minutes, seconds etc.) You can use extract(epoch from interval_value) to convert the interval into an … Read more

Grant permissions to user for any new tables created in postgresql

by

Found the answer. It is in this line in the ALTER DEFAULT PRIVILEGES documentation. You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. I was using alter default privileges from a different user than the one creating the tables. Make sure … Read more

Docker container shuts down giving ‘data directory has wrong ownership’ error when executed in windows 10

by

This is a documented problem with the Postgres Docker image on Windows [1][2][3][4]. Currently, there doesn’t appear to be a way to correctly mount Windows directories as volumes. You could instead use a persistent Docker volume, for example: db: image: postgres environment: – POSTGRES_USER=attendize – POSTGRES_PASSWORD=attendize – POSTGRES_DB=attendize ports: – “5433:5432” volumes: – pgdata:/var/lib/postgresql/data networks: … Read more

How do I know if my PostgreSQL server is using the “C” locale?

by

Currently some locale [docs] support can only be set at initdb time, but I think the one relevant to _pattern_ops can be modified via SET at runtime, LC_COLLATE. To see the set values you can use the SHOW command. For example: SHOW LC_COLLATE _pattern_ops indexes are useful in columns that use pattern matching constructs, like … Read more