ALTER SEQUENCE id_seq_player RENAME TO player_id_seq; is correct. You might want to add the schema name as well to ensure you are altering the correct one, but this should most likely work. If it timeouts, you might have another process that is locking your sequence. Is there a way for you to turn off all … Read more
UPDATED CREATE OR REPLACE FUNCTION jsonb_diff_val(val1 JSONB,val2 JSONB) RETURNS JSONB AS $$ DECLARE result JSONB; v RECORD; BEGIN result = val1; FOR v IN SELECT * FROM jsonb_each(val2) LOOP IF result @> jsonb_build_object(v.key,v.value) THEN result = result – v.key; ELSIF result ? v.key THEN CONTINUE; ELSE result = result || jsonb_build_object(v.key,’null’); END IF; END LOOP; … Read more
Basically, NULL values occupy 1 bit in the NULL bitmap. But it’s not that simple. The null bitmap (per row) is only allocated if at least one column in that row holds a NULL value. This can lead to a seemingly paradoxic effect in tables with 9 or more columns: assigning the first NULL value … Read more
You can dump a single table of data like so: $ pg_dump –no-acl –no-owner -h [host ip].compute-1.amazonaws.com -U [user name] -t [table name] –data-only [database name] > table.dump You can get all of the values needed with this: $ heroku pg:credentials:url [DATABASE] -a [app_name] Connection info string: “dbname=[database name] host=[host ip].compute-1.amazonaws.com port=5432 user=[user name] password=[password] … Read more
For a query like this, a partial index covering only unsynced rows would serve best. CREATE INDEX ON tbl (id) WHERE sync_done = FALSE; However, for a use case like this, other synchronization methods may be preferable to begin with: Have a look at LISTEN / NOTIFY. Or use a trigger in combination with dblink … Read more
Use jsonb_build_object. Notice the b for binary after json.
Rows are returned in an unspecified order, per sql specs, unless you add an order by clause. In Postgres, that means you’ll get rows in, basically, the order that live rows read on the disk. If you want a consistent order without needing to add an order by clause, create a view as suggested in … Read more
The trick is to make COPY send its output to stdout, then pipe the output through gzip: psql -c “COPY foo_table TO stdout DELIMITER ‘,’ CSV HEADER” \ | gzip > foo_table.csv.gz
Generally use TIMESTAMPTZ Here’s advice from David E. Wheeler, a Postgres expert, in a blog post whose title says it all:Always Use TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) If you are tracking actual moments, specific points on the timeline, use TIMESTAMP WITH TIME ZONE. One Exception: Partitioning Wheeler’s sole exception is when partitioning on timestamps, because … Read more
select nextval(‘mytable_seq’) from generate_series(1,3); generate_series is a function which returns many rows with sequential numbers, configured by it’s arguments. In above example, we don’t care about the value in each row, we just use generate_series as row generator. And for each row we can call nextval. In this case it returns 3 numbers (nextvals). You … Read more