Ezequiel Tolnay
  • Member for 7 years, 1 month
  • Last seen more than a week ago
pg_upgrade unrecognized configuration parameter "unix_socket_directory"
30 votes

I hacked the problem by running (as root): mv /usr/bin/pg_ctl{,-orig} echo '#!/bin/bash' > /usr/bin/pg_ctl echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' &...

View answer
Within a trigger is there a way to tell if an update or insert came from a trigger or not?
Accepted answer
2 votes

There are several ways for determining if the trigger is recursing. Checking the trigger depth (pg_trigger_depth()) is not one of them, because you don't know if the trigger itself is recursing or is ...

View answer
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL
2 votes

For checking of initials, I often use casting to "char" (with the double quotes). It's not portable, but very fast. Internally, it simply detoasts the text and returns the first character, and "char" ...

View answer
Scalable way to track who's viewed a post?
1 votes

Without knowledge of your schema, query attempted and statistics from explain analyze, any response can only deal in generics. In this sense and in terms of SQL, there are generally two commonly used ...

View answer
Migrating master databases between clusters by detaching/attaching them
2 votes

SQLServer can't work without a master database at all as it stores essential configuration information in it (such as the table of databases in the system), so it wouldn't allow you to detach it. ...

View answer
How to use RETURNS TABLE with an existing table in PostgreSQL?
3 votes

The different modes are interchangeable in some circumstances, but are different. What RETURNS TABLE is good for is described in the sentence you removed from your quote above: This is equivalent ...

View answer
Postgres 9.5 import to MSSQL Server 2012 r2 verification issue
0 votes

