PostgreSQL PL/pgSQL random value from array of values
Try this one: select (array[‘Yes’, ‘No’, ‘Maybe’])[floor(random() * 3 + 1)];
postgresql-9.2
Permission denied for relation in PostgreSQL
GRANT on the database is not what you need. Grant on the tables directly. Granting privileges on the database mostly is used to grant or revoke connect privileges. This allows you to specify who may do stuff in the database if they have sufficient other permissions. You want instead: GRANT ALL PRIVILEGES ON TABLE side_adzone … Read more
If I drop a PG table with an index, does the index still exist?
Drop table will delete all related constraints, & index, no need for you to do any kind of cleanup. Postgres will handle that.
Find and delete duplicate rows with PostgreSQL
Here is my take on it. select * from ( SELECT id, ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row FROM Photos ) dups where dups.Row > 1 Feel free to play with the order by to tailor the records you want to delete to your specification. SQL Fiddle => http://sqlfiddle.com/#!15/d6941/1/0 SQL … Read more
ROWID equivalent in postgres 9.2
Yes, there is ctid column which is equivalent for rowid. But is useless for you. Rowid and ctid are physical row/tuple identifiers => can change after rebuild/vacuum. See: Chapter 5. Data Definition > 5.4. System Columns
PostgreSQL cannot begin/end transactions in PL/pgSQL
A plpgsql function automatically runs inside a transaction. It all succeeds or it all fails. The manual: Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. However, a block containing … Read more
Creating partial unique index with sqlalchemy on Postgres
class ScheduledPayment(Base): id = Column(Integer, primary_key=True) invoice_id = Column(Integer) is_canceled = Column(Boolean, default=False) __table_args__ = ( Index(‘only_one_active_invoice’, invoice_id, is_canceled, unique=True, postgresql_where=(~is_canceled)), )
How to create sequence if not exists
Postgres 9.5 or later IF NOT EXISTS was added to CREATE SEQUENCE in Postgres 9.5. That’s the simple solution now: CREATE SEQUENCE IF NOT EXISTS myschema.myseq; But consider details of the outdated answer anyway … And you know about serial or IDENTITY columns, right? Auto increment table column Postgres 9.4 or older Sequences share the … Read more
Dropping column in Postgres on a large dataset
ALTER TABLE DROP COLUMN does just only disabling columns in system tables. It is very fast, but it doesn’t remove data from heap files. You have to do VACUUM FULL later to compact allocated file space. So ALTER TABLE DROP COLUMN is very fast. And to compact files, you have to call the slower (with … Read more
PostgreSQL 9.2 – Convert TEXT json string to type json/hstore
SELECT cast(settings AS json) from users; EDIT 7 years later I highly suggest that you don’t use unstructured columns unless your data is unstructured. RDBMS go a very long way. We built a fairly large platform and used user settings as a json column, and it endedup becoming a junk drawer which needed to be … Read more