If create_time is of type TEXT with valid date value, it’ll be easier to proceed with the change as follows (Be advised to first do a table dump as a backup): — Create a temporary TIMESTAMP column ALTER TABLE AB ADD COLUMN create_time_holder TIMESTAMP without time zone NULL; — Copy casted value over to the … Read more
Use alter table to add a new constraint: alter table foo add constraint check_positive check (the_column > 0); More details and examples are in the manual: http://www.postgresql.org/docs/current/static/sql-altertable.html#AEN70043 Edit Checking for specific values is done in the same way, by using an IN operator: alter table foo add constraint check_positive check (some_code in (‘A’,’B’));
Here’s a generic way to transform this update query from SQL-server form to PostgreSQL: UPDATE Users SET bUsrActive = false WHERE ctid IN ( SELECT u.ctid FROM Users u LEFT JOIN Users u2 ON u.sUsrClientCode = u2.sUsrClientCode AND u2.bUsrAdmin = 1 AND u2.bUsrActive = 1 WHERE u.bUsrAdmin = 0 AND u.bUsrActive = 1 AND u2.nkUsr … Read more
Use db.set_isolation_level(n), assuming db is your connection object. As Federico wrote here, the meaning of n is: 0 -> autocommit 1 -> read committed 2 -> serialized (but not officially supported by pg) 3 -> serialized As documented here, psycopg2.extensions gives you symbolic constants for the purpose: Setting transaction isolation levels ==================================== psycopg2 connection objects … Read more
Try this one: \dt schema_2.
SHOW datestyle; DateStyle ———– ISO, MDY (1 row) INSERT INTO container VALUES (’13/01/2010′); ERROR: date/time field value out of range: “13/01/2010” HINT: Perhaps you need a different “datestyle” setting. SET datestyle = “ISO, DMY”; SET INSERT INTO container VALUES (’13/01/2010′); INSERT 0 1 SET datestyle = default; SET http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE DateStyle – Sets the display format … Read more
An expression in an index declaration should be enclosed in additional brackets, try: CREATE INDEX event_creation_time_date_idx ON event ((creation_time::DATE));
You need to supply a format mask. In PostgreSQL there is no default: select to_char(1234, ‘FM9999′); If you don’t know how many digits there are, just estimate the maximum: select to_char(1234, ‘FM999999999999999999′); If the number has less digits, this won’t have any side effects. If you don’t need any formatting (like decimal point, thousands separator) … Read more
Here is a way to restore from a file located on the host machine: docker exec -i container_name pg_restore -U postgres_user -v -d database_name < /dir_backup_outside_container/file_name.tar
You could define a custom model field derived from models.CharField. This field could check for duplicate values, ignoring the case. Custom fields documentation is here http://docs.djangoproject.com/en/dev/howto/custom-model-fields/ Look at http://code.djangoproject.com/browser/django/trunk/django/db/models/fields/files.py for an example of how to create a custom field by subclassing an existing field. You could use the citext module of PostgreSQL https://www.postgresql.org/docs/current/static/citext.html If you … Read more