This is more readable and may perform better: SELECT date_part('year', month) as year, date_part('month', month) as month, count(*) as "trandate sum" FROM ( SELECT date_trunc('month', ...

View answer
How to create a Unique (Index) constraint across multiple tables?
0 votes

You could create a third table C, with a primary (or unique) key defined in it, and create before each row INSERT, UPDATE and DELETE triggers in both of your tables A and B that insert, update or ...

View answer
Remove accidental duplicates in PK table, cascade changes to FK table
1 votes

This solution requires first that there aren't any conflicting or cascading information in plant_name_mapping; to work, the following query must return no records: select * from plant_name_mapping o ...

View answer
How to copy part of a database with inconsistent privileges?
0 votes

You could perform the pg_dump with the parameters --no-owner and --no-acl, so no permissions or links to any users are presents in the dump.

View answer
Require JSON to have an element
Accepted answer
5 votes

You can do that with a column check: ALTER TABLE table ADD CHECK ((column->'id') is not null and (column->'name') is not null);

View answer
PostgreSQL not using index scan when a second ORDER BY column is present
3 votes

If you create an index on (row_time DESC, row_id DESC), then the second case will will act just as the first one. LIMIT will always operate after sorting is taken care of, so when sorting without an ...

View answer
Postgresql perl : "NOTICE: elseif should be elsif at line ..."
Accepted answer
1 votes

I can't reproduce your error, it works fine on my PostgreSQL 9.5 on Linux. What version and platform are you running? Note that your function can be exploited really badly, e.g. select ...

View answer
which time period covers date string over datetime column
Accepted answer
3 votes

A sargable exact alternative to DATE(ColumnName) = DATE'yyyy-mm-dd' is ColumnName >= TIMESTAMP'yyyy-mm-dd' AND ColumnName < TIMESTAMP'yyyy-mm-dd' + INTERVAL '1' DAY Something like ColumnName &...

View answer
Postgres inherit from one role and not another
0 votes

Any user (ROLE) can be a member of one or more user groups (which are also ROLES). According to the documentation for GRANT, "Any particular role will have the sum of privileges granted directly to ...

View answer
Best practice for multiple 0,1 to many relationships
0 votes

This will really depend on many factors, beginning with your choice of RDBMS, and followed by your application language and framework. The choice will be very different if you use some form of RAILS ...

View answer
Tricking SQL server into thinking it's a different date
6 votes

You could replace all instances of GETDATE (and equivalents) with your own function (e.g. ACMEGETDATE if your company's name is ACME), and have this function return either GETDATE straight, or when ...

View answer
Is it possible to set PostgreSQL roles with privileges this way?
2 votes

What is possible is what's specified in the documentation for GRANT (http://www.postgresql.org/docs/current/static/sql-grant.html). To make modifications on tables, a user must own the object. When a ...

View answer
Can I grant privileges to a Postgres user on all schemas belonging to a database with one command or script?
1 votes

Yes, using DO, cursors, execute, and the pg_namespace table: DO $$DECLARE _schema record; _user name := '<your_username>' BEGIN FOR _schema IN SELECT * FROM pg_namespace WHERE nspowner &...

View answer
Designing database containing code metadata
0 votes

"Millions of columns"? Or millions of rows? For single user applications and just a few megabytes of data, sqlite can be perfectly sufficient, so it will really depend on how many lines of code/rows ...

View answer
Compare two sets with a quantity field
Accepted answer
0 votes

You could create a query that first lists or groups elements as in your first example. Then select on that (as a subquery) grouping by basket and crating a string per basket through GROUP_CONCAT(). ...

View answer
Database in postgresql with missing rows, how to fill the missing rows with a backup copy?
Accepted answer
6 votes

You can restore all affected tables into a different schema, there are a variety of ways in which you can do that (several good ideas in https://stackoverflow.com/questions/4191653/i-want-to-restore-...

View answer
Insert data into oracle table with loop?
0 votes

This is only judging from the very little information you provide: SELECT h.MO_ID, h.LEVEL, l.SEQ_NO, l.QTY, h.SERIAL_NO, h.Counter, ROW_NUMBER() OVER (PARTITION BY h.MO_ID, h.SERIAL_NO ORDER ...

View answer
Count approximation with where clause
Accepted answer
0 votes

How many unique values do you have for crawled_name? How feasible it is that you'll hit an apartment_id value greater than 2 billion (in the next 5-10 years)? Note that COUNT(id) is the same as COUNT(...

View answer
Combine individual sensor values into an overall value with correction factors
Accepted answer
1 votes

You could do the following, provided that your sensor 6511 takes measurements reliably every 30 minutes (use instead the one that is most reliable). This will calculate measurements based on the ...

View answer
how do I change regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm') to only read 132 characters each time
Accepted answer
1 votes

This should work: with w(id, line#, line, rest) as ( select id, 1, SUBSTR(regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'), 1, 132), substr(to_char(bar), LEAST(132, regexp_instr(bar, '$', ...

View answer
SQL Server to MySQL migration, replace '\xA0' with a regular space
1 votes

Maybe you can use CHARINDEX casting your Address1 to varbinary(8000) to identify the offending data: select * from [ForgeDB].[Snapshot].[SnapshotPersonEmployee] where charindex(0xA0, cast(Address1 ...

View answer
Mysql Order , Select highest value item and update to another table
1 votes

In this case you can use INSERT INTO ... SELECT FROM ... ORDER BY DESC LIMIT 1: INSERT INTO data_2 (name, text1, text2) SELECT name, serial, CAST(point AS varchar(10)) FROM data_1 ORDER BY point DESC ...

View answer
Postgres LEFT JOIN with recurring dates and users
Accepted answer
3 votes

You have to do with users the same thing you're doing with days: WITH days AS ( SELECT generate_series(current_date-7, current_date, '1d')::date AS day ), eves AS ( SELECT user_id, ...

View answer
Conditional relationships? One-to-many relationship using a pivot table for only a type of entity
Accepted answer
0 votes

In most RDBMSs you'll have the ability to set-up column conditionals, which are useful for cases like this. Say your user types are 1, 2 and 3 (res, admin, super respectively). You can set users this ...

View answer
1
2 3 4 5 6