Tag Info

New answers tagged

-1

Try wrapping it inside another select. SELECT SUM(amount), contractor_id FROM ( SELECT SUM(extras.amount) as amount, appointments.contractor_id FROM extras INNER JOIN appointments ON extras.appt_id = appointments.appt_id WHERE appointments.time > CURRENT_TIMESTAMP - interval '8 days' AND appointments.time < CURRENT_TIMESTAMP AND ...


-1

seems to be god you can try WHERE appointments.time > CURRENT_TIMESTAMP - interval '8 days' AND appointments\ .time < CURRENT_TIMESTAMP AND appointments.confirmed = 1 AND (appointments.status = 10 OR appointments.status=12) after the group by like ... SELECT SUM(extras.amount), appointments.contractor_id FROM extras INNER JOIN appointments ON ...


7

A LANGUAGE sql function must be a complete SQL statement (or more than one). Yours is just an expression. Just prepend SELECT, like you would when running it stand-alone. $BODY$ SELECT (SELECT ... The reason it complains at the + is that, surprisingly, it's legal to parenthesise a top-level query. This, for example, is a valid query: regress=> ...


1

This part generating recovery.conf is suspicious: echo "restore_command = 'cp -f $PGDATA/pg_xlog/%f $PGDATA/pg_xlogRestored'" >>$PGDATA/recovery.conf It seems that the source and destination of this restore_command are reversed. Compare yours to the example from the doc in Continuous archiving: restore_command = 'cp /mnt/server/archivedir/%f %p' ...


-1

You are hitting the inherent scalability limits of Postgres (or any other RDBMS). Remember that an RDBMS index is a B-Tree. A B-Tree is O(log n) for both average and worst case. This makes it a nice, safe, predictable choice for reasonable values of N. It breaks down when N gets too big. NoSQL databases are (for the most part) hash tables. A hash ...


0

Possible workaround: we created a stored procedure for users that would accept a PID to be killed (eg: sp_kill 4221) and would write the requesting user name and PID into a table. That table was checked every 5 minutes by a script. If the PID requested belonged to the user who requested it and it was still active (ie, select pid, usename from ...


-1

For the folks, who are going crazy after changing password many times and trying everything mentioned here, remember to check user validity expiration. You can set it to 'never expire' like that in psql 9.x: ALTER ROLE <username> VALID UNTIL 'infinity';


1

The message: requested WAL segment 00000001000018F70000008A has already been removed Means that the master hasn't kept enough history to bring the standby back up to date. Since you are using version 9.1, you can use pg_basebackup to create a new slave. We use a command like: pg_basebackup -h masterhost -U postgres -D path --progress --verbose -c fast ...


2

If you have WAL archiving enabled on the master (archive_command is set and archive_mode is on), set a restore_command in your replica's recovery.conf to allow it to fetch WAL from the WAL archive. If there's no WAL archive, then there's no record of needed deltas between the master and the replica anymore. So you must resync them. Typically this is done ...


2

It sounds like you might be looking for pg_comparator. I haven't used it myself, but I've got some database check/compare work to do soon, and it's one of the options I need to look into as a starting point.


1

UPDATE appointments SET recurring = 0 WHERE id = 433 AND time = (select max(time) from appointments where id = 433); ... is the answer. You just needed brackets.


1

Let's try : CREATE OR REPLACE FUNCTION public.sp_get_user() RETURNS json LANGUAGE plpgsql AS $function$BEGIN INSERT INTO users (name, password) VALUES ('deadeye', 'test'); RETURN row_to_json(row) FROM (SELECT name, password FROM users WHERE name = 'deadeye') row; END;$function$ Now, let's test : SELECT sp_get_user(); ...


2

Yes. Everything you did inside the same transaction is visible to later commands inside the same transaction. Just not to other transactions until committed. This is true for all isolation levels except Read uncommitted where "dirty reads" are possible (but that does not affect your question per se). It's implemented with the MVCC model (Multiversion ...


1

Running a diff on a pg_dump from both databases is a very good way to find the differences. The output pg_dump produces is valid SQL and easy to read.


3

You half miss the connection between the two tables. The ...relid columns must match, too: SELECT attname, c.* FROM pg_attribute a JOIN pg_constraint c ON attrelid = conrelid -- this was missing AND attnum = ANY (conkey) WHERE attrelid = 'test_table'::regclass::oid;


2

Let's simply try it! I've created the table from your question, then did the following: INSERT INTO mytable SELECT json_object(ARRAY['myfield', i::text || 'bla'])::jsonb FROM generate_series (1, 100000) t(i); \di+ trgm_index_1 Schema │ Name │ Type │ Owner │ Table │ Size │ Description ...


1

All things considered, I would go with option 2. Dates will be evenly selected on, but I'm going to guess that for a given query only one or two date partitions will be involved. It's a shame you can't cluster on geolocation and partition on date, which would be ideal. Elevation tends to correlate with geolocation anyway, if the bounding boxes are ...


3

The pg_ctl status command tests that the postmaster process (corresponding to $PGDATA) does exist from the point of view of the operating system. In the positive case, it reports that it's running (note that the word available is not used). Essentially that's implemented by reading postmaster.pid and checking the result of kill(that_pid, 0). It does not ...


1

The problem had to do with a kernel feature called Transparent Huge Pages (THP.) You can diagnose this with perf top: 59.73% postmaster [kernel.kallsyms] [k] compaction_alloc 1.31% postmaster [kernel.kallsyms] [k] _spin_lock 0.94% postmaster [kernel.kallsyms] [k] __reset_isolation_suitable 0.78% postmaster ...


0

I'd suggest a key-value DBMS, but I throw this out there for interest's sake. Instead of performing INSERT & DELETE statements, only do UPDATEs. The table structure will be something like ID integer -- sequential ID Used boolean -- default FALSE Object -- whatever type is appropriate The object-holding column will be of fixed length to ...


5

except can be re-written as not exists query: select t1.id, t1.amount, t1.email, t1.date from t1 where not exists (select 1 from t2 where t2.amount = t1.amount and t2.email = t1.email and t2.date = t1.date); But your statement seems to indicate you do not actually want except but ...


1

This query should get only lines from t1 where amount, email and date match exactly with one line of t2. SELECT t1.ID, t1.amount, t1.email, t1.date FROM t1 INNER JOIN t2 ON t1.amount = t2.amount AND t1.email = t2.email AND t1.date = t2.date;


1

If you already have the PK value of a certain row in A, a simple SELECT is the fastest possible way: SELECT * FROM b WHERE a_id = ? If you need to retrieve a_id first, a join is your best options. Make that a LEFT [OUTER] JOIN only if there can be cases where there are no matching rows in B and you still want a_id in that case (or whatever, you didn't ...


4

The documentation says about INCLUDING CONSTRAINTS (emphasis mine): Not-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. Also, no distinction is made between column constraints and table constraints — when constraints are ...


0

I have only seen the above message when I set it up and there is a firewall or incorrectly specified the GTM port. I always recommend to first set up a simple local configuration, with 1 GTM, 1 Coordinator, and 2 Datanodes running on the same server. Once you get that working, try more complex architectures, it will be easier moving forward. Also, please ...


0

Doing an nmap over the EC2 instance: nmap -Pnv -p 5432 <public-ip> revealed that my ISP is intentionally blocking the port 5432(third-world country FTW...): Host is up. PORT STATE SERVICE 5432/tcp filtered postgresql It was never a problem with EC2 nor Postgresql in first place.


3

I would like to know if it is possible to share the structure and data between the two systems without duplicating and/or having to copy everything each time I load a new OS. Effectively, I would like all the data to be available to Ubuntu when it is created in Windows, and vice versa. You can't. Just not supported, not possible. No can do. PostgreSQL ...


2

To get all unique pairs of elements from an array of arbitrary length: WITH a(a) AS (SELECT '{A,B,C,D}'::text[]) -- provide array here , i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i) SELECT ARRAY[a[i1.i], a[i2.i]] AS pair FROM i i1 JOIN i i2 ON i2 > i1 , a; You can then join to the message table. Without knowing any ...


1

Query You UPDATE statement looks good except for one major problem. Fixed and re-formatted with some other minor improvements: UPDATE line_items li SET product_id = d.latest_product_id FROM products p JOIN vendors v ON v.id = p.vendor_id JOIN vendorgroups vg ON vg.id = v.vendorgroup_id JOIN ...


2

This is how I solved my problem. Upgrade Postgresql 8.4 to 9.4 in Centos 1. Yum Install PG9.4 2. wget http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm 3. yum install pgdg-redhat94-9.4-1.noarch.rpm 4. yum install postgresql94-server 5. service postgresql-9.4 initdb 6. chkconfig postgresql-9.4 on Backup Data 7. su ...


0

If performance is your primary concern, I suggest the second approach. No need to touch the parent User table. Use the serializable isolation level to address concurrency if the same user can have multiple sessions. CREATE TABLE Vote( userId int NOT NULL , contentId int NOT NULL , quantity int NOT NULL , CONSTRAINT PK_Vote PRIMARY KEY ...


3

As you have multiple rows inside the JSON column, you need a function that returns a set. This can be done using the json_to_recordset() function: select j.* from json_test, json_to_recordset(json_data) as j(name text, country text, hobby text, address text, sex text); Because this is an anonymous record type, you must explicitly define each column. ...


0

You can use pgAdmin to perform pg_dump operation and choose what exactly should be in backup file(tables, schemas). Moreover you can choose backup settings such as backup file format. It's very easy to use, if database is big You just wait until process will finish. To start backup after connecting to PostgreSQL just right click on database and choose ...


3

Just run pg_dump. The documentation says: pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). In turn, the state of the database that you see on the slave is also consistent. If ...


0

service postgresql92-postgresql status checks if the file postmaster.5432.pid instead of postmaster.pid. I'd recommend to use only pg_ctl as postgres and check with ps aux as root. Or use only service postgresql92-postgresql start/stop/status as root. Mixing the two methods would turn into conflicts.


2

A dump-restore cycle removes all bloat and dead tuples from your tables and restores with the minimum possible size - except if you have a fillfactor setting below 100 that reserves some wiggle-room per data page. Immediately after the migration, you get a lot more "extensions" (added pages at the physical end of the table (the file on disk). The ...


1

You're misunderstanding how REVOKE works. You can't REVOKE a right that was never GRANTed. It doesn't create a deny ACL (a negative right), it removes a granted right if it exist. You didn't GRANT rights to user1 on the schema; instead that user inherited them from the public role everyone's a member of. So you can't revoke it. You want to revoke the right ...


0

You managed to dump it? Using what? Perhaps a 3.23 version of mysqldump? If so, that is a text file that contains CREATEs and INSERTs. There is at least one incompatibility that it may have: TYPE=MyISAM -- In some version, TYPE was replaced by ENGINE. You say "can't get it into 4.0" -- Does that mean you have a 4.0 and the load fails? With what error? ...


0

The tutorial on that link is not complete. You should type apt-get install bdr-9.4 instead of apt-get install postgresql-bdr-9.4 After that everything is working fine.. I also lose some time to figure that out..


1

If I understand you requirements, this consists of selecting all the users that have a project in step 3, and using that list to select all the projects for just those users. Something like: SELECT user_groups.id , STRING_AGG( DISTINCT(projects.step_number), ', ') AS summary FROM user_groups g INNER JOIN ( SELECT users.user_group_id ...


0

You may try to move your WHERE clause in the LEFT JOIN : SELECT user_groups.id, STRING_AGG( DISTINCT(projects.step_number), ', ') AS summary FROM user_groups INNER JOIN users ON user_groups.id = users.user_group_id LEFT JOIN projects ON users.id = projects.user_id AND step_number = 3 GROUP BY user_group.id By doing this, you will not loose projects where ...


4

create table tree_data ( id integer primary key, code text, name text, parent_id integer, constraint fk_parent foreign key (parent_id) references tree_data(id) on delete set null ); SQLFiddle example: http://sqlfiddle.com/#!15/b9a62/1


3

When you create a foreign key in postgres, set ON DELETE SET NULL. ALTER TABLE public.tree_data ADD CONSTRAINT tree_data_id_fkey FOREIGN KEY (parent_id) REFERENCES tree_data(id) ON DELETE SET NULL;


3

Use an UPDATE statement. update student set total = social + math + science; But that column is totally useless. In general you should not store data in a database that can easily be derived from existing values. In your, adding the values of the three columns adds no (noticeable) overhead to a select statement, so there is no point in storing the ...


0

I suppose you have a problem with your channels table that may return more than one row per s.account_id. I propose this query : SELECT COUNT (DISTINCT s.account_id), COUNT (DISTINCT CONCAT(s.account_id, c.type)) AS installed_shopify FROM subscriptions s LEFT JOIN channels c ON s.account_id = c.account_id AND c.type ILIKE 'shopifychannel' WHERE ...


0

Have you set the environment variables to enable rasters ? As of PostGIS 2.1.3, out-of-db rasters and all raster drivers are disabled by default. In order to re-enable these, you need to set the following environment variables: POSTGIS_GDAL_ENABLED_DRIVERS and POSTGIS_ENABLE_OUTDB_RASTERS in the server environment. If you want to enable offline ...


0

You must either be the superuser or logged in as the same user who owns the session you wish to cancel. So connect as user myuser and you'll be able to pg_cancel_backend, or, if that doesn't respond, pg_terminate_backend.


3

1. Subquery expression You can fix it with parentheses like @a_horse commented: SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1)); But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system ...


2

That's a typical case of case misuse. When creating the view, notice that "objectifsBoutique" is written with a uppercase B plus double quotes. This implies that any future reference to this object must include the uppercase B. Yet in the error message « objectifsboutique » relation is not found it's a lowercase b so that name does not refer to the view, ...


2

According to the README included in the dmg file, postgres binaries are installed in '/usr/local/pgsql/bin/' You should add these lines in your ~/.bashrc : export PSQL_DIR=/usr/local/pgsql/bin/ export PATH="$PSQL_DIR:$PATH" Update your environment with 'source ~/.bashrc' or run a new shell and psql should be available.



Top 50 recent answers are included