Create, update, delete, and query the available customers in Square.

Table Specific Information

Select

The driver uses the Square API to filter the results by the following columns and operators while the rest of the filter is executed client-side within the driver.

• Id supports the following operators: =, IN.
• Email supports the following operator: =.
• CreatedAt supports the following operators: =, >=, <=, >, <.
• UpdatedAt supports the following operators: =, >=, <=, >, <.
• ReferenceId supports the following operator: =.
• Segments supports the following operators: =, IN.
• CreationSource supports the following operators: =.
• GroupIds supports the following operators: =, IN.

Retrieve all customers:

SELECT * FROM Customers

Retrieve a specific customer:

SELECT * FROM Customers WHERE Id = '0SY145EEWD0YXCW489VV386KH8'

Retrieve several customers at once by supplying their Ids with IN. The driver sends these to the Square bulk retrieve endpoint, so the list is served by a single request rather than one request per Id. A list longer than the maximum BatchSize is split across as many requests as needed. Note that = does not use this endpoint; a single-Id filter is answered by the per-customer request instead.

SELECT * FROM Customers WHERE Id IN ('W4JF00NKDQ857310HB7NNN74T4', 'Z3G4NNA8WCFKM2R8T32XAHWT1M')

An Id that does not match a customer is omitted from the results rather than reported as an error, so the row count can be lower than the number of Ids supplied. Any other predicate in the WHERE clause is applied client-side, and LIMIT does not restrict a bulk retrieve, which always returns the customers named in the Id list.

Insert

To create a customer, you must specify at least one of the following values: GivenName, FamilyName, CompanyName, EmailAddress, or PhoneNumber.

INSERT INTO Customers (GivenName, FamilyName, Email, PhoneNumber, AddressLine1, AddressLine2, AddressLocality, AddressAdministrativeDistrictLevel1, AddressPostalCode, AddressCountry) VALUES ('John', 'Doe', '[email protected]', '1-212-555-4240', '500 Electric Ave', 'Suite 600', 'New York', 'NY', '10003', 'US')

Update

Modify the details of an existing customer by providing the Id of the customer:

UPDATE Customers SET GivenName = 'Amelia', FamilyName = 'Earhart' WHERE Id = '32123'

Delete

Delete a customer from a business, along with any linked cards on file, by providing the Id of the customer:

DELETE FROM Customers WHERE Id = '32123'

Bulk Insert

The maximum BatchSize supported by Customers is 100.

You can create customers in bulk using TEMP tables.

INSERT INTO Customers#TEMP (GivenName, FamilyName, Note, ReferenceId) VALUES ('BulkA', 'TestOne', 'bulk probe 1', 'ref-bulk-a')

INSERT INTO Customers#TEMP (GivenName, FamilyName, Note, ReferenceId) VALUES ('BulkB', 'TestTwo', 'bulk probe 2', 'ref-bulk-b')

INSERT INTO Customers (GivenName, FamilyName, Note, ReferenceId) SELECT GivenName, FamilyName, Note, ReferenceId FROM Customers#TEMP

Bulk Update

The maximum BatchSize supported by Customers is 100.

Customers can be updated in bulk using their Ids.

INSERT INTO Customers#TEMP (Id, GivenName, ReferenceId, Note) VALUES ('W4JF00NKDQ857310HB7NNN74T4', 'BulkAUpd', 'ref-upd-a', 'updated one')

INSERT INTO Customers#TEMP (Id, GivenName, ReferenceId, Note) VALUES ('Z3G4NNA8WCFKM2R8T32XAHWT1M', 'BulkBUpd', 'ref-upd-b', 'updated two')

UPDATE Customers (Id, GivenName, ReferenceId, Note) SELECT Id, GivenName, ReferenceId, Note FROM Customers#TEMP

Square reports the outcome of each row in the batch separately. Query LastResultInfo#TEMP after the update to read the per-row Success, AffectedRows, and Id values, along with ErrorStatusCode, ErrorMessage, and ErrorDescription for any row that failed.

SELECT * FROM LastResultInfo#TEMP

Bulk Delete

The maximum BatchSize supported by Customers is 100.

Stage the Ids of the customers to remove in a TEMP table, then delete them in a single request. Only the Id is needed on each staged row.

INSERT INTO Customers#TEMP (Id) VALUES ('Z3G4NNA8WCFKM2R8T32XAHWT1M')

INSERT INTO Customers#TEMP (Id) VALUES ('W4JF00NKDQ857310HB7NNN74T4')

DELETE FROM Customers WHERE EXISTS SELECT Id FROM Customers#TEMP

Note: The staged form shown above is required to reach the bulk delete endpoint. A DELETE FROM Customers WHERE Id IN (...) statement removes the same customers but issues one request per Id. The same applies to bulk UPDATE, which must also read its rows from the TEMP table.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	String	True	The Id of the customer.
GivenName	String	False	The customer's given (i.e., first) name.
FamilyName	String	False	The customer's family (i.e., last) name.
Nickname	String	False	The customer's nickname.
Email	String	False	The customer's email address.
CompanyName	String	False	The name of the customer's company.
PhoneNumber	String	False	The customer's phone number.
AddressLine1	String	False	The first line of the address.
AddressLine2	String	False	The second line of the address.
AddressLine3	String	False	The third line of the address.
AddressLocality	String	False	The city or town of the address.
AddressSubLocality	String	False	A civil region within the address's locality.
AddressSubLocality2	String	False	A civil region within the address's sublocality.
AddressSubLocality3	String	False	A civil region within the address's sublocality_2.
AddressAdministrativeDistrictLevel1	String	False	A civil entity within the address's country. In the US, this is the state.
AddressAdministrativeDistrictLevel2	String	False	A civil entity within the address's administrative_district_level_1. In the US, this is the county.
AddressAdministrativeDistrictLevel3	String	False	A civil entity within the address's administrative_district_level_2.
AddressPostalCode	String	False	The address's postal code.
AddressCountry	String	False	The address's country, in ISO 3166-1-alpha-2 format.
AddressFirstName	String	False	Optional first name when it's representing recipient.
AddressLastName	String	False	Optional last name when it's representing recipient.
Birthday	String	False	The birthday associated with the customer profile, in RFC 3339 format. The year is optional. Timezone and times are not allowed.
CreatedAt	Datetime	True	The time when the customer was created.
UpdatedAt	Datetime	True	The time when the customer was last updated.
ReferenceId	String	False	A second Id you can set to associate the customer with an entity in another system.
Note	String	False	A note to associate with the customer.
Preferences	String	True	The customer's preferences.
Segments	String	True	The Ids of segments the customer belongs to.
CreationSource	String	True	The method used to create the customer profile. The allowed values are OTHER, APPOINTMENTS, COUPON, DELETION_RECOVERY, DIRECTORY, EGIFTING, EMAIL_COLLECTION, FEEDBACK, IMPORT, INVOICES, LOYALTY, MARKETING, MERGE, ONLINE_STORE, INSTANT_PROFILE, TERMINAL, THIRD_PARTY, THIRD_PARTY_IMPORT, UNMERGE_RECOVERY.
GroupIds	String	True	The Ids of customer groups the customer belongs to.
Version	Int64	False	The version of the object. When updating an object, the version supplied must match the version in the database, otherwise the write will be rejected as conflicting.
TaxIds	String	False	The tax Id associated with the customer profile.